Check invalid data

Excel allows you to use Data Validation tools to check worksheets for incorrect data that could lead to inaccurate calculations or results. Cells containing invalid data are displayed with a red circle around them so you can easily find and fix any problems:

Step I: Check or modify the Data Validation rule

   1.   Create the Data Validation rule (see Check data entry for invalid entries for more details) if you haven't already created one.

For example, in the Data Validation dialog box, on the Settings tab:

• In the Arrow dropdown list, choose Whole Number,
• Specify a comparison operator and appropriate values (for this example, the data should be between 0 and 10):
  

Step II: Check the Data Validation rule

   2.   On the Data tab, in the Data Tools group, click the Data Validation dropdown list, then select Circle Invalid Data:

Excel draws circles around the invalid entries:

Notes:

1. If you correct an invalid entry, the circle disappears:
   
2. You can create different rules for different cells or data ranges and check all invalid data.

   For example:

   • Column Qty has the rule: Length equals 1,
   • The values in column Data should be greater than 1/1/2020,
   • Column Time should contain values between 9 AM and 5 PM (working hours),
   • The values in the column Mark should be between 5 and 12:
     

   After clicking the Circle Invalid Data button, Excel shows all invalid data rounded by red circles:

   

Remove red circles

To clear the Data Validation check (to get rid of the circles), on the Data tab, in the Data Tools group, from the Data Validation dropdown list, select Clear Validation Circles: