Excel user entries different types of data such as, number, text, date etc. in the cell of a spreadsheet based on the document’s requirement. Normally data are inserted in the cell like text box and user can type any type of data there. But if it is require to restrict the value of any cell and force user to select cell value from predefined list then this type of feature can be implemented by using drop down list in the spreadsheet. It is used to set those predefined values which will be applied into a particular cell. Drop down list works as one type of data validation for the excel document because the user can’t able to insert any data outside the drop down list value and for that there is no possibility to make mistake in inserting data. How you can make a drop down list in the spreadsheet of excel document is shown in this article.
Select or prepare a excel document where you want to make drop down list. Suppose a spreadsheet with the following data will be used to create the drop down list. There is a field named Computer Type where data will be inserted from drop down list.
Select the cell where you want to make drop down list option. Click INSERT tab and Data Validation option from Data Validation group.
Select List from Allow list and click OK button from Data Validation dialog box.
Click on the button of Source field to add list items.
Type comma separated item values for the list. Here three values are set which are Laptop, Desktop and Net Book. Press enter.
Inserted values will be shown in the source field. You can add or delete any value based on your requirement. Click OK.
Now, if you click on the cell where the drop down list is created the following list with predefined values will appear.
The user can easily insert data from the drop down list by selecting particular item value, but if the user try to insert data in the cell of drop down list by typing text manually then the following error dialog box will appear to prevent user from inserting invalid data on that field. So data validation for specific list of data can be done by adding drop down list in the excel document.
Drop down list is added here for a particular cell. If you want to add drop down option for other four cells of this document then just select the cell with drop down list and right click the mouse on that cell. Select copy option from the pop-up menu.
Select the other cells where you want to add the same drop down list and right click the mouse to open pop-up menu. Click on the paste item form the menu to copy the drop down list.
After the copy and paste, all other required field will contain the same drop down list. Now the user will able to insert the valid data on those particular fields.
So you can easily use the drop down list in your excel document by following above steps and restrict user to enter wrong data in a particular cell. Many others types of validation can be added in excel document by using Data Validation feature which will be discussed in other article.