If you are a data jockey, you would agree with me that Pivot Tables are simply awesome! And under the hood of that awesomeness there are a lot of nick nack features (especially formatting ones) that make pivots even more awesome.
Let’s explore a few of these today!
Often you might find yourself writing formulas outside of the pivot table and that dirty GETPIVOTDATA thingy that pops up when you refer to a cell in a pivot table. You might have found a manual work around to this but here is how you can turn it off
Two more things..
Converting the Pivot into a Tabular Format will separate out the years and regions in 2 different columns. This is something that people usually achieve by Classic Style Layout, which I don’t suggest using
At times you feel the need to repeat the Row Labels across the pivot table (esp for long pivots)
Often when you add more than one field under Rows in a Pivot you’ll get a pivot table with Plus Minus buttons, essentially used to expand or collapse parts of the pivot table. Here is how you can remove them
You may choose to have or hide Sub Totals or Grand Totals. The design tab gives you multiple options to display Grand Totals and Sub Totals
If in case you want to remove the pivot table field headers, you can do it this way
Although Pivot Table’s Design Tab offers many different types of styles (look and feel) but I prefer no styling options. So I always go for None (well that’s a style name). It is neat and clean
When you are constantly shuffling fields (from rows to columns or in values) the column width changes automatically to fit the contents in the cell without wrapping it.
Although this is a good feature but I don’t find this to be helpful when my pivot tables are directly placed in Dashboards. Why..?? because this would constantly alter my screen width
So I turn off the auto fit option
I find these options incredibly helpful to move and select large pivot tables (by large I mean too many row / column fields). There are two options to select (the entire pivot or parts of it) and move the pivot table in the Analyse tab
In case your Pivot Table has any blank cells (for values). You can choose to customize their display
This happens a lot. The default sorting order of row or column (text) labels is A-Z or Z-A. Now there are 2 ways to sort the values in a custom order
This one is pretty straight forward. Sorting the field list helps especially if you have too many columns in your data.
This one is more used from a hygiene perspective.
You must have seen filters for Row / Columns items but not for Values. Well the filter buttons are missing from the pivots. Here are 2 ways to get it
Method 1 : Is by choosing value filters in the filter drop down of the row labels
Method 2 : Selecting the adjacent cell outside the pivot and press CTRL SHIFT L. This will directly give you a filter on the Sales Values
Double click on the cell inside a pivot table gets you its source data. You can disable that from Pivot Table Options
Note that the user can turn this back on, so you might want to protect your sheet before sending it
Please drop in a comment in case you have some regularly used formatting tactics for pivot tables.