We all know that to make a chart we must specify a range of values as input. But what if our range is dynamic and keeps on growing or shrinking. Wouldn’t it be cool if the ranges were dynamic and charts get updated automatically when you add (or remove) rows?

Question: How do I create a chart that dynamically updates the values, as i type them in the workbook?

Answer: Excel 2007 (and later versions) users, create an excel defined table. Excel 2003 (and earlier versions) users, create a dynamic named range. See links below. They are all links to this webpage and helps you to navigate this page.

What you can’t do with an excel defined table is to graph for example only the most recent 12 months data. Here you need to rely on named ranges in both excel 2003 and 2007.



Dynamic named range -> Dynamic chart (Works in all Excel versions)

This animated picture demonstrates how a dynamic named range  automatically adds new values to the excel chart, as they are typed.

Let´s say you created this basic chart below. Now you need to setup named ranges, they expand automatically whenever new data is added.

Excel 2003 instructions:

  1. From the menu bar, click on “Insert”
  2. From the drop-down menu select “Name”
  3. Click Define
  4. Name it “months”
  5. In “Refers to:” text box, type =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
  6. Click OK

Repeat above steps and create a named range named “numbers”. Use this named range formula:

=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$A:$A),1)

The named range formula is made assuming your data begins in cell A1 and that there are no headers.

Create a chart

  1. From the menu bar, click Insert and Chart
  2. Select a column chart
  3. Click Next
  4. Go to Series tab
  5. Add a series
  6. Type in values: =Sheet1!months
  7. Add another series
  8. Type in values: =Sheet1!numbers
  9. Click Finish


Make a dynamic chart for the most recent 12 months data

The following instructions are made in excel 2007. If you are an excel 2003 user, first read excel 2003 instructions above to understand how to create named ranges in excel 2003.

Create two named ranges

  1. Go to tab “Formulas”
  2. Click “Define name”
  3. Name it “recentmonths”
  4. Type “=OFFSET(‘recent 12 months’!$A$1,COUNTA(‘recent 12 months’!$A:$A)-12,0,12)” in “Refers to:”
  5. Click ok!
  6. Click “Define name”
  7. Name it “recentvalues”
  8. Type “=OFFSET(‘recent 12 months’!$B$1,COUNTA(‘recent 12 months’!$A:$A)-12,0,12) ” in “Refers to:”
  9. Click ok!

Insert chart

  1. Go to tab “Insert”
  2. Click “Column chart” button
  3. Click “Clustered column chart” button
  4. Right click on empty chart
    Dynamic chart - recent 12 months
  5. Click “Select Data…”
  6. Click “Add” button
  7. Type in “Series values:” =Sheet1!recentmonths
    Dynamic chart - recent 12 months - add series
  8. Click OK

Repeat above steps and add a new series using the named range =‘recent 12 months’!recentvalues.

Change horizontal axis labels

Dynamic chart - recent 12 months - add horizontal axis labels

  1. Click “Edit” button
  2. Type: =’recent 12 months’!Recentmonths
    Dynamic chart - recent 12 months - add horizontal axis labels2
  3. Press OK
  4. Click OK


Excel Defined Table – Dynamic chart (Works in Excel 2003 and above)

Create an excel defined table

  1. Select A1:B3
  2. Click “Insert” tab
  3. Click “Table” button
  4. Click OK!

Create a chart

  1. Select table
  2. Click “Insert” tab
  3. Click “Column chart” button

It is now possible to add more rows and columns to the table. The chart is instantly updated!

How to remove rows / columns

  1. Right click on a cell
  2. Click “Delete”
  3. Click “Table Rows” or “Table Columns”

Dynamic chart - remove values

You can also “hide” rows or columns and corresponding values in the chart are temporarily removed until you unhide those rows or columns again.

Download excel sample file for this tutorial.

Dynamic-chartv2.xlsx
(Excel 2007 Workbook *.xlsx)

Also read


Courtesy: get-digital-help.com

Leave a Reply

Your email address will not be published. Required fields are marked *

8 − one =

This site uses Akismet to reduce spam. Learn how your comment data is processed.