Category Archives: Excel

Working with Excel data? Learn Power BI

The need

Office is a funny product. Everyone thinks they know it. What is the definition of “Knowing” Office? = “I know how to get my work done”

Unfortunately, even if you get the work done, it may not be in the most efficient way. Data handling in Excel is probably the single most important drain on human effort everyday.

Billions of people who have the false sense of knowledge use the same old, bad, slow, contorted methods of working with text files, exported reports and spend their precious life cleaning up data, removing unwanted headers, unpivoting data manually, copying and pasting… it is a global pity.

The primary problem? Once you found ONE method which works, you never attempted to find out if there is another, better method available.

Over time Microsoft has been adding many new ways to simplify things and eliminate manual work.

But now, there is a revolution happening. Power BI tools from Microsoft.

All that I am talking about here requires Excel 2013 Professional edition. But don’t worry. You can actually sign up for a trial version for free. See below.

Why do we need it?

The way we work with data has not changed at the core.

image

Ideally we should be spending maximum time in the analyze phase – because that is where we learn from what happened. That knowledge will help us in improving the future.

Unfortunately, we spend too much time in getting data, cleaning it up and summarizing it.

Now Microsoft has added a set of tools to Excel to simplify and automate many of these useless tasks.

Getting data from various sources is now done with Power Query. It also helps you in cleaning up the data, merging / splitting, adding calculated columns, creating relationships and so on. Data can be retrieved (and periodically refreshed) from a variety of sources.

Power BI - Power Query

Don’t worry. The good old ODBC / OLEDB, Text and Excel also works!

Excel 2013 also adds a great feature called Flash Fill to help us clean data instantly – without formulas or manual work. Although this is not a part of PowerQuery, it is a very powerful feature. I would have named it Power Data Cleanser Smile

Once data is available and clean, it is NOT stored in Excel sheets – because there is a limit on number of rows. Furthermore, large data in Excel inflates the file size and makes it excruciatingly slow to work on it.

Therefore, the clean data is stored in a new powerful friend – PowerPivot. It can store millions of rows of data and still have very small file size.

SNAGHTMLc9e9125

Now what we have is called a Data Model. It is time to summarize data. This can be done in four ways.

  1. Traditional Pivot Table and Pivot Chart
  2. PowerPivot Pivot Table and Pivot Chart
  3. Power View
  4. Power Map

Traditional pivot table can still be created with this data and used to summarize the data. We are already familiar with it so no re-learning required. You still benefit from the ability to created relationships (eliminating the VLOOKUP trap) and millions of rows of data handling.

PowerPivot also creates Pivot Tables in Excel. These behave like the good old Pivot Tables but these provide new functions and features which are simply not available in traditional Excel. Many complex calculations which would otherwise require you to approach IT to create a data warehouse (Cube using a BI Tool) can now be done with these powerful Excel formulas. Technical name for these new formulas is DAX.

image

Power View is an entirely new way of summarizing and visualizing data. This gives you s blank page to start with. You just drag drop the fields you want and choose how they are represented (tabular, chart, map, etc). In the process you create a dashboard quickly. Every visual element acts like a filter giving you unimaginable level of analytical capability.

image

Power View also supports maps. Using Bing Maps, your data can be shown on a map for better understanding. This works right within Excel – no special software required.

Power Map is a more evolved version of mapping functionality – a free add-in for Excel 2013. This allows multiple layers, different visualizations and even creation of an animated step by step video to show your plan of action and to present data in a serial manner.

image

Ask queries in English language

As though all this was not enough, you have a miracle feature. You can actually type a query in English, like “Show sales qty for each region as column chart” and it actually does the job. You have to see it to believe it.

If you have been in IT for long enough, you will remember that there was English Query in earlier versions of SQL Server. Later Microsoft dropped the feature. Now it is back with a bang. It works even with simple Excel data with no vocabulary or synonym configuration!

Reporting and Dashboards

Creating reports is one thing. Next step is to share it with others. This is best done using SharePoint. It offers a special app for PowerBI reports. Reports can be seen and interacted with on any browser (HTML 5 version coming soon. Currently requires Silverlight) and any device or tablet (Microsoft, Android, iPAD, iPhone).

