Buy Now
Minimize typing errors with drop-down lists that auto populate based on your prior lists. This article shows you how to make drop-down lists.

Adding Drop Down Lists | Drop down Lists Excel

drop-down lists excel tips May 07, 2021

Drop-down lists save a lot of time and effort in entering data to your Excel file. Drop down lists ensure your data is entered correctly because you are choosing from the drop-down list instead of manually typing. We’ve all experienced spelling typos and how they can radically impact our results and reporting.

Using drop-down lists, we can minimize the errors and maximize our spreadsheet usability.

In this article, we will learn how to create drop-down lists and to work on the dropdown list based on another drop-down list.

While most of our templates are designed with drop-down lists, this feature will be clutch for any spreadsheet design you personally take on.

Creating Simple Drop-Down Lists

The table below from the sample file contains the names of people. We want to write the job of each individual and create a drop-down list so when the user selects the job instead of writing it manually.

Select the cells where you want to put the data validation. It can be multiple cells in which the function will be entered, in this case, C2: C6. You can do this with blank cells or even cells that already contain values in them.

Go to the Data tab and click Data Validation.

The data validation dialog box appears, as shown below.

In the allow box from the menu, choose List.

In the Source box, type the jobs you want.

Click OK

(PS: The options will appear in the order you place them in and should be separated by a comma.)

Now, let's test our function. Click on cell C2 and open the drop-down menu. It should show our list of inputs, of which we can choose one.

This is great and convenient if you're dealing with just a few options, but if you're dealing with loads of options, typing them down could get real tedious real fast. Luckily, we could also use cell references in order to create our drop downs. Go ahead and select a single cell or multiple cells, depending on what you want to create a drop down list on. With all the cells selected, go to the data tab and click data validation. The data validation dialog box appears again and we go into list. Make sure list is still selected in the allow box. In the source box, instead of manually typing in our options, we can click and drag all the cells that contain our list of values. You can also use this method to reference a named range which we'll get into more detail later.

Adding Input Messages and Error Messages Using Data Validation

Another cool feature of drop down lists is that you can create and customize an input message to guide users on what to enter, as well as an error message in case a user inputs invalid data that could potentially ruin your tables or formulas.

Input Messages

To enter an input message, go to the data tab > data validation > data validation dialog box > Input Message Tab.

Check the option to show input message when cell is selected. Add a title and the input message and OK.

When the cell with the data validation is the active cell, it shows your input message. This will help guide users to input only valid entries in your Excel file.

Error Messages

To enter an error message whenever somebody inputs invalid data, go to the data tab > data validation > data validation dialog box > Error Alert Tab. Make sure the check box is well... checked, and you can select the style of the error message icon, title and the error message in the text box provided.

Click OK and when we try to input invalid data in the selected cells, the error message appear letting the users know that they committed a mistake.

To remove the data inputted in the cells, select the single cell or multiple cells and simply press delete to delete items.

Creating a Drop-Down List Based On Another Drop-Down List | Creating Dependent Drop Down Lists

In this part, we will learn how to create a drop-down list based on another drop-down list. Think of this like Drop-down-ception (referencing the famous Leonardo DiCaprio movie, ‘Inception,’ in which dreams have dreams. It’s all very meta.)

We will use the same table as before, but choose the country and city in which the person lives. The user will select the country they wants, then when they select the city, only the cities in the country that were made appear as their choice.

Before anything, write the data from which the drop-down lists will be filled in. You can do it anywhere, whether on the same page or another page. In this example, we made it on the same page.

We will create four names as follows:

  • The name Country refers to the domain H1: J1
  • The name Egypt refers to the range H2: H5
  • The name Kuwait refers to domain I2: I5
  • The name UAE refers to the domain J2: J5

Making the name of Country

Go to the Formulas tab and click on Define Name.

A dialog box will appear, type the name and the range Refers to, then press OK.

Making the Name of Egypt

