No-drama drop-downs
With great choice, etc. etc.
The ability to craft great spreadsheets is a massively under-appreciated skill outside the finance and consulting professions. Getting good at this will give you huge leverage, both for your own work and when working with others. Let’s dive in!
Everyone loves a spreadsheet, especially one with lots of colour-coded drop-downs.
But the fun quickly fades if the way the drop-downs are set up is a hot mess. When other people send me their spreadsheets, poorly implemented drop-downs are one of the first things I look for and fix.
The typical approach is to hard code each drop-down to a list of values. This might be fine for a quick sketch, but can be fragile if you have multiple cells repeating the same list and / or you keep changing what's in the list. And as you add more drop-downs, staying on top of all the different lists can get pretty tricky.
The better way is to put the values for all your drop-downs into a separate sheet in your workbook, with each value in its own cell and each group of values allocated to a named range. You can then link each drop-down to one of your named ranges to fetch the associated values.
This has two big advantages:
It's harder to accidentally mess up one of your drop-downs
It's easier to keep track of (and update) the allowed values for all your drop-downs
Robust drop-downs are one of the fundamental ingredients for building awesome spreadsheet-based trackers and dashboards. I’ll be back with more tips for these another time soon.