In Referencing Pivot Table Ranges in VBA I showed how to identify certain sections of a pivot table using VBA. In Dynamic Chart using Pivot Table and VBA I showed how to update a non-pivot chart when a pivot table updates.

How about some code that makes a chart based on only part of a pivot table, and updates it on demand?

## The Pivot Table

The data we’re using shows causes of failure in a system, broken down by categories and types of failure. It’s the same data used in the dynamic chart article cited above.

Here’s the pivot table from the dynamic chart article. The duplicate columns Main Category and Category were needed in order to have the categories (electrical, mechanical, etc.) in both the row area and column area of the pivot table. Doing this plots each category’s defect data in a different series, so each category is shown in a different format.

If we just make a chart from the pivot table, here is the result. Not bad, but our objective is to show only one category, say, Environmental.

One of the limitations of a pivot chart is that we can’t plot a subset of the data. Well, we could, but we’d have to filter the pivot table so only the information we wanted to chart was visible.

Even if we selected only the data we wanted to plot, Excel would automatically expand the data range to the extent of the pivot table, and hand us a pivot chart.

We can still plot only part of the pivot table in a regular chart, but we need to take some special measures, as described in Making Regular Charts from Pivot Tables. For example, the selected range has to be nowhere near the pivot table when we insert the chart. Then we need to add the chart data one series at a time. Any attempt to enter a portion of the pivot table in the top portion of the Select Data Source dialog will convert the chart to a pivot chart and convert the source data range to the entire pivot table.

## Chart Source Data in the Pivot Table

Basically, we need the subcategories of the environmental category for our X axis labels, we want the corresponding defect totals for our Y values, and we need the environmental category label for the series name.

I’ll use the approach I described in Referencing Pivot Table Ranges in VBA to identify these ranges.

The code to select the environmental category label (series name) is

```
ActiveSheet.PivotTables(1).PivotFields("Category").PivotItems("Environmental")
.LabelRange.Select
```

Very good, the label is selected. One part done, the easy part.

If we use

```
ActiveSheet.PivotTables(1).PivotFields("Category").PivotItems("Environmental")
.DataRange.Select
```

we would select the entire column of environmental defect data, including the blank cells for the other categories.

Likewise, if we try

```
ActiveSheet.PivotTables(1).PivotFields("Main Category").PivotItems("Environmental")
.DataRange.Select
```

we end up with the entire row of data, including those blank cells.

What we need is the intersection of those two ranges, or

```
Intersect(ActiveSheet.PivotTables(1).PivotFields("Category")
.PivotItems("Environmental").DataRange,
ActiveSheet.PivotTables(1).PivotFields("Main Category")
.PivotItems("Environmental").DataRange).Select
```

Perfect. Now for the category labels. Trying

```
ActiveSheet.PivotTables(1).PivotFields("Category").PivotItems("Environmental")
.DataRange.Select
```

gives us all of the subcategories.

We only want some of those, so let’s poke around a little deeper.

```
ActiveSheet.PivotTables(1).PivotFields("Main Category").PivotItems("Environmental")
.LabelRange.Select
```

gives us the four cells we want, but not in the right column.

So let’s use the interesection of the column of subcategory labels and the entire row of environmental data:

```
Intersect(ActiveSheet.PivotTables(1).PivotFields("Subcategory").DataRange,
ActiveSheet.PivotTables(1).PivotFields("Main Category")
.PivotItems("Environmental").DataRange.EntireRow).Select
```

Good, so we’ve figured out the ranges we’ll need.

## Let’s Write Some Code

Now that we know what we’re starting with (a pivot table), how much of it we’ll need (the chart data ranges we divined above), and the output (a chart), it’s time to write some code.

I’ll do this in two parts. I’ll use a routine that updates a specified chart based on the ranges within the pivot table. I’ll write a routine that makes sure that the selected range doesn’t overlap with a pivot table and insert a new chart, then call the first routine to populate the chart. Finally I’ll write a short routine that calls the update chart routine, specifying the active chart.

The following routine accepts a chart from whatever code calls it, defines the various ranges it needs, uses the addresses of these ranges to construct the series formula, then clears the data out of the specified chart and replaces it with a series that uses this formula.

```
Sub UpdateChart(cht As Chart)
Dim rXVals As Range
Dim rYVals As Range
Dim rName As Range
Dim sSrsFmla As String
Dim PT1 As PivotTable
On Error GoTo LeaveThisMess
' define chart data ranges
Set PT1 = ActiveSheet.PivotTables(1)
Set rName = PT1.PivotFields("Category").PivotItems("Environmental").LabelRange
Set rXVals = Intersect(PT1.PivotFields("Subcategory").DataRange, _
PT1.PivotFields("Main Category").PivotItems("Environmental") _
.DataRange.EntireRow)
Set rYVals = Intersect(PT1.PivotFields("Category").PivotItems("Environmental") _
.DataRange, PT1.PivotFields("Main Category").PivotItems("Environmental") _
.DataRange)
' write series formula
sSrsFmla = "=series(" & rName.Address(, , , True) & "," & _
rXVals.Address(, , , True) & "," & rYVals.Address(, , , True) & ",1)"
With cht
' clear out chart data
Do
If .SeriesCollection.Count = 0 Then Exit Do
.SeriesCollection(1).Delete
Loop
' add brand new series with data we've defined
With .SeriesCollection.NewSeries
.Formula = sSrsFmla
End With
End With
LeaveThisMess:
End Sub
```

The following routine moves the active cell to the bottom right cell of the sheet, where hopefully there won’t be any pivot tables. I could do more checking, but I won’t. Then the sheet is scrolled up to the top left cell.

It disables screen updating while it does this, so the user doesn’t get a case of vertigo.

The routine inserts a chart in the default position (centered in the window), makes it a clustered column chart, then sends the chart to the routine above to be populated for the first time.

```
Sub CreateChartFromPartialPivotTable()
Dim cht As Chart
Application.ScreenUpdating = False
On Error GoTo BailOut
' make sure active cell isn't in pivot table
' if it were, the chart would be a pivot chart
With ActiveSheet
.Cells(.Rows.Count, .Columns.Count).Select
End With
With ActiveWindow
.ScrollColumn = 1
.ScrollRow = 1
End With
ActiveSheet.Shapes.AddChart
Set cht = ActiveSheet.ChartObjects(1).Chart
cht.ChartType = xlColumnClustered
UpdateChart cht
ActiveSheet.Cells(1, 1).Select
BailOut:
Application.ScreenUpdating = True
End Sub
```

This last routine makes sure a chart is selected, and if so, calls the first routine to repopulate it.

```
Sub UpdateActiveChart()
If Not ActiveChart Is Nothing Then
UpdateChart ActiveChart
End If
End Sub
```

Fairly elegant for something so quick and dirty.

## Finally, The Chart

And oh yeah, here’s the chart. I’ve shown it next to the pivot table with the plotted series selected, so you can see the highlighted ranges used to plot the data. Environmental defect totals as Y values (blue highlights), environmental subcategories as X values (purple highlights), and Environmental label as series name (red highlight).

Sobuj says

Thank you so much. This is just what I was looking for! Just wondering, can you please post a sample worksheet of this for better understanding.

Michael T says

Couldn’t you have done this by filtering the table so that only rows with Main Category=’Electric’ are used in the table. Ah, yes, but this would change the shape of the pivot table, which the user may not want…..

Jon Peltier says

Michael –

There are many methods to skin a cat, and each method has pros and cons in a given situation. The easiest thing to do might be to have a pivot table showing everything, then a second one showing only the category of interest, with a pivot chart based on the second pivot table.

Rudra says

I am getting an error as

“1004:Unable to get the PivotFields property of the PivotTable class” when I use following code:

ActiveSheet.PivotTables(1).PivotFields(“Category”).PivotItems(“Environmental”).DataRange.Select

Note:

My data is exactly as shown in the example.

Jon Peltier says

Make sure the pivot field and pivot item names are exactly as shown. “Category”, not “Categories” or “Category “. And make sure the pivot fields items are visible in the pivot table, and not filtered out.

Also make sure you’re using the Tabular report layout.

Jim says

I achieve the same effect by using formulae to define named ranges and then plotting those

If this is better or not, I have no idea, but at least I understand it

Got here from your article “Making Regular Charts from Pivot Tables”

I was trying named ranges for that too – thanks for putting me right

Jon Peltier says

Jim –

There are numerous ways to perform almost any task in Excel. What’s best is what works at the time.

STUART says

That’s Great. I was looking for something very similar. My pivot table has 3 columns. MONTH, PLAN and ACTUAL. Plan and Actual are both shown as Cummulative.

This is September, and the pivot chart therefore plots the same ACTUAL value for Sep, Oct, Nov, Dec.

In the Picot chart i want to plot the PLAN for att 12 months, but the actual only up to the current month.

It has to be done via a Pivot table so as we can drill down to the underlying data.

Can you help?

Thanks

Stuart

Jon Peltier says

Stuart –

First of all, any regular chart or pivot chart made from a pivot table will have its data changed around when you pivot the table. So unless you’re only filtering the overall results you probably should chart the data from a second copy of the pivot table.

Second, you may want to have a column beside the pivot table for the actual, with a formula like =IF(F4=F3,NA(),F4) where F4 is this month’s cumulative total and F3 is last month’s.

Then construct the chart as described in this article, Start with a blank cell not in or even touching the pivot table and add the series one by one using the bottom half of the Select Data dialog.