How to create a dynamic chart
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
 Select A1:B3
 Click "Insert" tab
 Click "Table" button
 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
 Select a cell on the excel defined table
 Go to tab "Insert" on the ribbon
 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:
 Select columns by selecting column letters above grid
 Right click on selection
 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
 Right click on a cell
 Click "Delete"
 Click "Table Rows" or "Table Columns"
Download excel *.xlsx file
Dynamicchartv2.xlsx
(Excel 2007 Workbook *.xlsx)
Recommended post
The following article shows you how to create dynamic chart data labels:
You can easliy change data labels in a chart. Select a single data label and enter a reference to a [โฆ]
This post demonstrates how to animate a chart:
The drop down list lets you select a region. An event macro checks if there is a new drop down [โฆ]
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
 Go to tab "Formulas"
 Click "Define name"
 Name it "recentmonths"
 Type "=OFFSET('recent 12 months'!$A$1,COUNTA('recent 12 months'!$A:$A)12,0,12)" in "Refers to:"
 Click ok!
 Click "Define name"
 Name it "recentvalues"
 Type "=OFFSET('recent 12 months'!$B$1,COUNTA('recent 12 months'!$A:$A)12,0,12) " in "Refers to:"
 Click ok!
Insert chart
 Go to tab "Insert"
 Click "Column chart" button
 Click "Clustered column chart" button

Right click on empty chart
 Click "Select Data..."
 Click "Add" button

Type in "Series values:" =Sheet1!recentmonths
 Click OK
Repeat above steps and add a new series using the named range ='recent 12 months'!recentvalues.
Change horizontal axis labels
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:
 From the menu bar, click on "Insert"
 From the dropdown menu select "Name"
 Click Define
 Name it "months"
 In "Refers to:" text box, type =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
 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
 From the menu bar, click Insert and Chart
 Select a column chart
 Click Next
 Go to Series tab
 Add a series
 Type in values: =Sheet1!months
 Add another series
 Type in values: =Sheet1!numbers
 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
