Pivot Table Chart Data Sourcing

Introduction

In the last post we created a dynamic table using the COUNTIFS function to update Chart 1 of our Garden Bird Tracker with the count of each species by day across the current week: 

In this post, we're going to change the Chart 1 source data to a Pivot Table, and take a look at the advantages and disadvantages of this approach in a project with a simple data-set.

Creating a Pivot Table

To create a Pivot Table, the data must be arranged logically, with each column having a header, and there must be no gaps across the range of columns. Blank rows are not a problem and can be filtered out of the Pivot Table later.

To start, place the your cursor in any of the cells in the data in the Bird Tracker tab of your workbook, go to the Insert tab on the ribbon, and chooses Pivot Table:

A dialogue box will appear with the source and location options for the Pivot Table.

In the "Choose table or range" option above, you can see that the reference is "TBLTRACKER". This is because I converted the data in my Bird Tracker tab to an Excel Table, and then named it "TBLTRACKER" (an Excel table is not a Pivot table, or vice versa). There are many advantages to converting data into a named Excel table, but for our project today, we can simply select the first four column headers, and then drag down the range to a row that is well beyond the current last row of data. I would suggest going down to row 500 so that we won't need to update the source range for the Pivot table for a while:

In the location option, we are going to select Existing Worksheet, go over to out Data sheet, select any cell in a blank are of the sheet and then click OK:

You'll now see this in your Data sheet, which is the empty Pivot table template that Excel has generated:

And on the far right, the Pivot Table Field List where we can select information to be displayed:

:

As you can see, the possible option are the four column headers in out Bird Tracker tab that we selected (if Excel encountered a column with no named header when we selected our range of data, it would generate an error message and fail). We are free to drag any of the four fields into any area of the Pivot table and produce different layouts, different filter options, and count or sum the source data as we choose. To emulate the dynamic table that we created, we need the rows to show the species, the columns to show the day of the week, and the values for each row and column to be the number counted (see the lower part of the Field List panel above. In this way, the layout produced is identical, and in the order we need for Chart 1: 

Note that the date field has been added to the Pivot table filter area. In our dynamic table, the COUNTIFS function automatically calculates the week based on the date held in our Data tab, but we need to manually filter the Pivot table we have created to the date range for the current week. To do this, click on the DATE filter drop-down, and select the required dates in the same way that any filter in Excel works:

Now that we have the correct data in our Pivot table, we can change the source data for Chart 1 by right clicking in the chart area, selecting Select Data, going back to our data tab and selecting this range in the Pivot table:

Click OK, and the chart is now using the Pivot table data as a source. If  you right click and select Select Data again, you'll see the reference is no longer a range of cells, but a Pivot table:

Pivot table pros and cons

Pivot tables are extremely powerful and allow detailed analysis of large amounts of data without the need to write long or complex formulas. Using multiple Pivot tables, Pivot table slicers and Pivot charts gives you the ability to create highly detailed and interactive reports, from a broad overview right down to a single data component. Using a Pivot table based presentation in a business situation gives you the ability to answer any question about the data with a few mouse clicks.

There are some disadvantages with using Pivot tables: every time data is added to the pivot table source, the table must be refreshed to pick up the new data:

The COUNTIFS formula in our dynamic table will recalculate in real time all of the data in our Bird Tracker without intervention.

Unless the Pivot table's source data is an Excel named table, then the source data range will need to be adjusted if new rows or columns are added to the data. Converting your source data range to an Excel table solves this, but not the refresh issue. With our dynamic table, the COUNTIFS function is calculating against whole column references, regardless of how many rows those columns contain, and will never need updating.

With our Pivot table, we also need to manually filter the date field to the current week. With our dynamic table, the COUNTIFS function will automatically calculate the current week's figures, again, without any intervention.

The refresh and filter issues can be overcome by using Visual Basic for Applications (VBA) and embedding the macro in the Data worksheet so that it refreshes the Pivot table and filters the dates every time the sheet is activated. This is something that we will look at in future posts. 

The final thing to bear in mind is that large Pivot tables can be very difficult to read, so some consideration is needed when designing them. In many cases, multiple Pivot tables are preferable, and as mentioned, can be linked to one or more Pivot table slicers (Excel 2010 onwards).

I hope you enjoyed this post - if you have any questions or need any help, get in touch.

Coming up in the next post - using the SUMPRODUCT function as a COUNTIFS function, and as a bridge to Array Formulas!