Microsoft Excel — Pivot Table Magic

From Condensed Format to Classic Format in One-Click

Pivot Tables — Condensed to Classic Format

Introduction

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…

Pivot Table Condensed to Classic macro — in action — https://www.youtube.com/watch?v=qSt1l8Nxicw

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.

Format a Pivot Table in Classic Style

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:

Classic Pivot Table format — with repeating column labels

The revised code that I used can be found here.


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.

Manual Pivot Table formatting to Classic vs macro — https://youtu.be/6Oa9VnEnDMM

About Don

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

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat

Or, just Google me…I’m everywhere