Excel provides Data Validation tool, which is extremely important when you need to check the validity of value being entered or showing messages to assist data entry. But if you have created a datasheet that never passed through the validation rules, then there is an extremely useful feature in Excel to mark out all the invalid entries. This post will cover how to identify invalid values in the datasheet.
Launch Excel 2010, and open a datasheet to check invalidity of values contained in cells. For instance, we have included a datasheet, containing marks obtained by students, as shown in the screenshot below.
Now we want to check if there is any invalid entry in our datasheet. For this, we need to apply data validation rule on the field. We will apply rule in the Marks field, that the value entered must be less than 100, as all the courses carries 100 marks.
To start off with applying rule on Marks column, Select the field on which you want to apply data validation rule and navigate to Data tab, click Data Validation.
Data Validation dialog will appear. On Settings tab, from Allow drop-down list you can select any data type that suits your selected field data. We have selected Whole number, from Data list select between, and select range of data from Minimum to Maximum value as shown in the screenshot below. Click OK.
Now from Data Validation options, click Circle Invalid Data.
Upon click, all the invalid entries will automatically be circled, as shown in the screenshot below.