When you’re working in a spreadsheet, it’s often useful to ensure that users input values that meet certain conditions. These are easy to set up, and help you control your files. Let’s learn how to use data validation in Microsoft Excel.
How to Use Data Validation in Excel
Imagine that you’re sharing a spreadsheet that requires a date to be entered into a certain cell. Or, you have a file that asks for a five-digit zip code to be entered into cell D1. If other people are responsible for those inputs, it’s useful to ensure that Excel won’t accept inputs that don’t match the given condition.
This feature is built into Excel, and it’s called data validation. It’s a quick and easy way to ensure you see the desired inputs when users edit a certain cell.
Using zip codes as an example, go ahead and click into cell D1. Since zip codes have five digits, you’ll want Excel to reject any input with more or less than five digits in it.
With the cell selected, go to the Data tab on Excel’s ribbon. Over on the right, you’ll see the Data Validation dropdown menu. Click on it. The Data Validation tab will open up.
On the Settings tab, you’ll see the Allow dropdown underneath Validation Criteria. This is where you begin specifying the conditions for a given cell. In this case, even though you’re using a numerical input, you’ll want to select Text Length. Other options include Date, Time, Custom, and more.
The minimum and maximum values should both be set as 5. This will lead Excel to reject any input that doesn’t have exactly five digits.
When you’re finished, click OK. Then, try and type a random, non-five digit number into cell D1. Excel will block the input and return an error message:
Type in a five-digit zip code, and the input will be accepted normally.
As you can see, data validation makes it easier to gain and maintain control over your Excel spreadsheets.
Print Area in Excel