How to use data validation in Excel
Posted on December 5, 2008 at 5:23 am
In Excel we usually find a situation wherein we want to limit the values of certain fields. This tip is useful if you want to standardize a certain field and limit it to a few predefined values.
For example, we want a gender field to have only one of the two values: “MALE” or “FEMALE”.
Instead of letting users type “MALE” or “FEMALE” we can provide a dropdown like field to make sure we won’t type other information:
As an added validation, Excel will also prompt you if you inputted a value other than the options provided:
This is what we call data validation in Excel. So how do we do that? First position your cursor on the field that you want to add data validation. Then click the Data menu and then Validation:
The Validation dialog box will appear, enter the list of choices delimited by comma on the “source” field, check in-cell dropdown option, choose “list” on the “Allow” field:
You can also apply the data validation rule to a column or row or even a few selected cells. With data validation, you can be assured that there are no invalid data inside your spreadsheet.
Ben Carigtan shows you how it’s done.
» Filed Under MS Office Tips
Related Posts
- How to use Data Validation in Microsoft Excel to create dropdown lists
- Importing delimited files in Excel
- How to quickly sort data in Excel
- How to use conditional formatting in Excel
- How to filter data in Excel