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
Category: Charts
Comments(161) Filed in category: Charts, Excel, Interactive
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 […]Comments(22) Filed in category: Charts, Excel, Interactive
Change chart data range using a drop down list (vba)
This blog post demonstrates how to quickly change chart data range. I have created a drop down list (form control) […]Comments(22) Filed in category: Charts, Drop down lists, Excel, Interactive
Interactive chart in excel (vba)
I found a basic interactive chart on the chitika website and my first thought was if I could do this […]Comments(17) Filed in category: Charts, Excel, Interactive
Format fill color on a column chart based on cell color
Table of contents Format fill color on a column chart based on cell color Change stacked bar colors Mark Graveson […]Comments(13) Filed in category: Charts, Column chart, Excel
Category: Interactive
Comments(161) Filed in category: Charts, Excel, Interactive
Change chart data range using a drop down list (vba)
This blog post demonstrates how to quickly change chart data range. I have created a drop down list (form control) […]Comments(22) Filed in category: Charts, Drop down lists, Excel, Interactive
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 […]Comments(22) Filed in category: Charts, Excel, Interactive
Interactive chart in excel (vba)
I found a basic interactive chart on the chitika website and my first thought was if I could do this […]Comments(17) Filed in category: Charts, Excel, Interactive
Excel charts: Multiple series and named ranges
Fatou asks: Going back to my question, I had created a table and used the data to create a chart. […]Comments(13) Filed in category: Charts, Excel, Interactive
- Download free PDF Excel guide
- Weekly new posts
- Excel tips and tricks
- Step by step tutorials
- VBA macros & custom functions
- Charts & Graphs
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>
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