I recently wrote Dynamic Array Histogram, a tutorial showing how to build a histogram with a normal curve overlay. It worked great, except that the chart was hard-coded to the worksheet ranges with the data. When I changed the Dynamic Array formulas, the spill ranges changed size, and I had to manually adjust the chart data ranges. Not too big a deal for just a few series, but still an inconvenience.
Dynamic Array with One Column
Here is what the data looks like. Cell D7 contains the following formula, which spills the numbers from 161 to 175 into the highlighted range D7:D21.
I’ve written many tutorials about Dynamic Charts (see the list at the end of this article). Ordinarily I would generate some Names (aka dynamic range names) using a formula like this
then add the Names to the chart.
But we don’t need to use an OFFSET or other function to determine the size of the range of data. It’s a Dynamic Array, which we can reference using a hash sign (#): The entire spill range of the Dynamic Array formula in D7 is referenced by D7#.
We still need to use Names in the dynamic chart, so click Define Name on Excel’s Formulas tab, give our Name a good name (
xWeights), for Scope select
Sheet1, and use
=Sheet1!$D$7# as the Refers To formula. If you click in the Name textbox and back in the Refers To box, the range defined by the formula will be highlighted as shown.
Similarly we had Dynamic Array formulas in cells E7 and F7, which spilled into E7# and F7#. We define Names
=Sheet1!$F$7#. These are the Names we will use in our dynamic chart.
Press Ctrl+F3 to open the Name Manager and see all of the Names. As above, clicking in the Refers To box will highlight the range defined by the formula.
A note about naming Names. I use a prefix of
y for chart data which will be used as X or Y values. I used to use a prefix of
cht, but a fluky behavior in Excel 2007 and more recent versions changed that. If the name of a Name begins with
C (think Row or Column), the name cannot easily be used in the series formula. If your language of Excel is not English, you can’t use the letters that the words for Row and Column begin with in your language.
Start by selecting the range of data (D6:F21), including the header row. And note that the top left cell D7 is blank, to help Excel determine that the first column is X values. Insert a column chart, then right click on the Curve series, choose Change Series Chart Type, and select a line style.
When the chart is selected, the data range is highlighted.
Select the Count series (the blue columns). The data is highlighted, and this
SERIES formula appears in the formula bar.
This means the series name is in
Sheet1!$E$6, the X values are in
Sheet1!$D$7:$D$21, the Y values are in
Sheet1!$E$7:$E$21, and it is the first series in the chart.
Right in the formula bar, change
Sheet1!yCount. Click Enter, so the
SERIES formula becomes
The worksheet data is still highlighted.
Repeat the procedure with the Curve series, changing the formula from
Dynamic Array with Multiple Columns
In the Dynamic Array Histogram tutorial, I showed how to make a multiple-column Dynamic Array from a single formula. This makes defining our Names more complicated, but only slightly so.
The Dynamic Array formula in cell D4 spills into multiple rows and columns.
=D4# (entered into cell I4) spills into the same size range.
But I can use
INDEX to return a portion of the Dynamic Array in D4#. For example,
INDEX(D4#,1,1) returns the cell in the first row and first column of D4#.
To get the first column, I specify the row as zero, in
INDEX(D4#,0,1). I could leave out the zero altogether as long as I have the right amount of commas:
If I’d wanted just the first row, I use zero (or a blank) for the column index:
As it turns out, I could use
INDEX(D4#,,) to reference the entire array.
I define my Names as above, name:
Sheet2, refers to:
=INDEX(Sheet2!$D$4#,0,1), create and format the chart, and plug in the Names for the hard-coded addresses.
Here are more articles on the Peltier Tech blog that talk about dynamic charts.
- Dynamic Charts
- Easy Dynamic Charts Using Lists or Tables
- Dynamic Charts in Excel 2016 for Mac
- Dynamic Chart using Pivot Table and Range Names
- Dynamic Chart using Pivot Table and VBA
- Dynamic Ranges to Find and Plot Desired Columns
- Split Data Range into Multiple Chart Series without VBA
- VBA to Split Data Range into Multiple Chart Series
- Dynamic Chart Source Data (VBA)
- Dynamic Chart with Multiple Series
- Display One Chart Dynamically and Interactively