Restricting Cell Contents in Excel Using Data Validation

 

Did you know that you can restrict, give guidance on and create drop-down lists for what you can enter in a certain cell in Excel? It’s a pretty cool feature and it’s called Data Validation.

 

However carefully you design your spreadsheets, even if you lock cells and protect worksheets, you will still need to make sure that any data input is appropriate for your calculations. To do that you will often need to ensure that the data falls within certain limits. For example, if you want a user to enter a month number, it will need to be a positive whole number (no decimal places) between 1 and 12. If the user were to enter 13.78 this may, at best, result in many cell results ending up as error messages or, at worst, your spreadsheet producing the wrong answer.

 

Without resorting to the use of Visual Basic code, there is no way to make absolutely certain that the values entered in Excel fit your criteria, but you can make this a lot more likely by using Excel’s Data Validation feature.

 

Here’s how to use Data Validation to restrict what can be entered into a cell in Excel:

 

 

We hope you enjoyed that short video. To learn more about Data Validation and hundreds of other things in Excel, take a look at the Excel course that has helped hundreds of thousands of people improve their Excel capability.