Unpivot Excel Data With Get & Transform

If you want to build a flexible pivot table, you might need to rearrange your source data, before you start. For example, if there is a separate column for each month’s sales, you should “unpivot” the data, to get all the amounts in one column. The good news is that you unpivot Excel data with Get & Transform, and your original data isn’t changed. If you don’t have G&T, there’s a macro that you can use instead

Source Data Problem

This screen shot shows a table with month sales in separate columns. It’s easy to enter the data in that layout, but it’s not ideal as a data source for pivot tables.

If you built a pivot table from that data, there would be 12 different fields with sales amounts – one for each month You’d have to add each month to the pivot table separately, and the pivot table won’t automatically show an annual total.

Source Data Layout Fixed

If your source data looks like the sample above, with amounts in separate columns, it already looks like data summarized in a pivot table.

To fix it for a pivot table, you’ll need to “unpivot” the data:

put each sales amount on a separate row
all the month names should be in one column
all the sales amounts should be in another column.

Here’s what the data would look like, after you “unpivot” it, with months in column D and sales amounts in column E.

Unpivot With Get & Transform

In newer versions of Excel, you can use the Get & Transform tools to quickly unpivot Excel data.

Select a cell in a named Excel table.
On the Ribbon, click the Data tab
In the Get & Transform section, click the From Table/Range command

The Query Editor will open, and follow these steps:

In the Query Editor, select all the columns that you want to group into one value field.
- In this example, select all the month columns are selected.
- Tip: Click on the Jan heading, then scroll right, and Shift+Click on the Dec heading.
Right-click on one of the headings, and click Unpivot Columns

The revised data will appear in the Query Editor, with the month names in an Attribute column, and the amounts in a Value column

To change the headings:

Select a column, and press the F2 key (or right-click the column and click Rename)
Type a new heading, and press Enter

Then, click the Close and Load button, to return the data to a new Excel worksheet.

Create a Pivot Table

After the data has been unpivotted, you can create a pivot table, based on this new table.

Select a cell in the new table
On the Insert tab at the top of Excel, click the Pivot Table command
Choose a location for the pivot table, and click OK

In the pivot table field list, choose the fields that you want in the layout. Now there’s only one Month field, and one Amount field.

The pivot table can automatically create totals for all the months

Filters and Slicers

With the pivot table based on the new table, it’s easy to summarize and filter the data.

You can put the Month field in the Filters area at the top, and select one or more months.
Or, insert a Pivot Table Slicer that shows the months, and click on a month name to see that data.

Updating the Pivot Table

Even though it’s on a separate sheet, the unpivotted data is connected to the original data.

So, if you update the original data, or add new records:

Right-click on the unpivotted data table, and click Refresh
Then, refresh the pivot table too, and it will show the latest data.

Unpivot Excel Data with Macro

If your version of Excel doesn’t have Get & Transform, or Power Query, there’s a macro on my Contextures website that you can use.

My macro is based on a trick that creates a Multiple Consolidation Range pivot table, then uses its Show Details feature to “unpivot” the data.

Get all the details on my website, and copy the macro code from the page, or download the sample file that contains the macro.

____________________