Excel Pivot Tables have heaps of calculations under the SHOW VALUES AS option and one that gets the most use is the DIFFERENCE FROM calculation.

You can show the values as the Difference From previous months, years, day etc.  This is just great when your boss asks you how you are tracking to the previous months, years, days…

In the example below I show you how to show the Difference From the previous month:

DOWNLOAD EXCEL WORKBOOK

STEP 1: Insert a Pivot able by clicking on your data and going to Insert > Pivot Table > New Worksheet or Existing Worksheet

pivot

STEP 2: In the Rows you have to put the Months field, in the Column the Years field and in the Values area the Sales field twice, I explain why below:

pivottable fields

STEP 3: Now click on the second Sales field’s (Sum of SALES2) drop down and choose Value Field Settings

value field settings

STEP 4: Now you need to select the Show Values As tab and from the drop down choose the Difference From

difference from

STEP 5: You need to select the Base Item as (previous) and Base Field as Sales Month and press OK.  So it will read the “Difference from the previous Sales Month

diff from

STEP 6: You can do some cosmetic changes by going back into the Values Field Settings (from step 3) and changing the Custom Name to show whatever you like eg. Diff. From Previous Month or Monthly Variance.

From in here you can also click on the Number Format (bottom left hand corner) to change the way the numbers show:

pivot table2

Diff From Previous Month

HELPFUL RESOURCE:

728x90

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

Show The Percent of Row Total With Excel Pivot Tab... Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF ROW TOTAL calculation. This option will immediately calculate the percentages for you from a table filled with numbers such as sales data, exp...
Sort Field List In a Pivot Table from A to Z Do you ever get in a situation where your Pivot Table Field List has over 20 fields and you spend heaps of time trying to locate one within the Field List? Well you can save time by sorting the Pivot Table Field List in alphabetical order! To do this you need to Right Click...
Show Averages With an Excel Pivot Table A Pivot Table is the most powerful feature within Excel as it allows you to analyze your data in many different ways, all with a press of a button. The Summarize Values By option allows you to choose a type of calculation (Sum, Count, Average, Max, Min, Count Numbers Produ...
Pivot Table Conditional Formatting Inserting a Pivot Table is very easy to do in Microsoft Excel: Inserting a Pivot Table Adding some Conditional Formats to the Pivot Table allows a user to highlight key data in a split second. See how easy it is to add some color to your analysis. DOWNLOAD WORKBOOK ...