It is important to be mindful of how you are entering repetitive data in excel. There is nothing worse than trying to validate and modify a column that has the same value written multiple ways! Look at this contact information, where the same state is recorded differently.
If you needed to filter this list on state you’d have to account for all representations of Texas!
Adding drop down validation is a great way to ensure values will be recorded consistently – and it is super easy to do in excel!
Let’s say Maggie’s Pet Supply sales team is calling a list of pet stores. One of the items they need to capture is the product they are interested in ordering
Rather than rely on the sales team to capture the correct product name, we can store the list of products as a validation list that can be selected by a drop-down.
- To do this, add a second sheet to store the list of possible products
- Next, highlight the column you want to be able to select the drop-down items
- Under the Home tab, select ‘Data Validation’
- For Validation criteria, allow ‘List’
- Click in the Source box and then Click on the Product sheet
- Highlight the values in the list and select OK
- Navigate back to the Call Sheet and you can now select items for your drop down!