You may be familiar with the relatively common “pivot table” in Excel, where you take a set of columns and you can aggregate the information inside them to provide quick insights into your information. If you answered yes to this, then you have almost certainly also seen data formatted in such a way as to severely limit the capabilities of the pivot table, perhaps something like the image below?

While it is possible to add this to a pivot table you end up limited in the types of comparative analysis you can do since the sales columns are completely seperate from one another. Had the data been formatted into columns instead; a date column, a region column, and a sales column, then you eliminate all those limitations. Unfortunately, it is very rare that we can go in and make significant changes like that on larger datasets.

What can be done, however, is utilizing an easily accessed segment of PowerQuery within Excel to resolve this issue for us! Note that this requires a version of Excel 2010 or newer to access these features. It is a simple(ish) five step process to fixing your data and this can be applied to any dataset which has value labels (like quarter and division) on both axis of a table.

Here is your step-by-step guide to resolving your data issues using the magic of PowerQuery!

Step 1: Launching PowerQuery

To launch PowerQuery we will start by selecting the entire table of data in our Excel sheet, navigating to the Data pane of the ribbon, selecting the “From Table/Range” option on the far left of the ribbon, and clicking “Ok” on the Create Table menu that appears.

Step 2: Selecting Your Columns

Now that the PowerQuery window is open, you will be seeing a lot of new options available to you, but the one we are looking for is in the Transform tab of the ribbon. You will want to select all the columns you wish to unpivot using either CTL or SHIFT (CMD or SHIFT on macOS) and once you have them all selected you want to press the “Unpivot Columns” button.

Step 3: Enjoy the Automagic of Excel!

As simply as that, Excel has taken each of the column headers, and turned them into a column of “Attributes” which get repeated for each row instance from your original report. Next to this you will see a column of “Values” which are the same values from your initial table but now instead of being spread over any number of columns they are aggregated into one nice and easy to access place!

(Optional) Step 4: Rename Your Columns

Depending on how important this is to you, the optional step you have is to rename your columns to give them a more insightful name. In our example I will want to rename two of the columns.

Quarterly Sales to Date

and

Attribute to Business Division

In order to do so, you can either right click on the column header and find the “Rename” option, or simply double click on the header itself and it will let you edit it directly!

Step 5: Loading your Data

Now with these steps out of the way all we need to do is bring it back into our Excel workbook! To do this you will navigate back to the Home tab of the ribbon, and the first icon on the left will be “Close & Load”. We will be clicking on the drop-down arrow to display the option “Close and Load To”, where then you can select if you want all of the data to be shown as a table, or if you only want to integrate the information directly to a Pivot Table.

And there you have it, your data is now in a much cleaner and more malleable state, and it only took five steps and a few minutes to get there. This is the perfect little trick to help save you time on recurring reports, as once you have built this workflow within an Excel sheet, you can replace the data in your source table (as long as it is in the same format) and just by refreshing your pivot table the query will load in all the new information for you without you needing to redo any of the original steps!

Try this out for yourselves, explore the other tools available in the PowerQuery window, and don’t forget to show off your new skills to all your coworkers!