Your PivotTable is looking good! Let’s make a few adjustments to supercharge it. You may be asking, why all the tweaks? We are modifying the PivotTable for the reasons we tweaked our vlookup formula. These are “analyst-optimized” settings. They set you apart and make your data analysis that much better than the other guy’s. Out of the box, Excel does a pretty good job formatting the PivotTable. But if you just put a couple of extra steps into it, you get extraordinary returns. Below is our current PivotTable. Let’s pick out a few issues.
Microsoft has made a lot of improvements in newer versions of Excel. The new PivotTable display is not one of them. Notice how Region and District are not labelled on your PivotTable. It is confusing to read. Aren’t your eyes skipping? What region belongs to what district? And why is the subtotal at the top of the field — don’t most people look for a total at the bottom?
When you are doing rapid-fire data analysis, you don’t want your eyes tripping over this odd formatting. Let’s fix it.
Right-click inside your PivotTable and select “PivotTable Options.” A dialogue box will appear. Go to the Display Tab and check on “Classic PivotTable Layout.”
Classic view is so much better. Notice how instead of collapsing the Region and District into one column, they are split out. Also see how the subtotals are at the bottom, not the top. This is a much more intuitive way of reading data.
Speaking of skipping eyes, I don’t know about you, but I wish the labels on Region repeated on each line. Wouldn’t it be that much easier to read if each line had its own complete labelling?
To do this, right-click anywhere in the Region column and select “Field Settings.” Click to “Layout & Print” in dialogue box that appears. Check on “Repeat Item labels.”
Here is your finished product. Notice how much easier it is to read through your data this way. This is going to save time for your boss — and wear on your eyes!
Leave a Reply