Charting data is an easy task and reusing a chart is efficient! By reusing, I mean to use the same chart object to chart multiple sources. In other words, I’m going to show you how to create a dynamic chart. They’re common in dashboards and you might be surprised to learn how easy they are to implement.

Instead of launching right into the technique, let’s take a quick high-end look at what this technique entails, because it has several steps:

  1. Add a dynamic column to the data range using a HLOOKUP() function. This column will be the chart’s real source, not the data range.
  2. Insert a simple chart.
  3. Add a scroll bar control that lets the user easily update the chart’s source, without really knowing what’s going on behind the scenes.

Now, let’s chart the data shown below. Specially, we’ll create a simple bar chart that displays the sales for each salesperson, by year. Ordinarily, you might think that you need four different charts, but that’s where our dynamic solution comes in- we’ll use the same chart object to show all four years.

In the first step, add a column of HLOOKUP() functions, as follows:

  1. In cell F2, enter a year from the headings. For instance, enter 2009.
  2. In cell F3, enter the following function: =HLOOKUP($F$2,$B$2:$E$6,2). Be sure to note the absolute addresses—they’re vital!
  3. Copy the function to cells F4:F6. The functions in column F return the appropriate values for the year identified in F2. In the following sheet, that’s 2009.
  4. In the copied formulas, update the last argument from 2 to 3, 4, and 5, respectively. (F4 is 3, F5 is 4, and F6 is 5.)

The next step is to insert a chart based on the personnel values in column A and the source column (F), as follows:

  1. Select A3:A6.
  2. Hold down [Ctrl].
  3. Select F3:F6. You just created a non-contiguous selection.
  4. Click the Insert tab.
  5. In the Charts group, click the Bar dropdown.
  6. Choose the first 2-D cluster bar chart option.
  7. In the resulting chart, delete the legend – just click it and press Delete. You don’t need it.

Changing the year value in F2 updates the chart. Go ahead and try it. Enter the year values 2008 through 2011 and watch the chart update automatically!

As is, it’s a handy chart, but users won’t know to update the value in F2. You need to provide a more intuitive method. We’ll use a scroll bar Form control, as follows:

  1. Click the Developer tab.
  2. In the Controls group, click the Insert dropdown.
  3. Click Scroll Bar (third control on the second line).
  4. Click inside the sheet to insert the Form control.
  5. Right-click the newly inserted control and choose Format Control.
  6. Click the Control tab (if necessary).
  7. Enter the following values: 2008, 2008, 2011, and F2 as the linking cell (as shown below).
  8. Next, drag the current year value in F2 and drop it near the scroll bar (see below as a guide). This last step isn’t necessary, but doing so will help the user understand what the scroll bar is for. Reformat the linking cell if you like (I chose not to so you could easily see that I dragged the cell from F2. You’d probably want to reformat it in a production workbook.)

Simply click the scroll bar arrows to increment its values from 2008 to 2011 – those are the controls minimum and maximum values, incrementing by 1. The chart will update accordingly. Using the scroll bar isn’t necessary, but it will make things much easier for your users, especially if you separate the chart from the actual data range in a dashboard type setting.

This workbook, for use as an example, is available for download.