Let’s say we just got handed a report in Excel and have been asked to calculate various things from it. And the report looks something like the image below. Now, there are two ways to go about it:
- Use VLOOKUP, INDEX, MATCH and IF statements to calculate all the statistics we need.
- Convert the sheet into a big, ugly wall of data (as a Table) and ease our pain.
As data analysts, we are very comfortable with the first method, so much so that it appears boring and a waste of time. The second method, however, looks like something we’d be very interested in, especially if it automates this process for us. And our very own Oz du Soleil is here to show us this trick of the trade.
Let’s begin!
1 – The Requirements
There are a few properties our scattered report data should have:
- Labels at the top, and
- Consistency in layout of each subsection.
With a few tries on this method, any of us would be able to see that this consistency requirement is actually not as strict as it appears.
2 – Loading Up Query Editor
- For Excel 2013, go to POWER QUERY tab, and select ‘From File’ under the Get External Data section. Now select ‘From Excel’.
For Excel 2016, go to DATA tab, and select ‘New Query’ under the Get & Transform section. Now select ‘From File’ and then ‘From Workbook’. - Now locate the active Workbook on the hard drive and press OK.
- Select the sheet which has the report data and press ‘Edit’. The sheet will open up in the Query Editor. The image on the right shows how the data.
3 – Formatting
This is actually a fun part. We need to manipulate the data Power Query has grabbed to make it look like a table we would want to work with. Here’s what we’ll do with the data:
- Right-click on the “State” header and select ‘Fill’ and then ‘Down’. This is shown in the image on the right. As we may have imagined, it fills the state names downwards wherever it finds “(null)”.
- Now left-click on the filter on “State”,select ‘Text Filters’ and then ‘Does Not Contain…’. Type “Total” and press OK.
- Now, filter out “(null)” from the “Rep” column.
4 – Let’s Load the Awesomeness
Once the cleaning has been done, all we need to do is press ‘Close & Load’ on the upper-left side of the Query Editor. And there we have it, the big, ugly wall of data we all are familiar with.
There’s another thing that needs to be highlighted. This wall is “dynamic”! What do we mean by that? Well, we wouldn’t want to do all of this again if new data is added to the report sheet. Especially when there are many formatting steps, not just three as in our example.
But there is no need to worry. When you would paste new data, click on any cell in this table just refresh it. Excel will automatically pick the new data up with the desired formatting! Check out the result in the image below.
What’s next?
Give it a shot! See if this eases up your life. And your experiences with us below.
Do not forget to tell your fellows about this tip. Let them save a lot of their time too.
- SSSVEDA DAY 7 – Every Team Needs Someone Who Understands Data - February 18, 2018
- SSSVEDA DAY 5 – When Data Analysis is Wrong - October 31, 2017
- SSSVEDA DAY 4 – Sharing the Excel Knowledge - July 18, 2017
