Dynamic chart – Display values from a table row or column
The following animated gif shows you a sheet where you can select column (Region) or a row (Month) and the chart updates correspondingly. I am only using named ranges and a table to create this effect.
The beauty with this dynamic chart is that you can easily add more rows or columns to the table!
Example,
- Select cell F14
- Press Tab
- A new row is created
- Add values
The table automatically expands and the drop down lists and chart are instantly refreshed with the new row or column values.
How I made this worksheet
I created five named ranges.
Named ranges
Month - Formula:
Region - Formula:
Chart - Formula:
ChartCat - Formula:
Series - Formula:
Create two drop down lists
- Select cell C16
- Go to tab "Data"
- Click "Data Validation" button
- Select List
- Type =Region in Source:
- Click OK
- Select cell C17
- Go to tab "Data"
- Click "Data Validation" button
- Select List
- Type =Month in source
- Click OK
Setting up the chart
- Create a bar chart
- Right click on chart
- Click "Select Data..."
- Click "Add" button
- Type =Sheet1!Series in Series name:
- Type =Sheet1!Chart in Series values:
- Click Ok
- Click "Edit" button
- Type =Sheet1!ChartCat in Axis label range:
- Click ok
- Click ok
Download excel *.xlsx file
Dynamic chart - Display any row or column.xlsx
Related articles
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
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 (drop-down list). The above […]
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 […]
Use a mouse hovering technique to create an interactive chart
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
Dynamic data labels in a chart
My fascination for charts and vba continues (see previous posts). This post demonstrates how to highlight a group of values […]
Quickly change chart data source
In excel it is not easy to change a chart´s data source without manually changing the data source in chart […]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
Use a mouse hovering technique to create an interactive chart
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 (drop-down list). The above […]
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 […]
This post describes how to add a map (background picture) to a chart. The following animated picture shows a scatter […]
4 Responses to “Dynamic chart – Display values from a table row or column”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
English
Spanish
Hi,
I am trying to create a dynamic chart (a line chart), but a bit different to the one in this example. I have thousands of records on file and would like to be able to create a chart where I can use multiple filters. The below description is just an example of what I want to achieve.
At the moment I have: one row per customer, with information about customers' behaviour. I would like to show on my graph how many customers there are with x number of purchases (I have this info) - so it would be my x-axis, and be able to filter this information out by acquisition campaigns, and whether customer is still seen as active or lapsed. Now there is a little bit more tricky part. What I really want on my chart is % of all customers with 1 purchase, 2 purchases etc. So if someone is recruited by campaign A (I want to use filter here and simultaneously another one with customer's status) then from 100% of recruited customers I want to see how many made 1 purchase, how many 2 purchases etc, so someone with 3 purchases would be included in 3 groups (because they have done 1 and 2 purchases before doing 3rd one). I hope it makes sense. It is like survival graph.
Another tricky part is to include two lines representing two different campaigns so that they can be compared on one chart at the same time. It seems that maybe reapeating the same excercise on the same graph and defining two filters for campaigns could be a solution, but I wouldn't know for sure.
Is it something you will be able to help me with?
Many thanks,
Joanna
example of the data:
campaign status Purchases
A Lapsed 5
B Lapsed 9
C Active 12
D Lapsed 6
D Lapsed 1
D Lapsed 13
E Lapsed 1
E Lapsed 1
E Lapsed 1
C Active 11
D Lapsed 2
C Lapsed 10
D Active 12
A Lapsed 1
B Lapsed 4
B Lapsed 4
D Lapsed 11
A Lapsed 3
B Lapsed 3
B Lapsed 1
C Lapsed 14
C Lapsed 9
C Active 12
D Lapsed 2
E Lapsed 1
E Lapsed 16
D Lapsed 4
D Lapsed 1
A Active 4
B Lapsed 2
A Lapsed 3
B Active 7
B Lapsed 4
E Active 8
C Lapsed 11
E Lapsed 2
E Active 8
D Lapsed 2
B Lapsed 8
Can you share the actual data file so that I can think of what can be done?
Hi There,
How can i share my excel sheet with you? I have a work assignment similar to the tutorial above but i will need to display two related graphs (side by side) when i click/select one of the option box.
Please help me.
Thanks in advance,
JL
Jamie. L
Upload a file