Home Work How to Circle Invalid Data in Microsoft Excel

How to Circle Invalid Data in Microsoft Excel

by Frank
How to Circle Invalid Data in Microsoft Excel


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.

Error settings for data validation

You may also leave a note instructing them on what they should input.

Error message for data validation

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.

Data validation for a number

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.

Formula result change

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.

Circle Invalid Data on the Data tab

You’ll then notice a red circle around the cell with data validation and inaccurate data.

Invalid data circled in Excel

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.

Clear Validation Circles on the Data tab

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.

Data validation for a text limit

In this case, using Circle Invalid Data works as expected.

Invalid data circled in Excel

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.

You may also like

Navhow is dedicated to teaching people all over the world how to do anything.



The Best How-To Newsletter Anywhere

©2022 Navhow, All Right Reserved