Excel Dropdowns Done Right: Data Validation and Named Ranges

By on in with 21 Comments

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 (I have an approach that actually updates cell values, too, and does so without using macros, but that’s beyond the scope of this post).

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!

 

21 Comments


  1. I doubt I’ll always remember the formula for your 3rd option.

    So what I usually do is:
    – set a tab for all the dropdown values
    – in column A, for instance, give the name for the list, like tasks
    – in column B, give the values for the list, just as you did, but without the header in the first row
    – select the whole column B and define a named range
    – use that name range in data validation, just like you did.

    Excal automatically removes the empty values from the name range and I just have to append my next value to the list.

    Hope this helps …

  2. Julien’s option may be cleaner in terms of adding future values, but it’s far less elegant if your name range is needed in other parts of your workbook. Moreover, you’ll need to add two columns for each dropdown / named range required.

  3. @Matt I’m not sure I understand the “if your named range is needed in other parts of your workbook” comment. As long as the range is set up to work for the whole workbook, I don’t think it’s any different than the third option I proposed, is it?

    As for the two columns requirement, that crossed my mind as a minor downside. However, one of the columns is solely for labeling purposes. If there were a lot of named ranges being created, I could see using just Column A and listing out, in order, what each of the named ranges are for reference. OR, even use a naming convention in the Name Manager that covers that.

    What these comments show, I think, is that there are multiple ways to skin this particular cat. I wish more Excel users realized that it was a cat worth skinning!

  4. Much easier to type the wanted list, set it up as a table and then name the range. When you add a value at the bottom of the table the named range automatically stretches to include to the new value!

  5. @Julien, ‘Excal automatically removes the empty values from the name range and I just have to append my next value to the list’

    Which Excel version you use? I’m using 2007 and blanks are showing up in the drop down list.

    @Tim Wilson, excel shows ‘you cannot use references to other worksheets or workbooks for data validation criteria’. Is this happening to you?

  6. @Tun Win Naing: I’ll need to track down a machine with Excel 2007 to confirm. Julien’s tip works great on 2010. As for the “you cannot use references…” are you having that happen with globally-defined name ranges as your reference for data validation? I’ve run into that issue in the past in some situations, but having a named range that is defined for the entire workbook has always worked fine.

  7. Sir, nicely posted. You said “I have an approach that updates cell values too, but that’s beyond the scope of this post.” Tim, I must have this method. Scope me in. It’s like one of those 3d photos that suddenly focus but I haven’t reached the suddenly focused part. Anyways, please share your method. As mentioned previously, I must have it.

  8. Good post sir.

    I know this is nearly a year old but can anyone comment on the ability (or not) to use Tables instead of named ranges. Tables would be an obvious choice (for Excel 2007+ users) as they dynamically expand as you add new items (no need for the OFFSET formula), however the data validation screen does not seem to accept a table range as a list source (unless anyone can correct me on this).

    The workaround to allow use of tables is to create the table, then create a named range to point to the table range, then link to the named range!! This means your range does dynamically expand but the downside is you have to manually create a named range AS WELL AS the table!!

  9. @Alex Brilliant! Honestly, I only really discovered Excel tables in the last 9 months or so. I tried referencing a table directly from data validation, and you’re right, it doesn’t work. BUT, on a hunch, I then tried putting the table name inside of an INDIRECT function, and it did! So, if I created a table and left the default name as Table1, I then put the data validation formula as:

    =INDIRECT(“Table1”)

    It appears to work!

    Thanks for the comment — this post now needs to be dramatically shortened and rewritten!

  10. Pingback Excel Tables — Overlooked, Yet Awesome | Gilligan on Data by Tim Wilson

  11. Hi Tim,
    Would you be able to help me?
    I can provide a copy of the sheet I am working on if you need to see it.
    But I am trying to have the user select/type a style number and it will automatically list all similar entries that match the first characters as the user type them in. When done,
    the rest of the row will automatically be filled in with data pertinent to the selection.
    The problem exist because there are several color for one style, so after the user select the style, how would I be able to have the user select the color from the “Color” column using a drop down list just showing only the color for the style selected?
    Please advise

  12. Francisco – this sounds like something that some lookup tables could be used to make work. I’ll follow up with you via email to get an example document and see what we can do.

  13. Thanks Tim. Spending a little more time to make things more robust and scaleable is a good approach. Thanks for the article – good advice mate.

  14. Tim – I definitely prefer the table approach, I’d just recommend using the column name in the INDIRECT() formula, rather than letting it default to the first column. That way, you can use values other than what’s in the first column. For example =INDIRECT(“Table1[Priority”) versus =INDIRECT(“Table1[Status]”).

  15. Hello all-

    Quick question… I am working in a Workbook with a dynamically named range and am looking to use that named range in another sheet within the workbook. I tried the “Table” functionality, also something I am not familiar with, and got nothing, since I can’t use the table name in the Data Validation– even when I tried “Indirect” as a modifier.

    My problem: Data validation is not allowing my named range! Here is the OFFSET function I have as the source of the named range–> “=OFFSET(‘Ingredient List’!$A$1,1,0,COUNTA(‘Ingredient List’!$A:$A)-1,1)”. Its name is “Ingredients.”

    But Data validation won’t allow “Ingredients” as a source, because it says “The List Souce must me a delimited list, or a reference to single row or column.”

    Any ideas/workarounds/solutions? Appreciate your assistance.

    Just FYI- I am working in Excel for Mac 2011.

  16. Hello all-

    Quick question… I am working in a Workbook with a dynamically named range and am looking to use that named range in another sheet within the workbook. I tried the “Table” functionality, also something I am not familiar with, and got nothing, since I can’t use the table name in the Data Validation– even when I tried “Indirect” as a modifier.

    My problem: Data validation is not allowing my named range! Here is the OFFSET function I have as the source of the named range–> “=OFFSET(‘Ingredient List’!$A$1,1,0,COUNTA(‘Ingredient List’!$A:$A)-1,1)”. Its name is “Ingredients.”

    But Data validation won’t allow “Ingredients” as a source, because it says “The List Source must me a delimited list, or a reference to single row or column.”

    Any ideas/workarounds/solutions? Appreciate your assistance.

    Just FYI- I am working in Excel for Mac 2011.

  17. Hi Edie,

    It may be that Excel for Mac is causing the issue — I’ve definitely run into issues with the Mac version of Excel when it comes to tables (and pivot tables) in the past.

  18. Hi, I would be interested in your approach to renaming values in the list so that the already entered values auto-update themselves. I am trying to figure it out and macros really seem like the only option at the moment.

    Thanks for a nice article BTW!
    Borek

  19. Pingback » Excel Tables — Overlooked, Yet Awesome | Tim Wilson's Blog at Web Analytics Demystified

Leave your Comment


« »