image

Try it yourself

GO to the Power BI Site and sign up for a trial subscription. It gives you everything you need… Full version of Office 2013, SharePoint and PowerBI app for SharePoint (full Office 365).

Worried that your existing Office installation of older version will be affected by the new version? No worries. Even that has changed now. The installation of Office 2013 can work in parallel with older versions of Office. Both work independently without affecting each other. This is called appV (application virtualization). The deployment method is called Click To Run.

We will cover more of Power BI in upcoming articles.

Update (Aug 2016): I’ve just released my first Power BI MOOC on Udemy. See details here.  Use discount code AUTUMN40 to get the course at 20% discount. 

How to avoid Data Entry Errors with Speak Cells

In a hurry? Already know the problem? Scroll down and see the solution directly.

Eliminate data entry errors with Speak Cells

The problem, the solution and the problem!

This article is useful for anyone who understands these terms “makers and checkers”. This is usually applicable in Banking, Insurance and Finance industry.

Even if you are not from this industry, but if you have people who perform a lot of data entry in Excel, this article is useful to you.

Makers are those who enter data by looking at some paper based document. These people usually keep looking at the paper, keep typing very fast on the keyboard and never look at the screen to cross check what was typed. Therefore errors happen. In short, Makers Make Errors and Checkers Check and Correct them.

So that is the problem and the solution. But there is a bigger, unsolved problem.

From Checker’s point of view, the process of checking is monotonous and lengthy. I look at a number on the paper, then I match it with a number on-screen – repeatedly – hundreds of time… Mind you, this process itself is error prone. Can this be simplified?

Going one step further, can we detect and correct errors WHILE the data entry is being done? That will make things really efficient – time-saving + accuracy.

Needless to say, Excel has provided this solution decades back! Sadly, very few people ever noticed it!

The solution – Speak Cells in Excel

The solution is to make Excel speak! Yes. Excel can speak. Till 2003 version, the Tools menu had Speech Tools menu. But nobody clicked on it. So Microsoft REMOVED that menu from 2007 onwards (so sad… defeat of technology).

Now you have to put the menu back into your ribbon (Quick Access Toolbar).

  1. Right click on the Ribbon and choose Customize Quick Access Toolbar
  2. From the list on the left side, open the dropdown Choose Commands From – select All Commands
  3. Now a long list of commands shown. Click inside this list. Type character S. Now the first command starting with S will be shown. Now press PgDn multiple times till you reach a command called Speak Cells.
  4. Click Add button 5 times – we need the following commandsExcel speak cells
  5. Click Ok to close the customization dialog. Now you should have all these commands in your QAT.Add speak cells to Quick Access Toolbar

Checkers: Checking quickly and accurately

Checker has to check the data in Excel against a paper based document.

Select the data

Choose whether you want to read by row or by column and click Speak Cells.

Excel will now speak the value in each cell. It understands numbers, dates and text. It is very smart. It pronounces numbers differently if you separate them with commas.

Now you never have to look at the screen. Just look at the paper and stop only if there is a discrepancy. To stop speaking, press SNAGHTML35a38d5

If the number is long, you will finish reading it faster but the speech will be slow. This makes it practically unusable. To solve this problem, you should increase the speed of speech (see below).

Makers: Detect and Correct mistakes while entering data

As a maker, or a data entry person, your hands are busy on the keyboard, eyes are busy looking at the input data on paper. What are your ears doing? Nothing… so make them work for you.

Click on Speak Cells on Enter button before you start data entry.

Now when you type the value and press Enter, Excel will read out what you just typed aloud. You can hear that and now you can cross check in your mind if that is what you intended to type. Simple. If you made a mistake, correct it immediately and move on.

Again, you will need to adjust the speed of speech to match your reading speed.

Changing the speed of speech (and the gender!)

Go to Control Panel, All Items, Text to Speech Settings (you can also search for Text to Speech directly)

Change the speed to match your reading speed. Click the Preview button to check it out. Change the default text with a long number to get a real feel.

Choose male or female voice based upon your preference… you are going to hear this voice thousands of times now… so might as well choose a pleasing voice Winking smile

