Excel Dropdowns Done Right

Do you used in-cell dropdowns in your spreadsheets? I used them all the time. It’s both an ease-of-use and a data quality maneuver: clicking a dropdown is faster than typing a value, and it’s really hard to mis-type a value when you’re not actually typing!

I use in-cell dropdowns a lot when I’m making a list of things and I want to classify the values in the list. For instance:

• Prioritizing the items: high / medium / low
• Assigning a person (submitter or owner of a task, for instance, if that’s a finite list)
• Assigning a status: open / in work / completed / on hold / cancelled
• Assessing whether each item meets some sort of criteria: yes / no / unknown

If each of these criteria uses a dropdown list that is well-built, then I’m just a few clicks away from List Analysis Nirvana Via Pivot Tables (LANVPT!).

I also use in-cell dropdowns when I’m giving the recipients of the spreadsheet some simple controls over what is displayed and how. For instance:

• Selecting a start date or end data (or both) for the displayed data
• Selecting which metric to display or to sort values by
• Selecting the granularity of trends being displayed (daily / weekly / monthly)

I’ve actually counted the number of ways in-cell dropdowns can be used and arrived at a number: oodles.

Now, while Excel form controls can be used to create dropdowns, I always-always-always use Excel’s “data validation” capability to create these (unfortunately, data validation in Google spreadsheets blows like a tuba player in the Boston Philharmonic…but I use it there, too, to the extent possible).

The technique I use is simple, fast (don’t be turned off by the length of this post – it takes less than 2 minutes to set up once you’ve done it a couple of times!), and flexible, and it allows easily updating a bunch of cells that need to have the same set of values in their dropdown. It relies on four main features of Excel:

• Data validation (obviously)
• Hidden sheets
• Tables
• The INDIRECT() function

“Enough with the lengthy preamble!” you exclaim. “I’m sold! Get on with it!”

Setting the Stage: The Example We’ll work from

Let’s say I have a spreadsheet that lists a bunch of different ideas for how I could try to take better pictures (this is a silly example, obviously – clearly, I just need to more liberally apply Instagram filters!).

Let’s say my initial list looks something like this:

Over time, I know I’m going to be adding to the list, and I’d really love to be able to select the value in the second column from a dropdown:

That’s really all we’re looking to do (but I’m going to add a small twist later in the example).

The Wrong Way: Entering the List of Values

The obvious way (if you know to search for “data validation”…which ain’t exactly “obvious,” IMHO), to create these dropdowns is to highlight all the cells where you want the dropdown to appear, click on Data >> Data Validation, and then enter the list of values you want to use:

That seems like a good way to go about things, but it is a fragile and risky approach indeed, as we’ll discuss later (spoiler: it has to do with updating that list over time).

There is a better way, and it doesn’t take much more time to set up than the painting-yourself-into-a-corner approach I just described.

Step 1: A Table on a Hidden Worksheet

In my last post – about Excel-based dashboards – one of my tips was to always create a Settings sheet. If you have one of those, use it. If not, make a new worksheet called Lookups (or Settings or Tim Is Awesome…the name of the worksheet doesn’t really matter).

On that worksheet, make a list (with a heading) of the values you want in your dropdown:

You may wind up with multiple lists on this sheet. You can arrange them any way that makes sense. Ultimately, we’ll hide this sheet, anyway.

Tip: In practice, when I’m creating high/medium/low-type lists, I often wind up adding a number to them: 1 – High / 2 – Medium / 3 – Low. That makes the list more easily sortable. Alas! In English, an alphabetical sort of these three words does not put them in a reasonable order!

After making the initial list, turn it into an Excel table:

1. Select any cell in the list
2. Select Insert >> Table

The result, if you’re using the default Excel sheet, looks something like this:

You can adjust the table style if you like (I usually do), but that’s not strictly necessary.

Then, for built-in documentation purposes, give the table a name by clicking on Design under Table Tools and entering a name:

I like to prepend these tables with some sort of consistent prefix – “tbl_,” “lookup_,” or even simply “t_.” That way , if I use a lot of named ranges, all of my lookup tables will show up in one group in the Name Manager.

Step 2: Figure Out How to Reference Those Cells

Ultimately, we want the dropdown list to be “the first column in this (simple, 1-column) table.” But, the specific syntax for referencing table components can get a little confusing, so we can cheat to figure out exactly how to reference the cells.

First, make an unused cell active and put an equal sign in it:

Then, move the cursor over the heading of the table until a black down arrow appears. Click in that spot, and just the data in the column (not the whole column, and not the heading for the column) gets highlighted. And, in our blank cell, we now have the proper syntax for referencing that column:

All we have to do is cut the value in that blank cell (omitting the equal sign, so just “lookup_cost[Cost]” in this example) so we have it on the clipboard.

In theory, we can now hide this worksheet. In practice, we’ll leave it unhidden until we’ve got everything built out and the spreadsheet is ready for distribution. At that point, we might even go beyond hiding it and set its property to xlVeryHidden (overachievers reading this post who do not know how to do that already: feel free to open a new tab and start Googling).

Step 3: Create the Data Validation

Now, we go back to the sheet where we want to use this list for our dropdown. The first thing we do is exactly what I described in the “Wrong Way” section earlier: we highlight the cells we want the dropdowns in and select Data >> Data Validation.

But, then, rather than manually entering a list of values for the dropdown, we actually enter a formula:

“Whoaaaaaa, Nellie! Where did this ‘INDIRECT’ nonsense come from?!!!” you ask! Well…that’s the teensiest of wrinkles: Excel, for some inexplicable reason, just doesn’t quite play nice with normal cell references when it comes to data validation. So, logically, this should work:

=lookup_cost[Cost]

In practice, we have to drop that reference inside the INDIRECT() function (inside quotation marks!) for it to actually work:

=INDIRECT(“lookup_cost[Cost]”)

‘tis a trifle to do!

That’s it! You’re done!

But…the Reason for Doing It This Way?!

In the example we’re using here, what happens if we realize that our list is incomplete? What happens if we suddenly realize we really need a “Very High” option in the dropdown and we need an “Unknown” value in the dropdown? (In a more realistic example, we may realize that our task status dropdowns of Open / In Work / Complete are missing two values: Cancelled and On Hold).

In our “Wrong Way” approach (entering the list values manually in the Data Validation dialog box), it’s still no big deal: we simply re-highlight the cells, select Data >> Data Validation again, and update our list.

That’s a little clunky, but maybe not too bad. BUT, imagine what happens if we actually have multiple lists: in addition to this worksheet, we have another worksheet where we’ve listed ways I can become a better guitar player, and yet another worksheet where we’ve listed ways I can become a better analyst? If we want to update the possible values on all three worksheets, we have to go worksheet by worksheet selecting all the cells with dropdowns in them and update the list values. Ick!

That’s where the beauty of the approach described in this post comes in. By using a table on a hidden worksheet, all we have to do is update a single table! And, tables have the nice feature of autoexpanding when you enter something in the cell that abuts the table. So, when we enter “Very High” like this:

Once we press Enter, the table expands to include the new value:

We can also insert a new row in the table and add a new value anywhere in the list (in this case, “Unknown” as the first entry):

This won’t change any of the values already selected in our existing dropdowns, but, now, all of the dropdowns that reference that table will have an updated list of values to choose from:

Easy, peasy, no?

Extending the Usage a Bit

While “updating a bunch of cells that have the same dropdown list values” is the most compelling use case (in my mind) for this technique, there are some other ways it can come in handy.

Example 1: Say we have a date selector dropdown that updates what gets displayed on a bunch of charts. As we add data to the spreadsheet, there are more possible dates that could be selected. But, we want the dropdown to be manageable, so we only want to give the user a list of the most recent 12 weeks to choose from.

The solution? A 12-row table where we dynamically figure out what the most recent available data is (with a formula), and we put that in the top row. Then, with a simple formula ([the cell above this one] – 7), we populate the next 11 values in the table. If we use data validation to reference that table, we always have a compact, timely list!

Example 2: Say we want to assign numeric values to Unknown / Low / Medium / High / Very High in our original example so we can estimate total costs. We can simply add another column to the lookup_cost table and populate a value for each option (if you’re using Adobe Analytics, think of this as Classifications; if you’re using Google Analytics, think of it as dimension-widening; if you’re not a web analyst, skip this entire parenthetical comment). Now, with a simple VLOOKUP, we can grab a numeric value for each entry, and we know the VLOOKUP will always return a value, because the list of options in the dropdown comes from the same table where each of those options has an assigned value:

Note that, even though we added a column to the table, the original reference to the first column — lookup_cost[Cost] — is still valid. We haven’t affected the dropdown functionality itself at all.

Slicker than greased baby poop, ain’t it?

What other tips do you have for creating dropdowns in Excel? I’d love to hear ‘em!