Author: Oscar Cronquist Article last updated on September 25, 2018

Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet?


The following animated picture demonstrates what an excel defined table can do for your charts:

The excel table expands automatically when you add new data. Use the tab key to move to next cell in an excel defined table.

Create an excel defined table

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

Become more productive – Learn Excel Defined Tables

An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

Become more productive – Learn Excel Defined Tables

Create a chart

  1. Select a cell on the excel defined table
  2. Go to tab "Insert" on the ribbon
  3. Click "Column chart" button

Recommended article:

How to create an interactive Excel chart [VBA]

This article describes how to create an interactive chart, the user may click on a button or multiple buttons and […]

How to create an interactive Excel chart [VBA]

How to temporarily hide rows or columns

The following animated picture shows you how to hide columns or rows.

Instructions on how to hide columns:

  1. Select columns by selecting column letters above grid
  2. Right click on selection
  3. Click on "Hide"

Here is a post I did about how to hide columns using vba:

Hide specific columns programmatically

This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]

Hide specific columns programmatically

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

Download excel *.xlsx file

(Excel 2007 Workbook *.xlsx)

Recommended post

The following article shows you how to create dynamic chart data labels:

Custom data labels in a chart

You can easily change data labels in a chart. Select a single data label and enter a reference to a […]

Custom data labels in a chart

This post demonstrates how to animate a chart:

An animated column chart

The drop down list lets you select a region. An event macro checks if there is a new drop down […]

An animated column chart

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 below 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 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:


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

Functions in this article:

How to use the OFFSET function

The OFFSET function returns a reference to a range that is a given number of rows and columns from a […]

How to use the OFFSET function

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