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
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 example file for this post. Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.
Download the file: 0028 Drop down lists in Excel.xlsx
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.
Click Data > Data Validation > Data Validation…
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.
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.
Typing an invalid entry in the cell will result in an 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.
A drop-down list created in this way would look like this:
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)
The list of values can be stored in a named range, provided it is a continuous range of rows or columns.
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.
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
This will highlight the cells as follows:
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.
Right-click on a blank part of the ribbon and select Customize the Ribbon… from the menu
From the Excel Options window click Customize Ribbon, enable the Developer option then click OK.
Click Developer > Insert > Combo Box (Form Control)
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.
Right-click on the drop-down list and select format control…
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.
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.
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.
The formula in cell G2 is:
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)
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.
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.
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.
Unlike the form control version, the ActiveX drop-down displays the value directly in the linked cell.
ActiveX has more extensive formatting options than form controls.
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:
- Avoid ActiveX, if possible.
- Use Form Controls for single-cell selections.
- 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.
Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
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:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- 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.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: