Pinterest Pixel

Show The Difference From Previous Months With Excel Pivot Tables

Excel Pivot Tables have heaps of calculations under the SHOW VALUES AS option and one that gets... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

Excel Pivot Tables have heaps of calculations under the SHOW VALUES AS option and one that gets the most use is the Excel Chart Month on Month Comparison.

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

 

Follow the step-by-step tutorial on How to Show Excel Month on Month Comparison and download this Excel workbook to practice along:

Download excel workbookDIFFERENCE-FROM-PREVIOUS-MONTH.xlsx

 

STEP 1:Select any cell in the data table.

Show The Percent of Grand Total With Excel Pivot Tables

 

STEP 2: Insert a new Pivot table by clicking on your data and going to Insert > Pivot Table.

Show The Percent of Grand Total With Excel Pivot Tables

 

STEP 3: Insert a new Pivot In the Create PivotTable dialog box, select the table range and New Worksheet, and then click OK.

Show The Percent of Grand Total With Excel Pivot Tables

 

STEP 4: In the ROWS section put in the Sales Month field, in the COLUMNS put in the Financial Year field and in the VALUES area you need to put in the Sales field twice, I explain why below:

See also  Microsoft Excel Pivot Table Course

Show The Difference From Previous Months With Excel Pivot Tables

The Pivot Table will look like this:

Show The Difference From Previous Months With Excel Pivot Tables

 

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

Show The Difference From Previous Months With Excel Pivot Tables

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

Show The Difference From Previous Months With Excel Pivot Tables

 

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

Show The Difference From Previous Months With Excel Pivot Tables

STEP 8: 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:

Show The Difference From Previous Months With Excel Pivot Tables

STEP 9: To create a chart with this data, Go to PivotTable Analyze > PivotChart.

See also  Conditional Format a Cell´s Value

Show The Difference From Previous Months With Excel Pivot Tables

STEP 10: In the Insert Chart dialog box, select Column and click OK.

Show The Difference From Previous Months With Excel Pivot Tables

The month to month comparison excel chart will appear in the worksheet.

Show The Difference From Previous Months With Excel Pivot Tables

STEP 11: Click on the filter button in the chart and select 2012.

Show The Difference From Previous Months With Excel Pivot Tables

 

This completes our tutorial on month over month comparison Excel!

Show The Difference From Previous Months With Excel Pivot Tables

Further Learning:

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

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

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!