The Two Tables approach stores the choices in two tables, and its formulas are a bit easier to set up initially. Plus, you can remove blank choices from the drop-down. This is the most “bulletproof” approach, and a good option to use when you have frequent changes to the drop-down choices or when you want to ensure that other users can easily add choices. As a result, once the formulas are set up it is easy to add new primary and secondary drop-down choices without updating the formulas.

The One Table approach stores all drop-down choices (for both the primary and secondary drop-downs) in a single table, but, the formulas are the hardest to set up initially. INDIRECT – easiest formulas volatile includes blank choices.Two Tables – easier formulas harder ongoing data management nonvolatile.One Table – hardest formulas easiest ongoing data management nonvolatile removes blank choices.Please check out this post for more information. Another option is available in versions of Excel that include dynamic array functions and spill ranges.Please check out the Slicers post for more information. Beginning with Excel 2013 for Windows, we can use Slicers as an easier alternative to the solutions presented below.This post explores three such solutions, and if you have a preferred approach, please post a comment, I’d love to hear about it! OverviewĪs with just about anything in Excel, there are several ways to achieve the goal. This post explores macro-free methods for using Excel’s data validation feature to create an in-cell drop-down that displays choices depending on the value selected in a previous in-cell drop-down.

Mac excel 2013 data validation