Introduction
To prevent inaccurate data entry, utilize data validation in Microsoft Excel. However, there may be situations when such validation does not perform as expected. You may use the Circle Invalid Data function to assist you.
You may have a formula in the cell or data previously typed in the cell when using data validation. Unfortunately, in certain cases, the data validation you need may be bypassed. If this occurs, you may get wrong data. Let’s see how to find it by having Excel circle it for us.
Data Validation and Error Handling
When you configure data validation for a cell or range, you can also configure error handling. This is a nice technique to inform the user that the information they are attempting to input is invalid.
You may also leave a note instructing them on what they should input.
Invalid Data Circle
When you utilize a formula, you have a wonderful example of data validation failing to perform its job. You could need a certain number, decimal, or date. If the outcome of that formula changes and the data no longer matches the criterion, that data is permitted and no errors will be shown.
Consider the following example. Here is a formula that sums the values from cells A1 and A2 and stores the result in cell A3. In cell A3, we have data validation set up to only accept numbers between 10 and 20.
At first, the formula’s output meets the data validation criteria.
However, if the values in cells A1 or A2 change, causing the formula’s result to alter, the data is still permitted in that cell, even though it is incorrect. Furthermore, no error notice is shown.
You may utilize the Circle Invalid Data functionality in this scenario.
Go to the Data tab, then to the Data Validation drop-down arrow, and then to the Data Tools area of the ribbon.
You’ll then notice a red circle around the cell with data validation and inaccurate data.
This gives you the ability to make any required changes. After then, the circle is eliminated. If you wish to accept the data, go to Data Validation > Clear Validation Circles.
Another example of incorrect data is when you have data in a cell and then apply data validation to it. We have text in our cell that is more than 10 characters long. Even if we configured the validation to permit only text of less than 10 characters, the erroneous data is not caught since it already exists.
In this case, using Circle Invalid Data works as expected.
Using Circle Invalid Data works as predicted in this scenario.
Other than these examples, there may be times when erroneous data enters a cell using data validation. This may happen when you copy and paste data into the cell, utilize the fill tool, or execute a macro, according to Microsoft. However, you may merely encounter an error message that prevents you from copying or filling in the data. In certain circumstances, data validation is simply dropped.
…validation messages will not show, and incorrect data may be entered if you copy or fill in data in a cell, if a formula in the cell calculates an invalid result, or if a macro enters faulty data in the cell.
While Circle Invalid Data may not work in all of these additional instances, keep them in mind while configuring data validation. Remember to utilize the Circle Invalid Data option whenever possible.
