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:
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.