Data Cleaning with Excel: How to Remove Duplicates & Identify Blank Cells with Conditional Formatting ⚙️
Data cleaning is one of the most important steps in deriving accurate insights, and it is also a time-consuming task in any data analysis workflow. Developing valuable data cleaning skills can significantly enhance reliability and credibility.
I am going to introduce common issues in data, such as blank cells and duplicate rows, and the fixing method using Excel’s built-in features that make it easy to spot issues and fix them efficiently.
Through these data cleaning journeys, you’ll not only learn what to do but why these steps matter, helping you build a solid foundation in data cleaning.
In this guide, we’ll walk through the essential techniques every beginner should know, how to identify blank cells with conditional formatting and remove duplicates, with hands-on practice to clarify how these techniques work in a real dataset. Let’s get started!
Identify Blanks with Conditional Formatting
💎Working environment for this content: Updated version of Excel, 2025
There is a wide range of options for this task using conditional formatting in Excel. Here are two methods to quickly identify blank cells in a specific data range.
The first approach is to use ‘Highlight Cells with’ in conditional formatting to identify blank cells.
To identify blank cells with conditional formatting, first click on any cell in the data range and then click Command + A (on Mac) or select your data range.
Next, go to the Home tab, select Conditional Formatting, and choose New Rule. The conditional formatting dialog box will automatically appear on the right side of the workbook.
Check the data range you want to inspect for blank cells in your data, and click the arrow next to ‘Highlight Cells with’ to choose the ‘Blanks’ option.
Once that is done, choose the ‘Format Style’ to select a specific color. Finally, click the ‘Done’ button. The blank cells in the data range will appear in the color you chose.
The second method is to use ‘format cells where a formula is true’ in conditional formatting, input the formula ISBLANK(A1:H20).
In the same way, check the data range or edit it, click the formula button, input the formula, choose one of colors, and lastly click the done button. The final result will appear in the color you selected.
👉Tips:
If you want to remove the highlighted blank cells in conditional formatting, go to the Home tab, click on ‘Conditional Formatting’, choose ‘Clear Rules’, and then click on ‘Clear Rules from Selected Cells’. This will automatically remove all highlighted cells in the conditional formatting and return them to their original condition.
Another method is to use the ‘Clear’ button (eraser icon) in the ‘Home’ tab. Click on the ‘Clear’ button and select ‘Clear Formats’.
👉Learning Resources:
- Excel Insider, How to find blank cells in Excel: https://excelinsider.com/excel-pro-tips/blank-cells/how-to-find/
- YouTube Video: https://www.youtube.com/watch?v=IBDG43yqfjA
Remove Duplicates
To remove duplicates in the data, first use conditional formatting to find and highlight them. Before actually removing the duplicates, ensure they are truly duplicated entries in the data.
Get WonHee Lee’s stories in your inbox
Join Medium for free to get updates from this writer.
To find duplicates, go to the Home tab and click on Conditional Formatting. Then, choose the ‘Highlight Cells Rules’ in the box, and click on the ‘Duplicate Values’.
A dialog box for conditional formatting will appear on the right side of the workbook. Then, choose the data range where you want to find duplicates, choose the ‘Highlight Cells with’, select the ‘Duplicate Values’, pick a color in the Format Style, and finally, click the ‘Done’ button. The duplicated entries in the data will now be highlighted in the color you selected.
Next, go to the Data tab, click on the ‘Remove Duplicates’ button in the Excel ribbon, and then a dialog box will appear. Make sure to check the correct columns and select ‘My data has headers’.
Finally, click on the ‘OK’ button. You will then receive an Excel message indicating the number of duplicates that were found and removed from your data. Once you confirm the message, click on the ‘OK’ button. Excel will instantly remove the duplicate rows.
👉Learning Resources:
- Microsoft, Find and remove duplicates: https://support.microsoft.com/en-us/office/find-and-remove-duplicates-00e35bea-b46a-4d5d-b28e-66a552dc138d
- Excelgraduate, How to Remove Duplicates: https://excelgraduate.com/remove-duplicates-in-excel/
- YouTube Video about removing duplicates in Excel: https://www.youtube.com/watch?v=GoZMQLYIgMA
Key Takeaways
You have strengthened two essential skills in data cleaning by learning how to remove duplicates and identify blank cells through conditional formatting in Excel.
You now understand that duplicates can mislead data analysis, and missing values can also affect data accuracy. These methods enable you to spot and handle data efficiently.
Through the step-by-step practice, you’ve learned:
- How to identify blank cells in data using the Conditional Formatting feature
- How to remove duplicates in data using the Remove Duplicates feature
These features support you in building foundational data cleaning skills to manage clean and reliable data.
Keep going-your skills are growing one step at a time!
Here is more data cleaning with Excel learning content:
🐢 Data cleaning with Excel: learn the Excel user interface and basic data cleaning tasks: https://medium.com/@whee.2013/data-cleaning-with-excel-learn-the-excel-interface-and-basic-data-cleaning-tasks-d93f97263969
🔗Connect with me:
