Linked Combo Boxes

by Mynda Treacy
(last updated April 19, 2014)

7

Excel's combo boxes are handy for incorporating interactivity into your reports. You can see an example of this below where the user is able to select the date range for the report and both the data and charts automatically update. (See Figure 1.)

Figure 1. Combo boxes provide a great way to interact with a worksheet.

Like many Excel reports my workbook has multiple sheets; a Summary and one for each region: (See Figure 2.)

Figure 2. Multiple worksheets show each region and a summary.

I have included a combo box on each sheet to save the user having to go back to the Summary sheet to change the dates, and I've linked all 'From' combo boxes to the same cell so when one combo box is changed, they all change; likewise for the 'To' combo boxes. (See Figure 3.)

Figure 3. The "From" and "To" combo boxes appear on each worksheet.

To insert a combo box, follow these steps:

  1. Display the Developer tab of the ribbon.
  2. Click the Insert tool (in the Controls group) and click on the Combo Box icon from the Form Controls group. The mouse pointer turns into a crosshairs. (See Figure 4.)
  3. Figure 4. Choosing to insert a combo box.

  4. Left click and drag your mouse on the worksheet to draw the combo box. Use the pull handles to resize it, if required.
  5. Right click on the combo box you just inserted and choose Format Control from the resulting Context menu. Excel displays the Format Control dialog box.
  6. Make sure the Control tab is displayed. (See Figure 5.)
  7. Figure 5. The Control tab of the Format Control dialog box.

  8. Change the Input Range to reflect the range of cells containing the list of values you want to appear in your combo box.
  9. Change the Cell Link to reflect the cell in the workbook that will house the number of the item the user selects from the drop down list. (E.g. the first item is 1, the second is 2 etc.)

The trick with this example is that every 'From' combo box uses the same cell for the 'Cell link', likewise for the 'To' combo boxes. This way the user can change the 'From' or 'To' dates in any sheet and they will all update to match the latest selection. (See Figure 6.)

Figure 6. Changing combo box selections modifies what shows in the worksheet.

The creative use of combo boxes in this manner can be a great boon to allowing users to interact with your worksheets. You can download a finished workbook (161 KB) that shows how this all works, plus you'll discover some other treats, like these:

  1. The INDEX function converts the value in the 'Cell link' cell (column O), to the month selected (column P). (See Figure 7.)
  2. Figure 7. Acting upon what Excel puts in the "Cell link" cell.

  3. SUMIFS formulas return the relevant figures which dynamically update based on the selection in my combo box. (See Figure 8.)
  4. Figure 8. Dynamic information is determined based upon selections the user makes.

  5. What's that 'tbl_data[Amount]' business in the SUMIFS formula? Those are Structured References and they're super cool. (Learn more about them here.)
  6. My charts also dynamically update to only show data for the selected dates. The trick here is to use a dynamic named range for the X axis source data.

[Editor's note: If you'd like to learn how Mynda put this report together, or more reporting tips like these then you might like to consider her hugely popular Excel Dashboard course.]

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13074) applies to Microsoft Excel 2007, 2010, and 2013.

Author Bio

Mynda Treacy

Mynda Treacy is co-founder of My Online Training Hub, author of their comprehensive Excel Formulas list, and popular Excel Blog. ...

MORE FROM MYNDA

Dynamic Text Boxes

You probably know that text boxes can contain text. (Else why call them text boxes?) Did you know that you could make that ...

Discover More

10 Commandments for Excel Charts

Excel makes creating charts easy. Even though it is easy, you still need to exercise prudence in making sure that your chart ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Deleting Blank Columns

Import data from another program, and you could end up with a lot of blank columns in your data. Here's the quickest way to ...

Discover More

Inserting and Copying Rows

Want an easy way to insert a new row in a worksheet and copy everything from the row above? (You end up with two identical ...

Discover More

Spreading Out a Table

If someone sends you a worksheet that has lots of data in it, you might want to "spread out" the data so you can have some ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 3?

2015-02-03 02:35:40

Ronald Obwoni

Some time the chart do not show when I click an option in the combo box. What might be the problem?


2014-05-02 12:07:47

C Knutson

Here's the fix. Use the dynamic ranges just as already presented, but for the data:

create dynamic ranges in the Workbook for each data set such as "dv_Northdatarange"
=INDEX(Analysis!$B$39:$M$39,Summary!$O$3):INDEX(Analysis!$B$39:$M$39,Summary!$O$4)

Then, in the dataseries, substitute "Mynda13074.xlsx!dv_Northdatarange" for "Analysis!$B$39:$M$39"


2014-05-02 11:53:48

C Knutson

I really like this example, but the charts do not work correctly. The months shown for the axis change and the number of months of data shown is adjusted, but the data shown on the charts always starts with the numbers for January, even if you have selected to start with March. March-December selection really shows January-October figures.


2014-05-01 10:14:36

mike

-- to get to the Excel file.
save the file/folder to desktop.
from within Excel: Menu/file Open.
choose the file/folder on the desktop.
*Excel will have an alert, do you want to...
yes, open the file.
Save the file as xlsx.


2014-04-21 10:22:08

Mitchell

I clicked on "download a finished workbook" above but the download only has XML files, not XLSX files. What am I doing wrong?

Thank you.


2014-04-19 09:06:31

Col D

I can see how using combo boxes are useful in this application where multiple combo boxes are linked to the same linking cell that drives the values returned to the report and charts. However, I think data validation is easier to use for a simple one-to-one relationship between user selection and the result.


2014-04-19 08:42:51

General Ledger

Very good example of a somewhat complex topic.

One caution and suggestion for the next iteration. A user can select a To Date that is before the From Date. Although the graphs continue to work fine, the charts result in all zeros.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.