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.
