Here is the list of articles I have written about Show Values As in Pivot table. This is a very important set of features but are mostly ignored.
Once you learn how to use these options, the next question is, which one to use when? The answer seems to be simple: I will choose the option based upon the data. Unfortunately, the answer is WRONG.
Remember our definition of what analytics is all about?
We must find ALL possible useful information from the data and use it to our advantage. Therefore, I strongly suggest that while you are creating reports using Pivot Tables, you MUST apply each of these Show Values As options where relevant. This way, you can unearth a wealth of additional information about your data.
Watch the video
Video Table of Contents
Jump to specific topic in the video from here…
- 00:00 – Intro
- 02:49 – Pct of grand and column total
- 03:37 – Pct of row total
- 06:56 – Pct difference
- 10:35 – Parent totals
- 14:05 – Running total and Pct
- 16:45 – Ranking
- 17:20 – Index option
Show Values As explained in detail
- Understand why Show Values As is better than adding formulas outside the pivot table
- Pivot Table – Show Values As – Part 2
Covers % of row, column and total. Same data but different ways of interpreting it.
- Pivot : Show Values As – Part 3
Covers Difference From, % Of and % difference from.
- % difference from Secrets
Covers additional details.
- Pivot: Running Totals and Ranking
Covers running totals (% as well) and Ranking.
- How to calculate YOY growth in Excel Pivot Table
Explains a brilliant way to use % difference from to calculate YOY, month-on-month growth in seconds.
If I add more articles about Show Values As, this list will be updated. This is a live book on this topic.