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

Answer:

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.

Comments(0) Filed in category: Built-in features, Excel, Excel table

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:

Interactive chart in excel (vba)

I found a basic interactive chart on the chitika website and my first thought was if I could do this […]

Comments(17) Filed in category: Charts, Excel, Interactive

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 (vba)

Cyril asks how to hide all columns of a range except columns whose header is found in specific cells. I am […]

Comments(5) Filed in category: Charts, Excel, Excel table, VBA

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

Dynamic-chartv2.xlsx
(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 easliy change data labels in a chart. Select a single data label and enter a reference to a […]

Comments(11) Filed in category: Charts, Excel

This post demonstrates how to animate a chart:

An animated excel chart

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

Comments(1) Filed in category: Animate, Charts, Excel

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:

=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

Functions in this article:

Explaining OFFSET function

In this post I am going to provide some basic examples to demonstrate how the OFFSET function works. I also […]

Comments(2) Filed in category: Excel, Functions

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