Depending upon the languages installed on your Office version, multiple types of voice and languages will be available. This feature is available in many languages.

image

Implementation Guidelines

  1. In most cases, IT disables speakers on PCs. You will have to approach your IT team and show the business benefit of using this feature. Then they will enable speakers.
  2. Obviously, you must use headphones to avoid disturbing others around you. Any low-cost headphones are fine. Single ear headphone also is good enough. Don’t buy bulky ones. In-ear headphones are best. If the company does not give headphones, you buy it  yourself!

Facebook analytics using Excel 2013 Power Query: Without programming!

In few minutes you can analyze any public Facebook page data.
Here we see how to analyze the likes and comments – instantly.

You need Excel 2010 or Excel 2013 (Professional Plus edition) or Office which is installed from Office 365.

  1. Close Excel if it is open
  2. Download a new, extremely powerful tool Power Query for Excel
  3. Install the tool
  4. Open Excel
  5. You should now see a tab called Power Query
  6. Open Power Query tab and open the From Other Sources dropdown
  7. Choose FacebookFacebook analytics using Excel 2013 Power Query
  8. Choose me and Statuses (you can choose other types as well)

    image

    Remember that you can type any username or object id which has information accessible to you

  9. Click Ok
  10. Sign in to your Facebook account
  11. Save the credentials
  12. Now it will open a query result window and extract status update information from FB
  13. At this stage it shows few results. Later it will fetch all possible results.image
  14. Keep the columns Message, Updated Time, Likes and Comments.
  15. Remove other columns by right clicking on each column and deleting it
  16. Now we want the count of likes and comments
  17. As of now, both show Table.
  18. Notice the small icon next to the Likes and Comments column headingsSNAGHTML17052850
  19. Click on that icon and do the following for Likes and Comments
    1. Choose Aggregate and Count of ID
    2. Now the Column shows count
  20. The date time column contains locale information. We want a simple Date Time column.
  21. Right click on the Updated Time column and choose Date Timeimage
  22. On the right side pane, expand the Steps area
  23. Notice that all the steps you performed are remembered.image
  24. Click the Done button
  25. Wait for data to be fetchedimage
  26. Now you can use this data in a Pivot Table or PowerView to analyze the FB data easilyimage

    This is a PowerView chart of one FB site

  27. Here is another one as a PivotChart based upon another public siteimage

 

Enjoy…
Now you can do sophisticated FB analysis without knowing any programming, FQL or Graph API syntax.

Explore Power Query.

It has a very powerful syntax. Yes it is complex but it is extremely powerful.
We will cover more of Power Query, Power View and Power Map in future posts.

Mini Charts Part 2: Sparklines Usage scenarios

In the previous article , we have explored a new kind of mini chart called Sparklines.  In this article, we will explore when and where to use Sparklines to quickly identify trends,  compare data visually and much more…

Quick Recap: Sparklines are mini charts drawn in a single cell based upon data from multiple cells. It is an easy yet effective way of comparing data.

Financial statements

Typical financial statements show data in reverse order. Current year first followed by past years.

image

You have to read each number from right to left and then compare them mentally to form a pattern. Then we read each row and try to correlate it with other rows.

Now see the same data with Sparklines.

Financial statement with Sparklines

Problem: Chart is visually in reverse order

Look at the Operating Income. At the first glance it looks as though it is decreasing over time. But in reality, it is increasing over time – data is in reverse order.

To avoid this visual confusion, choose the Sparkline Tools –
Axis options – Plot from right to left. Problem solved.

image

Comparing Quotations, Specifications, Scores

Any kind of comparison can be performed visually using Sparklines as long as the data is numeric.

Visual comparison with Sparklines

Notice how it helps you see a clear difference across the models without reading the numbers. It highlights the winner clearly and without ambiguity.

How does it happen? Because it takes the minimum value across the row as the beginning of the axis. Usually all bar charts start with zero. However in this case, the Power bar chart is starting at 177. Therefore the smaller value becomes visually insignificant.

Due to this visual contrast, it is easier to interpret the information. Of course, remember that this may lead you to underestimate actual values. If you want true to life representation of proportions, traditional charts are better.

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.