Tag Archives: Excel

Mini Charts: New method of visualizing data

The need

Visual data analysis usually means charts.  There are some limitations of using charts.

  • In some cases, traditional charts don’t work well. Primary and secondary Y axis allows two different scales of data to be shown visually. But more than three scales are impossible to plot.
  • Chart is a separate layer and needs to handled separately.
  • Charts occupy lot of space because there are so many elements to be shown like gridlines, axes, labels and so on.
  • If you try to reduce the chart size, the pattern gets flattened and difficult to interpret.

Visualize data with Mini Charts (Excel 2010 or above)

Fortunately, there is a new way of data visualization available in Excel (since Version 2010) – Mini charts.  Sparkline is a type of mini chart that is drawn in a single cell based upon data from multiple cells. Select one or more blank cells. Choose Insert Sparklines – Line. Select the input data. Click OK.

image

image

image

image

You can apply this to multiple rows or columns. Make sure you select the data correctly.

If you use this in a table, when more data is added to the table, new Sparkline will be automatically added. You can also increase the Sparkline by dragging it from the corner – just like formulas.

Powerful customization possible

When you Click inside the Sparkline, the Sparkline Tools tab appears. Make sure you go through EVERY option of this menu. You can then customize the visualization for your data quickly.

SNAGHTMLac5032a

To show fluctuation across time? Use Line Sparkline

To show relative size of the data, use Bar Sparkline

Mini charts - Bar Sparkline

This diagram uses Line for monthly fluctuation and Bar for country specific values.

To show positive vs negative use Win/Loss

Mini charts - Win Loss

The last column is Win/Loss type of chart. It only depicts positive or negative outcome (Profit / Loss or Pass / Fail or Select / Reject). The line chart shows the fluctuation. In order to visually understand negative values, the Show Axis option puts a reference line at the zero position.

Mark the max or min values for easier interpretation

Mini charts - Mark Min or Max value

In case of bar style, the minimum value is always shown as a thin line. Negative minimum values look better in bars.

In the next article, we will explore some interesting practical scenarios for using Sparklines.

Excel data cleanup with Flash Fill

Problem

One of the participants (Mr. Amjad) in a seminar I conducted asked me this question. The scenario is common enough. But the solution is not even noticed – it is called Flash Fill. Instead, we struggle to solve it manually (and inefficiently).

There is data in one column which contains name followed by amount. We want to split the amount.

flash fill

Doing it using a formula is tricky. Because in some cases there are two spaces, in some cases there is only one space. And may be if the data is large, there could be three spaces as well.

Easy solution: Excel 2013 – Flash Fill

This is an amazing new feature. I will cover it in detail in a separate article. However, here is the way it works.

In column E, specify what you want. Just type the value. NO formula.

flash fill

Then choose Data tab – Flash Fill

flash fill

Do the same thing for the name in column F. That’s it. Excel has an amazing pattern matching engine which does the job for you.

flash fill

What if you don’t have Excel 2013? Use Word!

Then you will have to resort to complex formulas or VBA. But not really. We often get too focused on the product and forget that Office has other tools as well.

Remember. Excel is great with Numbers and Dates. But not text.

Who is the text expert? Word Logo

In fact what Excel 2013 Flash Fill does is technically called pattern matching (Regular Experssions). But Word had this feature since many years.

Let us see how to solve complex Excel data handling problems in Word.

  1. Copy the column data and paste it in Word
  2. It will be pasted as a table. But the table borders will not be visible.
  3. Click inside the pasted data, you will see the Table Tools tab
  4. Click the Layout tab and choose View Gridlines
    Now you can clearly see the tableimage
  5. We want to select only the numbers
  6. Press Ctrl H to open the Find – Replace Dialog
  7. We don’t want to replace anything so click the Find tab
  8. In the Find textbox, type this exactly as shown here [0-9]{1,}
  9. Click the More>> button and choose Use Wildcards optionimage
  10. Now try Find Next multiple times. Notice that it is selecting all the numbers one by one
  11. But we want to select all numbers. So open the Find In dropdown and choose Main Document
  12. Now all the numbers will be selected. Close the Find dialog.image
  13. Press CTRL X to cut the numbers and put them in clipboard
  14. Only the names are left in the first column.
  15. Now add a new column.
  16. Select the second column
  17. Press CTRL V to paste the numbers
  18. Now select the whole table and paste it into Excel
  19. Job done!

