Unpivot Excel Data

Unpivot by Jeff Lenning

Excel easily summarizes flat, tabular data. When data is stored in a crosstab style format instead, Excel users have to spend a bit of time preparing the data for use. There are many ways to accomplish just about any Excel task, but in this post, I’ll demonstrate how to quickly unpivot the data. Thanks to Patrick who submitted this question.


Before we dig into the mechanics, let’s be sure we are clear about the data formats and our objective.

Here is an example of flat, tabular data:

Flat data by Jeff Lenning

Here is an example of the same data stored in a crosstab style format:

Crosstab data by Jeff Lenning

We can easily convert tabular data into a crosstab format using a PivotTable. But here, we want to do the opposite. We want to unpivot the data, converting it from a crosstab format into a tabular format.

Note: please note that unpivoting the data is not the same as transposing it. Transposing the data would place departments in rows and accounts in columns. If you need to transpose instead of unpivot, check out this Excel University blog post instead.

Here is another example that shows students and the trips they have attended.

Excel example by jeff lenning

And one more example that tracks who is assigned to various tasks.

Excel worksheet by Jeff Lenning

Now that we have our bearings and can visually see our objective, let’s work through the details.


The unpivot command is available without any additional downloads in Excel 2016 for Windows. If you are using a different version, you may need to first download the free Power Query add-in from the Microsoft site. At the time I’m writing this, it is available from the link below.

The four easy steps we’ll use to unpivot our crosstab data are:

  • Store the crosstab data in a table
  • Get & Transform From Table
  • Unpivot
  • Load

We’ll take them one at a time.

Store the crosstab data in a table

First, we need to ensure that our crosstab data is stored in a table. If it already is, you can skip this step. Our data isn’t stored in a table, and it currently looks like this.

Crosstab data by Jeff Lenning

To convert it into a table, we select any cell in the data range and click the Insert > Table command. Now, it is stored in a table and looks like this.

Excel table by Jeff Lenning

Hey, that was pretty easy. Let’s move to the next step.

Get & Transform From Table

The next step is to use the Get & Transform From Table command. Again, this is built-in beginning with Excel 2016 for Windows. If you have a different version, you’ll want to download and install Power Query using the link below, and note that the navigation may be slightly different from the screenshots presented below.

First, we select any cell in the table. Then, we click the following Ribbon command located in the Get & Transform group.

  • Data > From Table

This opens the Query Editor, as shown below.

Query editor by Jeff Lenning

Hey, that was pretty easy…let’s take the next step.


Before we use the unpivot command, we first need to tell Excel which columns we’d like to unpivot. To do this, we can select the first column we want to unpivot, hold down the Shift key, and then click the last column. The results are shown below.

Selected columns by Jeff Lenning

Note: if you would like to undo a step in the Query Editor, you click the x in the Applied Steps list box.

Now that we have identified which columns to unpivot, we can use the unpivot command. The unpivot command is located on the Transform tab. Since the Ribbon dynamically sizes itself based on the dialog size, you may see the unpivot columns command with a text label, like this:

Unpivot by Jeff Lenning

Or, it may appear without a text label, like this:

Unpivot by Jeff Lenning

Just click the command icon, and bam, Excel unpivots the data, as shown below.

Unpivot complete by Jeff Lenning

Hey, that was pretty easy! The final step is to load the data back into our workbook.


With the data unpivoted, we just need to get it back to our workbook. To do so, we use the Close & Load command on the Home tab. Excel drops the unpivoted data back into our workbook, as shown below.

Unpivoted by Jeff Lenning

That was easy!

The unpivot command is but one of the many powerful capabilities in the Query Editor dialog. If you retrieve data from external sources and perform data transformations often, you’ll definitely want to investigate the additional commands.

Additional Resources

This article was written by jefflenning


  1. Karen Haubenstock

    Very interesting!

    1. jefflenning Post author

      It is such a cool feature :-)

  2. Barb Giss

    Perfect timing! I was able to put this to use this morning. It was just what I needed. Thanks so much!

    1. jefflenning Post author

      Wow…that was perfect timing, glad to help :-)

  3. Bronson Quon

    This is very useful. Sometimes you just need the data in a flat, tabular format, especially if needing to import the data into another application! Thanks

Comments are closed.