Restrict data input in Excel

We all enter bad data into Excel from time to time. Maybe you're rushing, or someone distracts you, or you've just been doing it too long without a break. And when you open a document up to colleagues to help maintain, your data is only as good as their level of training and attention to detail.

Fortunately, there's an easy way to limit data entry to certain values. For instance, you could limit a range of cells to whole numbers between 1 and 100 if your values are always non-zero percentages. Or if you have alphanumeric product codes that are always the same length, you could have Excel enforce text strings that are exactly 8 characters.

Whatever your need, select the cells you want to restrict, go to the Data tab, and click the Data Validation button:

Now select the data type you want to allow on the Settings tab:

Now enter the values you wish to enforce:

If you like, you can enter a friendly Input Message that appears when a cell with data validation is selected:

The message above looks like this in a worksheet:

If you're sharing the workbook with your coworkers, you should also enter an Error Alert for invalid entries:

Now you'll see an alert dialog like this one whenever the data entered doesn't meet your criteria:

Suzanne