Tired of rewriting the same data over and over and worried of making mistakes? Or maybe you want to restrict the values that users enter into cells?
Insert drop-down lists in Excel to restrict data entry or to make sure the data is correct using Data Validation.
Ready to learn how to add Data Validation in Microsoft Excel?
Where is Data Validation in Excel?
In MS Excel, find Data Validation clicking Data then Data Validation.
Why is Data Validation greyed out?
If Data Validation is unavailable, your worksheet is probably shared and/or protected.
How to create a drop-down list in Excel
There are various ways of adding a drop-down in a worksheet, today we share some of them so you’ll be able to insert drop-down like a pro!
Insert drop-down menu from cells
If you already have a list of the data to use in your Excel table, follow these 5 easy steps to create drop-down from cells:
- select the cell(s) where you want the drop-down to appear;
- click Data then Data Validation (the Data Validation window is now open);
- change “any value” to “list” under Allow;
- click the source field and select cells containing the options you want in your drop-down list;
- click OK.
If you want to create a drop-down menu from scratch, insert your list directly in the source field and enter comma (,) between values.
Add Data Validation in Excel
To create a drop-down menu from scratch in Excel, follow the same steps as mentioned before except you need to add the values you want users to choose from in the source field.
In this example, we want to insert Data Validation to restrict the values related to the completion of our Online Courses.
The only values we want are: 0%, 25%, 50%, 75%, 100%.
We want to add the data for Michelle, who is a new student of Ted Jordan: she just joined our Certifying MS Excel Course. Her completion is 0% as of today.
Watch this 40sec. video to learn how we did it!
If you want to add drop-down for cells C33 to C37, select them at the beginning.
If you are curious to know how we automatically inserted completion icons, read our tutorial about Conditional Formatting.
How to copy Data Validation to other cells?
Select the cell with Data Validation and press Command+C then click cells where you want to copy Data Validation to and press Command+V.
How to update drop-down list in MS Excel
To update Data Validation list, click the cell(s) you want to update then select Data>Data Validation. Now modify the source and click OK.
How to remove drop-down in Excel
To remove Data Validation from cells, select the cells you want to clear, click Data>Data Validation>Clear All then OK.
Now, you know how to insert, modify and delete Data Validation in Excel worksheets. Join our MS Excel Course to learn how to create input or error messages for this tool.
We’ll teach you how to circle Invalid Data and how to protect your Excel sheets too!
