This is a great technique that you can use to create dynamic charts that will change when your users change the values from the picklist. I detailed how you can create a pick list (drop down list) in Excel in my last post. You can check it out by clicking on this link. It is a complete tutorial and video:
Creating Pick Lists in Excel for your Dynamic Dashboard
This tutorial will take you from just after you have created your picklist to creating a chart that changes when you change the picklist. Also, at the bottom of this post, you can go directly to the video tutorial.
The Breakdown:
1) Create Your Data Set
2) Create Your Pick List
3) Create Chart
4)Change Vertical Axis
5)Remove Legend
6)Add Dynamic Title
7) Create Named Range for the Chart
8)Update Chart Series with Dynamic Named Range
Step-by-Step Tutorial
1) Create Your Data Set
First we need to set up our data. This tutorial has the data series in a column format. Here is how my data looked:
2) Create Your Pick List
I have created my pick list in cell F1. My last post details this technique using Data Validation in Excel. Check it out here:
Creating Pick Lists in Excel for your Dynamic Dashboard
3) Create Chart
Now that we have the basics set, now lets create the chart. We don’t need to create a complex chart only to have to delete several data series, we only need to create a basic chart with one data series. So highlight the horizontal axis categories (months and the first data series ($A$4:$B$16).
Then go to the Insert Ribbon and select the 2-D Clustered Column chart from the Column button:
Your chart should now look like this:
4)Change Vertical Axis
Now since we are going to use the same chart for 3 different data series, we don’t want the Vertical Axis to keep adjusting to the current data being displayed. We want it to be a fixed amount so that the data can be compared across the data points and will not confuse the users. You can learn more about why Excel is doing this here:
Problems with an Excel Dashboard Goal Chart
So what we need to do is right click on the Vertical Axis and select Format Axis.
Then from there we need to change the Minimum and Maximum values to Fixed:
Make sure that these values are 0 and the Maximum of all the chart data points that you may display. Your chart won’t change after this as we chose the data range that has our maximum in it already.
5)Remove Legend
Also since we are only showing one series, the default from Excel to create a legend doesn’t make much sense, so lets delete that. Just select your chart, then select the legend and press your Delete key. Your chart will now look like this:
6)Add Dynamic Title
Since users will be changing the data series that is displayed in our Dashboard Chart, we don’t want to have the Chart title fixed as just one value, but we want it to change as the user updates the picklist. To understand the dynamics of this step, check out this post:
How-to Make an Excel Chart Title Change Dynamically
7) Create Named Range for the Chart
Here is where we get into the real meat of this technique. We need to use the Offset formula with our chart to make it dynamically choose the right data series. Here are a few tutorials that will show you this technique in great detail:
Case Study – Creating a Dynamic Chart in Excel Using Offset Formula
Create a Dynamic Excel Pie Chart
For this step we need to create a named range. Go to the Formulas Ribbon and press the Name Manager button.
From there press the New button and enter the following formula in a name titled “ChartColumnSeries”:
=OFFSET(Sheet2!$A$5,0,MATCH(Sheet2!$F$1,Sheet2!$B$4:$D$4,0),12,1)
Here is a breakdown of the offset formula:
=OFFSET(Sheet2!$A$5,0,MATCH(Sheet2!$F$1,Sheet2!$B$4:$D$4,0),12,1)
=Offset(starting point, move starting point down how many rows, move the starting point how many rows right by matching the value in cell F1 to the range of B4:D4, how many rows in the range, how many columns in the range )
You can learn more about the offset function here:
This is the Bomb: or How I came to love the Offset function
8)Update Chart Series with Dynamic Named Range
Now this is the last step in creating our drop down list chart for our company dashboard
How-to Make a Dynamic Chart Using Offset Formula
First select your chart and then right click on the chart to launch the “Select Data” dialog box:
Now select the “Revenue” Legend Entries (Series) and press the edit key:
Then change the “Series Values:” to the named range you created in the earlier step:
We are now done and when you change the drop down list your chart should look like this when you select Revenue in cell F1:
And like this for Net Revenue:
This basic technique can be used to make your company dashboard shine and make you look like an Excel Master.
Video Tutorial
Check out this video where you can see me recreate this dynamic chart using pick lists here:
How do you use Picklists in your Excel Spreadsheets? Let me know in the comments. Also, don’t forget to sign up for my blog and also my youtube channel so that you get the next post delivered directly to your inbox.
Steve=True
Thanks for the step-by-step tutorial.Its very helpful. Can i ask how should i do this if i have 2 data points on one single graph? Say like, the actual monthly revenue (which is what u have shown) and a line graph showing monthly “targeted” revenue which was assigned by the boss?
Thanks.
Jamie
Hi Jamie Lee,
Thanks for the great comment. Yes you can do this. I will create a tutorial on this topic and post it for you and others.
Thanks again
Steve=True
Hi Steve,
Thanks for your kind response and yes i am waiting eagerly for your post. I ran into another problem which I hope you could somehow help me with. This is related to the above demostration. The thing is i need to show two charts (side by side) when i click on one of the droplist.
For example:
Revenue by month – the column chart as what you have shown
Revenue by country – maybe a pie chart
These two data are related so i want them to be shown together. how do i do it?
Really appreciate your help on this.
Thanks again.
Jamie
Thanks again Jamie, your comment on exactly what you are looking for is great. I have your request in the queue (that word always looks spelled wrong to me 🙂 ) and I may get to it in next week’s posts. Stay Tuned!
Steve=True
Hello –
This video has been extremely helpful, thank you!
Like Jamie, I too, am trying to create a dynamic chart with stacked data columns. I see these posts are fairly old. Did you ever create a video for that request?
I look forward to watching / learning from your video.
Thanks, again.
Jess
Sorry all, I need to finish part 2 and 3 on this topic. Coming soon. Steve=True
Awesome tutorial on picklists! Thanks very much.
I have a similar question as Jamie Lee in that I want to show side-by-side bar chart data for every month that on my picklist. Example: “Jan Actuals Prod A” and beside it “Jan Budget Prod A”, Jan Actuals Prod B, Jan Budget Prod B etc.
Steve – wondering how you do this with 2 drop downs. Example show sales for different regions and then switch between dollars and volume with the other pick list?
Hi Rachel, I will add this to my list. Do you have a sample data set? I will shoot you an email. Steve=True
Hi,
The same thing i am trying to do with pivot data but its not happening.
Hi Vinit, for pivot data, you would want to use Slicers. Check out this video and see if it helps.
https://www.exceldashboardtemplates.com/how-to-insert-slicers-into-an-excel-pivot-table/
Thanks Steve=True
These step by step videos are extremely helpful–Thanks!
Glad to help. Thanks for the great comment.
Hi Steve,
Do you have a video that shows how to create a graph using 2 different drop down menus that are dependent on each other? I need to graph staff data but want the user to be able to choose between (1) Day of the Week and (2) Staff Type. The Horizontal access would be hours (0-24) for a 24 hour shift. I have searched for days on a way to create this and just haven’t had much luck.
Thanks! Brooke
Hi Brooke, not yet. What problem are you having?