Are you looking for ways to make your Excel spreadsheets interactive but you don’t want to use VBA? For instance, what if you want to create a simple Excel dashboard where you make selections from a drop down menu, and Excel immediately updates the dashboard charts without any programming or manual copy and paste to re-run the figures?
Let’s look at Excel’s built in drop down menus, which you can use as the basis for impressive and interactive charts and dashboards. We’ll see how you can create drop down menus with two methods: 1) Data Validation and 2) Combo Box Form controls. By changing the selection in your drop down menus you will drive automatic chart updates.
And here’s a bonus – you’re going to learn a neat trick to display an image that changes to match what you select in your drop down menus. This looks great when you want to spice up a dashboard with custom graphics.
We prepared a workbook with examples of each method. Download it here and get ready to learn about drop downs for interactive Excel charts.
Download the Sample Workbook
In order to follow along with the video and article please download the Sample Workbook “Drop_Downs_for_Dashboards.xlsx” by clicking here
Note: the sample workbook has been tested on Excel 2010 (32-bit, English language version) and you may get different results in other version of Excel. If you find that you can’t use this workbook on your version of Excel, please let us know using the comment section below.
Introduction to Drop Down menus for Dashboards
In our previous article we saw step by step instructions for how to create drop down lists for data entry using data validation. Here we take the idea of using drop downs a step further with formulas linked to charts.
You can use the methods in this article to create simple interactive reporting Dashboards (here I use the word Dashboard to refer to a powerful visual way of presenting information using Excel). Here are some ideas where you can use drop down menus in your Dashboards:
- Reporting Period (select the year, quarter or year to report)
- Geographical Region (select which country or city to report)
- Marketing Segment (split your analysis into market segments)
- Product Type (choose between different products)
- Metric Type (select sales, expenses, pageviews, return rates, customer satisfaction)
With a little creativity you can combine two or more drop down menus and other form controls. In a couple of hours it’s possible to create a dynamic Dasbhoard that’s interactive and exciting to use.
Excel Tutorial Video – Drop Down menus for dynamic Dashboards
Watch our 17 minute video showing how to create drop down menus for dynamic Excel charts and Dashboards. Please note that the video was made using Excel 2010, hence the specific menu and look will be for Excel 2010.
Click on the video below to play it and listen to my audio commentary.
00:07 Immeria Web Analytics Dashboard (shared under Creative Commons license)
00:50 What’s in the sample workbook
01:43 Chart source data = Launch Excel pageview data from Google Analytics
Method 1 (Chart)
02:56 Drop down using Data Validation
03:23 Show the Name Manager (CTRL + F3)
04:48 Formulas that drive the chart
Method 2 (Chart)
06:23 Drop down using Combo Box Form Control
07:24 Quick Tip: Hold ALT key to snap object to cell borders
07:48 Quick Tip: Hold CTRL to select form control
09:31 How to get the Developer Tab in Excel 2010
Method 3 (Flags)
10:05 Image lookup using Combo Box Form Control
13:24 OFFSET formula for image lookup
15:10 INDEX formula for image lookup (alternative)
15:50 How to insert and position the flag images
Remember to practice these tips
Try working through these tips after you watch the video or even while watching the video (you can pause and rewind) using the sample workbook that you can download above.
And please add a comment to tell us what you think of using these methods. We all love to learn tips & tricks and it’s helpful to share other resources if you have links to them! Use the comment section below.
Using Drop Down Menus for Interactive Charts and Dashboards in Excel – Video Transcript
Here’s the list of the techniques split into 3 sections. Click on the section titles to jump to them.
- Drop down using Data Validation – How to use Excel’s data validation feature to create a drop down menu that appears when you click on the drop down cell
- Drop down using Form Control – How to use a Combo Box Form control to create a drop down menu that remains visible even when you don’t click on the drop down cell
- Image lookup – Create a dynamic image lookup driven by a drop down menu
In this video we take a look at some interactive charts and dashboards using drop downs in Excel. The timecodes show you where you can find the matching video explanation and they are in this format [mm:ss]. You can also read the video transcript before watching the actual video, or instead of watching the video, because I’ve included screenshots to illustrate the text.
In the video you see a demo Dashboard for web analytics, created by Stéphane Hamel at Immeria.net:
If you want to download the sample Web analytics dashboard from Stéphane Hamel you can find it on his blog here. His Excel workbook is shared under a Creative Commons non-commercial license.
Here is the Creative Commons Attribution:
Stéphane Hamel’s Dashboard is an example of a dashboard for web analysts. What he’s done is use a drop-down to allow selection of the reporting period, when this is changed you see the metrics update in the table and the trend charts change to match the reporting period.
And you can select any month within the range and so the charts on the right and the dashboard numbers interactively change depending on what month you select. That’s the kind of effect it would be nice to have in a dashboard, so let’s go and have a look at how you might be able to create something like this.
[00:51] Download the sample workbook
Here is a workbook that you can download and it demonstrates 3 different techniques for creating drop down menus for dashboards.
The first type is Data Validation Dropdowns where you select the cell and it pops up with a little selection dialog. And you can select from the dropdown the list of countries.
The second type is this combo-box where I’ve added a form control and again you can select different countries from this list.
The third technique is a form control linked to a picture, so if I change the country you can see the flag changes. So this is an image lookup using Named Ranges and some offset formulas.
To create the drop-downs I defined 9 different named ranges. When you download the workbook you can play through this and have a look.
[01:44] Source Data: Launch Excel pageviews
The data is sourced from Google Analytics pageview data for this website LaunchExcel.com. I exported the number of pageviews by country from June 2011 to March 2012. The total for all countries is shown in a separate Total Row.
[02:27] Note on the Dashboard layout
I set up an Area Chart which picks up data from range C10:L10 which in turn is driven by the exported source data. In this sample workbook you can see the chart, lookup table and source data all on one worksheet. For a real dashboard you should separate out the source data from the presentation – the reason I combined them here is to make it easier for you to check out the formulas and how the tables fit together.
[02:56] Method 1 – Data validation drop down
On the first worksheet “Dropdown 1” there is data validation set up in cell B4. To set up Data Validation in Excel 2010, here are the steps:
- In the Excel 2010 Ribbon ➜ Click on the Data Tab ➜ Select Data Validation
- Select Allow: List
- Enter Source: =Db1_Country (which is a named range defining all the countries in the exported source data)
To see all the defined names in the workbook open the Name Manager with CTRL + F3. Select named range Db1_Country and you will find that it refers to the range “=DropDown1!$B$15:$B$155”
The data validation in cell B4 gives you a drop down menu with 141 rows, one row for each country from Albania to Zimbabwe and one row for “All Countries”.
If you click on the drop down and select “United States” you see that the actual text “United States” is put directtly in cell B4. This is different from method 2 (form control) where the drop down is linked to a different cell.
In cell C7 is a match formula “=MATCH(B4,Db1_Country,0) which checks the selected country in cell B4 “United States” against the list of countries in Db1_Country, and gives us the row number with an exact match. The match formula evaluates to 133.
[04:46] Method 1 – How the formulas work
You can use the Trace Dependents function to show how the result in C7 is used in other cells.
To trace dependents of Cell C7, follow these steps:
- Select Cell C7
- In the Excel 2010 Ribbon ➜ Click on the Formulas Menu
- Find the Formula Auditing Tab ➜ Click on Trace Dependents
Column numbers have been defined in row 8 (cells C8:L8). C7 is defined as the named range “Db1_Selected Row”. The numbers in cells C10:L10 drive the chart, and they are driven by cells C7 and C8:L8 as you can see here:
In cell C10 is the index formula “=INDEX(Db1_Pageviews, Db1_Selected Row, C$8)” which looks in the database of pageviews (Db1_Pageviews), goes to row 133 (Db1_Selected_Row) and column 1 (C$8).
In cell D10 is the index formula “=INDEX(Db1_Pageviews, Db1_Selected Row, C$8)” which looks in the database of pageviews (Db1_Pageviews), goes to row 133 (Db1_Selected_Row) and column 2 (D$8).
And so on for each of the columns 1 to 10 (E10, F10, …, L10)
With “United States” selected in cell B4 you can see that formulas in C10:L10 show the pageview numbers for the United States.
Note that in the sample workbook I only have 10 columns as I extracted the data for the first 10 months, typically this kind of analysis might show 13 months (12 months in the year plus one more for Y-o-Y comparison).
If you click on the drop down and select “United Kingdom” you see that formulas in C10:L10 change to show the numbers for the United Kingdom. And the chart automatically updates.
What could go wrong?
You must be accurate when you create the named ranges, otherwise you risk creating an incorrect or incomplete range reference. When you define the Data Validation, do a check to make sure that the first and last items in the drop-down menu are what you expect (in this case first item is Albania, last item is All Countries).
You might also find that the charts do not update by themselves when you change selection. This will happen if you have turned Excel into manual calculation mode instead of automatic calculation mode. In manual mode you press F9 to tell Excel to recalculate all cells.
[06:24] Method 2 – Using Combo Box Form controls
On the second worksheet “Dropdown 2” there is a Combo Box Form control set up over cell B4.
When you click on the drop down arrow you see there are more rows than with Method 1. This is because the form control has been set up to show more rows – in fact you can also reduce the number of visible rows.
With the Combo Box in Method 2, the drop down arrow remains visible when you click on another cell, whereas with Data Validation in Method 1 you only see the drop down arrow when you click on cell B4.
To insert a form control follow these steps:
Excel 2010 Ribbon ➜ Developer Menu ➜ Controls ➜ Insert ➜ Combo Box (Form control)
Tip for fine-tuning object size and position
After you insert a form control you can resize it precisely by holding the ALT key as you drag the resize handles to snap to cell borders. You can use this tip to resize charts, pictures and other form controls.
Now to select the form control instead of activating the drop down menu, you should hold the CTRL key as you click on the form control. The CTRL key and left mouse click can be used to select form controls, chart objects and pictures for resizing and repositioning.
[07:58] Method 2 – Set up the Combo Box form control
Right click on the Combo Box and select Format Control. In the Format Control dialog box, on the Control tab, define the following:
Input range: Db2_Country (named range pointing to list of countries in source data)
Cell link: $C$7 (when you select a country, the value of cell C7 will change)
Drop down lines: 20 (number of rows you can see at one time, scroll to see the other rows)
Try that out by selecting different countries in the Combo Box dropdown. Select “India” or “Italy” and you will see the chart automatically updates with the correct data.
The formulas in the cells that drive the chart are identical to Method 1. The only difference is that we use a Combo Box form control instead of Data Validation.
What could go wrong?
Just like for Method 1, you must be accurate when you create the named ranges in Method 2, otherwise you risk creating an incorrect or incomplete range reference. When you define the Data Validation, do a check to make sure that the first and last items in the drop-down menu are what you expect (in this case first item is Albania, last item is All Countries).
Also you need to make sure that the Combo Box cell link is pointing to the correct range, as you will use this cell link to drive other formulas that in turn update your chart. If you point the cell link to a different cell then your formulas won’t update.
[09:31] How to set up the Excel 2010 Developer Tab
If you don’t have the Developer Tab visible, here are some instructions to bring it up:
- Choose the File tab, and then choose the Options button.
- In the categories pane, choose the Customize Ribbon button.
- In the list of main tabs, select the Developer check box.
- Choose the OK button to close the Options dialog box.
[10:05] Method 3 – Picture Lookup using Form Control
On the third worksheet “Top_20” is a Combo Box form conotrol set up in cell A4, and resized to fit underneath the flag picture. When you select a country in the drop down menu you will change the displayed flag in cell A3.
Cool effect – but how does this work?
The Image in cell A3 is linked to a named range called “ImgFlag”. You can see this when you click on the image and you look in the formula bar “=ImgFlag”.
Bring up the Name Manager with CTRL + F3 and inspect “ImgFlag”. It refers to an offset formula “=OFFSET(Top_20!$O$2, Top_20!$A$3,0,1,1)”
This is a lookup formula to point to the correct flag image based on what is selected in the drop down menu. You see, the drop down menu selection drives the value in cell Top_20!$A$3 and you can find this out by these steps. Right click on the Combo Box Form control and select Format Control. In the Format Control dialog box, on the Control tab, you have the following:
Input range: Top_20_Country (named range point to list of countries in source data)
Cell Link: A3 (this is where the magic happens)
Drop down lines: 8 (number of rows you can see at one time, scroll to see the other rows)
Select “Australia” in the drop down and cell A3 is updated with the value 1.
Select “Canada” in the drop down and cell A3 is updated with the value 2.
And so on, for each item in the drop down cell A3 is updated with the corresponding value.
What could go wrong?
For this to work accurately you need to make sure that the flag images are exactly lined up inside the correct cells. Use the ALT key while resizing the images to make the borders snap to cells. I discuss how this is done later in the video (see below).
You will also need to ensure that the correct flag is referenced by the lookup formula. Imagine picking “Canada” in the drop down menu but getting the US flag instead of the Maple Leaf! To prevent this problem, it’s worth doing a final check once you have set up the image lookup – pick out a sample of items and check that they all work correctly.
[13:26] Method 3 – Back to the offset formula for image lookup
Remember we saw that the named range “ImgFlag” is defined as “=OFFSET(Top_20!$O$2, Top_20!$A$3,0,1,1)”
OFFSET(reference, rows, cols, [height], [width])
reference: $O$2 (starting point)
rows: A3 (number of rows to offset)
cols: 0 (number of columns to offset)
height: 1 (optional, picks out 1 row of data)
width: 1 (optional, picks out 1 column of data)
Since the image in cell A3 has the formula “=ImgFlag” it will automatically pick out the contents of the cell that is specified by the offset formula. Each country’s flag has been arranged in ascending order in column O, so selecting a country in the drop down will link the image in A3 to the corresponding flag.
[15:06] Method 3 – Alternative index formula for image lookup
Instead of the Offset formula, you can use an Index formula instead:
INDEX(array, row_num, [column_num])
array: Top_20_Flag (named range specifying cells with flag images)
row_num: A3 (number of rows to offset)
[column_num]: 1 (optional, humber of columns to offset)
This Index formula has the same functionality as the Offset formula, however Index is non-volatile and Offset is volatile so Excel needs to recalculate Offset formulas on every sheet calculation. The more Offset formulas you use in a workbook, the slower the Offset method becomes. But here we are only working with one formula so we don’t need to worry here.
[15:51] Method 3 – How to insert and arrange the images
I downloaded a set of public domain flag images from the internet and inserted them into the workbook with these steps:
- Excel 2010 Ribbon ➜ Click on the Insert Tab
- Find the Illustrations Group ➜ Click on Picture
- Select the image (e.g. flag-of-australia) from the file/folder listing
- Click Insert
Insert all the flag images into column O in the correct order, then copy one of the images into cell A3. Change the formula for the image by selecting the image in A3 and entering “=ImgFlag” into the formula bar.
Tip for fine-tuning object size and position
Remember that to reshape and reposition the image you can hold down the ALT key while dragging the resize handles and moving the image. This snaps the edges to cell borders, making it easy to line up multiple images accurately.
Do you need advice or help to create a dashboard?
You can easily spend weeks or months learning about different techniques for creating Dashboards before you actually create your own robust and scalable reporting solutions. If you need a a reporting Dashboard and don’t have the time or bandwidth to create your own, please get in touch with us through our Excel consulting service.
We are experienced in creating reporting solutions and would be happy to discuss your specific requirements. Whether you need a totally new Dashboard for your company metrics, or want us to modify and improve an existing Dashboard, we are ready to help. You can read more about our spreadsheet services on the Excel Consulting Services page.
Or if you would prefer to learn about how to create Excel Dashboards for yourself, check out my review of an excellent online Dashboard training course by MyOnlineTrainingHub which you can find here.