Click here to see part 1 of this series if you have not already

..(cont.)

Conditional Formatting inside a Pivot Table

Even though our pivot table looks ready to send off, let’s put a nice finishing touch before shipping it.

• Highlight all of the data in the middle of the pivot table (not the sum column/row)
• Click on the conditional formatting tab (home page)
• Click “Color Scales”

Color scales provide an easy way to highlight the highest and lowest values in a dataset. This is very important if key decision makers want to compare winners/”um, not winners..”. The first rule we chose, going from red to white clearly shows the highest values, but clashes with our blue pivot table.

After some experimentation with pivot table color and conditional formatting color, we have one that’s a bit easier on the eyes:

Depending on the rest of the reports that you’re shipping, you can play around A LOT with the color/style of these features, but either way its clear to see that our best performing branches and years stand out a lot.

Calculated Item

Let’s say that your boss comes back to you and says that he wants to show the Year over Year difference between the highest performing branch and the lowest performing branch. How do you do it? Create a Calculated Item.

(Note: If you’re just getting started with pivot tables, what we’ve learned so far may be enough, but this is a cool feature to have in your bag of tricks). 

If you’re feeling overwhelmed, don’t worry. We’re almost done!

Step 1: Make sure you’re in one of the header cells of the pivot table (here, ‘West’). Click “fields, items, and Sets” -> “Calculated Item”

NOTE: Calculating a FIELD vs. an ITEM are two different things. Right now we’re focusing on the difference between two ITEMS, the West Branch vs. the South Branch.

Step 2: Build your formula. Click the item “West”. Insert. Minus sign. Click the item “South”. Insert. Title the formula

Let’s see what happened.

We can now see our calculated item. We also added conditional formatting to the column. It seems like the West Branch out performed the South 4 of the 7 years we tracked, and across all 7 years it performed $138,238 better than the South. From here we can:

• Dig in to 2013 and discover the cause of the big spike in Sales
• Figure out why although West is out performing the South in long term, why is there so much volatility?
• What can the South do to improve? Is our comparison of the two branches really apples to apples or are there other things to consider?