Friday, 24 April 2015

How-to: Make a drop-down list in Excel


If you're wanting data validation in spreadsheet forms and template documents, a drop-down list remains the most common way to achieve it.

Skipping over the question why am I covering a Microsoft product when there's a perfectly good LibreOffice alternative, let me just say; 'because someone asked me.'

The question was this:
I’d like to make my Event Duration cell B4 have a drop down menu with two options:
- Full day
- Half day

I’d like to make my Facilitator's Pack cell B34 have a drop down menu with two options:
- In the box with delegate materials
- To trainer’s home address ahead of delivery
Since this is the simplest kind of drop-list you can do, we can dive right in.
  • On a new worksheet (which I renamed Options, just to be clear what it's for), type the entries that you want to appear in your drop-down list.
  • The entries should be in a single column or row without any blank cells
  • I include a header above mine so I know what list values are used where
  • Sort your data in the order you want it to appear in your drop-down list.
  • Select all of your entries, right-click on the selected cell group, and then click Define Name from the context-menu.
  • In the Name box, type a name for drop-list, for example, B4_duration, and then OK. Name can't have spaces or dashes and must start with a number or digit. The name will link the values to your drop-down list.
  • Go back to your main worksheet. Click in the cell in the worksheet where you want to use the drop-down list.
  • From the ribbon menu, select Data, Data Validation.
  • On the Settings tab, under Allow, choose List, then tick the box for In-cell dropdown.
  • In the Source box, type and equal sign followed by the defined name you gave your list, so =B4_duration. I could as easily click the source select icon on the right and use the mouse to choose my worksheet and values list.
  • Click OK
  • The B4 cell on my main worksheet now changes to a drop list.
You would do the same for your second drop-list.

Old School Method 2
If you are an old-school Excel user addicted to manual sheet and cell references, you could set your list source to =Options!$A3:$A4 which also works.

Lazy person's Method 3
You needn't mess with cell ranges at all; for such a simple example as this, with only two values in the list, it also works if you just type your two values into the Source box as a comma-separated list:

Full Day, Half Day

or

In the box with delegate materials, To trainer’s home address ahead of delivery

If you dig further, there are more complex examples of conditional lists and multi-column lists. RC

No comments:

Post a Comment

At least try to be nice, it won't kill you...