Let me guess: you are working in Google Sheets and your data is a mess. You know there are duplicates, but you just don’t know how to find duplicates in Google Sheets and remove them all manually. I have been there. It gets worse when you try to sort data or create reports, and everything gets skewed because of repeated entries.
Whether you are managing an email list, handling student names, or tracking sales, duplicates can seriously mess things up. So today, I am going to show you, in a very clear and simple way, exactly how to find duplicates in Google Sheets, highlight them, and even remove them.
And yes, I will also touch on how to delete duplicates in Excel because many of us bounce between the two.
What Are Duplicates and Why Should You Care?
Start creating Verdict in seconds, and convert more of your visitors into leads.
Before getting into the nitty-gritty, let me explain what duplicates are and why they matter.
“A duplicate is simply any repeated value or set of values in your data.”
This could be a single word, number, email address, or even a full row. Duplicates might look harmless, but they can mess up your analytics, inflate your results, and create confusion in decision making.
Whether you are cleaning up a contact list or reviewing customer entries, knowing how to highlight duplicates in Google Sheets can save you hours of manual effort and give your data the polish it deserves.
How to Find Duplicates in Google Sheets?
Start creating Verdict in seconds, and convert more of your visitors into leads.
If you are someone who likes to see duplicates visually, this is the perfect method. Conditional formatting lets you highlight duplicates in Google Sheets instantly.
How to Highlight Duplicates in Google Sheets – Step-by-Step
- Open your Google Sheet where you want to check for duplicates.
- Select the column or cells you want to examine. For example, if you want to check column B, select your entities.
- In this example, I am selecting B1:B9.
- Go to the “Format” tab in the top menu.
- Click on “Conditional formatting” from the dropdown.
- A panel will appear on the right side of your screen.
- Under “Apply to range,” confirm that your range (like B1:B9) is correctly selected.
- Under “Format cells if,” choose “Custom formula is.”
- Enter this formula: =COUNTIF(B:B, B1)>1
- Pick a formatting style, like a background color, to highlight duplicates.
- Click “Done.”
That’s it. You will now see duplicate values in that range highlighted in your chosen color.
If you want to merge cells in Google Sheets, you can read our guide on How to Merge Cells in Google Sheets
How to Remove Duplicates After You Find Them
Start creating Verdict in seconds, and convert more of your visitors into leads.
Once you have identified duplicates, the next question is – what now? You probably want to clean up your sheet. Luckily, Google Sheets makes it pretty easy.
1 – Remove Duplicates Using the Built-in Tool
There is a built-in feature that helps you delete duplicate rows in a snap. Here is how you can do it:
- Select the range of data.
- Click on Data from the menu.
- Choose Data cleanup.
- Click Remove duplicates.
- Confirm your selection and click Remove duplicates.
Google Sheets will scan the selected range and automatically remove any identical rows. Super quick and effective.
2 – Filter and Delete Duplicates Manually
Sometimes, you want a bit more control. In those cases, you can use filters and remove the duplicate entries in Google Sheets manually.
- Select the data range.
- Click Data and then Create a filter.
- Choose the filter icon on the column.
- Sort A to Z or Z to A.
- Scroll through and manually delete the repeated entries.
This method is useful if you want to review the duplicates before deleting them.
How to Delete Duplicates in Excel (Because Many of Us Use Both)?
Start creating Verdict in seconds, and convert more of your visitors into leads.
Now, let’s be real. Most of us jump between Google Sheets and Excel. So, if you are wondering how to delete duplicates in Excel, I have got you covered there too.
Use Excel’s Remove Duplicates Feature
- Open your Excel file.
- Select your data range.
- Go to the Data tab.
- Click Remove Duplicates.
- Select the columns to check and hit OK.
Boom. Excel removes those pesky duplicates in a flash. It is almost identical to the process to remove duplicates in Excel with Google Sheets’ cleanup tool.
Things to Keep in Mind When Working With Duplicates
Start creating Verdict in seconds, and convert more of your visitors into leads.
Before we wrap this up, I want to share some personal tips that help me avoid mistakes when handling duplicates.
- Always Keep a Backup: Before removing anything, duplicate your sheet or make a copy. I learned this the hard way after deleting valuable entries once.
- Review Column Selection: When using the built-in cleanup tool, make sure you select the correct columns. Sometimes duplicates are not exact across all columns, so double checking is worth it.
- Automate When Possible: If you frequently work with large datasets, consider using Google Apps Script or plugins like “Remove Duplicates” by Ablebits to automate things.
Final Thoughts – Keep Your Data Clean and Efficient
Start creating Verdict in seconds, and convert more of your visitors into leads.
That’s all about how to find duplicates in Google Sheets. Finding and removing duplicates might seem like a small thing, but trust me – it can make a huge difference in how accurate and trustworthy your data is. Once you get the hang of these methods, you will start spotting and fixing duplicates like a pro.
Whether you use formulas, conditional formatting, or built-in tools, just remember to stay organized, double-check your actions, and always keep a backup. Clean data equals smart decisions. That’s a fact I have learned over and over again.
