Dynamic Charts Archives - Page 6 of 6

In Using Pivot Table Data for a Chart with a Dual Category Axis, I showed how to make a Chart with a Dual Category Axis using a pivot table to properly arrange the source data. I generally prefer using a regular chart, because pivot charts are pretty inflexible when it comes to formatting. Unfortunately, a regular chart doesn’t know when the pivot table has been resized by a refresh. If you know that the pivot table will only grow in one direction, that is, the number of series remains the same but the number of points in a series may change, you can define dynamic ranges that stretch with the pivot table.

The data initially looks like this:

and the pivot table like this, with the top left corner of the pivot table in cell F2:

Let’s define the names according to this table:

Name	Refers To
‘PT Names’!PTDefectCount	=COUNT(‘PT Names’!$D:$D)
‘PT Names’!PTData	=OFFSET(‘PT Names’!$A$1,0,0,1+’PT Names’!PTDefectCount,4)
‘PT Names’!PTCatList	=’PT Names’!$F$3
‘PT Names’!PTCat1	=’PT Names’!$H$3
‘PT Names’!PTCat2	=’PT Names’!$I$3
‘PT Names’!PTCat3	=’PT Names’!$J$3
‘PT Names’!PTCategories	=OFFSET(‘PT Names’!PTCatList,1,0,’PT Names’!PTDefectCount,2)
‘PT Names’!PTValues1	=OFFSET(‘PT Names’!PTCat1,1,0,’PT Names’!PTDefectCount,1)
‘PT Names’!PTValues2	=OFFSET(‘PT Names’!PTCat2,1,0,’PT Names’!PTDefectCount,1)
‘PT Names’!PTValues3	=OFFSET(‘PT Names’!PTCat3,1,0,’PT Names’!PTDefectCount,1)

Note that all names are defined locally for the worksheet containing the data and pivot table. PTDefectCount is the number of rows of data in the original table and in the pivot table. PTData is the data range used as the pivot table’s source. PTCatList is an anchor for the PTCategories name, which in turn encompasses the two columns of main and sub categories, the category labels for the chart. PTCat1 through 3 are the names of the three series in the chart, and the anchors for PTValues 1 through 3, the Y value ranges for the three series.

The chart is constructed like that in Using Pivot Table Data for a Chart with a Dual Category Axis. The three series formulas are:

=SERIES(‘PT Names’!PTCat1,’PT Names’!PTCategories,’PT Names’!PTValues1,1)
=SERIES(‘PT Names’!PTCat2,’PT Names’!PTCategories,’PT Names’!PTValues2,2)
=SERIES(‘PT Names’!PTCat3,’PT Names’!PTCategories,’PT Names’!PTValues3,3)

Regular Dual Cat Axis Chart from Pivot Table Data

This looks like the previous chart, but it’s now dynamic, ready to change as the data table and its pivot table are updated. Add some rows to the pivot table source data range:

The name PTData expands to include the added rows. Refresh the pivot table:

The chart data ranges also expand, and the chart reflects the added data:

Dynamic Dual Cat Axis Chart from Pivot Table Data

The Mechanical category has moved to the left of Electrical, because its total is now highest. The Category field in the column area of the pivot table has been changed so it is not sorted by defect count. Therefore the series are plotted in the same order (electrical is first), and the colors of the series do not change.

This technique works because I know that the number of columns in the pivot table will not change, only the number of rows. If there is a chance that the number of main categories will change, then this technique will not work, because a dynamic name can only be used to define the X or Y values of a chart series, or its name. A dynamic name cannot be used to specify the number of series in the chart.

Note: In a chart that is not based on a pivot table, a dynamic name can be defined that grows in height and width, and such a name can be used for the chart’s entire source data range (X values, Y values for all series, and all series names). There are two shortcomings with this approach. When you use a name to define the source data range, excel replaces the name by its cell address, and you lose the benefit of having defined a dynamic name. Also, if you define the source data for a chart, and the range includes a pivot table, then the pivot table becomes the data source, and the chart becomes a pivot chart.

Third in a series

Chart with a Dual Category Axis
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

Dynamic ranges (or “Names”) are commonly used to identify a range of the appropriate length, so that a chart will plot the right number of points. For example, you may want to plot year to date sales, without blanks for the months which are still in the future. The dynamic range uses COUNT or similar function to determine how many cells to include. As you add data, the range expands, and the chart automatically adjusts, so it plots all the months to date, but no months beyond that.

You can also use dynamic ranges if you don’t know what column your data is in. You can use MATCH and similar functions to determine which column has a particular header, and selectively plot the data in that column.

I was once given the following task:

We have two systems that produce similar data and need to chart both in a similar manner. However, the exact columns are not the same in each system’s output files and may even move from time to time. I need to do the following:
Find the columns containing “Elapsed Time”, “Azimuth”, “Elevation” and chart them with an XY chart with lines connecting the points. The column “Elapsed Time” must the the X axis, while “Azimuth” and “Elevation” are antenna pointing angles that must be plotted on the Y axis.

The following is a sample of the data, with irrelevant columns blanked for this exercise.

This can be done with dynamic ranges. Assuming the labels are in row 1 of Sheet1, here are some defined names that construct the X and Y value ranges for your chart. Press CTRL+F3 to open the Defined Name dialog, and define these names:

Name: Sheet1!ET
Refers To: =MATCH(“Elapsed Time”,$1:$1,0)

Name: Sheet1!AZ
Refers To: =MATCH(“Azimuth”,$1:$1,0)

Name: Sheet1!EL
Refers To: =MATCH(“Elevation”,$1:$1,0)

(ET, AZ, and EL are the column numbers where these labels are found.)

Name: Sheet1!Xrows
Refers To: =MATCH(1E+307,INDEX($1:$65536,1,ET):INDEX($1:$65536,65536,ET),1)

(This is the number of rows used in the Elapsed Time column)

Name: Sheet1!TheX
Refers To: =(INDEX(Sheet1!$1:$65536,2,ET):INDEX(Sheet1!$1:$65536,Xrows,ET))

Name: Sheet1!TheAZ
Refers To: =(INDEX(Sheet1!$1:$65536,2,AZ):INDEX(Sheet1!$1:$65536,Xrows,AZ))

Name: Sheet1!TheEL
Refers To: =(INDEX(Sheet1!$1:$65536,2,EL):INDEX(Sheet1!$1:$65536,Xrows,EL))

(These are the data ranges under the labels.)

Let’s go to the VB Editor’s Immediate Window to see what we’ve got:

Now use the chart wizard to create a chart. In step 2, click on the Series tab, Select or Add the first series, name it “AZ”, and enter =Sheet1!theX in the X Values box and =Sheet1!theAZ in the Y Values box. Add the second series, again enter =Sheet1!theX in the X Values box, and enter =Sheet1!theEL in the Y Values box. No matter which column has the particular labels (and the left to right alignment doesn’t matter), the dynamic ranges will find the appropriate data for the chart.