Go to the Formulas tab and click on Define Name.

A dialog box will appear. Type the name and the range Refers to, then press OK.

Making the name of Kuwait and UAE with the same steps, you will now create the first drop-down list for selecting the country. Choose the domain from which the country will be entered, cells D2: D6.

Go to the Data tab and click on Data Validation.

A dialog box will appear.

Allow from the menu choose List.

In the Source box, write the following equation:

= Country

This formula will populate the drop-down list with the data in the range referred to by the country name as follows:

= Country

= Egypt, Kuwait, UAE

- Click on OK

You will now create the second drop-down menu for selecting the city; choose the range from which the city will be entered, which is E2: E6.

To be able to proceed, we'll be needing the INDIRECT formula. Go to the Data tab and click on Data Validation.

A dialog box will appear:

Allow from the menu choose List

In the Source box, write the following equation:

= INDIRECT (D2)

The INDIRECT formula will fill the drop-down list with the data in the range indicated by the name chosen from the first drop-down list of the country selection.

The INDIRECT function was used in the equation because the name chosen from the first drop-down list of the country selection is stored in the form of text, so the INDIRECT function must be used to convert it into a reference.

For example, if the user selects the United Arab Emirates from the first drop-down list for selecting the country, the equation will be executed in the following sequence:

= INDIRECT (D2)

= INDIRECT ("UAE")

= UAE

= Dubai, Abu Dhabi, Sharjah, Ajman

- Click on OK

Now, let's see if that works. Click on cell D2, open the drop-down menu, and choose the country you want.

Click on cell E2 and open the drop-down list. Only cities in the country that have been selected will appear. From the first drop-down list for choosing the country, select your desired city.

Creating a Dynamic Drop Down List In Excel

With the advent of Microsoft Office 365, with it came this little known group of functions called Dynamic Array Functions. These functions are also known as Spill functions because they're dynamic in the sense that it automatically spills over the cells to return the data. We won't go into detail about dynamic formulas in this article, but make sure to stay tuned since we'll be on that soon.

The cool thing with referencing Spill functions for your drop down lists is that the data validation drop down list would automatically update whenever the values returned by the Spill function changes. This is a huge game changer and really opens up tons of possibilities. Leila Gharani has an excellent video on how you can create a search box drop down list using data validation and some spill functions.

How can we reference a dynamic array formula to create a dynamic drop down list? Simple, use a hashtag. Adding the #, or hashtag, or pound sign, or number sign in the source box after the the cell containing the Spill function tells Excel that you want to pick up the values that the formula in that cell is spilling over.

Over here in the first column, we have some employee names. Imagine this is a list in Excel, kind of like an attendance sheet. So as the days stack on, names start to repeat.

We want to get a list of all the employee names but just want their names to appear once. We can use the UNIQUE function for this. How this formula works is it gets and lists down all the values in the cell range but only does it once. Let's place this in the first cell of the second column.

For example, we want that list to be arranged alphabetically, we can nest the UNIQUE function inside the SORT function as follows.

Now, time to create our dynamic drop down list. Head over to the data tab > data validation. In the Allow box, select list and in the source box we select the first cell, cell B2. Once we have that there, we can then include the hashtag right after. Click OK and viola!

When we look at our list, all the values in the spill function are there despite us just inputting a single cell.

Whenever we add new, unique data inside first column and check our drop down list, we can see that it automatically updated.

We hope you enjoyed this guide for creating drop-down lists and use it to streamline your spreadsheets and data entry. The best part about this simple process is that you can also do this on Google Sheets! Just look for the data validation dialog box in the data tab on the user interface.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras sed sapien quam. Sed dapibus est id enim facilisis, at posuere turpis adipiscing. Quisque sit amet dui dui.

Call To Action

Stay connected with news and updates!

Join our mailing list to receive the latest news and updates from our team.
Don't worry, your information will not be shared.

We hate SPAM. We will never sell your information, for any reason.