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 YEAR:

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 COLUMNS 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: (previous) and Base Field: Financial Year and press OK.  So it will read the “Difference from the previous Financial Year

 

show values as

 

STEP 6: To format the values you need to select the Pivot Table and go to Pivot Table Tools > Analyze/Options > Select > Entire Pivot Table

select entire pivot table

Then you need to once again go to Pivot Table Tools > Analyze/Options > Select but this time select the Values

select values

Now press CTRL+1 to bring up the Format Cells dialogue box and make your formatting changes within here and press OK.  

NB: This will fix the number format permanently and any new field that get added into the Pivot Table will have this format.  Coll hey!

format cells

 

STEP 7: To change the Sum of SALES2 name within the Pivot Table, you need to click on a cell in the Pivot Table which contains Sum of SALES2 and manually make the change and press Enter

name change

 

STEP 8: You need to select the whole column that contains the empty values and Right Click and select Hide

hide column

 

You now have your Pivot Table, all formatted and showing the Difference from the previous Year:

pivot table

Difference From Previous Year

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

Add Custom Symbols With Your Numbers in Excel A cool feature within Excel is the ability to format a cell's value by pressing CTRL + 1 on any cell. This brings up the Format Cells dialogue box and under the Custom category you can customize the Type to whatever you like and even insert symbols! There are four formattin...
Different Ways to Filter an Excel Slicer Excel Slicers are the best thing since sliced bread! They are a new feature from Excel 2010 onward and Mac for Excel 2016 and are visual buttons that shows you what items have been filtered or selected in a Pivot Table. There are several ways that you can filter an Exce...
Color Scales in a Pivot Table Color Scales are new in Excel 2010 and they highlight the smallest and largest data points within your Pivot Table. It works like a heat map, in that it gives the highest number a shade of color and the lowest number a different shade of color, so your numbers can stand out at...
Custom Date Formats in Excel Custom date formats in Excel allow you to display only certain parts of the date. Say you had a date of 18/02/1979, which coincides to be my birthday 🙂 You can use the Format Cells dialogue box to show only the number 18, the day that corresponds to that date (Sunday), the...