For the sake of completeness, the names column will have an extra space at the end. Add a new column and use the TRIM function to remove the trailing spaces if required.

Sample Word file for practice Download

How did this work?

The magic happened in that complex looking search criteria

[0-9]{1,}

Wildcard option means do not search what is typed literally. Try to interpret it.

[0-9] Means search for any number between the given range. But that would select only one digit.

{1,} Means that Word should search for one or more instances of number. That is how it selects the entire number

Want to know more?

Click in the Find textbox in Word, enable Wildcards and click the Special button.

You will see the entire syntax of what is possible. This is infinitely powerful and easy to use once you understand the concept. Try it out.

Character in Range

 

9 benefits of using Excel Tables

If I have to teach ONE thing to the world, it would be Excel Tables.

Excel tables

I recently created this video for a Microsoft Webcast.
Excel Tables were introduced in Office 2007.
This video explains the importance of using Excel Tables.

Prevent Wrong Decisions using Excel Tables

Outdated formulas in Excel are a big operational risk

Tables eliminate that risk proactively.

Additional Benefits of Excel Tables

This in itself can become a series. But here is a short description of the benefits

Auto-update of formulas and references

All dependent formulas are auto-updated across sheets. If you have a linked file, the file must be open for the auto-update to work.

Automatic formatting of tabular data

If you have manual formatting applied, the Table formatting cannot override it. To override, right click on the desired format and choose Apply and Clear Formatting option.

Automatic copying of calculated column formulas

Any formula added in new columns in the table is auto-copied across the table. The formula is copied all the way to the end of the table even if the left column has empty cells.

This saves you the trouble of using time consuming DRAG or error prone DOUBLE CLICK for copying formulas.

If you don’t like this behavior then use the SmartTag to UNDO the auto-copy action.

Automatic copying of calculations when more data is added

This is very useful. If more data is added, calculated columns are auto-filled.

To take advantage of this feature, make sure that you add calculated columns at the end of the raw data. This way, when you get more raw data, you can simply paste it at the bottom of the Table.

Automatic display of headings

Headings are shown in place of regular column headings. It also offers Auto-Filter dropdown (2010 onwards). This eliminates the need for Split and Freeze Pane.

Automatic copying of formatting, conditional formatting and validations

All attributes of cells are auto-copied when you add more rows in a Table.

Ability to use Table and Column names in formulas

Ability to integrate with SharePoint and Visio

You can upload an Excel table to a SharePoint site. It automatically creates a list, creates relevant columns, uploads the data to SharePoint and finally creates a one way (SharePoint to Excel) link with the data.

In Visio you can create a visualization based upon a Visio based Pivot Table functionality.

Top 3 Efficiency Habits: Use Word Styles, Excel Tables and PowerPoint SmartArt

Office has thousands of features. And contrary to popular belief, all of them are useful to everyone under certain circumstances. Learning these things is not complex but time consuming.

My approach is to help you learn these things in a simple and intuitive manner. In future articles I will show you some easy methods of learning Office while you work.

But let us start with three very important things you must know.

No negotiation. No option. Absolutely Mandatory. Trust me on this!

Word Styles, Excel Tables and PowerPoint SmartArt.

I would like to know if you already use these features. Please vote. Just three clicks!

Word Styles

Problem

We spend too much time on formatting and too little on the core content.

Solution: Let Word handle the formatting

Don’t do formatting manually. Word is better than you. That is why it is called a word-processor. Let Word handle the formatting.

Most common formatting is to show titles and subtitles in a document. Word has already created formatting for up to 9 levels of topics (headings). Just use it.

How? By choosing the appropriate style.

Styles are just ready to use formatting which is created by graphics experts. There are 9 styles – Heading 1 to Heading 9

While creating a document, you know which of the paragraphs are Headings or Topics or Titles. As soon as you type the paragraph, choose the style.

word styles, excel tables and powerpoint smartart

Main topics get H1, sub topics get H2 and so on.

