Software

Two ways to build dynamic charts in Excel

Users will appreciate a chart that updates right before their eyes. In Excel 2007 and 2010 it's as easy as creating a table. In earlier versions, you'll need the formula method.

If you want to advance beyond your ordinary spreadsheet skills, creating dynamic charts is a good place to begin that journey. The key is to define the chart's source data as a dynamic range. By doing so, the chart will automatically reflect changes and additions to the source data. Fortunately, the process is easy to implement in Excel 2007 and 2010 if you're willing to use the table feature. If not, there's a more complex method. We'll explore both.

The table method

First, we'll use the table feature, available in Excel 2007 and 2010-you'll be amazed at how simple it is. The first step is to create the table. To do so, simply select the data range and do the following:

  1. Click the Insert tab.
  2. In the Tables group, click Table.
  3. Excel will display the selected range, which you can change. If the table does not have headers, be sure to uncheck the My Table Has Headers option.
  4. Click OK and Excel will format the data range as a table.

Any chart you build on the table will be dynamic. To illustrate, create a quick column chart as follows:

  1. Select the table.
  2. Click the Insert tab.
  3. In the Charts group, choose the first 2-D column chart in the Chart dropdown.

Now, update the chart by adding values for March and watch the chart update automatically.

The dynamic formula method

You won't always want to turn your data range into a table. Furthermore, this feature isn't available in pre-ribbon versions of Office. When either is the case, there's a more complex formula method. It relies on dynamic ranges that update automatically, similar to the way the table does, but only with a little help from you.

Using our earlier sheet, you'll need five dynamic ranges: one for each series and one for the labels.  Instructions for creating the dynamic range for the labels in column A follow. Then, use these instructions to create a dynamic label for columns B through E. To create the dynamic range for column A, do the following:

  1. Click the Formulas tab.
  2. Click the Define Names option in the Defined Names group.
  3. Enter a name for the dynamic range, MonthLabels.
  4. Choose the current sheet. In this case, that's DynamicChart1. You can use the worksheet, if you like. In general, it's best to limit ranges to the sheet, unless you intend to utilize them at the workbook level.
  5. Enter the following formula: =OFFSET(DynamicChart1!$A$2,0,0,COUNTA(DynamicChart1!$A:$A))
  6. Click OK.

Now, repeat the above instructions, creating a dynamic range for each series using the following range names and formulas:

  • SmithSeries: =OFFSET(DynamicChart1!$B$2,0,0,COUNTA(DynamicChart1!$B:$B)-1)
  • JonesSeries: =OFFSET(DynamicChart1!$C$2,0,0,COUNTA(DynamicChart1!$C:$C)-1)
  • MichaelsSeries: =OFFSET(DynamicChart1!$D$2,0,0,COUNTA(DynamicChart1!$D:$D)-1)
  • HancockSeries: =OFFSET(DynamicChart1!$E$2,0,0,COUNTA(DynamicChart1!$E:$E)-1)

Notice that first range reference starts with row 2. That's because there's a row of headings in row 1. The second set of references refers to the entire column, enabling the formula to accommodate all values in the column, not just a specific range. The addition of the -1 component eliminates the heading cell from the count. The first formula (for the labels in column A) doesn't have this component.

It's important to remember that you must enter new data in a contiguous manner. If you skip rows or columns, this technique won't work as expected.

You might be wondering why I added the Series label to each range name. Using the name, alone, will confuse Excel. The series headings in row 1 are also names. Because the chart defaults will use the label headings in each column for each series name, you can't use those labels to name the dynamic ranges. Don't use the same labels for both your spreadsheet headings and your dynamic range names.

Next, insert a column chart, as you did before. If you enter new data, the chart won't yet reflect it. That's because the chart, by default, references a specific data range, DynamicChart1:A1:E3. We need to change that reference to the dynamic ranges we just created, as follows:

  1. In the chart, right-click any column.
  2. From the resulting submenu, choose Select Data.
  3. In the list on the left, select Smith and then click Edit. (Remember the naming conflict I mentioned? Excel uses the column heading (cell B1) to name the series.)
  4. In the resulting dialog, enter a reference to Smith's dynamic range in the Series Values control. In this case, that's =DynamicChart1!SmithSeries.
  5. Click OK.

Repeat the above process to update the remaining series to reflect their dynamic ranges: DynamicChart1!JonesSeries; DynamicChart1!MichaelsSeries; and DynamicChart1!HancockSeries.

Next, update the chart's axis labels (column A), as follows:

  1. In the Select Data Source dialog, click January (in the list to the right).
  2. Then, click Edit.
  3. In the resulting dialog, reference the axis label's dynamic range, DynamicChart1!MonthLabels.
  4. Click OK.

You don't have to update February; Excel does that for you. Now, start entering data for March and watch the chart automatically update! Just remember, you must enter data contiguously; you can't skip rows or columns.

This formula method is more complex than the table method. Be careful naming the dynamic ranges and updating the series references. It's easy to enter typos. If the chart doesn't update, check the range references.

For a dynamic chart technique that takes a different route, read Create a dynamic Excel chart and make your own dashboard. Two example Excel worksheets demonstrating these versions of dynamic charting are available as a free download.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

18 comments
jasmith4
jasmith4

With the table method you can dynamically add both categories and series -- great!  Is there any way with the formula method for adding BOTH categories AND series, e.g., by inserting rows AND columns?  I don't want to have to define a range name for every series.  What do I do?

stacy365
stacy365

It's good practice for data and charts to be on separate page. When I try the offset method with data on a different page, I get an error that says the page must be open. 

PKlug57
PKlug57

Is there a way to use the offset method when the data being updated is horizontal  - using rows and not columns? I cannot figure out how to change the formula to refer to data going across and not down.

rochekil
rochekil

Can I create a dynamic chart in excel by incorporating the "networkdays" function?

chip_long
chip_long

The dynamic formula method is great. I've been playing with this and trying to figure it out on a banking spreadsheet I maintain. The problem is, I've been maintaining the sheet for over 2 years now, so I have a lot of data in it. I don't want to lose the old data, but I don't always want to see the old data in my charts. I was able to create the lists using the offset, but I could never use them in the tables. It turns out the only thing I was doing wrong was entering the names without a sheet reference. By the way, here are the formulas I used to select the data for the charts: DateSeries: OFFSET(Sheet1!$A$1,StartDateRowNum-1,0,EndDateRowNum-StartDateRowNum+1) PaymentSeries: OFFSET(Sheet1!$C$1,StartDateRowNum-1,0,EndDateRowNum-StartDateRowNum+1) PostedSeries: OFFSET(Sheet1!$D$1,StartDateRowNum-1,0,EndDateRowNum-StartDateRowNum+1) StartDateRowNum: MATCH(StartDate,OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A)),0) EndDateRowNum: MATCH(EndDate,OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A)),0) The StartDate and EndDate are named cells that contain the statement dates for the range. I always started my offset at the first cell in the row. Then I would define a row number after that to start the data. This proved easier than trying to do an "indirect" and "address" function to try to set the starting point.

Ole88
Ole88

I can see applications for this already. I have a current project kicking off that I could use this for until I get the web application built for it. Thanks Susan!

HardwareEng
HardwareEng

I discovered that the table was not created correctly, so I guess I didn't follow Susan's instructions to the letter after all. Thanks for the help!

HardwareEng
HardwareEng

As a hardware engineer, I guess I must be daft. I tried using the table method several times and followed the instructions to the letter, but when I added the March row and data, the chart did not dynamically update. I looked for a setting in Office/Excel 2010 options, but could find nothing. Is it a setting?

RU7
RU7

I just use a mixed (absolute and relative) range for the series. For example, the first series when you have 3 rows of data could be DynamicChart1!$A$2:$A5 (no dollar sign for the 5). I start out with one more row in the range than I actually have of data. I leave the last row blank or indicate that it is a place holder. To add more data I select the place holder row and insert a new one above it. to eliminate any ill effect from having the last (non-data) row included in the chart you can use zero or functions like MAX($A$2:$A4), MIN($A$2:$A4), or AVERAGE($A$2:$A4), depending on the chart type and how it is used.

liljim
liljim

The accompanying Excel spreadsheets are wrong. The series for Michaels and the series for Hancock are identical. Hancock should be the series adjacent to Michaels. Other than that one error, this is a very useful article. Thank You.

VISHNARAIN
VISHNARAIN

I have a simple main form which derives the value from a another form's combo box. The main form has a subform . I have a conditional macro on the subform "On Open" event which works when I open the subform separately but when the Main form is opened through the combo form I get an error "that the control name could not be found/ it is mis spelt .... " Why does the Main form not read the sub form control name or the macro of the sub form?

TMgHav
TMgHav

@PKlug57 I also have the same question but with no answer so far.  Have you got a solution yet?  If so, could you please let me know?

ssharkins
ssharkins

Dynamic charting has tremendous potential for creating user-friendly apps that return good visual information for little effort.

jody.burton
jody.burton

Excel > Options > Proofing Click the "AutoCorrect Options" button In the dialog box that pops up, select the second tab, "AutoFormat As You Type" Make sure the box for "Include New Rows and Columns in Table" is checked Susan's table method worked fine for me (XP/Excel 2010)

ssharkins
ssharkins

I just sent corrected workbook demos to the editor -- they'll be available soon. The fix is easy, in the meantime. Just delete MichaelsSeries and recreate it.

ssharkins
ssharkins

That error message means it can't find the control you've referred to. Since you're working with a main/sub form configuration, my best guess is that you've not used the proper syntax to refer to a control outside the current form. http://support.microsoft.com/kb/113352

ssharkins
ssharkins

Effort on the user's part -- not yours, but fortunately, it isn't a difficult technique to implement. Just requires knowing your data and a little imagination.

ssharkins
ssharkins

Jody is correct -- there is an option that might impact this behavior and Jody's instructions are perfect. By default, this option is enabled, so I didn't think to mention it. I should have and I apologize for the inconvenience. Just remember, changing these settings can have unintended consequences -- someone disabled it for a reason most likely!

Editor's Picks