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. 

ribbon

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

   menu

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. 

list

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. 

example

example2

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

About these ads

7 thoughts on “PivotTable Styles Explanation and Breakdown – Because someone had to!

  1. Thanks for putting this together….. I agree completely that this is a nightmare. Also hard to find info on the web that lays out what each element controls.

  2. Thanks for making this beautiful legend! :) Pivot Table formatting has annoyed me for years. My strategy has been to just use the “None” design and avoid the headache altogether.

    Oh, you want to format your Pivot Table AND use the compact outline layout? Nope!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s