How to Create Drop Down List in Excel
How to Create Drop Down List in ExcelExcel with just a few clicks ? This question is answered by our step-by-step guide with a simple example. We’ll explain how to automatically extend and format drop-down lists in Excel .
How to Create Drop Down List in Excel
Excel is a spreadsheet software that offers many interesting options for companies and private users. This allows you to manage data and perform automatic calculations. A useful tool has been the creation of dropdown lists. This little guide will explain how to insert them into your Excel files.
When should a dropdown list be used?
An important question – when and why should you use this tool at all? Basically, drop-down lists are always useful if you want to apply well-defined default values for a specific cell. That means at least two choices. There is no maximum limit on the drop-down menu items. For clarity, we recommend not using more than 10-15 different elements.
If you take this into account, this instrument offers many advantages – including the considerable simplification of input. Instead of typing the value yourself, just select an entry. This always guarantees a valid answer, even if the user of the tool does not know the desired result yet. To avoid unnecessary mistakes.
Another advantage: You can later assign specific values to the various options, which can be used for further processing.
Use a drop-down list in a cell if options specific to that cell are available. For example, you can create drop-down lists that include options such as Male and Female , Yes and No, or other custom list of options. This Microsoft Excel feature could save you so much time! This Microsoft Excel feature saved you so much time! Do you often need to enter the same records in your Excel spreadsheets? Just create a custom list and use AutoFill to apply it. Continue reading .
We’ll show you how to add drop-down lists to your Excel spreadsheets.
create a dropdown list in Excel
Creating a drop-down list is easy in Excel, but the process is not obvious. The following is a summary of the steps to create a custom drop-down list in Excel:
- Creating a named range: This step is required to create the list we have in your drop-down list.
- Add Data Validation: This is the not so obvious step in which you prime the cell to provide a drop-down list.
- Add Named Range for data validation: Finally, you merge everything and define the source of the drop-down list (ie, your previously named range).
- Set Input Data Validation Message: This step is optional. You can add a pop-up message to control your spreadsheet users.
1- Create a named area
One way to add a list of items to a drop-down list using data validation is to add the list to a worksheet and name the range of cells with the list. You can add the list to the same worksheet 5 Excel document settings that you never have 5 Excel Document Settings That You Must Never Review If you select an Excel template or share a table, you may have custom settings that you do not inherit.
I do not want to see anyone. Always check these settings. Continue reading to where you want to add the drop-down list or another worksheet. Using a named range of cells in a drop-down list facilitates management.
For example, we will create a drop-down list of some different types of foods on Table2 . Enter each element in a separate cell in either a column or a row. Select the items, in the Name box, type a name for the range of selected cells and press Enter .
2- Add data validation
Go to the worksheet where you want to add your drop-down list. Click on the tab data and then in the section Data Tools on data validation .
Make sure the Settings tab is checked in the Data Review dialog box .
3- Add Named Range for data validation
Then select from the drop-down list allowing the option list from. We use the name from the named range of cells that we defined to populate the drop-down list. Enter the following text in the Source field .
Replace ” food ” with whatever name you have specified your cell range. Click OK .
The Ignore blanks check box is selected by default. This allows you to select the cell and then deselect the cell without selecting an item. If you want to select an option from the drop-down list, clear the Ignore blanks check box.
4- Define input message for the data check
If you want a pop-up message to appear when the cell with the drop-down list is selected, click the Input Message tab . Check the Show Input Message when the cell is selected and complete the Title and Input Message fields .
You can also use the Error Alarm tab to add a message that appears when an invalid entry is entered in the drop-down list (for example, if someone enters the cell instead of selecting an option). Check the error display after invalid data has been entered . Choose a styleand fill in the fields title and error message .
Click OK .
When You Select a Cell Excel Quick Tips: How to Mirror Cells & Switch Rows or Columns Excel Quick Tips: How to Flip Cells & Rows or Columns Quickly transform your data into the correct format for Excel to perform its magic. Use these strategies to quickly split rows, convert columns to rows, mirror rows, and save lots of manual labor.
Read More contains a drop-down list, the down arrow to select an option appears to the right of the cell. The down arrow key is displayed only when the cell is selected. We’ll show you a way to display a down arrow next to a drop-down list all the time later in this article.
If the drop-down list contains more than eight items, you’ll see a scroll bar in the drop-down list when you click the arrow.
Advanced drop-down list options in Excel
Now that you have a simple drop-down list, you can dive into Excel’s advanced settings to edit and customize your list.
Edit or delete a named area
If you need to edit or delete a named range, you must use the Name Manager . Click on the tab formulas and then in the section Defined nameson behalf Manager .
To change the cell range for a name in the Name Manager dialog box , select the name in the list, and then click the cell range button at the bottom of the dialog box. Then select the cell range and, in the compact version of the Name Manager dialog box, click the cell range button again, as described in the section above.
Then click the green checkmark to save the new range of cells for the selected name .
You can also change a name by selecting it in the list and clicking Edit ., Edit the name in the Edit Name dialog box , and then click OK . You can also change the range of cells in the Edit Name dialog box .
To delete a name, select the name in the list and click Delete .
Create a dependent drop-down list
A dependent drop-down list is one in which the options change based on the selection in a different drop-down list.
For example, if we select pizza from the drop-down list we created in the Create a Drop-down List section above, a second dependent drop-down list will contain the different types of pizza. If you select Chinese , the options in the second dependent drop-down list include different types of Chinese dishes.
I’ve just learned how to create a dependent drop-down list in excel, such as: Cell1-state, cell2-cities in this state. GUYS! It is a way of life.
Before you proceed, go back to the How to create a drop-down list and create the main favorite food drop-down list if you have not already done so.
Now we will create three more lists and name them. Enter a list for each of the options in your main drop-down list. Select one of the other lists, enter a name for this list in the Name field and press Enter . Repeat for each list.
The names of the other lists must match the options in the main drop-down list. For example, one of our other three lists contains types of cookies and is called cookies , as shown below. The other two lists in the red box below are called Pizza and Chinese .
Before you create the dependent drop-down list, you must select an item in the main drop-down list. It does not matter which option you choose. Then select the cell to which you want to add the dependent drop-down list.
Click on the tab data and then in on data validation . “Data Tools” , select the drop-down list allowing the option list from.
Enter the following text in the Source field . Replace ” $ B $ 2 ” with the reference to the cell that contains your main drop-down list. Keep the dollar signs in the cell reference. This indicates an absolute reference to the cell that does not change, even if you copy or move the formula that references that cell.
= INDIRECT ($ B $ 2)
The INDIRECT function returns the reference specified by a text string, in this case the text from the option selected in the main selection list in cell B2. For example, if you select Chinese in the main drop-down list, = INDIRECT ($ B $ 2) returns the Chinese reference . As a result, the second drop-down list contains the Chinese elements .
Click OK .
The list in the Favorite Dish drop-down list in the following example changes depending on what is selected in the list. Favorite food drop-down list.
Copy and paste a drop-down list
If you need to duplicate a drop-down list with data validation in other cells, copy and paste the cell with Ctrl + C and Ctrl + V a. This copies the drop-down list and the formatting.
If you want to copy only the drop-down list with data validation, but NOT with the formatting, select the cell and copy it normally with Ctrl + C . Then go to the tab home page and click on the section clipboard to paste . Select Insert Content .
From the dialog box Paste Special option validation in inserting section. Then click OK .
This copies only the drop-down list and not the formatting in the original cell.
Note: Be careful when working with drop-down lists in Excel. When you copy a cell that does not contain a drop-down list over a cell that contains a drop-down list, the drop-down list is lost. Excel does not notify you or ask you to confirm the action. You can, however, undo the action with Ctrl + Z.
Select all cells with drop-down lists
Because the down arrow does not appear in a drop-down list when that cell is not selected, it’s hard to know which cells contain drop-down lists. You can apply different formatting to the cells using drop-down lists. However, you must first find all the drop-down lists if you have already formatted them. There is a way to select all cells that contain drop-down lists so you can see where they are.
First, select a cell with a drop-down list. Go to the Home tab and click Find & Select in the Edit section . Then select Go to Special .
Dialog box, select Go to Special option data validation from. The All option under Data Review selects all cells to which a data validation rule has been applied. The Equal option selects only cells with drop-down lists that use the same type of data validation rule as the one in the selected cell.
We accept the default selection of All because our drop-down lists have slightly different rules. One uses a named range to get its value, and the other uses the INDIRECT function.
Click OK .
Our two dropdown lists are selected.
Now you can format these cells How to quickly delete all empty cells in Excel How to quickly delete all empty cells in Excel Do you have an Excel spreadsheet that is full of empty rows and / or columns? Do not waste time deleting it by hand. This method makes it easy.
Read more to distinguish them from other cells so you know where all the drop-down lists are. If you do not want to apply other formatting to drop-down lists, the next section will show you another way to make them always visible.
Always make the drop-down list arrow visible
The down arrow in a drop-down list disappears if the cell is not selected. We’ll work around this problem by adding a permanent down arrow to the right of the drop-down lists.
We’ve taken a screenshot of the down arrow button in a drop-down list in Excel. We’ll paste this image into the cell to the right of the drop-down list so you’ll see an arrow down even if the drop-down list is not selected. When you select the drop-down list, the down arrow for the list appears over the pasted image.
First, download the file drop-down-arrow.png (right-click on this link and select Save link as ). Then select the cell to the right of a drop-down list and go to the Insert tab .
Then click on Illustrations and select pictures .
In the Insert Picture dialog box, navigate to the location where you savedthe drop-down arrow . .png file and select the file. Then click on Paste .
The image is inserted on the left side of the cell. It looks like it’s connected to the drop-down list in the cell on the left. Now you know where that drop-down list is and you can select that cell, and the real down arrow key is displayed over the wrong one.
Remove a drop-down list from a cell
If you decide you want to remove the drop-down list from a cell, select the cell and open the Data Validation dialog box as described above in the To create a drop-down list (go to the Data tab and click Data Validation in the Data Tools section Click the Clear All button that is available, no matterwhich tab is currently selected.
The options in the Data Validation dialog box are reset to their default values. Click OK .
The drop-down list is removed and the cell is reset to the default format. If an option was selected when removing the drop-down list, the cell is filled with the value of that option.
If you do not want to keep the values when you drop the drop-down list, you can copy and paste an empty cell. it over the cell with the drop-down list. The drop-down list is removed and the cell becomes an empty cell.
Make drop-down lists work for you
Make full use of the data entry drop-down lists in Excel to be more productive and make your life easier. In the Controls section of the Developer tab, there are many other form controls that you can experiment with in your worksheets.