How to select styles quickly

  1. Click the style in the Home tab – Styles drop down (it is called a Gallery).
  2. Or use keyboard shortcuts CTRL ALT 1 for H1 CTRL ALT 2 for H2 and
    CTRL ALT 3 for H3
  3. Or use the shortcut Shift Alt Up arrow to apply Higher styles, Shift Alt Down arrow to apply lower styles

Benefits

  1. Automatic table of contents
  2. Live navigation pane
  3. Ability to create PowerPoint presentation automatically
  4. Automatic numbering
  5. Ability to rearrange the document by just drag drop of headings
  6. Expand / Collapse headings to make long documents easier to manage (2013 only)
  7. and many more

We will cover these benefits in the upcoming articles.

Excel Tables

Problem

Excel is a very dangerous piece of software. We use complex Excel files to take critical decisions – personally as well as at organizational level.

All of us know that Excel contains two primary things. Data and Formulas (Charts are visual formulas, Pivot Tables are automated formulas)

When you add a formula Excel calculates the results. Usually we do not cross check the results for every formula. Which is very dangerous.

Why? Because, when you add more data, you must update all the related formulas. Often we forget to do that. Due to this, the formulas may be outdated but you or others may not notice that. If you trust a formula which is showing outdated results, your decisions will be wrong.

Solution? Excel Tables

I will explain it in the upcoming articles. But to cut a long story short, Excel tables automate the process of updating formulas when you add more data.

If there is an outdated result, Excel shows an error marker (Green mark on top left corner). Unfortunately, most people in the world have never bothered to find out the meaning of that poor little green mark. We simply ignore it and take the risk of looking at wrong information.

Excel tables

Benefits

  1. The primary benefit is auto-update of formulas and references
  2. Automatic formatting of tabular data
  3. Automatic copying of calculated column formulas
  4. Automatic copying of calculations when more data is added
  5. Automatic display of headings
  6. Automatic copying of formatting, conditional formatting and validations
  7. Ability to use Table and Column names in formulas
  8. Ability to integrate with SharePoint and Visio

PowerPoint SmartArt

The purpose of PowerPoint is to present your case to the audience to achieve the desired impact. The impact could be sales, training, convincing, debating, explaining, and so on.

Problem

Most presentations are boring and uninteresting. If your audience loses interest in what you are showing or if they cannot understand it clearly, your objective will not be achieved.

There are thousands of ways of making better presentations. But one of them stands out.

Solution: SmartArt

That is called SmartArt. It is a very quick way of converting text (bullets) to appealing and high-impact visuals (diagrams). PowerPoint offers around 130 diagrams – intelligently created to help us depict various type of information (Lists, Processes, Hierarchy, Relationships and so on).

Creating a SmartArt Diagram is very easy. But choosing the right one in the right place does take some amount of practice.

SmartArt

Benefits

  1. Easy to create visually appealing diagrams
  2. Automatic formatting and resizing
  3. Eliminates the need for manual alignment, formatting and animation
  4. Automatic diagram creation from multiple pictures of different sizes

PowerPoint SmartArts

We will cover SmartArt in detail in upcoming articles.

Copy Paste – Part 8 – How to open a collateral file with Insert Action

The Location

Writing this blog sitting under this beautiful Auckland Sky Tower… Lovely weather.

image

The need

  • Let us say you are presenting  sales performance by month
  • In a particular month, the sales is very low
  • Someone asks you to show details of the transactions

image

  • You have to now open the original Excel file which contains the raw data.image
  • How do you do it?
  • You have to stop the presentation, open Explorer and figure out where the file is
  • Many things are against you right now:
  • You are under stress… You may not find the file or find an outdated version
    Worse still, you may open some unwanted folder

Common mistake: Stuffing raw data into PowerPoint

We want to avoid such trouble during a presentation. Therefore, we try to solve the problem by trying to copy pasting the raw data into PowerPoint. Unfortunately, large amount of data cannot fit into the limited space available on the slide.

This is not really a limitation of PowerPoint. It is just that you are using the wrong approach. Here is the correct way…

The solution: Be Prepared

When you suspect that someone may question you on your summary data, you need to be prepared. How do you do that? You already know.

