Exceljet

Quick, clean, and to the point

Pivot table month over month

In the example shown, a pivot table is used to show the month over month variance in sales for each month of a given year.  The variance is displayed both as an absolute value and also as a percentage. The year is selected by using a global filter.

Source data

The source data contains three fields: Date, Sales, and Color converted to an Excel Table. Below are the first 10 rows of data:

Date Sales Color
1-Jan-2018 265 Silver
2-Jan-2018 395 Green
3-Jan-2018 745 Green
4-Jan-2018 665 Blue
5-Jan-2018 565 Blue
6-Jan-2018 145 Blue
7-Jan-2018 115 Red
8-Jan-2018 400 Green
9-Jan-2018 605 Silver
10-Jan-2018 595 Blue

Fields

The pivot table uses just two of the three fields in the source data: Date, and Sales. Because Date is grouped by Years and Months, it appears twice in the list, once as "Date" (month grouping), once as "Years":

Month over month pivot table field list

The Date field has been been grouped by Months and Years:

Date field grouped by Years and Months

The grouping automatically creates a "Years" field, which has been added to the Filters area. The Original "Date" field is configured as a Row field, which breaks down sales by month.

The Sales field has been added to the Values field three times. The first instance is a simple Sum of Sales, renamed to "Sales " (note the extra space at the end):

Sales field configuration

The second instance of Sales has been renamed "$ Diff", and set to show a "Difference From" value, based on the previous month:

Difference as value configuration

The third instance of Sales has been renamed "% Diff", and set to show a "% Difference From" value, based on the previous month:

Difference as percentage configuration

Steps

  1. Create a pivot table
  2. Add Date field to Rows area, group by Years and Months
  3. Set Rows area to show Date only (month grouping)
  4. Add Years to Filter area
  5. Add Sales to Values area as Sum, rename "Sales "
  6. Add Sales to Values area, rename to "$ Diff"
    1. Show values as = Difference From
    2. Base field = Date
    3. Base item = Previous
  7. Add Sales to Values area, rename to "% Diff"
    1. Show values as = % Difference From
    2. Base field = Date
    3. Base item = Previous
    4. Set number formatting as desired
  8. Select desired year in Filter

Notes

  1. When Date is grouped by more than one component (i.e. year and month) field names will appear differently in the pivot table field list. The important thing is to group by year and use that grouping as the base field.

Pivot Table Training

If you use Excel, but don't know how to use Pivot Tables, you're missing out...wasting time trying to do things that a Pivot Table can do for you automatically. Core Pivot is a step-by-step Excel video course that will teach you everything you need to know to use this powerful tool. With a small investment, Pivot Tables will pay you back again and again. See details here.