It is common in Excel to have columns of months, quarters, weeks, or years. But those data sets make horrible pivot tables. You really need one column with the time value and one column with the sales value.
This episode shows how to use Mike Alexander's Multiple Consolidation trick to unwind the data, but there is a twist; this data set has 3 columns of labels. Update! If you have Excel 2010 or Excel 2013, there is a faster way to solve this problem using Power Query. See
This episode shows how to use Mike Alexander's Multiple Consolidation trick to unwind the data, but there is a twist; this data set has 3 columns of labels. Update! If you have Excel 2010 or Excel 2013, there is a faster way to solve this problem using Power Query. See
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel From MrExcel, Podcast Episode 1787: Unwind Data Before Creating A Pivot Table.
Hey. Welcome back to the MrExcel netcast.
I'm Bill Jelen. I've done this trick before but not quite as complicated. I want to do the more complicated version here on the podcast.
This trick, by the way, comes courtesy of Mike Alexander. He's my co-author on the Pivot Table Data Crunching books. Check it out.
He's a funny, funny guy.
So, we have data here. We want to create a pivot table from this, but the quarters or months or weeks are going across, and for a good pivot table, they need to go down.
So, if we have a 100 records here, we need to have 400 records, in this case each quarter.
I used to do this with copy and paste, but this this method is so much better, but here's the “got you.” Mike's trick only works with 1 column of labels and we have 3 columns of labels here.
So, I'm going to insert 2 columns. I'm going to create something here called KEY. This KEY field is going to be equal to A2& and I'm Going to put a , in between. If your data already has commas, then choose something else like a vertical bar or something like that. You want it to be unique, ,& and San Francisco. Why am I doing that? I'm doing that because, at the very end, I could then use text to columns to break it back out into 3 fields, okay? So, that's the one limitation of this. By the way, if you happen to have the new data explorer trial from Microsoft, they have a great way to unpivot data, but I'm assuming that you're just using a base version of Excel here. Excel 2003 up through ’13, this trick works in all of those. [ =A2“,”&B2&“,”&C2 ] Now, we need to create a special kind of pivot table called multiple consolidation range.
It's no longer in this dialog box here, INSERT, PIVOT TABLE. So, what you have to do is do ALT+D for data, P for pivot, and that brings us back to the old Excel 2003 style of that dialog box where we can choose MULTIPLE CONSOLIDATION RANGES, click Next, say that you're going to CREATE THE PAGE FIELDS. Now, you're not actually going to create them but you don't want Excel to create them. So, click NEXT, and then where's the data? The data is right here from our KEY field. Make sure to include the headings. CONTROL+SHIFT+DOWNARROW, CONTROL+SHIFT+RIGHTARROW.
Click ADD. HOW MANY PAGE FIELDS? None. Just click FINISH.
We get a brand new worksheet that looks exactly like the sheet that we just had but now it's a pivot table, and I was like, Mike, this is a complete waste of time, when he showed it to me the first time, but here's the real trick. We're going to do CONTROL+DOWNARROW here. I'll scroll down a little bit. The grand total, grand total. So, the GRAND TOTAL row, GRAND TOTAL column. I'm going to double click there to drill down on that cell. Now, normally, drilling down in the cell just gives you your original data back, right, but here we don't get our original data. We get how it's stored in the pivot table cache and it is stored vertically. So, instead of the quarters going across, the quarters are now going down.
Isn't that beautiful? All we have to do here is rename things. So, this is SALES, this is QUARTER, and then here we have 3 fields in column A. So, I'm going to insert 2 new columns. Select this range. I’m going to use ALT+D E for data text to columns. It is DELIMITED by a COMMA, click FINISH. They're going to warn me and say, hey, you might overwrite some blank rows there. [ unintelligible – 03:20 ] that's nice. Excel 2013 doesn't warn me anymore, and we now have this as MODEL, this is REGION, this is MARKET, and we're good to go now to create a pivot table from this data. I should spell that right. QUARTER. Alright.
So, unwinding data, when you have multiple labels of columns, you can just join them all together with the commas. Use Mike Alexander's trick, get the pivot table, double click on the grand total, grand total, and then data text to columns. [ unintelligible – 03:55 ].
You now have a great data set for creating a better pivot table.
Well, hey. I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
Learn Excel From MrExcel, Podcast Episode 1787: Unwind Data Before Creating A Pivot Table.
Hey. Welcome back to the MrExcel netcast.
I'm Bill Jelen. I've done this trick before but not quite as complicated. I want to do the more complicated version here on the podcast.
This trick, by the way, comes courtesy of Mike Alexander. He's my co-author on the Pivot Table Data Crunching books. Check it out.
He's a funny, funny guy.
So, we have data here. We want to create a pivot table from this, but the quarters or months or weeks are going across, and for a good pivot table, they need to go down.
So, if we have a 100 records here, we need to have 400 records, in this case each quarter.
I used to do this with copy and paste, but this this method is so much better, but here's the “got you.” Mike's trick only works with 1 column of labels and we have 3 columns of labels here.
So, I'm going to insert 2 columns. I'm going to create something here called KEY. This KEY field is going to be equal to A2& and I'm Going to put a , in between. If your data already has commas, then choose something else like a vertical bar or something like that. You want it to be unique, ,& and San Francisco. Why am I doing that? I'm doing that because, at the very end, I could then use text to columns to break it back out into 3 fields, okay? So, that's the one limitation of this. By the way, if you happen to have the new data explorer trial from Microsoft, they have a great way to unpivot data, but I'm assuming that you're just using a base version of Excel here. Excel 2003 up through ’13, this trick works in all of those. [ =A2“,”&B2&“,”&C2 ] Now, we need to create a special kind of pivot table called multiple consolidation range.
It's no longer in this dialog box here, INSERT, PIVOT TABLE. So, what you have to do is do ALT+D for data, P for pivot, and that brings us back to the old Excel 2003 style of that dialog box where we can choose MULTIPLE CONSOLIDATION RANGES, click Next, say that you're going to CREATE THE PAGE FIELDS. Now, you're not actually going to create them but you don't want Excel to create them. So, click NEXT, and then where's the data? The data is right here from our KEY field. Make sure to include the headings. CONTROL+SHIFT+DOWNARROW, CONTROL+SHIFT+RIGHTARROW.
Click ADD. HOW MANY PAGE FIELDS? None. Just click FINISH.
We get a brand new worksheet that looks exactly like the sheet that we just had but now it's a pivot table, and I was like, Mike, this is a complete waste of time, when he showed it to me the first time, but here's the real trick. We're going to do CONTROL+DOWNARROW here. I'll scroll down a little bit. The grand total, grand total. So, the GRAND TOTAL row, GRAND TOTAL column. I'm going to double click there to drill down on that cell. Now, normally, drilling down in the cell just gives you your original data back, right, but here we don't get our original data. We get how it's stored in the pivot table cache and it is stored vertically. So, instead of the quarters going across, the quarters are now going down.
Isn't that beautiful? All we have to do here is rename things. So, this is SALES, this is QUARTER, and then here we have 3 fields in column A. So, I'm going to insert 2 new columns. Select this range. I’m going to use ALT+D E for data text to columns. It is DELIMITED by a COMMA, click FINISH. They're going to warn me and say, hey, you might overwrite some blank rows there. [ unintelligible – 03:20 ] that's nice. Excel 2013 doesn't warn me anymore, and we now have this as MODEL, this is REGION, this is MARKET, and we're good to go now to create a pivot table from this data. I should spell that right. QUARTER. Alright.
So, unwinding data, when you have multiple labels of columns, you can just join them all together with the commas. Use Mike Alexander's trick, get the pivot table, double click on the grand total, grand total, and then data text to columns. [ unintelligible – 03:55 ].
You now have a great data set for creating a better pivot table.
Well, hey. I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
