How to use Drop down menus to make Interactive Charts and Dashboards in Excel

 In Excel Dashboards

[Image] Drop downs for Dashboards

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

[Image] Download 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.

[Image] Drop Downs for Interactive Charts

Use drop down menus to make your Excel Charts interactive

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:

  1. Reporting Period (select the year, quarter or year to report)
  2. Geographical Region (select which country or city to report)
  3. Marketing Segment (split your analysis into market segments)
  4. Product Type (choose between different products)
  5. Metric Type (select sales, expenses, pageviews, return rates, customer satisfaction)
[Image] Image lookup with drop down menu

Create a dynamic image lookup driven by a drop down menu

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.
[tubepress video=”mJ4GCFN8tpk”]

Image Credits for Video and Workbook:

Paper Map illustration courtesy of Fangol.
Country Flag images from Public Domain.

Video highlights

Dashboard Demo

00:07 Immeria Web Analytics Dashboard (shared under Creative Commons license)

Sample workbook

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.

  1. 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
  2. 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
  3. Image lookup – Create a dynamic image lookup driven by a drop down menu

Video Transcript

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

[Image] Immeria web dashboard

Web analytics dashboard from Stéphane Hamel

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

[Image] Download 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 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

[Image] Dashboard layout for learning purposes

For learning purposes we have shown the 1) Chart, 2) Lookup Table, and 3) Source Data, all on the same worksheet. With a real dashboard we usually recommend separating the presentation from the lookups and source data.

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:

  1. In the Excel 2010 Ribbon ➜ Click on the Data Tab ➜ Select Data Validation
  2. Select Allow: List
  3. 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.

[Image] Tracing dependents to show where cell C7 is used

Trace dependents from cell C7 to see how the result in C7 is used in the lookup table

To trace dependents of Cell C7, follow these steps:

  1. Select Cell C7
  2. In the Excel 2010 Ribbon ➜ Click on the Formulas Menu
  3. 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

[Image] Combo Box drop down menu

Method 2 - Combo Box drop down

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:

  1. Choose the File tab, and then choose the Options button.
  2. In the categories pane, choose the Customize Ribbon button.
  3. In the list of main tabs, select the Developer check box.
  4. 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.

[Image] Image lookup with drop down menu

Create a dynamic image lookup driven by a drop down menu

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:

  1. Excel 2010 Ribbon ➜ Click on the Insert Tab
  2. Find the Illustrations Group ➜ Click on Picture
  3. Select the image (e.g. flag-of-australia) from the file/folder listing
  4. 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.

Victor Chan
Victor has been using Excel intensively since 2002. He is a Chartered Accountant (Fellow of the ICAEW) and has an MEng in Manufacturing Engineering from the University of Cambridge. He is on a mission to help you learn how to use Excel better.
Join over 9,000 subscribers


Recommended Posts
Showing 15 comments
  • Muhammad Nadeem

    Kindly send me the 2007 version Excel file / tutorial.

    • Victor

      Hi Muhammed, thanks for commenting. The current download version is compatible with both Excel 2007 and 2010. There are some minor compatibility issues with Excel 2003/earlier, as reported by the Excel Compatibility Checker :

      • For Excel 2003/earlier there are 18 occurrences where “Some cells or styles in this workbook contain formatting that is not supported by the selected file format. These formats will be converted to the closest format available.”

      Quick note about how to use the Compatibility Checker:
      (Excel 2007) On the Office button, click Prepare, click Run Compatibility Checker
      (Excel 2010) On the File tab, click Info, click Check for issues, click Check Compatibility

      You can click on this download link to access the file. If the link doesn’t work, let me know and I can email you the file.

      Thanks – Victor

  • Cynthia

    I am using Excel2010 and unable to scroll pass row 9 on “dropdown1) and “dropdown2”.

    • Victor

      Hi Cynthia. Thanks for your comment. You are probably unable to scroll past row 9 because the spreadsheet has Freeze Panes turned on from row 14.

      On the View tab, in the Window group, click the arrow below Freeze Panes. Then click on Unfreeze Panes.

      Using Freeze Panes is a useful way to keep parts of the worksheet locked in place, while allowing other parts to scroll. But if the Freeze Panes is applied outside your viewing area then the whole worksheet appears locked. Another way to see this working is to zoom out and reveal more rows in your worksheet, or increase your screen resolution.


      P.S. I discuss how to use Freeze Panes in my eBook “Power Tips for Excel” which you can check out here

  • Chona

    Kindly please help me, I have been trying different method but since I’m a newbee in Excel I cant get it right. My problem is:

    I have drop-down menu -> inside is dates 14 to 16 dates.. then when I choose a date (e.g. 01/04/12) the data that I input with the validation comes out but when I choose date (e.g. 18/04/12) the formula runs already.. I need the data from specific date. So when I keep changing dates the data that show is the data really on that date.

    Can anybody help me please!!!!! Thanks in advance (can give me an answer using formula coz I’m not familiar in VBA)

  • Emma

    I enjoy reading through a post that can make people think.
    Also, many thanks for permitting me to comment!

  • Hannah

    Thank you so much for this helpful tutorial. You are an amazing instructor and teacher. All the best,

  • Jennifer

    I wanted to thank you so much for this tutorial. It was a blast learning to do this. Definetly will be implementing this in reporting at work.

    Cheers 🙂

  • Regina

    You need to take part in a contest for one of the most useful websites online.
    I will recommend this web site!

  • Shane

    Excellent post. I was checking continuously this blog and I’m impressed! Extremely useful info specifically the last part 🙂 I care for such info a lot. I was looking for this particular information for a very long time. Thank you and good luck.

    • Victor

      Thanks Shane. Hope you enjoy the rest of the info on my blog. Cheers, Victor

  • pity 2013

    Hello I am so happy I found your web site, I really found you by accident, while I
    was researching on Yahoo for something else, Nonetheless I am here
    now and would just like to say thanks for a fantastic post and a all round thrilling blog
    (I also love the theme/design), I don’t have time
    to look over it all at the minute but I have bookmarked it and also
    added in your RSS feeds, so when I have time I
    will be back to read a great deal more, Please do keep up the great b.

  • Jayatu

    Not able to enter formula =imgflag while coping to another cell !! please help with this part.

pingbacks / trackbacks

Leave a Comment

Contact Us

We're not around right now. But you can send us an email and we'll get back to you, asap.

Not readable? Change text.

Start typing and press Enter to search