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 posts:
Excel charts: Use dynamic ranges to add new values to both chart and drop down list
Excel 2007: How to create a dynamic chart
Dynamic stock chart in excel – Add date ranges
Interactive chart in excel (vba)
Select a cell in a table and the chart updates automatically (vba)
Recommended categories
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?