You can create a simple list for your users to select from in your worksheet. This will greatly reduce errors because you are limiting input from the user’s keyboard and makes things quick and easy for them as well. Everyone loves quick and easy right? I know I do!
You only need to things for your list and they are data and an entry cell.
Below is an example of a simple dropdown list in an Excel worksheet. You can see how users can click the dropdown arrow to display a list of items stores in A2:A4. Should a user attempt to enter something that does not appear on that list, Excel will, by default, reject it.
- Create the list in cells A1:A4.
- Select cell E3, the data entry cell.
- Click the Data tab.
- Click the Data Validation option in the Data Tools group.
- From the Allow option’s dropdown, select List.
- Click the Source control and select cells A1:A4.
- Alternatively, you can simply enter the reference =$A$1:$A$4. Be certain the in-Cell dropdown option is selected. If you deselect this option, Excel will still force users to only enter list values, but it will not present a dropdown list.
- Click OK.
Your users can now use the dropdown and select an item to enter as a value in cell E3 and E3 equals the selected value.
It should be noted that the dropdown arrow will only be visible when the cell is active. To make it easier to use, you could apply a fill color or border to the cell.