Three Ways to Unpivot Data in Excel

Overview

Most Excel users are quite familiar with PivotTables (if not, add that to your to-do list!).  But have you ever needed to take a report where the data is already pivoted – spread horizontally across several columns – and stack it vertically instead?  This is known as “unpivoting” (see the screenshot below), and it’s more common than you might think.  Many reports come in a pivoted fashion where data points like periods, locations, departments, people, etc. are displayed across several columns.

While pivoted data like this may be helpful for reviewing and comparing, it’s a lot tougher to build formulas around, filter and sort, or perform other analytical steps.  Most people will unpivot reports like this using a giant copy/paste exercise, which is slow, ripe for errors, and not too fun.  But there are much better ways to do this, each with their own pros and cons.

In this article, we’ll look at the three different ways you can unpivot data using dynamic array functions (DAFs), Power Query, and macros.  This will save you time and reduce the risk of errors.  Make sure to download the example file below to leverage these examples and use as a reference!

Screenshot of pivoted >> unpivoted data

Comparing the Approaches

Each of these approaches has its own purchase and pros and cons.  Here’s a quick comparison – see each approach below for the best use case.

The DAF Approach

DAFs have given us so many new ways to restructure data, and unpivoting is a great example.

Pros:

• Flexible parameters
• Can update with your data as it changes

Cons:

• The underlying formula can be complex if you need to edit
• Recalculation time can be lengthy and annoying

Best for:

When you need the unpivoted data to be dynamic and change with your data instantly

While there are many ways you could probably build this, here’s the approach I’ve taken with the custom LAMBDA formula below:

=UNPIVOT_DATA(data_range,header_row_ct,header_column_ct,[pivot_header_range])

The arguments within this function are:

• data_range (required) – this is the range of all the data – headers and data – that you want to unpivot.
• header_row_ct (required) – this is the number of header rows above your data that need to be unpivoted.
• header_column_ct (required) – this is the number of header columns to the left of your data that need to be unpivoted.
• pivot_header_range (optional) – this is a list of header labels for each of the new columns that will be unpivoted and the value column.  It should a number of values equal to the column headers + 1 (for the value column), either as an array of values or a referenced range (see the examples below).

Examples:
=UNPIVOT_DATA($A$1:$T$1000,2,2,Z1:Z3) ‘ References a range for the unpivoted headers
=UNPIVOT_DATA($A$1:$T$1000,2,2,{“Type”,”Location”,”Amount”}) ‘ Specifies unpivoted headers as an array of text values

Below is a screenshot of the formula behind the scenes for this LAMBDA definition.  Make sure to download the example workbook you can copy from (download using the form at the top or bottom).

Screenshot of Unpivot_Data LAMBDA function

The Power Query Approach

Power Query is one of the most powerful, yet under-utilized features Excel has to offer.  There are all sorts of ways to enrich and transform your data, including unpivoting it.

Pros:

• Automatically unpivot when refreshing the source data
• Can combine with other data steps

Cons:

• The steps are generally tied to the data set
• Adjusting parameters can be tricky based on your data set

Best for:

When you need to unpivot the same data set repeatedly and the structure isn’t expected to change

This approach will vary a little bit based on how your headers are structured.  Generally, the steps should look like the steps below.
***Note: Steps 6-10 are not necessary if you have just one header row above your data!

1. Set the data set as a table [Insert (tab) > Table] and rename it if you’d like.
2. Load the data into Power Query as a table [Data (tab) > Get and Transform Data (group) > From Table/Range].
3. Rename the query if you’d like to something more specific to unpivoting.
4. Remove the automatically applied Changed Type and Promoted Headers steps from the query.
5. [Optional] Fill in missing data for blank rows using a fill down operation on the appropriate column(s) [Transform (tab) > Any Column (group) > Fill > Down].
6. Merge the column headers (to the left of the data set) using a semicolon as the delimiter into a new column named Merged. Select the columns then merge them [Transform (tab) > Text Column (group) > Merge Columns]. (Note: The separator can be any character as long as it is not in the data.)
7. Transpose the table [Transform (tab) > Table (group) > Transpose].
8. [Optional] Fill in missing data if needed on the first column [Transform (tab) > Any Column (group) > Fill > Down].
9. Promote the first row as a header row [Transform (tab) > Table (group) > Use First Row as Headers].
10. Remove the automatically applied Changed Type step from the query.
11. Select the first and second columns and unpivot the other columns [Transform (tab) > Any Column (group) > Unpivot Columns > Unpivot Other Columns].
12. Select the merged column and split by delimiter [Transform (tab) > Text Column (group) > Split Column > By Delimiter] (using semicolon or whatever delimiter you chose).
13. Remove the automatically applied Changed Type.
14. Rename the columns to something more read-friendly.
15. [Optional] Re-order the columns if desired.

After applying the steps, your Power Query Editor window should look like the screenshot below.  Download the accompanying workbook to see all the steps for unpivoting the example data set.

Screenshot of unpivot steps in Power Query Editor

The Macro Approach

Macros are a tried and true way to automate all sorts of repetitive, manual steps.  Unpivoting is a macro I’ve used for a long time, and it still has its place among the other options.

Pros:

• Flexible parameters
• Quick to initiate and process

Cons:

• Doesn’t update with the source data as it changes
• Requires VBA/macros to run

Best for:

When you need to quickly unpivot a data set on an ad-hoc basis

There are several ways to build a macro that can unpivot.  The two primary approaches are 1) to automate copying and pasting as if you were to do it manually, and 2) to perform the calculations within an array.  The bigger the data set, the more an array will likely be the better choice to run quickly.

Regardless of the approach, the steps are what you’ll want to build into a macro.  Download the accompanying workbook to see one example of a macro that can be used for unpivoting.  The screenshot below shows an example of an unpivot macro in action.

If you unpivot frequently or want this and hundreds of other tools ready to use in all of your workbooks, check out the XLEV8 Excel Add-in.  

Steps to build:

1. Identify the source data (user should select before running the macro).
2. Prompt the user for the number of header rows above the data and the header columns to the left of the data that should be unpivoted.
3. Repeat the left header columns for each data column that will be unpivoted.
4. Insert the number of columns equal to the header rows above the data, repeating for each of the data columns
5. Finally, stack all the data columns into one unpivoted column.

Screenshot of unpivot data macro in process

Video

Summary

Unpivoting is a valuable task to do in Excel.  The three approaches discussed above give you several tools you can leverage to unpivot quickly and correctly.  Don’t forget to download the workbook with examples of all three approaches so you have them when you need them!

What kind of data have you needed to unpivot before?  What approach did you take?  Let us know in the comments below!