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

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Insert Subtotals to a Pivot Table When you create a Pivot Table that has multiple fields in the Row Labels, Excel will automatically add a Subtotal to the top of the Group. What about if you want to change the Subtotals to show at the bottom of the Group or take the Subtotals out all together? Well you have...
Insert Blank Rows In a Pivot Table Pivot Table reports are shown in a Compact Layout format as a default and if you have two or more Items in the Row Labels (e.g.Month & Customer), then the Pivot Table report can look very clunky... There is a cool little trick that most Excel users do not know about th...
Group Sales with a Pivot Table A Pivot Table allows you to Group your transactional sales values by any range that you want.  You can then summarize each sales group and see the total sales and the number of transactions that they have produced.  This is useful for pricing analysis. DOWNLOAD WORKBOOK ...
Classic Pivot Table Layout View For all of you old schoolers out there who used to use the drag and drop Pivot Table option in Excel 2003 or prior, well you still have that option in Excel 2010 and onwards. To activate this option you need to Right Click in your Pivot Table and choose Pivot Table Options. ...