Spreadsheets are often shared with others, and users can make changes to them. But you may find yourself wanting to control the values that users can insert into given cells. For example, you may need users to insert a value between 1 and 10 into cell D5. Fortunately, Excel lets you control inputs to ensure that correct data is used. Let’s learn how to use Data Validation in Microsoft Excel.
How to Use Data Validation in Excel
In the above example, you’re seeking inputs in cell D5 equal to at least 1, but no greater than 10. You want to stop others from erroneously entering in a value like 25, for example. You could add written instructions, but this isn’t a fully effective way to control your data. Excel’s Data Validation feature is here to help.
Make sure that you have the cell selected that you wish to set conditions for. To use Data Validation, navigate to the Data tab on Excel’s ribbon. You’ll see a variety of options here. The one you’re searching for is the Data Validation button on the right side of the Data tab. Click on it.
Excel will launch the Data Validation window. Here, you can specify conditions that the value in the cell should meet. On the Allow dropdown, you can choose from categories of data. For this example, click Whole Number.
The Data dropdown helps you specify ranges. You can choose Between in this example, or other conditions like Greater Than or Equal To. Finally, in the Minimum and Maximum boxes, you can numerically define your range.
When you’ve set your conditions, click OK. Excel will apply the Data Validation rules to your selected cell. If you or another user tries to type a number not matching the conditions, Excel will reject the input and display an error message.
As you can see, Data Validation is a quick and easy way to control inputs to cells on your spreadsheets.
Background in Excel