How to add lines between stacked columns/bars [Excel charts]
The image above shows lines between each colored column, here is how to add them automatically to your chart. Select [โฆ]
How to use mouse hover on a worksheet [VBA]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option [โฆ]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (dropdown list). The above [โฆ]
Create dependent drop down lists containing unique distinct values
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We [โฆ]
How to create a dynamic pivot table and refresh automatically
This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete [โฆ]
How to use mouse hover on a worksheet [VBA]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option [โฆ]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (dropdown list). The above [โฆ]
Thanks a lot for this illustrative and easy way to do a dynamic chart. Really helped me a lot!
Hi
This is great if your data (rows or columns) is increasing in size. However, doesn't seem to work if you reduce the number of columns, the graph still displays blanks for the 'missing' data
Can you help?
Excel seems to not let me enter the new series equation in the formula bar. I can type it, but pressing enter or the check mark doesn't do anything. If I do it from "Select chart data", it determines the initial state and then uses that cell reference from then on.
For example, "=SERIES(,O!OpDescriptions,O!CycleTimeValues,1)" would become "=SERIES(,O!$C$2:$C$12,O!$E$2:$E$12,1)" after initial entry. Any ideas?
erik,
change it manually via "Select Chart Data", series by series.
changing it via formula bar wont work (testd on Excel 2007).
The reason cutting and pasting doesn't work is that his examples have commas, and instead it should be semicolon (;). In the description of the functions, in fact, he uses semicolons. If you substitute commas with semicolons it works. Thank you for this tutorial!
giulia tonelli,
It has to do with regional settings. I use ; but most of my readers are americans and they use ,. That is why I use , on this blog.
I know david's post was a while back, but I really appreciate your post. I have the same problem Erik listed. I've spent hours trying to resolve  updates, etc. Using "Select Data" worked. Thanks for the article and allowing the comments.
Marvin,
You are welcome!
If you have your data in a table, you don't need to use the OFFSET function or defined name ranges (Excel 2007 only)
How do you edit the data if you have multiple series? I do not want to edit each one manually.
if there are two or more blank rows? as shown the following
janurary 5
feburary 4
march 6
apr 2
july 7
aug 8
zhen qin,
I am not sure what you are trying to accomplish. If you want two or more blank nonexisting bars in your chart, try changing the named ranges to:
Named Ranges
Month: =OFFSET(Sheet1!$A$1, 0, 0, MATCH(9,9999E+307, Sheet1!$B:$B))
Numbers: =OFFSET(Sheet1!$B$1, 0, 0, MATCH(9,9999E+307, Sheet1!$B:$B))
Where exactly do i find the chart bar. I am working in EXCEL 2007 and I don't see that.
Keith,
Thanks for asking, I have added pictures to this post.
This was a big help.
Thanks!!
tina
Tina,
I appreciate your comment! :)
Thanks!
Help! What if my data range includes a set of "IF" formulae that sit blank until other datasets are completed elsewhere, (i.e. i have 20 rows, but currently only 5 have data, but the others are formulaed and the data will appear as data is added elsewhere). It appears that the dynamic graph still considers them as values and includes them?!
(btw, otherwise thanks for this  really useful!)
James,
Named range Month
Named range Number
You, my friend, are a legend. Many thanks!
Thanks a ton !
James and Neha,
You are most welcome!
Oscar,
I am trying to implement your response to James's question as I am working on the exact same problem. When I use the below;
=OFFSET(Sheet1!$B$1,0,0,SUM((Sheet1!$B$1:$B$20"")))
I get an error. "A formula in this worksheet contains an one or more invalid references"
Your insights are greatly appreciated
Thanks
CM
CM,
Maybe this example file is helpful:
DynamicchartIF.xlsx
Excel 2007 *.xlsx
Oscar,
Thanks for the prompt response. I should have mentioned that I am trying to create a line chart. And if I have a month populated but not the number the line drops to the x axis which I am trying to avoid. This is a multi series chart. Any thoughts?
Thanks
CM
CM,
And if I have a month populated but not the number the line drops to the x axis which I am trying to avoid.
Strange, the line doesnยดt drop here. What formula is shown when you click on a chart line?
Hi
Thanks for this, it's just what I needed, however I need to take it a step further.
If looking at your example, my 'Numbers' are created from an If Statement. entering "" for blank cells. Excel doesn't seem to recognise this as an empty cell and thus plots it on the graph anyway.
Do you know of a way I can use get the chart to exclude these items?
Many thanks in advance
MK
MK,
Create a table.
Click black arrow on column header.
Deselect blanks.
Download excel 2007 *.xlsx file
MK.xlsx
Hi Oscar
Thank you very much. This has made my charts a thing of joy!
MK
Hello Oscar,
Using your example above, how would the "define name" functions/formulas change if I wanted to remove one data point for every one I add? (i.e. right after I add "April" to the data series, not only would I like "April" to automatically be added to the graph but also for "January" to be removed).
Thanks for your time!!!
Philip,
Named Range  Month
Named Range  Numbers
Download excel *.xlsx file
Dynamicchartdynamicrange.xlsx
Dear Oscar,
Many thanks for the interesting site!
Please could you advise how to apply this dynamic formating to include more data for example:
Expenses January Febuary March
Petrol 45 60 50
Cellphone 25 30 25
Hotel 65 75 50
Thank you in advance for any assistance you may be able to offer!
Kind regards,
J13
Jungleist13,
Example, excel table and a bar chart.
What excel version are you using and what chart?
Dear Oscar,
Many thanks for your swift response!
Excel 2007 not really bothered about type of chart, column is sufficient I just need to understand haw to apply the dynamic features to it? I can,t figure out how to alter the formating to cover the additional series?
Kind regards,
Jungleist13
Hi  I am trying to build a line graph that needs 2 series displayed dynamically. How would i go about building that? I underrstand how to do it for one series, but cannot figure out for 2.
Thanks.
Katy
Katy,
Is this what you had in mind?
yes  where the total number of records being plotted can vary
This is great! I was able to make it work. I do have a concern and a question on this issue that you may be able to help me with. 1) When adding a new date/month to the bottom of your list, the xaxis updates in the graph with the prior data remaining in place. This could lead to an incorrect graph if the data is also not updated. Shouldn't the data also shift over to the left so the added date/month shows blank data in the graph? If so, how? 2) How would this be adjusted for a table where the dates are already prepopulated, only adding in the data each day/week/month?
Clarification  2) How would this be adjusted for a table where the dates are already prepopulated, only adding in the data each day/week/month?
In this instance, the data table is prepopulated with dates. The graph only shows the most recent 13 entries, rolling each week/month as data is added. To clarify, not all dates are showing in the graph at one time  only the most recent 13.
Eureka! I figured it out  I changed my offset formula for the "months" to reference the month row but count the data row. This results in the table updating the date and data only once the data has been added!!!
Oscar, Thank you SOOOO much. I cannot tell you how much time this will save me on my weekly/monthly reporting! I've been mulling this over for quite some time, not knowing how to do it. You've been a godsend!
Oscar, in line with the questions above, I'm looking to add further columns of data for each month, e.g. Months, Numbers1, Numbers2 etc.
How do you adjust the "=SERIES(,Book3.xlsx!Months,Book3.xlsx!Numbers,1)" formula to include third, fourth and fifth columns?
Question on the NAMES and "SERIES" formulas. Should the NAMES be saved in the SCOPE of "workbook" or the specific worksheet and does that impact the "SERIES" formula? Always use the workbook name or worksheet name instead, depending on how the NAMES were set up? Please advise.
Katy,
Download workbook *.xlsx
katy.xlsx
Instructions excel 2007
1. Create a table
2. Go to "Insert" tab
3. Click "Line charts" button
New question  I've used these formulas successfully now on a number of graphs. Now, my data is designed horizontally, not vertically and I'm having difficulty converting the formula accordingly. I've tried using "COUNTA(sheet1!$B$2:$RXM$2)" and also changing the last two numbers from "0,13" to "13,0" to no avail. i.e. Months run across row 1 with data in rows underneath with row headers in column A. Please advise.
Another new question  I have difficulty each time I wish to automate a graph with multiple series even thought I have changed the final number in the series (i.e. 1,2,3, etc.) I have charts with stacked columns, and others with two columns with a third series line. When I attempt to change the series information on the graph, it doesn't accept it  no error, nothing, I hit enter to submit it and it just sits there and doesn't change it. Also, is there a limit to the number of names used within a single workbook? Please advise.
Max,
Oscar, in line with the questions above, I'm looking to add further columns of data for each month, e.g. Months, Numbers1, Numbers2 etc.
How do you adjust the "=SERIES(,Book3.xlsx!Months,Book3.xlsx!Numbers,1)" formula to include third, fourth and fifth columns?
Excel 2007:
Create a table
1. Select cell range
2. Click "Insert" tab
3. Click "Table" button
Create a clustered column chart
1. Select table
2. Click "Insert" tab
3. Click "Column" in charts window on the ribbon.
4. Click "Clustered Column Chart"
Oscar,
I have used your suggestions above which work as expected, but I have a slight twist on what I am trying to do...
So, firstly I am trying to create a dynamic chart with daily data being added to a column with a weekly total in the eighth row.
I am trying to chart the weekly totals only (ie every eighth row). I seem to be able to do either:
1. Chart Dynamic data
2. Chart every eighth row
But not both together :( Any suggestions? Any advice appreciated.
Missy,
Question on the NAMES and "SERIES" formulas. Should the NAMES be saved in the SCOPE of "workbook" or the specific worksheet and does that impact the "SERIES" formula? Always use the workbook name or worksheet name instead, depending on how the NAMES were set up? Please advise.
The names are Named Ranges and does NOT impact the "SERIES" formula. You have to use the full name: excelfile!Named_range in the series forumla.
Example:
Book3.xlsx!Month
Excel 2007 and 2010 users can happily create a table and then create a chart. Nothing to worry about series formulas or named ranges. And yes, I know, the blog post title says Excel 2007:How to create a dynamic chart..
Oscar, can you please help me modify the Name Ranges and SERIES formula for a table where the months (to use your example) are set up horizontally instead of vertically? I am using historical tables that go back five years or so. Changing their layout is not prudent. What I have is column headers with mmmyy, with multiple row labels of varying categories. I have made attempts by changing the range from columns $A:$A, for example, to rows $A$2:$XX$2, to no avail. Please advise.
Missy,
see this attached file:Missy.xls