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:
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”:
The Date field has been been grouped by Months and Years:
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):
The second instance of Sales has been renamed “$ Diff”, and set to show a “Difference From” value, based on the previous month:
The third instance of Sales has been renamed “% Diff”, and set to show a “% Difference From” value, based on the previous month:
Steps
Notes
Author
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
