Subscribe Now
Email Enter your email
Subscribe
Please bookmark this page and visit often :) -Steve=True
Phone Holder with Dual USB
How-to Create Dynamic Excel Dashboards Using Picklists
Posted On 30 May 2013 By : admin Comments: 9 Tag: Chart, Dashboard, Drop Down List, Dynamic,
Dynamic Chart, Excel, Graph, Pick List
Ads by Google ► Excel Templates ► Dashboard ► Excel Formulas ► Gantt Chart Excel
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 Expenses:
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
Ads by Google ► Gantt Chart Excel ► Microsoft Excel ► Excel 2013 ► Excel Database
About the Author
Previous Story Next Story
Creating Pick Lists in Excel for your Dynamic Protecting Your Excel Charts and
Dashboard Dashboards with Array Formulas
Related Posts
How-to Make a Don’s Macro Friday Challenge – How-to Graph Three
Music Festival Enabled Friday Create a Chart to Sets of Data
Schedule Using Challenge Answer – Display a Music Criteria in an Excel
