Drop down lists can give a totally different look to your Excel spreadsheet. They are particularly handy when you have a lot of data to enter, or data is hard to spell, also when someone else needs to fill in your spreadsheet and you need to control the responses. By using drop down lists you can minimise errors and spend less time on typing. Although creating drop down lists is very easy in other programs, the steps may be less intuitive in Excel.
Adding a drop down list to an Excel cell
To create drop down list in Excel, you need to use Data validation feature in Excel.
First create a list that you want to show in a drop down format. You can create a list in the same sheet where you will insert data or in a totally different sheet.
Click on Data Validation button in the Data menu, under Data tools tab.
From the Allow options, select List.
Click on the source field and than on the exact cell where you want to create drop down list. For instance, if your list items start in A:2 and finish in A:4 just click on the cell A:2 with the left mouse click and drag to the cell A:4. You will see the values appear in the source field.
If your list has a name, don’t select that cell, where the name is. Let’s say your list is called Groceries and then you have listed under that cell different vegetables. If you want to have a drop down menu for groceries, just drag from the first to the last grocery on the list.
Another way to enter values in the source field is manually, but the first one is easier.
The last step would be to click OK. Note, the box “in-cell drop down” is usually checked by default, but before clicking OK, make sure it is.
Create multiple drop down lists in Excel
If you want to create the same drop down list in multiple cells, you can just copy and paste it where you want. To do that, select the drop down list, copy it and click on Paste special option, then Data validation.
Another way to automatically create drop down lists in multiple cells at the same time:
1.Press CTRL on your keyboard and select all the cells where you want to include the dropdown lists. Keep pressing that key until you have selected all the cells.
2. Repeat the steps above. So, once you have selected the cells, go to Data Validation and from the Allow, select List. Click on the source field, and then drag from the top to the bottom of your list. Once you click OK you will see that all selected cells will have drop down lists inserted automatically.
Edit drop down list in Excel
You can easily edit drop down list in Excel by simply changing the data in your list. Once you do it, you will see that the drop down list will be changed everywhere in the spreadsheet.
If you want to prevent others from changing your drop down list, you can apply the following settings:
- Go to Data Validation in the Data menu.
- Click on tab Error Alert.
3. In the Title and Error message fields write your preferred error message.
4. There are three styles of error message you can choose from: stop, information and warning.
5. Click OK to confirm the settings.
How to remove drop down list in Excel
Select the cells where the drop down list is placed and go to Data validation options. Click on the button Clear All. Repeat the process for each drop down list or keep Ctrl key on the keyboard, select all the cells you want and then click on Clear all.
If your spreadsheet is big and you don’t know where exactly are your drop down lists, there is a quicker way to remove them.
Press CTRL and G at the same time. Click on Special button. Click on the Data Validation option and then on All or Same. If you want to remove all drop down lists, click on All. If you want to remove only one type of drop down list, click Same.
