Prevent Invalid Data Being Entered in to Cells in Excel

Excel includes tons of useful functions, commands and other features. To grasp all of them is not easy. For example you can see several functions like date & time, database, engineering, financial, information, logical etc. in Excel. In my opinion it is not important to learn all of them. It is better to concentrate on the area that matter you most.

But some features in Excel can’t be just ignored by anyone. In this tutorial I wish to introduce you one such feature – Data Validation. If you apply Data Validation properly, you can ensure the data is being entered in the workbook accurately. Data validation options are located in the Data Tools group (Microsoft Excel 2007). It is advisable to apply Data Validation when you want to share your workbook with others in your organisation.

See the screenshot taken from my sample data prepared for this tutorial below.

Marks scored by four students out of 50 in three different subjects are shown. If any user enter marks of students out of 100 instead of 50 (Example : A’s mark in Physics as 90 instead of 45), you can ask Excel to prevent the wrong entry.

Here I will explain you how to configure data validation.

You can ask Excel to show your customized message (hint) when a user selects a cell for entry. To do this first select the cells (in the sample data B2:D5). Go to Data Validation->Input Message and enter title and message to be shown.

To limit the marks being entered in between 0-50, under Settings tab set the criteria as follows.

Here you should note one important point. The above configuration will only allow user to enter value between 0-50 as the default settings under Data Validation->Error Alert is Stop. You can set it to Warning or Information (with your own title and message).

See the three Error Alerts below.

You can also set the data validation criteria to date, time, text length etc. to control the input value depending on your data.

To remove Data Validation, select the cells where it is applied and go to Data Validation->Settings and Click Clear All.

If you have any query regarding the above article feel free to ask me in the comment section below.