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
Recommended posts
Search for a cell in a table and then display the column title in excel
Arielle asks: I have to search for a cell in a table and then display the column title. search value […]
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 […]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 […]
4 Responses to “Dynamic chart – Display values from a table row or column”
Leave a Reply
How to add a formula to your comment:
<code>your formula</code>
Remember to convert less than and larger than signs to html character entities before you post your comment.
How to add VBA code to your comment:
[vb 1="vbnet" language=","]
VBA code
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.
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