How to add a drop-down list in Excel

We come across drop-down lists on the internet and other software applications all the time.  They are useful because they limit the options available to the user, as only pre-validated items can be selected.  In this post, we’ll look at different ways to add a drop-down list in Excel.

In Excel, there are three different types of worksheet based drop-down lists:

  • Data validation
  • Form control
  • ActiveX

So how do you know which one you should use?  As with so many things, the answer is “it depends”.  Let’s take a look at these three options and understand where to use each one.

Download the example file
I recommend you download the files which support this post, as you’ll be able to work along with examples.  This is the best way to learn.  You’ll be able to see the solutions in action, plus the file will be useful for future reference.  The support files are available for FREE to newsletter subscribers.

Click below to subscribe and gain access to the subscriber area.  You will also receive:

  • My favorite tips and tricks direct to your inbox
  • Exclusive content (which is only available to subscribers)
  • FREE tools and downloads

Download Icon

If you’re already a subscriber, click here to log-in to the subscriber downloads area.

The filename for this post is 0028 Excel drop-down lists.zip

Data validation drop-down list

The most common form of drop-down is a data validation list.  Let’s create one of these.

Enter the values to be used in the drop-down list into a range of cells.

Then select the cell in which the drop-down should exist.

Scenario for drop-down list

Click Data > Data Validation > Data Validation…

Data drop-down with data validation list from ribbon

The data validation window will open.  On the settings tab, select list from the allow box, and enter the range of cells containing the list in the source box.

Data validation drop-down options

Click OK to close the data validation dialog box.

To use the drop-down, click on the cell containing the data validation list.  We can now only select a value from the list.

Using a data validation drop-down list

Typing an invalid entry in the cell will result in an error message.

Data validation drop-down error message

Other data validation list options

There is lots of flexibility in this style of drop-down list.

The list can be hardcoded values

The list doesn’t have to be on a worksheet; the values can also be hardcoded into the source field.  The screenshot below shows how to include the options Yes, No and Maybe without using cells.

Hard Coded values in Data Validation list

A drop-down list created in this way would look like this:

Yes No Maybe drop-down list

The list can be in rows or columns

If using values in worksheet cells as the source, the list of values can be arranged in rows or columns (but not both at the same time)

Named Ranges

The list of values can be stored in a named range, provided it is a continuous range of rows or columns.

Tables

I love Tables, but they don’t work as well with data validation lists as they should.  Check out this post to find out how to make it work.

Data validation and dynamic arrays

If you have a dynamic array enabled version of Excel (only Microsoft 365 subscribers at the time of writing), then data validation lists can contain the result of a dynamic array.

If the first cell in the output array were in cell A2, we could use =A2# as the reference in the source box.  This method is excellent for advanced techniques, such as shrinking lists, or dependant lists.

Download the 100 Excel Macros ebook

100 Excel Macros Book

  • Contains 100 Excel VBA macros
  • Learn VBA by following along with the example codes
  • Apply to your macros, automate Excel, save time.

Download the ebook today!

Click the button below to subscribe, you’ll gain access to the subscriber area in which you can download the ebook.

Download Icon (on mid-green background)

Useful notes

Before we move on, I want to make you aware of a few issues with data validation lists:

Identifying the cells containing data validation

The drop-down arrow can only be seen when a user clicks on the cell.  Formatting the cell may be useful to indicate to the user that it is a drop-down list.

Copy and Paste dangers

When we copy and paste cells containing the data validation to other cells, the drop-down list is transferred as part of the format.  This is great if we want to create a lot of drop-down lists containing the same items.

However… now for the danger of copy and paste.

A user can copy and paste values into a cell, even if those values are not in the data validation list.  If a user types into a cell, the data validation process runs; if a user pastes values into a cell, the validation is not triggered at all.  This is a big problem

Here is another quirk…

If the worksheet is protected, the copy and paste will overwrite the value, but retain the drop-down list.  But if the sheet is not protected, copy and paste will apply the format from the source cell.  As a result, data validation may be overwritten

