#Create pivot chart on excel for mac how to
#Create pivot chart on excel for mac code
#Create pivot chart on excel for mac series

I’ll use a routine that updates a specified chart based on the ranges within the pivot table. 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. Good, so we’ve figured out the ranges we’ll need. PivotItems('Environmental').DataRange.EntireRow).Select 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,ĪctiveSheet.PivotTables(1).PivotFields('Main Category') Gives us the four cells we want, but not in the right column. ActiveSheet.PivotTables(1).PivotFields('Main Category').PivotItems('Environmental') We only want some of those, so let’s poke around a little deeper. Trying ActiveSheet.PivotTables(1).PivotFields('Category').PivotItems('Environmental')

PivotItems('Environmental').DataRange).Select What we need is the intersection of those two ranges, or Intersect(ActiveSheet.PivotTables(1).PivotFields('Category')ĪctiveSheet.PivotTables(1).PivotFields('Main Category') We end up with the entire row of data, including those blank cells. Likewise, if we try ActiveSheet.PivotTables(1).PivotFields('Main Category').PivotItems('Environmental') We would select the entire column of environmental defect data, including the blank cells for the other categories. If we use ActiveSheet.PivotTables(1).PivotFields('Category').PivotItems('Environmental')

#Create pivot chart on excel for mac code

The code to select the environmental category label (series name) is ActiveSheet.PivotTables(1).PivotFields('Category').PivotItems('Environmental') I’ll use the approach I described in Referencing Pivot Table Ranges in VBA to identify these ranges.

#Create pivot chart on excel for mac series

Chart Source Data in the Pivot Tableīasically, 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. 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. Then we need to add the chart data one series at a time. For example, the selected range has to be nowhere near the pivot table when we insert the 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. Well, we could, but we’d have to filter the pivot table so only the information we wanted to chart was visible.Įven 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. One of the limitations of a pivot chart is that we can’t plot a subset of the data. Not bad, but our objective is to show only one category, say, Environmental.

If we just make a chart from the pivot table, here is the result. Doing this plots each category’s defect data in a different series, so each category is shown in a different format.

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. Here’s the pivot table from the dynamic chart article. It’s the same data used in the dynamic chart article cited above. The data we’re using shows causes of failure in a system, broken down by categories and types of failure.

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

#Create pivot chart on excel for mac how to

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