First step is to get the context of the original file into the slide.
Use Paste Link, Embed or Insert Object

In either case, WHILE EDITING the presentation, you can write click on the pasted object and open the Excel file. What we really want is to have the same ability DURING THE PRESENTATION.

To understand how it is done, we need a small detour.

PowerPoint Presentation = Trigger + Action

I will cover this in detail in another article. But here is the shorter version.

A presentation contains slides. Each slide has various shapes and objects on it. When you run the presentation – PowerPoint shows the first slide and waits. When you click it goes to next slide … again waits … then you click – next slide … and so on till you end the presentation.

Is this a problem in word as well?

The CLICK is called the TRIGGER and MOVING TO NEXT SLIDE is the ACTION. In our case when we click on the slide containing the summary data, it simply goes to the next slide. If you right click, the menu now shows presentation related controls. The open worksheet command is missing.

The Solution: Insert Action

This is the time we change the default behavior of PowerPoint and ask it perform a DIFFERENT ACTION when we click on the Summary object. How to do that?

  1. Click on the Excel object (it could be Paste Link or Embedded or Inserted Object icon)
  2. Open Insert tab on the ribbon and choose ACTION
  3. As you can see the default action is DO NOTHING (which means do nothing special – do the default action – which is going to the next slide)
  4. Choose Object action and select OPEN
  5. Click Ok

Powerpoint Insert Action

Now run the presentation and see what happens. On this slide, if you click anywhere outside the Excel data, it will just go the new slide as expected.

However when you move the mouse cursor over the Excel data, the cursor shape changes to indicate that it is a hyperlink. Click on it to open the Excel file.

image

Show the details and then press ALT TAB to come back to the presentation. It is still running undisturbed.

Next article: PowerPoint and Psychology

Although this method works with inserted objects, there is a practical problem there. In fact there is a psychological problem.

What is the problem?

Inserted Object is shown as an icon. Therefore, your audience knows that you have a collateral file. So even if they have no reason to trouble you, they may just ask you to show the file. You don’t want that to happen. Why ask for unnecessary trouble?

We will handle that interesting situation in the next article.

Copy Paste – Part 7 – How to Insert Object

Please read the Intro post and the Embedding post before reading this article.

Insert Object embeds an existing file or new file into your document. In that sense it is like Embedding – which we discussed in the previous post. So what is the difference?

The starting point is different. In case of Embedding we first open the source document, highlight some portion of it and then paste it into destination while choosing EMBED option.

In case of Insert Object, we first go to the destination and then choose the ENTIRE FILE to be embedded. Therefore, we do not get any control over which part of the inserted file will be shown after it is added to the destination.

How to Insert Object

If you choose an existing Excel file and Insert it into PowerPoint it tries to show the contents of the Excel file in PowerPoint. Excel file can contain many worksheets. Which worksheet to show? PowerPoint does not know that because we never mentioned that – we just selected the file to insert. Therefore, PowerPoint shows you the contents of the sheet which was active when the Excel file was closed the last time.

If this sheet happens to have large amount of data, the Insert Object command may take a very long time to complete and the results will be very deplorable.

Insert Object

The solution

The solution is simple. We just need to specify that there is no need to show anything from the inserted file. Just show it as an icon. That option is shown in the Insert Object dialog itself :

Insert Object - Display as Icon

Now the Inserted file looks like this.

image

It is a good idea to change that default description to something more business context specific. Click on change icon and change the title. Also note that there are many icons available. Choose the one which suits the content type.

Change display icon

File size

File size does increase as much as the original size of the file. While embedding files, make sure you are choosing the new file formats. If you embed older formats (XLS, PPT, DOC), the file size will be much larger.

Note that you can use any file in the Insert Object dialog. It need not be Office document. It could be any type of document.

Resizing the embedded Icon

The default size of the icon is very small. If required, you must manually increase the size of the icon to make it visible during the presentation.

Remember, if you make the icon visible enough, you are also taking a risk. Anyone from audience may ask you to show the contents of the file and then you must do so.

In fact that is the topic of our next article:

Next Post: Problem and Solution

