Software, Technology

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

Related: LibreOffice default office suite in Ubuntu

About Robin Catling

Writer; performer; project manager; sports coach; all-round eccentric.

Discussion

One thought on “How-to: Make a drop-down list in Excel

  1. At last an Excel tutorial I can understand.

    Posted by Eyk | June 1, 2015, 3:14 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Twitter Updates

Follow us on Twitter @EverythingExpre

Find Us on Facebook

Enter your email address to follow this blog and receive notifications of new posts by email.

Categories

Library

BBC World News

BBC World News
Opens the BBC World News page.
%d bloggers like this: