If you use Google Sheets for monthly reporting, data cleanup, or tracking entries from multiple sources, you’ve probably spent more time than you’d like hunting down duplicates by hand. It’s tedious, easy to mess up, and, worst of all, completely avoidable.
We used to do the same thing. Scrolling line by line, comparing names, IDs, or invoice numbers to make sure nothing was entered twice. But once we started using Google Sheets’ built-in tools to highlight duplicates, it became one of those productivity hacks we rely on every single month.
Duplicates in your spreadsheets aren’t just annoying. They can skew results, inflate totals, and throw off analysis. Whether you’re pulling in customer data, logging expenses, or compiling leads, it’s crucial to catch any repeated entries before they reach your final report.
Let’s say you run a small event-planning business and use Google Sheets to track client inquiries. You’ve got one form on your website, another used internally by your sales team, and a few leads coming in from networking events that get entered manually. (Or maybe you connected Google Forms to Sheets to automate it). In any case, all of that data gets dropped into a shared spreadsheet every week.
By the end of the month, you’ve got dozens of entries, and it’s not uncommon for a client’s name or email to appear more than once, especially if someone filled out the form twice or a teammate logged a lead you already had. In the past, you might have skimmed through the list to catch duplicates by eye or tried sorting the sheet and scanning for repeats.
Now, using the highlight duplicates trick with conditional formatting, you can flag any repeated contact details instantly. You avoid sending two different people the same follow-up email. You also avoid chasing leads that have already been closed.
In just a few clicks, you clean up the list and save yourself hours of cross-checking. And your CRM stays more accurate as a result.
That’s where Google Sheets comes in. With just a few clicks, you can apply Conditional Formatting to highlight any repeated values in a column or across a range, making errors jump out instantly. The trick is highlighting duplicates in Google Sheets.
Here’s the step-by-step process we use every month:
- Select the column or range of cells you want to check for duplicates.
- In the top menu, go to Format > Conditional formatting.
- In the side panel, under Format cells if, choose Custom formula is.
- Use the formula:
=countif(A:A, A1)>1
This example checks column A. You can adjust the range and cell reference to match your sheet.
- Choose a highlight color, then click Done.
Immediately, any cells with duplicate values will be highlighted. No more guesswork. No more manual cross-checking.
Clean As You Go
Once duplicates are highlighted, it’s easy to decide what to keep and what to remove. You can sort the sheet to group duplicates together or use filters to isolate and review them one at a time. If you want to remove duplicates entirely, you can also use the Data menu. Choose Data cleanup. Then, choose Remove duplicates.
Just make sure you have a backup copy in case you need to double-check anything later.
This trick has saved us hours. Instead of eyeballing spreadsheets or writing complicated scripts, we can now scan a sheet in seconds. For recurring reports, especially those that pull in data from shared forms or multiple team members, this kind of automation makes reporting cleaner, faster, and far less stressful.
Whether you’re managing a budget, organizing survey results, or consolidating customer feedback, catching duplicates early makes everything downstream smoother.
