Circle Invalid Data in ExcelEasily spot invalid data by circling them

  |

Even if there are other tools out there nowadays that do spreadsheets and workbooks, Excel is still widely used and highly favored.

If you take a look at the many features and functions you can use in Excel, you’d understand.

One of these features that can help you when working with a dataset, be it large or small, is Data Validation.

What is Data Validation?

With Data Validation in Excel, you can set parameters on which type of data can be entered into your worksheet.

You can set this cell by cell, by a group of cells, or by the entire worksheet.

For example, you can restrict an entire column to only have numeric values 0 to 15.

Here’s an illustration:

What I did here is set column A to only have whole number values of 0 to 15 with Data Validation.

Now when I try to enter a value that is outside of these parameters, an error message will appear:

I tried to enter a whole number of 16 in cell A, but Excel is preventing me from doing that.

This is because 16 is outside the parameters I set with Data validation. Now I’ll try to enter a non-numeric value:

And the same error message appears.

Data validation is a great tool to restrict which type of data any user of the worksheet may enter.

If you want a certain column to only contain dates, Data Validation can help you with that.

If you want a particular column to only have a specific number of characters or less, Data Validation can also help you with that.

Not only that, but you can also control formulas and the results with Data Validation.

You can also customize Excel data validation messages to help guide users of the worksheet on which values are valid.

The Data Validation Button

Pretty nifty feature, isn’t it? You can access Excel’s Data Validation feature from the Data tab.

In the middle-right section of the ribbon, you’ll find the Data Validation button. Click on it, and you’ll be presented with three options:

(1) “Data Validation” allows you to set the parameters for data validation, (2) “Circle Invalid Data” circles any value that is outside of the set parameters, and (3) “Clear Validation Circles” removes the circles added by Circle Invalid data.

Invalid Data (even with Data Validation)

Now while Data Validation is a great Excel feature, it still has its limitations.

It works best if the cells don’t have any data in them yet. But for cells that do, even if you set the Data Validation parameters to make certain values invalid, such data will not be removed.

You won’t even see an error message.

To illustrate, let’s say we have this dataset here:

I will set the Data Validation parameters in cells B3:D12 to only have a numeric value greater than or equal to 50.

After clicking the OK button, any value that is below 50 should be removed, right?

Wrong. The invalid data are still there. Just see cell B9 which has a value of 48 (which is less than 50).

Even if you have set the Data Validation parameters on an empty cell, it can easily be bypassed by copying from a cell that doesn’t have data validation and then pasting it on the empty cell with data validation. This will destroy or replace the data validation in such a cell.

Using formulas can also bypass a cell’s data validation.

For example, here I have set the data validation in cell A3 to only have a numeric value of 15 to 30.

Notice that cell A3 contains a formula that adds the values in cells A1 and A2.

With the values now, cell A3 is within its set data validation parameters.

But what if change the value in cell A2 to make cell A3 have a value that is outside of the data validation parameters? Will an error message appear?

The answer is no. Excel will continue to treat the data as valid (even if it’s already invalid).

So what are you to do in these situations?

Circle Invalid Data in Excel

Thankfully, Excel has another data validation feature that can help you easily spot invalid data within a dataset: Circle Invalid Data.

This feature does what it exactly says, circle invalid data.

It can be accessed by clicking on the Data Validation button and selecting the Circle Invalid Data option.

When you click on this option, any value that is not within the parameters set via Data Validation will be circled.

This makes it easier for you to find any value or data that needs to be corrected or removed.

For example, let’s say we have this dataset:

It contains the scores each player got for each game (1,2, and 3).

To proceed to the next round, a player must have a minimum score of 50 for each game.

For example, if a player has scores of 51, 51, and 51, s/he will proceed to the next round.

If a player has scores of 91, 100, and 49, s/he will not qualify. Basically, if a player scores less than 50 in any of the games, they’re no longer qualified.

Thus, we want to identify scores that are less than 50. To do so, we will use the Circle Invalid Data feature.

How to Circle Valid Data

  • Select the cells where you want to perform data validation. In our illustration, these are cells B3:D12.
  • Open the Data Tab. Then, click the Data Validation Button, and select Data Validation from among the options.

  • This will open the Data Validation menu. In our illustration, we need to set the parameters so that only values (scores) that are at least 50 are valid. We click the OK button after setting the parameters.

  • The selected cells should now have the data validation parameters.
  • To identify any invalid data, open the Data Tab. Then, click the Data Validation Button. This time, select Circle Invalid Data from among the options. This should circle any scores below 50.

  • We have successfully identified invalid data.

To remove the circles, you only need to click on the Data Validation button and select Clear Validation Circles from among the options.

Also, note that these circles will not be printed.

Conclusion

And that’s how you can easily identify invalid data in Excel: by using the Circle Invalid Data feature.

While Excel’s Data Validation feature is great, it still has its limitations.

Thankfully, there’s the Circle Invalid Data feature to address these limitations.