How To Add Drop Down List In Excel?

how to add drop down list in excel

To work more efficiently in worksheets, you must know how to add drop down list in excel. Drop-down lists are the most useful tools available in Microsoft Excel. Basically, a drop-down list allows people to easily add more items or delete items from the list. In today’s post, I will teach you how to add a drop down list in Excel. There are many different ways to create and edit a drop-down list. You can do this task by using Microsoft software on your PC or Mac computer. Let us now check out how to add drop down list in Excel more in detail.

How To Add Drop Down List In Excel?

Follow these steps to learn how to add drop down list in Excel.

  1. Open a new worksheet in Excel.
  2. Here, type the entries you want to appear in your drop-down list.
  3. Select the cells in the worksheet where you want the drop-down list.
  4. From the main menu, go to the Data tab.
  5. Select Data Validation from this section.
  6. This opens the Data Validation dialog box.
  7. Click on the Settings tab.
  8. From the Allow text box, select List.
  9. Now, click the Source box to select your list range.
  10. During this, leave the header row.
  11. Check the Ignore blank and In-cell dropdown boxes.
  12. Now, go to the Input Message tab.
  13. Check the ‘Show input message when cell is selected’ box to pop up a message when the cell is clicked.
  14. Type a title and message in the Input Message text box.
  15. Then, go to the Error Alert tab.
  16. Check the ‘Show error alert after invalid data is entered’ box. This gives an alert when someone enters something that’s not on your list.
  17. From the Style box, pick up an option from the drop-down menu.
  18. Type a title and message in the Error Message text box.
  19. Click on the OK button to apply all these changes.

Thus, a drop-down list will appear in the selected cell range. This feature is amazing as it protects the validity of your data. You can use the same procedure for how to add drop down list in Excel cell on Mac

Add Drop-Down List In Excel iPad

You must be wondering how to add drop down list in Excel on an iPad. Unfortunately, the drop-down list feature is not supported on iPad. They do not show the options displayed while working on the Excel desktop version.

Let us now check out how to add color to the drop down list in Excel.

Add Color To Drop-down List In Excel

In the first section, I have thoroughly explained how to add drop-down list in Excel teams. Excel allows you to color-code the drop-down list.

Follow these steps to know how to add or create drop down list in Excel with color.

  1. Open a new worksheet in Excel.
  2. Here, type the entries you want to appear in your drop-down list.
  3. Select the cells in the worksheet where you want the drop-down list.
  4. From the main menu, go to the Data tab.
  5. Select Data Validation from this section.
  6. This opens the Data Validation dialog box.
  7. Click on the Settings tab.
  8. From the Allow text box, select List.
  9. Now, click the Source box to select your list range.
  10. During this, leave the header row.
  11. Check the Ignore blank and In-cell dropdown boxes.
  12. Click on the OK button to apply all these changes.
  13. Now, go back to your worksheet.
  14. From the Home tab, click on Conditional Formatting.
  15. Select New Rule from the drop-down menu.
  16. This will open up the New Formatting Rule dialog box.
  17. In the Select a Rule Type section, click the Format only cells that contain option.
  18. Go to the Edit the Rule Description section.
  19. Here, choose Specific Text from the drop-down list.
  20. Then, select Containing from the second drop-down list.
  21. Click on the button to select the value to add a specific color.
  22. Now, click on the Format button.
  23. From the Fill tab, select any one color of your choice.
  24. Click on the OK button to apply these settings.

Thus, the cell will be colored with its specified color automatically when you choose the value from the drop-down list. You have to repeat the steps from 21 to 24 for each value in the drop-down list.

Edit Drop-Down List In Excel

Surprisingly, you can easily edit a drop-down list in Excel based on a named range. Follow these steps to know how to edit drop-down list in Excel.

  1. Open the Excel worksheet containing a drop-down list you want to edit.
  2. Now, go to the Formulas tab.
  3. Here, select Name Manager.
  4. This opens the Name Manager dialog box.
  5. Select the named range that your drop-down list is based on.
  6. Then, edit the References formula to either add or remove the cell you just added.
  7. Click on the Close button to save all your changes.
  8. Click on Yes to confirm these changes.

Let us understand more important topics on addweez.com.

FAQ

How Do I Create A Drop-Down List In Excel With Multiple Selections?

To create the drop-down list:

  1. Select the cell or cells you want the drop-down list to appear in.
  2. Click on the Data tab on Excel’s ribbon.
  3. Click on the Data Validation button in the Data Tools group.
  4. In the Data Validation dialog, in the Allow: list select List.
  5. Click in the Source: box.

What Are Drop Down Lists Used For In Excel?

An Excel drop down list[1] is a data validation function that allows users to select an option from a list of choices. It can be particularly useful in performing financial modeling and analysis by incorporating scenarios and making a spreadsheet more dynamic.

How Do I Select Multiple Options In A Dropdown?

To select multiple options in a drop-down list, use the multiple properties. It allows you to select more than one option while pressing CTRL key.

How Do I Select Multiple Items In A Drop Down Menu?

To select multiple items, we need to use the VBA code. Open the Microsoft Visual Basic for Applications window (press ALT + F11 to open it). Now double click on the worksheet name or number where you want to select multiple items within the drop-down list. You will find the code window for that particular sheet.

Where Is The Data Validation Button In Excel?

Open the Data Validation dialog box (Data tab > Data Validation). On the Settings tab, select List in the Allow box. In the Source box, type the items of your Excel validation list, separated by commas.

Conclusion

In the above post, we studied how to add a drop-down list in Excel. MS Excel supports has a tool ‘data validation’ that helps to create a drop-down list. You’ll need only two things for this process: a list of values and a blank cell. Using the excel drop-down list formula, you can easily create a drop-down list in Excel. You can also add a drop-down list in the Excel web version. If a user tries to enter something that isn’t an item, Excel rejects the entry. Thus, you can now easily create a drop-down list by understanding how to add drop down list in Excel.

 

To Know Some Great Stuff Do Visit CrazzyCricket

To Know Some Great Stuff Do Visit CricFor

To Know Some Great Stuff Do Visit EagerClub

To Know Some Great Stuff Do Visit EarthCycle

To Know Some Great Stuff Do Visit EkSankalpJob