Convert a Summary Table in Excel Into a Pivot Table

Often times, when you download a data table from somewhere it’s not in the format you need it to be. Tables are often in a summary format where you have months going down and years going across, or vice versa. It’s the end result of what you want a pivot table to look like, but you can’t easily turn that into a pivot table itself. Below, I’ll show you how to turn a summary table in Excel that looks like this:

Into this:

This format is much more Excel-friendly and one that you can easily convert into a pivot table.

Converting the table

The data I’m using is the same one that I used in an earlier post that went over transposing data. Transposing data, unfortunately, isn’t enough to make data workable if you want to convert it into a pivot table. You’ll want data to be in a tabular format so that there’s a header for the month, year, and value.

You could manually transpose one year at a time and copy the data one by one. But of course, that isn’t optimal at all. The good news is I’ve got a macro that can help you flip that data in one click. It will go through the painstaking process of reorganizing the data for you.

Here’s the code for the macro. You can just put it into a module (I’ll leave a template to download below if you aren’t comfortable doing this step yourself):

Sub flipdata()

Dim cl, nxtcl As Range
Dim lastcol, lastrow, firstcol, firstrow As Integer


'get total number of rows and columns in range
lastcol = Selection.End(xlToRight).Column
lastrow = Selection.End(xlDown).Row

'get first column and row
firstcol = Selection.Column
firstrow = Selection.Row

'assign output starting point
Set nxtcl = Cells(lastrow + 2, firstcol)

nxtcl = "Header 1"
nxtcl.Offset(0, 1) = "Header 2"
nxtcl.Offset(0, 2) = "Value"

Set nxtcl = nxtcl.Offset(1, 0)


'cycle through data

For yr = (firstrow + 1) To lastrow

    For mth = (firstcol + 1) To lastcol

        nxtcl = Cells(firstrow, mth)
        nxtcl.Offset(0, 1) = Cells(yr, firstcol)
        nxtcl.Offset(0, 2) = Cells(yr, mth)
        Set nxtcl = nxtcl.Offset(1, 0)

    Next mth

Next yr

End Sub

It will output the data a couple of rows below where your data ends. It’s important to select the entire range of data before running the macro since it will go through the range that you’ve selected, nothing else. And if there’s data below your selection, it will overwrite that.

After you’ve selected the data, then you run the macro. In my template, I’ve got a button that you can press that will do the job for you and then you’ll get something that looks like this:

Once in this format, you can easily create a pivot table:

If you’d like to download the file that contains the macro, it’s available here.

If you liked this post on how to convert a summary table in Excel into a pivot table, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.