Skip to Main Content

Cleaning & Analyzing Data in Excel

This guide complements the Cleaning & Analyzing Data in Excel workshop

Data Validation

To help ensure consistent data, you may be interested in standardizing values for a particular column. To do so, following the instructions below --

1. Be sure that the column you want to standardize has a title

2. Create a new worksheet titled "list"

3. Create a column in this new worksheet of the terms that would be acceptable in a particular column

4. Return to the original worksheet and select the column that you wish to standardize

5. Visit the menu ribbon, select DATA, and select the DATA VALIDATION option in the DATA TOOLS box. 

6. In the dialog box, select LIST from the ALLOW dropdown menu. 

7. Enter your cursor in SOURCE box and visit your list worksheet. Select the terms you want to use as standardized values for your column.

8. Once you select OK, your column cells should include dropdown menus featuring the values in your list.


Complete the following cleaning step(s) on the practice tab of the workshop_data Excel file.

  • Add a validation rule to the city column. Use the validation list from the practice_reference tab
Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License.