Today, I want to show you a trick that’s been around for a while. I think I first learned this trick from J-walk’s site pre-Walkenbach pony tail.
With this trick you can transpose an entire table using a ‘Multiple Consolidation Range’ PivotTable.
In this example, I want to transpose this dataset so that months are going down, leaving me with one Value field.
This comes in handy when you find yourself constantly normalizing data for use in a database.
Step 1: Combine all non-column-oriented fields into one dimension field.
Due to the nature of Multiple Consolidation PivotTables, it’s important that you have only one ‘dimension’ column.
In this example, anything that isn’t a month field is considered a dimension. So the ‘Line of Business’ field and the ‘Manager’ field needs to be pulled into one column.
To do this I type a simple formula that concatenates these two fields with a semicolon delimiter. Be sure you give your new column a name. Here, I name my column ‘Key’.
Step 2: Remove all but one dimension field.
After I have my concatenated column, I do a ‘Past-Special-Values’ on it then remove the ‘Line of Business’ and ‘Manager’ fields.
Step 3: Start a Multiple Consolidation Ranges PivotTable.
The next step is to start the old PivotTable and PivotChart Wizard.
In Excel 2003, this is as simple as starting a pivot table.
In Excel 2007, you’ll have to type Alt+D+P on your keyboard to call up the old wizard. You’ll need the old wizard because it’s the only place you’ll find the ‘Multiple Consolidation Ranges’ option.
Step 4: Choose to create your own page fields.
Select the ‘I will create the page fields’ option, then click Next.
Step 5: Specify the range you are pivoting and click Finish
Define the range you are working with, then click Finish.
Step 6: Double-click on Grand Total intersection of row and column.
At this point, you’ll have a useless pivot table that looks just like your original dataset.
Go to the intersection of the row and column Grand Totals and double-click the number.
You’ll get a new sheet that is essentially a transposed version of your data.
Now, you’ll want to parse out the ‘Row’ column into separate fields. Select the ‘Row’ column (column A) and call up the ‘Text to Columns’ dialog box.
Step 7: Perform Text-to-Columns on your dimension field.
The idea here is to parse out the concatenated field using the semicolon delimiter.
After a few bits of formatting and renaming, you’ve got your transposed dataset.
RELATED STUFF
the only problem is that it’s like a magic spell that you have to write down somewhere, and try to find when you need it, because it’s complex.
I would instead copy down the left hand columns 11 more times, and then use an offset formula to add the correct month figure at the right. Not as elegant, but very simple and quick, and I wouldn’t need to consult my book of magic Excel tricks.
dermotb: Come on. Excel is full of magic syntax, mystical hot keys, and vba voodoo that requires some level of memorizing steps. That’s why I can make a living peddling “tips and tricks”.
at least you’re honest!
There was a time when Walkenbach didn’t have a ponytail?
This completely changes my world view…
Well explained and offers a bit of insight into useful voodoo that Excel has hidden from me until now. Thanks alot for this!
Simply awesome. Would have saved me a couple weeks over the years. Glad I asked over at Experts Exchange
Until I looked at it closely the ‘double click on the grand total’ step did look a bit like magic. After due inspection it make sense, but at first….
Nice trick!
Now, oh Magic One….
Can you automate this without creating an intermediary sheet? (That is to say, get the same result as double click on the Grand Total field, but bring the data into VB structures rather than onto a new sheet?)
I have a problem with this opening dataset in new sheet. Is it possible to open it always in same sheet, or in sheet where grad total is…I have problems with refreshing data too…is it possible to solve thoese problems?
thanks
I found this surfing on the internet about auto blogging. You can test your own autoblog just for $5.. does anyone know if it is effective or have tried it? any kind of tips will be accepted…
For some reason, this process is deleting records from my original table. Do you know what may be causing this issue?
Leonard: I’m assuming that you are loosing some of the first few dimension columns. Remember that the consolidated pivot table can only return one column on the left. So you would have to combine columns into a helper column first.
For example, If I had Region, Market, Branch as columns before the months, I would use a simple formula to combine Region, Market, Branch into a dummy column called KEY.
example: from the North; Charlotte; Stookey Branch.
Then I would use that one dummy column to transpose.
Once the transposition is done, I would then split that column back out using Text to Columns.