PivotTable Styles Explanation and Breakdown – Because someone had to!

I’ve been meaning to do this for about 100 years…where years equals maybe days.

Anyway, I’ve never found a very helpful reference that actually shows you what each style element in a pivot table formats.  I tried to write something up which would explain each piece, but I found it pretty hard to explain, since there are so many potential uses for pivot tables and so many types of data you might want to use them to analyze. 

So, instead, I created a little workbook doohickey which will allow you to fuss with a pivot table to see all the different options and combinations – complete with a color coded key so that you can see what each one does!  First, a tiny bit of background and a couple of gotchas.  After that, feel free to download this little guy and use it to familiarize yourself. 

Pivot Table Styles

If you go to “Pivot Table Tools” in the ribbon and click on “design”, you will see a library of PivotTable styles on the left hand side. 

If you duplicate an existing style, you can modify it. 

   

You’ll find it has a “convenient” list of elements…but what each item actually controls is not intuitive or friendly, at least not to this Excel guru wannabe. 

Add the fact that your settings for layout and style options can render certain style elements irrelevant, and the whole thing is beyond iritating.  I’ve included a couple of screenshots below which give you an idea how the workbook can help you sort this out. 

One thing which drove me crazy for a long time was where the very first value “whole table” gets its font information.  You can’t change the text size of the stupid thing from inside the pivot table style – the whole pivot table will have the font you have set for “Normal” in your cell styles library.  You can override the text size for some portions of the pivot table, but if you’re refreshing from a database, it often reverts the text back to the default.

Another “gotcha” is that it all matters how you have the pivot table laid out.  Don’t anger the pivot table, don’t tease the pivot table, and for the love of pete, be prepared to ctl+z the heck out of the thing when you’re trying to get the layout to like you.

So without further ado, here’s the spreadsheet with the built-in PivotTable Style that looks like the inside of a circus tent for kindergardeners.  It with either give you great insight, or it will make you go blind.

Pivot Styles