by Mynda Treacy
(last updated April 19, 2014)
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:
Figure 4. Choosing to insert a combo box.
Figure 5. The Control tab of the Format Control dialog box.
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:
Figure 7. Acting upon what Excel puts in the "Cell link" cell.
Figure 8. Dynamic information is determined based upon selections the user makes.
[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.
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!
Excel provides a few ways that you can freeze or split what you see in your worksheet. The appropriateness of these tools to ...Discover More
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
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
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.