Highlight all cells with invalid options

Excel can perform a check to identify any cells containing values that do not meet the data validation criteria.  Click Data > Data Validation (drop-down) > Circle Invalid Data

Data validation circles

This will highlight the cells as follows:

Data validation circle invalid data

To remove the circles, click Data > Data Validation (drop-down) > Clear Validation Circles.

Form control drop-down list

Form controls can be used as another type of drop-down list.  While they remove some of the negatives from data validation drop-downs, they come with their own unique challenges.  VBA can be used to control form control elements; however in this post, we’ll be keeping things simple and looking at the basic non-VBA functionality (which is still pretty powerful).

Let’s start by creating a form control drop-down list.  These are contained within the Developer ribbon.

Display the Developer ribbon

Right-click on a blank part of the ribbon and select Customize the Ribbon… from the menuCustomize Ribbon to display Developer Ribbon

From the Excel Options window click Customize Ribbon, enable the Developer option then click OK.
Display Developer Ribbon in Excel Options

Click Developer > Insert > Combo Box (Form Control)

Developer - Insert Form Control Drop-Down

The mouse pointer will change to a cross.  Click and hold the mouse while you draw the location to place the drop-down. Pressing the ALT key while placing the object will also snap the drop-down to the grid.

Inserting a Form Control drop-down list

Right-click on the drop-down list and select format control…

Right-click Form Control for options

The format object window will open.  Click on the control tab.  The key fields are:

  • Input range – the range of cells to include in the drop-down list.
  • Cell link – the cell in which to output the value.

Format Object - Options

The cell selected for the cell link shows the chosen position in the drop-down box.  As East is the third item in the list, cell F2 displays 3.

Linked cell show index of select item

The biggest issue with the form control drop-down is that the cell link shows the position in the drop-down rather than the value itself.  By using the INDEX function and the input range cells, we can retrieve the value from the list.

INDEX function to display result as text

The formula in cell G2 is:

=INDEX(A2:A6,F2)

Useful notes

It is possible to copy and paste the form control drop-down list, but we must go into the settings each time to select the new cell link.  Making it very time intensive if you require a lot of drop-down lists.

ActiveX drop-down list

Now for the final type of drop-down list; ActiveX.

Click Developer > Insert > Combo Box (ActiveX Control)

Insert ActiveX Drop-Down

Just like the form control option shown above, draw the drop-down box on the worksheet. Right-click on the drop-down and select properties from the menu.

ActiveX Drop-Down Properties

The properties window opens.  The critical parameters for our purposes are LinkedCell and ListFillRange.  These are similar to the form control drop-down list; enter equivalent ranges into these fields.  Click on the [x] in the top right corner to close the Properties window.

VBA Properties for ActiveX drop-down

Clicking Developer > Design Mode will toggle between using and editing the drop-down list.  When in design mode, the drop-down can be edited, but not used.  Equally, when in display mode, the drop-down can be used, but not edited.

Developer - Design Mode

Unlike the form control version, the ActiveX drop-down displays the value directly in the linked cell.

ActiveX inserts value into a cell

ActiveX has more extensive formatting options than form controls.

Useful notes

ActiveX drop-down boxes have many features to be aware of:

  • When in design mode, the ActiveX drop-down can be copied or deleted, but not when in display mode.
  • ActiveX doesn’t work on a Mac.  So, if you want a genuinely cross-platform workbook, this is not the option for you.

Which option to choose?

So, which option should you choose?  As I said in the introduction, it depends.  My general advice is:

  1. Avoid ActiveX, if possible.
  2. Use Form Controls for single-cell selections.
  3. Use Data Validation, where a drop-down may need to be copied.

That’s just a basic rule I use.  In all cases, suitable worksheet protection needs to be applied to prevent accidental changes.  Ultimately, you’ll need to experiment with all the options and work out what is best for your circumstances.

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

Your email address will not be published. Required fields are marked *