Analyze trends using pivot tables
Table of Contents
- Introduction to pivot tables
- Create pivot table
- Group data
- Analyze data (pivot table)
- Compare performance, year to year, quarter to quarter (previous year), month to month (previous year)
- Product trends
- Region trends
Introduction to pivot tables
A pivot table can quickly summarize and categorize many table records into a single report. Here is a picture of a table containing random fake data.
Let me show you what you can do with the data above and a pivot table.
You can group dates into months, quarters and years and sum corresponding data.
You can also categorize data into regions, products, salesperson or whatever categories you may find interesting.
It is possible to spot sales trends and quickly examine the underlying data, for instance on specific years, regions or products. Later in this post I'll show you how to compare sales from year to year or any year. The pivot table is somewhat "intelligent" and knows that you are interested in comparing selected (expanded) quarters or months.
Create a pivot table
Group data
Now your pivot table looks like this:
- Click and hold on Date in Pivot table field list
- Drag and release over "Row labels" area
- Right click on any date in the pivot table
- Click Group...
- Select months, quarters and Years
- Click OK
Analyze data (pivot table)
Add "Amount" to the pivot table.
- Click and hold "Amount" in the pivot table field list
- Drag and release over Values area.
All sales in each year are summarized.
Click on any + to expand that specific year. Since you grouped dates into years, quarters and months, the next "level" is quarters.
Click again + on a quarter and months are displayed.
You can expand or collapse all fields with one click. Right click on any date and select "Expand Entire Field".
Compare performance, year to year, quarter to quarter (previous year), month to month (previous year)
Add pivot table field "Amount" to the Values "Area". You already did that? Do it again.
- Click on "Sum of Amount" field in "Values" area
- Click "Value Field Settings..."
- Go to tab "Show Values As"
- Select Show values as "Difference From"
- Select Years in Base field
- Select (previous) in Base item.
- Click OK
There is a sharp decline in sales in 2011. Expand 2011.
Notice that there are no +/- values in the quarters. That´s because only one year is expanded. Let's compare with year 2010, expand 2010.
Sales were pretty good in the three first quarters but the fourth quarter was terrible. You can quickly compare sales to year 2009. Collapse 2010 and expand 2009.
See that! Values in the +/- column changed! Quarters in 2011 are now compared to quarters in 2009.
Product trends
Which products show a decline in sales quarter 4, 2011?
- Click "arrow" near "Row Labels"
- Select only year 2010 and 2011
- Remove "Sum of Amount" in Values "Field"
- Drag Products from Pivot table field list to Column area.
"Access points" and "Modems" have the largest declines in sales in quarter 4, 2011.
Region trends
Do all markets have the same decline in sales? This time calculate % difference from the same quarter last year.
- Click on "+/-" in Values area.
- Click "Value Field Settings"
- Change Custom name to "%"
- Change Show values as: % Difference From
- Click Ok
- Remove Products from Column Labels area
- Add Region to Column Labels area
All markets show a decline in sales.
Final thoughts
The data is random and from 2005-01-01 to 2011-11-05. That is why sales decline in quarter 4, 2011.
Recommended articles
In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]
Use hyperlinks in a pivot table
Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]
Change PivotTable data source using a drop-down list
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
How to create a dynamic pivot table and refresh automatically
This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete […]
To be able to use a Pivot Table the source data you have must be arranged in way that a […]
Count unique distinct records (rows) in a Pivot Table
Excel 2013 allows you to count unique distinct values in a pivot table, this article explains how to use a […]
This article demonstrates how to build a calendar in Excel. The calendar is created as a Pivot Table which makes […]
How to calculate totals of stock transactions based on dates
Did you know that you can use a pivot table to summarize portfolio holdings at any point in time? If you trade […]
Count unique distinct values in an Excel Pivot Table
ExcelBeginner asks: I have a small problem that I am not sure on how to solve. I now have a […]
Disable autofit column widths for Pivot table
I read this interesting article Quick Trick: Resizing column widths in pivot tables on the Microsoft Excel blog. It is […]
2 Responses to “Analyze trends using pivot tables”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Sir,
can you please let me know how to represent this data in pie chart.
[…] highly recommend reading this blog post: Analyze trends using pivot tables, there is also a quick introduction to pivot […]