You are presenting some summarized data – say – sales performance by month

  • In a particular month, the sales is very low
  • Someone asks you to show details of the transactions
  • You have to now open the original Excel file
  • You have to stop the presentation, open Explorer and figure out where the file is
  • You are under stress
  • You may not find the file, find an outdated version, open some unwanted folder… all sorts of things can and will go wrong

Do you want an easier solution?

Now that we know Paste Link, Embed and Insert Object, there is an easy solution. If you think you may have to show details during the presentation, use one of these approaches to make sure that you have the file either linked or embedded.

Now the question is – how to tell PowerPoint to open the file – ON DEMAND?

That is what we will discuss in the next article.

Copy Paste – Part 6 – How to embed a file in Powerpoint

In the previous post, we saw how linking of files works. Now let us understand what Embedding means.  We will continue with the same scenario of embedding an Excel File in PowerPoint.  However, this concept would work across all Office applications.

You have copied some data from Excel. Now you paste it into PowerPoint and choose the EMBED option.

Copy Paste - How to embed a file in PowerPoint

It is still showing the Excel range you copied. It behaves like a picture if you try to resize it. But it actually made a copy of the ENTIRE EXCEL FILE and stuffed it into your PowerPoint presentation.

If the original Excel file was, say, 20 MB in size, the presentation size will also grow by 20 MB.

When to Embed?

If you want to package the Excel file with PowerPoint presentation, then this is the best option. The benefit is, you can still show specific summary data on the slide.

If you want to send the presentation with collaterals – this is the best way.

How to open the embedded file?

While editing the presentation, just right click on the object – Choose Worksheet Object and Select Open.

Open an embedded file

DO NOT choose EDIT. If you do, it tries to give you Excel editing capabilities in the small area in which you have pasted the data. The ribbon automatically changes to Excel options rather than PowerPoint options. This is called in-place editing. But most people get confused with it. If you want to expand the range of selection, this is a good option to use.

In either case, the Excel file which it opens is NOT the original file. It will have a name like worksheet in Presentation3. This file now lives inside the presentation (in geek language, it is called OLE File System).

Removing the embedded file

For any reason, if you feel that you should not have embedded the file, how to manage the situation? I am sure you will know this answer if you have read all the Copy Paste Series posts.

Copy the Embedded object, Paste it as Picture and delete the original embedded object!

Next post

We will explore the Insert Object feature in the next post. It is similar to Embed but with a small difference. In fact Insert Object and Embed are interchangeable!

Copy Paste – Part 5 – Paste Link and Embedding

Paste Link and Embedding are extremely useful. But most of us have not fully understood it. I am going to split this post in to multiple parts because I want to keep individual post short.

The need

Let me list down common requirements.

  1. Eliminate repetitive manual copy paste from the same Excel file
  2. Show summary from Excel and attach an Excel file as a collateral and send it along with the presentation
  3. Just attach an Excel file to a presentation as a collateral – without showing any content from it.

These three requirements translate into three different methods of Copy Pasting from Excel to PowerPoint. Paste Link, Paste Embed and Insert Object.

All these Copy Paste methods work between all Office products. Excel to PowerPoint is just an example scenario.

Eliminate repetitive copy paste with Paste Link

We often have summarized version of data in a worksheet. For example, you are showing YTD results (cumulative) across the year. The data is updated every month. You need to copy paste the current month status into PowerPoint for monthly review presentation.

Every month you copy paste SAME range in the SAME worksheet in the SAME Excel file.

In this case you can save yourself the trouble by linking the Excel data to PowerPoint. Thereafter, PowerPoint will keep the data updated automatically.

Here are the steps:

  1. Copy from Excel as usual
  2. Choose Paste Special in PowerPoint (Alt E S or Ctrl Alt V)
  3. Choose Paste Link
  4. Choose the first option and click OKCopy Paste - Paste Link
  5. Save the Presentation
  6. Now make some changes to the data in Excel
  7. Come back to the presentation. Notice that the changes are automatically shown.
  8. Changes to data as well as formatting are automatically reflected.
  9. This is called Paste Link
  10. If you right click on the linked item, you can actually see this menuimage
  11. Using the Open option you can even open the related Excel file from right within PowerPoint

