Excel charts: Use dynamic ranges to add new values to both chart and drop down list
This post demonstrates how to automatically add new values to a drop down list and a chart.
This tutorial contains three steps:
- Create two named ranges
- Create a drop down list
- Set up a chart
Create two named ranges
- Click Formulas tab on the ribbon
- Click Name Manager button
- Click New...
- Name: xaxis
- Refers to:=OFFSET(Sheet1!$A$1, 1, 0, COUNTA(Sheet1!$A:$A))
- Click OK button
- Click New...
- Name: rng
- Refers to:=OFFSET(Sheet1!$A$1, 1, MATCH(Sheet1!$D$12, Sheet1!$B$1:$IV$1, 0), COUNTA(Sheet1!$A:$A))
- Click Close
Create a drop down list
- Select cell D12
- Click Data tab on the ribbon
- Click Data Validation button
- Click Settings tab
- Allow: List
- Source:=$B$1:INDEX($1:$1, 1, COUNTA($1:$1)+1)
- Click OK button
Set up a chart
- Click Insert tab on the ribbon
- Click Column chart button and select a clustered column chart
- Click Design tab on the ribbon
- Click Select data button
- Click Edit button in Legend Entries (Series) field
- Series name: =Sheet1!$D$12
- Series values: =chartandlist.xlsx!rng
- Click OK button
- Click Edit button in Horizontal (Category) Axis Labels field
- Axis Label range: =chartandlist.xlsx!xaxis
- Click OK button
- Click OK button again
Add values to table
New row headers and data values are instantly displayed in chart. New column headers are automatically added drop down list.
Download excel file
chartandlist.xlsx
(Excel 2007-2010 Workbook *.xlsx)
Recommended blog posts:
Excel 2007: How to create a dynamic chart
Create a dynamic named range
Related posts:
Dynamic chart – Display values from a table row or column
Excel: Dynamic charting with drop list
Change chart data range using a drop down list (vba)
Create a dynamic stock chart using a web query and a drop down list in excel
Recommended categories
As a novice excel user I have been messing around with your workbook concerning 'excel-charts-use-dynamic-ranges-to-add-new-values-to-both-chart-and-drop-down-list'. i tried moving it to a different workbook and I can not get it to work. After i close your book it states reference is not valid, reference must be to an open worksheet. I tried changing where it says chartandlist.xlsx!rng and xaxis to the new sheet name but no luck. Could you possibly tell me what i am missing?
i tried moving it to a different workbook and I can not get it to work. After i close your book it states reference is not valid, reference must be to an open worksheet. I tried changing where it says chartandlist.xlsx!rng and xaxis to the new sheet name but no luck.
Steve,
You also have to change chartandlist.xlsx to your new workbook name.
Does it always have to say chartandlist? What if my workbook name is Sales by Region couldn't I just change the references to that workbook name and it work? I tried recreating it from scratch and called it the default name Book1, and changed chartandlist.xlsx to Book1.xlsx but it still stated reference is not valid. I am not questioning the design I am trying to understand why it doesn't work if I change the references, is there something i am overlooking. Thanks...
Steve
Steve,
Does it always have to say chartandlist?
No
What if my workbook name is Sales by Region couldn't I just change the references to that workbook name and it work?
Yes
I tried recreating it from scratch and called it the default name Book1, and changed chartandlist.xlsx to Book1.xlsx but it still stated reference is not valid
You have to save your new workbook and then change file names. Your new workbook must also contain two named ranges, rng and xaxis.
Click on a bar on the chart. A formula appears in the formula bar.
In this blog post example:
When changing the series value and axis label range I was entering the name of the workbook. For the axis label I tried =workbookname.xlsx!xaxis and for the series value I entered =workbookname.xlsx!rng. Each time I tried that it did not work but, when i entered the worksheet name in lieu of the workbook name in both locations it worked every time no matter what I called the workbook. So i was able to make it work which is good because I can see many uses for this design. Thanks for your time.
Steve
Steve,
I am happy you figured it out. Thanks for commenting!
Dynamic charts can also be created using Excel tables. Which is more easier that working with offset function. Offset function is volatile and can more time in calculating if the file is bigger. Your Info was pretty good. Love it..
Ramesh
I created a dynamic chart with excel 2007 tables. It works fine!
Thanks for commenting!
Hi,
That's a great example when all the data columns share roughly the same y axis values, however is it possible to 'trap' (for example) different columns against the values in column A. If column C for example comprised of 4 digit numbers could the y axis be configured to dynamicallly display this, and when column A was selected automatically adjust to display those values?
Dave
I am using Excel 2011 for Mac and when I enter the List source for the drop down list I get the message "You may not use unions, intersections, or array constants for data validation criteria"
Any ideas what this means or what I should be doing!
what mean Sheet1!$A$1?
Ksn,
=OFFSET(Sheet1!$A$1, 1, 0, COUNTA(Sheet1!$A:$A))
Offset function
Returns a reference to a range that is a given number of rows and columns from a given reference
OFFSET(reference,rows,columns,height,width)
Sheet1!$A$1 is a cell reference to cell A1 on sheet1
Thank you Oscar, I´m trying to switch North, South, West to Y (Vertical) and Months in X (Horizontal)
For the list I tried with this code
=$A$1:INDEX($A:$A,2,COUNTA($A:$A)+1)
rng
=OFFSET(Sheet1!$A$1, 500, MATCH(Sheet2!$H$5, Sheet2!$A$1, 500), COUNTA(Sheet2!$A:$A))
xaxis
=OFFSET(Sheet2!$A$1, 500, 0, COUNTA(Sheet2!$A:$A))
But it doesn´t work, can you help me with that please?.
Ksn,
1. Copy table A1:D:8
2. Select a destinations cell
3. Paste special..
4. Select transpose
5. Click OK.
Convert the new table to an excel defined table.
1. Select new table
2. Go to tab "Insert"
3. Click "Table" button
4. Click Ok
Create a new chart
1. Select newtable
2. Go to "Insert" tab
3. Create a column chart
4. Right click on chart
5. Click "Select Data.."
6. Click "Switch Row/Column" button
7. Click OK
Download excel file
chartandlist_question.xlsx
Thank you so much oscar, i read your articles and i did it!
Horizontal Range:
=OFFSET(Sheet1!$A$1, MATCH(Sheet1!$D$12,Sheet1!$A$1:$A$100,0)-1, 1,, COUNTA(Sheet1!$1:$1))
North, South, West on XAXIS
=OFFSET(Sheet1!$A$1,0,1,,COUNTA(Sheet1!$1:$1))
Dropdown List
=OFFSET($A$1,1,0,COUNTA($A:$A))
Thanks Again!.
Ksn,
Thanks for sharing a solution!
Oscar can you help in how to start with...
I want roll the months and values below on the chartandlist file
http://s22.postimg.org/go8my4iwh/rollingmonth.png
I have one idea to do Automatically with months, in Cell
B1 I Put =TODAY() Function
C1 (Next Month) =DATE(YEAR(A1), MONTH(A1)+1, DAY(A1))
D1, and so on.
Cells are formated to show just the month
But I dont have much experiencie with excel to know how can I move the values with his current month, the last month on the list must have values on 0 or None, I will appreciate your help.
Ksn,
Create an excel defined table and hide old months (columns).