Adding Drop Down Lists | Drop down Lists Excel

Drop-down lists save much time and effort in entering data into your Excel file. Drop-down lists ensure your data is entered correctly because you choose from the list instead of manually typing. We've all experienced spelling typos and how they can radically impact our results and reporting. We can minimize the errors and maximize our spreadsheet usability using drop-down lists. In this article, we will learn how to create drop-down lists and work on the drop-down 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 take on.

Creating Simple Drop-Down Lists

The table below from the sample file contains the names of people. We want to write each individual's job and create a drop-down list, so the user selects the job instead of writing it manually. Select the cells where you want to put the data validation. In this case, it can be multiple cells in which the function will be entered, C2: C6. You can do this with blank cells or even cells containing values. 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 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 typing them down could get tedious if you're dealing with loads of options. Luckily, we could also use cell references to create our drop-downs. Select single or multiple cells, depending on what you want to create a drop-down list on. With all the cells set, go to the data tab and click data validation. The data validation dialog box appears again, and we enter the list. Make sure the list is still selected in the allow box. Instead of manually typing in our options in the source box, we can click and drag all the cells containing our list of values. You can also use this method to reference a named range, which we'll discuss 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 and an error message in case a user inputs invalid data that could 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 an input message when the cell is selected. Add a title and the input message, and OK. When the cell with the data validation is active, 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. Ensure the check box is checked, and you can select the style of the error message icon, title, and error message in the provided text box. Click OK, and when we try to input invalid data in the selected cells, the error message appears, letting the users know that they committed a mistake. Select single or multiple cells to remove the data inputted in the cells and press delete to delete items.

Read more: How to fix a 'Reference Isn't Valid' error in Excel.

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 a Drop-down caption (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 where the person lives. The user will select the country they want, then when they choose 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. You can do it anywhere, on the same page or on 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 Referred 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 Referred 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 to 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, E2: E6. To be able to proceed, we'll need the INDIRECT formula. Go to the Data tab and click on Data Validation. A dialog box will appear: Allow from the menu to 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. Select your desired city from the first drop-down list for choosing the country.

Creating a Dynamic Drop-Down List In Excel

With the advent of Microsoft Office 365 came this little-known group of functions called Dynamic Array Functions. These functions are also known as Spill functions because they're dynamic in that it automatically spills over the cells to return the data. We won't discuss active formulas in this article but stay tuned since we'll be on that soon. The cool thing about 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 change. This is a huge game changer and opens up tons of possibilities. Leila Gharani has an excellent video on creating a search box drop-down list using data validation and some spill functions. Can we reference a dynamic array formula to create an active drop-down list? Simple, use a hashtag. Adding the #, hashtag, pound sign, or number sign in the source box after 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 day's stack on, names start to repeat. We want a list of all the employees' names but want them to appear once. We can use the UNIQUE function for this. This formula works by getting and listing down all the values in the cell range but only doing it once. Let's place this in the first cell of the second column. For example, if 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; in the source box, we select the first cell, cell B2. Once we have that there, we can include the hashtag immediately. 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 the first column and check our drop-down list, we can see that it is automatically updated. We hope you enjoyed this guide for creating drop-down lists and using them to streamline your spreadsheets and data entry. The best part about this simple process is that you can also do this on Google Sheets! Look for the data validation dialog box in the data tab on the user interface.