How to Add Input Restrictions to Validate Data in Excel

When entering data in Excel, sometimes there are specific things you don’t want in your dataset. For example, maybe you’d like prices to be rounded up to the nearest dollar, or to ensure that people in a survey are between a specific age. Today we’re going to learn about input restrictions, and how to use them to ensure improper data isn’t corrupting your workbook.

1. Open Excel and choose a Blank workbook. You can also use an active workbook with the data already filled in, just be sure to save a copy in case you make a mistake. We’re going to use dummy data to set input restrictions on age, although we could also use it for gender in this example, by requiring entries to be a single letter: either M or F.

2. Select additional cells in the column you’d like to add validation restrictions for. In this case, we’ll select to row 15, and ensure that each college student answering our survey questions is between the ages of 18 and 25. If we should enter a larger or smaller number, we’ll receive an error message informing us about the input restriction.

3. Click the Data tab at the top of the page.

4. Click the Data Validation icon, and select the top option, Data Validation.

5. In the Allow drop-down, select Whole number.

6. Choose a minimum and maximum age and add it to the appropriate section.

7. Switch to the Input Message tab within the same box.

8. Add a title and input message. This message displays when someone enters text outside of your pre-determined parameters.

9. Press OK. Now when you enter text outside of our age range (18 to 25), you’ll be met with a pop-up error message telling you about your mistake. The workbook will not accept numbers outside this range, at least in the column(s) we selected previously.