Dynamic Dropdown Menus in Excel
Dynamic Dropdown Menus in Excel
Want to create a dropdown menu in Excel? You should! Dropdown menus save time and make spreadsheets look cool. Dynamic dropdown menus in Excel are particularly sophisticated and useful – they will impress your colleagues!
The first video shows you how to create a basic – or ‘static’ – dropdown menu.
As the first video demonstrates, we create a simple dropdown menu in Excel by making a list of data and using the Data Validation dialogue box to reference the list. This allows users to select from the dropdown menu and to avoid unnecessary keyboard work. Not only that, it embellishes your spreadsheet with a user-friendly feature that is beyond many people’s Excel capabilities.
Now, what if we wanted to add to the list of data that our dropdown menu refers to? We could add to the list, then manually update the dropdown menu using the Data Validation dialogue box. This would work perfectly well but would be time-consuming; moreover – what if somebody else were using the spreadsheet? Ideally, the dropdown menu would automatically update to include new entries in the data list. This is where dynamic dropdown menus in Excel come in handy, as the second video in this series explains.
In the second video, we explore how to create ‘dynamic’ dropdown menus in Excel – menus that update as you add to your list of data. We utilise the COUNTA and OFFSET formulae to create a dynamic reference to a list that we can update with new data. Formula building is tricky because, in the Data Validation dialogue box, we cannot see the prompts that Excel’s formula editor provides. Therefore, dynamic dropdown menus in Excel require some practice; but they will save you a lot of time and impress your clients, colleagues or classmates.
Having fun? Great! Check out more interesting Excel tutorials on our YouTube channel.