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.
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.
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.
Here, we have used the Microsoft 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.
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.
- Third, press the Circle Invalid Data option which marks the incorrect data as shown in the image below.
Read More: How to Perform Data Validation for Alphanumeric Only in Excel
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.
📌 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.
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.
📌 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”.
- 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).
- 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”.
- Finally, select the C5:E13 range >> hit the Circle Invalid Data button.
Read More: Excel Data Validation for Date Format
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.
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.
- Boom! That is how simple it is to clear the circles from the dataset as evident from the screenshot below.
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.
Download Practice Workbook
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
- How to Use Data Validation in Excel with Color
- How to Create Data Validation with Checkbox Control in Excel
- [Fixed] Data Validation Not Working for Copy Paste in Excel
- Excel Data Validation Greyed Out
<< Go Back to Data Validation in Excel | Learn Excel
