Excel Dropdowns Done Right: Data Validation and Named Ranges

NOTE: There is an updated version of this post posted here. I recommend reading that one rather than this one.

Every once in a very rare while, I find myself not motivated to expound upon deep and meaningful subjects. So, this post is not about the latest turn in world of privacy legislation, it’s not about my deepening fascination with two-tiered segmentation, it’s not about the perplexing and depressing indefinite postponement of Demystified Days, and it’s not even about pondering when Team Evil Forces will have a web site.

Nope. Not today. This is just a good ol’, “Hey, let’s look at a handy capability of Excel…and how to use it to the best of its ability.”

This came up last week when a co-worker asked me: “How do I get dropdowns working in cells in Excel?” She knew she had done it before, but she couldn’t remember how. In the course of showing her, I realized that, therein, was one of those handy little tips worth sharing. I’m going to walk through three different ways to accomplish this:

• The totally common, mundane way — straightforward, but it has limitations
• The way I always do it — almost no more effort to implement than the first way…but with fewer limitations
• The way I may start doing it (sometimes), which would make the approach just that much slicker

Bounce around as you see fit!

The Scenario

You’re using Excel to enter a table of data, where one or more of the columns have a standard set of possible values. For instance, let’s say you’ve made a list of household chores, and you use that list to both assign a priority to each task as well as to note the status of the work:

For both the Priority and the Status column, you’d like to enter the values using a dropdown menu, rather than needing to retype a value in each cell:

The wrinkle is that you expect this list to live for a while, and there’s a good chance that you may want to have other values available for either the Priority or the Status columns (or both). We’ll get to that.

The Standard Excel Way — Data Validation

The quickest way to set this up is with basic data validation:

1. Highlight all of the cells that will use the same dropdown values
2. Select Data » Data Tools » Data Validation
   
3. Change the Allow dropdown to List
4. Enter the values in the Source box (separating different values using commas)
5. Click OK
6. Repeat for each set of cells that has a unique set of dropdown value options.

That’s all there is to it, and it works.

The Limitation: Suppose that you decided you wanted to add a new value to the list of options, and that, rather than four cells right next to each other, this same data validation rule was used across numerous non-contiguous cells, even cells across multiple worksheets. Going in and updating the available list of values is a real pain. That brings us to…

My Standard Way — Data Validation with a Named Range

I regularly use dropdowns to make Excel-based reports more dynamic — enabling the user to choose whether he wants to see a weekly or a monthly version of the report, as well as to select the specific date range (this isn’t so much for the user’s benefit as it is for mine — it means I don’t make a “new report” each week or month, but, rather, update the data in the same workbook and then update the dropdown to get the current report; read more about my approach for that in this post).

I have a standard way of generating dropdowns that gets around the limitation described earlier: rather than entering the list of values directly in the data validation dialog box, I reference a named range. Using the same household chores scenario, I would accomplish the same end result, sans limitation, as follows:

1. Add a new worksheet (I usually name it something like “Lookups” and then hide the worksheet once everything is set up so it’s never something that the user sees)
2. Enter the lists of values at the top of that sheet — one list per column
3. Select all of the values for one set of dropdown options and enter a name for that range (in this case, “List_Priority”)
4. Repeat this  for the other list of values (I named it “List_Status” — I like to prepend the names of similar types of named ranges so that they group easily in the Named Ranges dialog box)
5. Now, it’s the same basic process as described earlier, except, rather than entering the specific values in the data validation Source field, you enter a named range (note the “=” before the named range!):
6. Click OK, and you’re good to go again!

Now, if you ever want to update values in the list, you can edit the values on the Lookups sheet. This won’t update the cell values that have already been populated — just the available values in the dropdown anywhere that named range is used.

The Limitation: even this approach has a limitation, but it has a couple of workarounds. Let’s say you decide to add a value to one of your lists — say you want to add “Unknown” as an option for Priority. If you simply type it at the bottom of the list, it falls outside of the named range and won’t be reflected in your dropdowns. Two different ways to work around this:

• After adding the value, edit the named range (Formulas » Defined Names » Name Manager) to include the additional cell
• Before adding the value, select the bottom value in the current list, right-click, and select Insert » Shift cells down » OK.This will have effectively expanded the named range by a cell. You can then either add the new value in the blank cell or copy and paste the “bottom” value (“Low” in this case) into the blank cell and then enter the new value into the bottom cell

Both of these approaches are a little bit clunky, so let’s add a twist to make the named ranges a bit more elegant…

Data Validation with Named Ranges with a Clever Twist

[Update: See the first comment below — from Julien. As he notes, the formula described here is a little messy, and he proposes a cleaner solution. I’m leaving my original approach here to provide a “multiple ways to skin a cat” demonstration…but I expect I’ll be using the approach described in the comment.]

This is simply a couple of additional steps beyond the steps described in the previous section to make the named ranges a little smarter:

1. Select Formulas » Defined Names » Name Manager
2. Select List_Priority and click Edit to see the current definition
3. Replace the Refers to: formula with the following formula:

=OFFSET(Lookups!$A$2,0,0,COUNTA(Lookups!$A:$A)-1)

And, voila! You can now go nuts with adding and removing values from the Priority list and the dropdowns will have updated values with no additional effort!

To do the same for the List_Status named range, the formula you would use for the named range would be:

=OFFSET(Lookups!$B$2,0,0,COUNTA(Lookups!$B:$B)-1)

To break down the OFFSET formula usage (using List_Priority as the example):

• Lookups!$A$2: start at cell $A$2, which is the first value in the list
• 0: stay in that same row (so still at $A$2)
• 0: stay in that same column (so, again, still at $A$2)
• COUNTA(Lookups$A:$A)-1: count the number of cells in column A that have values and then subtract 1 (the heading cell: “Priority”); grab an area that is that tall, starting with the cell currently “selected” ($A$2)

By checking Excel’s documentation on the OFFSET function and fiddling around a little bit with the formula, you can see how it’s working pretty easily.

Is It Worth the Effort?

I always use the second option described in this post. You just never know when a hastily hacked together spreadsheet will get “legs” and start growing and expanding its footprint. Better to spend an extra 10 seconds to add flexibility and maintainability.

Will I use the third option? I might. We’ll see. It didn’t occur to me that I should even try until I showed my co-worker the second option…and then watched her immediately get tripped up trying to add a new value to the list. If I’m handing off a document where flexibility in the dropdown values is needed, I might just Google my way back to this post to see how it’s done!