Using a Pivot Table you can show the variance from your previous month’s sales very easily…

See how to Show The Difference From Previous Months With Excel Pivot Tables here

pivot table2

Now that we have the Pivot Table report above, we can show the variances per month using an up/down or directional icon suing Conditional Formatting.

DOWNLOAD EXCEL WORKBOOK

STEP 1: Click on any variance value in the Pivot Table and go to Home > Conditional Formatting > Icon Sets > Directional

icon sets

 

STEP 2: This will bring up the Apply Formatting Rule to dialogue box.  Choose the 3rd option as this will apply the conditional format on all the values except the Subtotals

apply formatting rule

Your Pivot Table will look like this:

Pivot Table1

 

STEP 3: Now we need to make some edits in the Conditional Formatting Rule in order to get the Icons right.

Go to Home > Conditional Formatting > Manage Rules > Edit Rule

 

STEP 4: Inside the Edit Formatting Rule box you need to make the following changes:

Value = 0

Type = Number

“Check” the Show Icon Only box and press OK to confirm the changes:

edit formatting rule

 

STEP 5: In the next screen you will get the Conditional Formatting Rules Manager.  Hit Apply to see the changes and OK to confirm them:

rules manager

Now you have your Pivot Table showing the differences from the previous months with directional icons only!!!

pivot table 3

Diff From Previous Months Icons

HELPFUL RESOURCE:

300x250

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Pivot Charts & Slicers In a previous post I showed you how to Insert a Pivot Chart.  Now we will take this concept once step further and insert a Slicer.  The cool thing about this is that the Slicer will control both the Pivot Table and the Pivot Chart.See how you can start creating some awesome i...
Save A Chart Template Saving a Chart template in Excel is an efficient way to reuse your favourite or your company´s standard chart template.So whenever you have new data and want to create a chart with your (company´s) standard formatting, all you need is a couple of clicks and you will have it s...
Icon Sets In A Pivot Table An Icon Set is a Conditional Formatting icon/graphic that you can include in your cells or Pivot Tables.The icon will depend on the cell´s value so you can highlight key variances or trends.  There are a few icon sets that you can include, like:DIRECTIONAL (Change in valu...
Errors When Grouping By Dates With Excel Pivot Tab... In our Group By Date post, we grouped our sales numbers by weeks.However there are times when we try to group by Dates and we get an error message!  That is frustrating!Instead of checking the dates one by one to find out where the error occurred, I will show you a cool w...