Excel Tip: PivotCharts | Click Consulting

PivotCharts are dynamic charts, based on the solid foundation of PivotTable technology. They are used when we want to create a dynamic chart. Dynamic charts are best applied when either (a) the underlying data is frequently changing or (b) we want to quickly and easily �play� with the chart organization and presentation. An additionally powerful fact of PivotCharts is their ability to perform aggregate functions on the underlying data, thus saving us steps. For example, the PivotChart can sum all transactions by month. In a traditional chart, we would need to first prepare the data such that there is one row per month.

PivotCharts

PivotCharts are dynamic charts, based on the solid foundation of PivotTable technology.

PivotCharts are best used when we want to create a dynamic chart. Dynamic charts are best applied when either (a) the underlying data is frequently changing or (b) we want to quickly and easily “play” with the chart organization and presentation.

An additionally powerful fact of PivotCharts is their ability to perform aggregate functions on the underlying data, thus saving us steps. For example, the PivotChart can sum all transactions by month. In a traditional chart, we would need to first prepare the data such that there is one row per month.

In our example, we would like to analyze sales data. There are many variables to our sales data, and we would like the ability to quickly and easily see them. Thus, we decide that a Pivot Chart is just the answer to this project. A sample of the sales data is shown in Figure 269 below.

Figure 269

Firstly, we would like to see sales over time, grouped by month. This can quickly be performed by selecting Data -> Pivot Table or Pivot Chart. You will be presented with step 1 of the wizard, as shown in Figure 270 below.

Figure 270

Select PivotChart and click Next. Step 2 (shown in Figure 271) asks you to identify the Range of data. Highlight the range of data you would like to chart, and click Next.

Figure 271

The last step of the wizard asks about where you want to put the chart. In our case, and new worksheet is fine so click Next, as shown in Figure 272 below.

Figure 272

Now, similar to creating Pivot Tables, the objective is to the drag and drop the field elements into one of 4 locations on the chart: Category, Series, Data, and Page, as shown in Figure 273 below.

Figure 273

For our first chart, looking at sales amount by month, we would simply drag the Amount item into the Data range, and the Date item to the Category range, as shown below. The initial chart shows the x axis with one data point per date as shown in Figure 274 below.

Figure 274

However, we would like our data grouped by Month. Thus, right click the Data field item and select Group. The Grouping dialog box will open, as shown in Figure 275 below.

Figure 275

After selecting “Months” and clicking OK, the Pivot Chart aggregates the amounts and plots the sums by month, as shown in Figure 276 below.

Figure 276