Here is an example of a dynamic chart that changes based on the selected cell. Move up or down to see more data points for a category, move sideways to switch between categories. Done with a SelectionChange macro and dynamic ranges.

You may know about dynamic charts and how to construct them. You may even have done a few yourself. Most often, dynamic charts involve some kind of parameter selection and one or more dynamic ranges that feed the chart series to automatically display something like the last few rows of a data set. In this article, you can read how to construct a dynamic chart that changes its data when you move the cell selection in the underlying data table.

Let’s start out with some data. We have daily readings for several categories of fruit:

Daily data for several categories

The goal is to create a chart that shows the values for one fruit category over a number of days. Something like this:

Creating a chart like this is fairly simple. Select the data, column A for the X axis, one of the fruit columns for the Y axis, create a column chart and do some basic formatting to get rid of the Excel defaults, especially the ugly dark grey background in Excel 2003.

Now the  goal is that if the user wants to view another fruit category, there should be no drop-down boxes or check boxes to select. Simply using the arrow keys to move around the data table should change the chart display.

OK. Here’s how it’s done. We need:

• three fixed named ranges
• a little macro
• a helper column
• two dynamic ranges

Fixed named ranges: In the attached sample file range names have been assigned to these cells:

L2 = cCol — the active cell’s column number
L3 = cRow — the active cell’s row number
L4 = cRows — a count of the populated rows in the data table.

Leave L3 and L3 empty. They will be filled by the macro created in the next step. In L4, we need to count how many rows of data we have. This will be based on column K, so the formula is:

=COUNTA(K:K)

A macro to record the current cell: In order to fill column K with the data for the series to be charted, we need to determine which column currently hosts the active cell, i.e. the cell we clicked last or moved to with the arrow keys.

This can be done by creating a small macro in the sheet module of the active worksheet.  Hit Alt-F11 to open the Visual Basic Editor, find the VBA project for the workbook and double-click Sheet1 in the left hand navigation pane. Then enter this code into the code window:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   'only change the cCol value if selected cell is in data range
   If Target.Column > 2 And Target.Column < 10 Then
      Range("cCol").Value = Target.Column
   End If
   Range("cRow").Value = Target.Row
End Sub

The macro assesses the active cell whenever the cell selection changes, i.e. whenever you move the cell highlight with the enter or arrow keys. If the active cell is between columns 2 and 9 (that’s where our fruit categories are) then the column number will be written into the cell called cCol (we defined L2 with that name above. Also,  the current row number will be written into a cell called cRow (which we assigned to L3 above).

Helper Column: Leaving column J empty we use column K as a helper column to hold the values of the data series that we want to chart. The macro we’ve just entered provides us with the column index of the active column. This value can now be used to pull the data of that column into column K. The formula for K1 is:
=INDEX(A1:I1,1,cCol)

This formula looks at the range from A1 to I1 and returns the nth cell in the range, where n is the value in the cell cCol. If cCol holds a 4, then the 4th cell in the range will be returned, i.e. cell D1. The formula needs to be copied down all rows of the data table. Now, whenever the active cell changes to a different column, the data in column K will recalculate and show the data of the column where the active cell is.

We can now easily construct a chart based on column K and see the chart change whenever a different column is selected.

The added twist now is to also influence how many data points, i.e. rows of data,  are displayed in the chart. For this, we use

Two dynamic ranges: The goal is to chart only the values from the current cell and below, so we need to define a chart range that does not include the whole of column K.

In Excel 2003 click Insert – Name – Define. In Excel 2007 and later click Define Name on the Formulas ribbon. Enter the name “DateRange” (without the quotes) into the Name field and the following formula into the Refers To field
=INDEX(Sheet1!$A:$A,cRow,1):INDEX(Sheet1!$A:$A,cRows,1)

Define a second range name as ChartRange with the formula
=INDEX(Sheet1!$K:$K,cRow,1):INDEX(Sheet1!$K:$K,cRows,1)

Let’s look at the ChartRange formula: the first INDEX function looks at the whole of column K and returns the row specified by the value in cRow. The second INDEX function looks at the whole of column K and returns the last populated cell in that column (from the named range cRows in L4 defined above). So, if our active cell is in G26, column K will have the values of column G, and the ChartRange will be K26:K33 in the attached data sample.

The DateRange is calculated with the same principle to provide the correct X axis labels.

Putting it all together – As a last step, we now need to define the source data for the chart and replace any hard-coded ranges with the range names. One way is to click the chart series and edit the formula in the formula bar to be:
=SERIES(Sheet1!$K$1,’chart by selected cell.xls’!dateRange,’chart by selected cell.xls’!ChartRange,1)

Another way is to edit the source data (Excel 2003: click the chart, then click Chart – Data source – Series tab. For Excel 2007: click the Design Tab in the Chart Tools, then Select Data – click the series – click Edit) and enter the “=Sheet1!DateRange” as the X axis definition  and “=Sheet1!ChartRange” in the data values field. Make sure that the series name is defined as “=Sheet1!$K$1”, since that cell automatically updates with the name of the data series currently selected.

If this was all a bit over your head, don’t worry. Just download the attached file and let your fingers do some walking. You can copy and paste your own data into the worksheet. If you insert or delete columns, you need to adjust the macro to specify which columns hold your categories. Adding or deleting rows won’t affect the performance, since the data range is calculated automatically.

The attached file was created in Excel 2010 and has been saved in Excel 2003 compatible format.

chart by selected cell.xls