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.

Excel 2003 - Dynamic named range - Dynamic chart

Excel 2007 - Excel defined table - Dynamic chart

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.

Make a dynamic chart for the most recent 12 months data

Excel 2003 - Dynamic named range -> Dynamic chart

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 2007 - Dynamic chart

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. Here is a post I did about how to hide columns using vba:

Hide specific columns (vba)

Download excel sample file for this tutorial.

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

Functions in this article:

OFFSET(reference, rows, cols,  [height], [width])
Returns a reference to a range that is a given number of rows and columns from a given reference

COUNTA(value1, [value2], )
Counts the number of cells in a range that are not empty