You can use data validation in Microsoft Excel to stop incorrect data entries. But there may be times when that validation doesn’t work as planned. To help, you can use the Circle Invalid Data feature.
With data validation, you may have a formula in the cell or have data already entered in the cell. Unfortunately, these types of situations can skip right past the data validation you require. If this happens, you can end up with incorrect data. Let’s look at how to spot it by having Excel circle it for you.
Error Handling With Data Validation
When you set up the data validation for a cell or range, you can add error handling at the same time. This is a good way to let the user know that what they’re trying to enter isn’t valid.
You can also provide a message letting them know what they should enter.
Circle Invalid Data
A perfect example of data validation not doing its job is when you use a formula. You might require a specific number, decimal, or date. If the result of that formula changes and the data no longer meets the requirement, that data is allowed, and you won’t see an error.
RELATED: The Basics of Structuring Formulas in Microsoft Excel
Let’s look at an example. Here we have a formula that adds the values in cells A1 and A2 and places the result in A3. We have data validation set up in cell A3 to only allow a number between 10 and 20.
At first, the result from the formula fits within the data validation requirement.
But if the numbers in cells A1 or A2 change, thus changing the formula’s result, the data is still allowed in that cell even though it’s invalid. Plus, you don’t see an error message.
In this case, you can use the Circle Invalid Data feature.
Go to the Data tab, select the Data Validation drop-down arrow, and pick “Circle Invalid Data” in the Data Tools section of the ribbon.
You’ll then see that cell with data validation and incorrect data with a red circle around it.
This allows you to make whatever corrections are necessary. The circle is then removed. You can also select Data Validation > Clear Validation Circles if you want to accept the data.
Another prime example of invalid data is if you already have data in a cell and then set up data validation. Here, we have text in our cell that’s longer than 10 characters. Even though we set up the validation to accept only text less than 10 characters, the validation doesn’t catch the invalid data because it’s pre-existing.
In this case, using Circle Invalid Data works as expected.
There may be instances other than these examples where invalid data makes its way into a cell with data validation. As Microsoft mentions, this can happen when you copy and paste data into the cell, use the fill feature, or run a macro. However, you may simply see an error disallowing you from pasting or filling the data. Or, in some cases the data validation is simply removed.
While Circle Invalid Data may not work in each of these additional situations, keep these other scenarios in mind when you initially set up data validation. And, remember to use the Circle Invalid Data feature when you can.
RELATED: How to Add a Drop-Down List to a Cell in Excel