Real-life scenario

In the above example, both files were open at the same time. However, in real life, you will usually edit the Excel data across the month and at that point of time, you presentation is not going to be open.

Similarly, when you open the presentation to prepare for the end of the month review, the Excel file is unlikely to be open.

But you don’t have to worry. The PowerPoint presentation remembers that there is a link and it manages that link for you. Whenever you open the presentation, it will ask you if you want to update the link. If you choose Continue, it will find the Excel file and open it (behind the scenes) and update the data.

image

This dialog may look a little different depending upon your settings and version of Office.

Link information

In the File – Info page of PowerPoint, you can view and edit the links.

Powerpoint Edit Link

Links are not good for others – break them!

Who benefits from the linking? You – the creator.

If you send this file to some other person, they don’t really need this automated linkage. They are unlikely to have access to your original Excel file. When they open the file, the update dialog will still appear – confusing them unnecessarily.

Therefore, when you send the presentation to others, it is better to break the links.

But wait! Don’t remove links from the original file. Make a copy first.

Go to File – Info – Edit Links, Select the links and choose Break Link.

Now the linked items just become pictures.

Remember, linking a file does not increase the size of the presentation.

The disadvantage

The PowerPoint file has information about the linked Excel file. But the Excel file has no clue that there is a presentation linked to it.

Therefore, if you rename or move the Excel file, PowerPoint update may fail. If you know the new name or location of the file, you can choose it again and the link will be re-established.

In the next article, we will see how to Embed a file.

Copy paste – Part 4 – XL to PPT – Edit after Paste (Step by step)

In the last  post, we saw the video. It was a fast paced video. In this post, I will explain the same thing in a step by step manner – with some additional details.

The objective is to copy from Excel, Paste it in PowerPoint, Edit it in PowerPoint and then display it properly (in adequately large size) on the slide.

The concept is simple. Pasting data can be done in many formats. Table format allows editing, and Picture format allows easy resizing of the image. We have to use both these formats in this case.

Steps

  1. Copy the data from Excel as usual
  2. Paste it in PowerPoint and choose – Keep Source Formatting option
    Edit after paste
  3. The data looks very small in PowerPoint and you cant edit it.
    Why so? Because in Excel we usually use a higher level of ZOOM and in PowerPoint we have a lower level of ZOOM. While copy pasting the ZOOM level is NOT copied. That is why it looks so small and unreadable.
  4. This paste is in Table format (Look at the top menus and notice Table Tools tab)
  5. Zoom in to the table by using CTRL and Mouse wheel
  6. Edit the content and zoom out
  7. Run the slide show to notice that the size of the data is still small
  8. We need to increase the size of the data. However, it is still a table. and it will not resize properly
  9. Now we need a picture.
  10. That is simple. Copy this edited table first.
  11. Paste it and choose Picture format
  12. Now you can resize it (remember CTRL and drag from corner)
  13. That’s it. We got best of both worlds

In case you want to edit the data again, you cannot use the picture. Delete the picture. The edited table is still available there. Edit it and repeat the process.

Remember, Copy Paste is about answering two questions:
Where to Paste ? and How to Paste?

If you answer these questions correctly, Office will give you the desired results immediately. If you do not provide clear instructions while copy pasting, Office uses default behavior which may not be what you intended.

In the next article we will see when to use Paste Link, Embed and Insert Object.

If you have time, view the video in the previous post and tell me which method you prefer. Video (short and crisp) or step by step instructions (takes longer to read).

Copy Paste – Part 3 – Excel to PPT – Edit after Paste

Please read Part 1 and Part 2 before reading this post.

As we saw earlier, default paste from Excel to PowerPoint is a Table. What if you want to edit the content after paste? Of course, Table can be edited but not resized easily.

Here is the solution … view this 44 sec video. The video is very fast paced. But just remember the context. Then you will understand it easily. Rerun, pause and try it out if required.

Copy from Excel, Paste in PowerPoint, Edit and enlarge the image

Was it too fast? Would you prefer slow video or step by step instructions as screenshots?

Let me know your feedback. The next post will be step by step instructions with screenshots for this video.

Try it out. If you find it useful, share it with the world.