What do you do when you want to give users the ability to select from 20 or more choices in an Excel drop-down or combo box? Even giving them more than 12 in a drop-down menu can get messy and it increases the chance of a data entry error.
When you have long lists or categories of multiple lists you can help your users by creating a category drop down menu that then selects the items to show in a detail drop down menu. Selecting from the first drop-down menu automatically changes the contents of the second drop-down. And you don’t need to program with macros or VBA!
This example is from an actual dashboard built to monitor Joint Commission standards for a hospital, so if you are working on hospital or healthcare metrics with Excel here’s a step in the right direction. It’s cool, it helps users, and it’s not that hard to do.
This set of drop-down menus is described in the Hospital Clinical Quality example on page 274 in Balanced Scorecards and Operational Dashboards with Microsoft Excel. This example is from an actual dashboard built to monitor Joint Commission standards for a hospital, so if you are working on hospital quality here’s a step in the right direction. I forgot to include the file with the original examples in the book. So this is it.
Click here to download Creating-Cascading-Drop-Down-Menus. This sample file works in Excel 2003, 2007, and 2010.