You can add flexibility to your data analysis by grouping variables. For example, you may have daily sales figures that fluctuate greatly in value, some days having no sales at all, some days with small values, and some with large values. A plot of this data is very noisy, and it’s hard to see any patterns.

In a pivot table, you can group dates by various measures ranging from seconds to years, to get a clearer view of the data. Plotting by month will smooth out the noise of a daily plot.

You can combine groupings, for example, to compare monthly sales by year.

These plots are regular charts which use pivot table data as their source. This technique will be covered in a (near) future post. The charts below are the corresponding pivot charts.

Microsoft hosts a number of decent Pivot Table examples. I will use one of them for this tutorial. Go to the Microsoft example 25 easy PivotTable reports, which has a link to Excel 2002 Sample: PivotTable Reports. From this page you can download a self-extracting executable named Reports.exe, which contains four workbooks. The top few records of the Source Data worksheet of the SampleSalespersonReports.xls workbook looks like this:

To create a pivot table, select one cell within this data range, and choose Pivot Table and Pivot Chart Report from the Data menu. To keep things uncomplicated, place the pivot table onto a new worksheet. Drag the Order Date field to the rows area and the Order Amount field to the Data area. The top few rows of the pivot table looks like this:

Click in this pivot table and click the chart wizard button. The resulting pivot chart looks like this when it is relocated to the worksheet:

To group the dates, right click anywhere in the date field range, choose Group and Show Detail, then choose Group.

To group by any time period, select that period in the dialog. To group by weeks, select Days, and change Number of Days to 7. To group by month, choose Months; note that this grouping will combine data for each month regardless of year. In this example, group by both Months and Years.

The Order Date column of the pivot table is split into a Years and a Months column.

With the new grouping of months and years, the pivot chart changes to plot months and years along the category axis.

Click on the Years field button, and drag it to the columns area, where the Total label is located. This splits the data into three yearly columns, each showing monthly totals for the respective year.

The pivot chart now shows one series of monthly sales for each year.

The dynamic nature of this pivot chart illustrates one of the benefits of pivot charts: the chart continues to display the data no matter how the table is pivoted. This makes pivot charts indispensable during the early stages of data exploration. The pivot chart’s relative inflexibility when it comes to formatting and handling of arbitrary (non-pivot-related) data, make pivot charts a liability during later stages of data presentation.

In an upcoming blog entry, I’ll describe how to create regular charts like those in the beginning of this article using data from a pivot table.

For more about pivot tables, check out these pages on my web site:

• Pivot Tables and Pivot Charts
      Contributed by fellow Excel MVP Debra Dalgleish. Check out Debra’s Excel Tips page
      – Introduction to Pivot Tables in Excel
      – Working with Pivot Charts in Excel
      – Pivot Table Programming
      – Pivot Table and Pivot Chart Links
• Pivot Tables, Pivot Charts, and Real Charts (TechTrax Article)
• Books on Excel Pivot Tables
      – Excel Pivot Tables Recipe Book: A Problem-Solution Approach by Debra Dalgleish
      – Beginning PivotTables in Excel 2007: From Novice to Professional by Debra Dalgleish
      – Pivot Table Data Crunching by Bill Jelen and Michael Alexander
      – Pivot Table Data Crunching for Microsoft Office Excel 2007 by Bill Jelen and Michael Alexander
   

Possibly Related Posts:

• Regular Charts from Pivot Tables
• Referencing Pivot Table Ranges in VBA
• Explore Your Data With Pivot Tables
• Pivot Table Conditional Formatting with VBA
• Preliminary Data Exploration with Excel Pivot Tables
• Date Axis with Centered Years
• Using Pivot Table Data for a Chart with a Dual Category Axis
• Dynamic Chart using Pivot Table and Range Names
• Dynamic Chart using Pivot Table and VBA
• Update Regular Chart when Pivot Table Updates