How to create dropdowns in Excel
By
SpreadCheaters
You can watch a video tutorial here.
To validate the data that is entered in an Excel cell, it is possible to define the list of values that are allowed by creating a drop-down list. This is especially useful when creating data entry forms and you need to restrict the values that are entered. By restricting the values, you can ensure that the data collected is uniform and that less effort is spent on cleaning the data later. When you are creating a form that users have to fill in, wherever the responses have limited options it is better to create dropdowns.
Step 1 – Open the Data Validation window
– Place the cursor in the cell for which the drop-down list is to be created
– Go to Data > Data Tools
– Click on Data Validation
Step 2 – Enter the list of values
– Select the Settings tab
– Under Allow select List
– Ensure that the In-cell dropdown box is ticked
– Type the list in the Source textbox:
>Under 18,18 to 50,Over 50
– Click OK
Step 3 – Check that the drop-down list has been added
– A drop-down arrow will appear on the right border of the cell that will display the list of allowed values when clicked
– The user can select any value from the list
Step 4 – Create the rest of the drop-downs
– Repeat Steps 1 to 3 above for the rest of the cells in the ‘Answer’ column, using the following values:
>Area: Rural,Urban
>No. of people in your household: 1,2,3,4,Over 4
>Do you subscribe to a newspaper: Yes,No
>Do you subscribe to an OTT: Yes,No
Step 5 – Check the result
– Try to enter ‘Five’ as the answer for question 4
