Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Circle Invalid Data in Excel (4 Suitable Ways)

Oftentimes, our Excel dataset contains invalid data that is difficult to identify and causes problems during Data Analysis. Now, wouldn’t it be great if there was a tool to identify the invalid data in a dataset? Fortunately, in this article, we’ll discuss 4 cases of how to circle invalid data in Excel. Moreover, we’ll also describe how to clear validation circles in Excel.


Download Practice Workbook


What Is Invalid Data in Excel?

In simple terms, Invalid Data refers to unexpected or out-of-bounds data caused by corrupted or missing information at the source. Hence, it creates the incorrect generation of the data.


Invalid Data Example

In this situation, an example of invalid data can help us understand more about this common issue. In the screenshot given below, the marked cells represent invalid data since “Price” cannot be a negative number.

circle invalid data in excel invalid data example


4 Useful Ways to Circle Invalid Data in Excel

First and foremost, let’s assume the List of Cell Phone Prices dataset shown in the B4:D13 cells, which contains the “Cell Phone” name, “Old Price”, and “New Price” respectively. Here we want to circle invalid data in Excel using the Data Validation feature. Henceforth, let’s observe and learn each of the cases in detail and with the appropriate illustrations.

Dataset for Circling Invalid Data in Excel

Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.


1. Circle Invalid Data Based on Number

In the first place, let’s start with the most common invalid data, that is to say, the invalid number data. So, let’s see how we can easily identify and amend this issue.

📌 Steps:

  • First, select the D5:D13 cells >> go to the Data tab >> click on the Data Validation option.

Circle Invalid Data Based on Number

Now this opens the Data Validation window.

  • Second, choose the Whole Number option >> enter the between option >> insert the Minimum and Maximum bounds i.e., “0” and “2000” respectively >> hit OK.

Data validation steps for whole numbers

  • Third, press the Circle Invalid Data option which marks the incorrect data as shown in the image below.

Circle Invalid Data in Excel Based on Number

Read More: How to Put a Circle Around a Number in Excel (4 Quick Tricks)


2. Circle Incorrect Data According to Text Length

In a similar manner, we can also apply Data Validation to a range of cells containing texts. This is most useful when checking for incorrect ID numbers, phone numbers, etc. In this scenario, let’s consider the Sales Rep of the Year dataset showing the “ID”, “Year”, and “Name” of the employees. Here, the “Employee ID” consists of 7 characters, and we want to identify any anomalies present within the data.

Circle Incorrect Data According to Text Length

📌 Steps:

  • First of all, select the range of cells, in this case, B5:B13 cells >> move to the Data tab >> hit the Data Validation icon >> follow the steps in real-time in the GIF given below.

Circle Incorrect Data in Excel According to Text Length

Read More: How to Circle Text in Excel (3 Easy Ways)


3. Circle Unsound Data Based on Date, Time, and List

Alternatively, we can also check for incorrect data even if it is formatted as Date, Time, or listed from other cells. Impressive right? That is the power of Excel. On this occasion, suppose we have the Product Delivery Info dataset shown in the B4:E13 cells containing the “Product”, “Delivery Date”, “Time”, and “Day” columns.

Circle Unsound Data Based on Date, Time, and List

📌 Steps:

  • At the very beginning, choose the C5:C13 range >> navigate to Data Validation >> select the Date option >> click on less than >> enter the End date, which is “2/28/22”.

Data Validation for dates

  • Likewise, click on the Time option >> choose between option >> type in the Start and End times, for instance, “9:00 AM” and “5:00 PM” (typical working hours).

Data validation for time

  • Later, for the “Day” column, press the List option >> enter the Source, for example, the B16:B20 cells which contain the weekdays from “Monday” through “Friday”.

Data Validation for list

  • Finally, select the C5:E13 range >> hit the Circle Invalid Data button.

Circle invalid Data in Excel Based on Date, Time, and List

Read More: How to Draw Circle in Excel (4 Simple Methods)


4. Circle Erroneous Data with Custom Formula

For one thing, we can also utilize Excel functions to check for any invalid data lurking within the dataset. In this situation, we’ll employ the ISNUMBER function which checks if a cell contains a numeric value or not and returns TRUE or FALSE accordingly.

📌 Steps:

  • In truth, the procedure is remarkably similar, so you can follow the steps shown in the animated GIF given below.

Circle Erroneous Data with Custom Formula

Read More: How to Circle Something in Excel (2 Suitable Ways)


How to Clear Invalid Data Circles in Excel

Last but not least, once the invalid data has been identified and fixed, you may want to remove the circles. Luckily, clearing invalid data circles in Excel involves only a handful of clicks. Therefore, just follow along.

📌 Steps:

  • To begin with, select the cells containing the circles (here it is the D5:D13 array) >> in the Data Validation drop-down, press the Clear Validation Circles button.

How to Clear Invalid Data Circles in Excel

  • Boom! That is how simple it is to clear the circles from the dataset as evident from the screenshot below.

Clear circle invalid data in excel


Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Practice Section


Conclusion

To sum up, we hope this tutorial has provided you with helpful knowledge on how to circle invalid data in Excel. Now, we recommend you apply all this know-how in the practice dataset by downloading the practice workbook. In addition, feel free to comment and provide your valuable feedback.


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo