Microsoft Excel — Pivot Table Magic
From Condensed Format to Classic Format in One-Click
Most pivot table users eventually discover a need to convert the default format pivot table into the older “classic” style format (classic is the tabular layout, with columns side by side).
If you have ever done this, it’s remembering and executing a few clicks, possibly repeating column labels, etc.
There’s got to be an easier way. There is…
Automating This Process
A Google search quickly found the solution to accomplish this. The VBA (“visual basic for applications”) to do this looks complicated, but it is NOT hard to implement.
To implement — simply add the code into your spreadsheet in a module in your VBA Editor. If you are unfamiliar with this, here’s the basics:
Open the Visual Basic Editor (keyboard shortcut “Alt + F11”)
Adding Code to an Excel Workbook (go for it, it’s step by step…)
Here’s the VBA Code
I did a Google search, which brought up the contextures.com (BTW, I highly recommend this site) tip — almost EXACTLY what I was looking for.
I copied and pasted the vba code into my spreadsheet (VBA Editor) and it worked perfectly. However, I needed to revise for one item in column labels — I wanted repeating labels:
But what does this really save me?
A great YouTube video, included in Contextures article referred to, walks through the manual steps necessary to accomplish what the macro does. It’s a very simple pivot table example, but it drives home the beauty of just a little automation.
“On a mission to challenge the status quo to a more productive and effective end…”
Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.