How to Validate Data in Excel

1. Know the types of data that you can validate in an Excel spreadsheet. Numbers, dates and times and length can be limited to whole numbers, or ones including decimals, be a minimum or maximum amount or within a range.
2. Use a list of values for the cell. This is helpful when categorizing items with a specific list of data or when the cell always contains one choice such as small, medium or large.
3. Decide if you want the user to view a message when the cell is selected prior to them entering data (input message) or when incorrect data is entered (error message.) Error messages can simply provide information about what should be in the cell, a warning that the data doesn't fit the cell or a stop message not allowing any data that isn't correct to be entered.
4. Set up and name the list. The list can be in the same worksheet or in a different one. Once the data is entered, highlight all relevant cells and click on the 'Insert' command. Choose 'Name' then 'Define.' Enter the equals sign and the name of the list, for instance, '=listname.'
5. Choose one of the cells that you want to validate and select the 'Data' command. Choose 'Validation' and under the 'Settings' tab select the appropriate description for the cell limits. Depending on the choice, additional boxes will appear that need to be filled in.
6. Click the 'Format Painter' icon on the top of the toolbar and click the cell you just validated. Highlight any other cells that have the same validation criteria.