How To Find Duplicates In Google Sheets (2025 Update)

Have you ever found yourself buried in a mountain of Google Sheets rows and columns, scratching your head because the data just doesn’t add up?

Well, the chances are that duplicates are to blame! 

Learning to find duplicate data can seriously make navigating your spreadsheet and analyzing information faster, more accurate, and much less frustrating.

Essentially, to find duplicates within your Google Sheets data, you must use conditional formatting and the ‘custom formula’ rule.

This will automatically highlight all duplicate data within your spreadsheet.

To highlight duplicates in Google Sheets, simply use conditional formatting with a custom formula: =COUNTIF (A:A, A3)>1

I know this seems complicated….but trust me, it’s easier than it sounds!

To highlight duplicates within Google Sheets:

Open the relevant Google Sheets spreadsheet.

Select the entire column you want to scan for duplicates.

Click ‘Format’ in the top menu.

Select ‘Conditional formatting’ from the dropdown list.

In the right-hand sidebar, go to the ‘Format cells if’ dropdown and select ‘Custom formula is.’

Enter the COUNTIF formula, for example: =COUNTIF (A:A, A3)>1

This formula detects duplicates by essentially checking the entire column for any value that appears more than once.

Depending on which column you want to highlight data in, simply tweak the formula to match your column.

For example, if you want to highlight all names that pop up more than once in column ‘B’, type =COUNTIF (B:B, B3)>1

And that’s it! You should now be able to spot any duplicates within your Google Sheets spreadsheet at a glance.

But what if you want to find duplicates within various columns and rows within your spreadsheet?

To count duplicates in multiple rows and columns within your Google Sheets spreadsheet, you must use a different custom formula that scans the entire spreadsheet data.

Here’s how you can do it:

Highlight the columns and rows you want to check for duplicate data.

Repeat steps 1–3 from the earlier section to open the right-hand ‘Conditional Formatting’ sidebar.

In the ‘Format cells if’ dropdown, select ‘Custom formula is.’

Type the following formula =COUNTIF($A:$Z, INDIRECT(ADDRESS(ROW( ), COLUMN()))) > 1

This formula essentially checks the entire range (from column A to Z) to showcase any value that shows up more than once, across all your rows and columns!

Under ‘Formatting style,’ you can then select how you want these duplicates to appear (e.g., bold text, background color).

Click ‘Done’ to apply the rule.

And what if you want to only find duplicates in specific rows and columns? Simply use the ‘Apply range’ feature! To do this:

Within the Conditional Formatting sidebar, navigate to the ‘Apply to range’ box.

As you can see, you don’t need to add a completely new rule! Simply edit the range, and Google Sheets will update your rule automatically. It’s that easy.

If you’re quickly wanting to remove duplicates in Google Sheets (without manually finding them first), Google Sheets makes it super simple!

Just follow these steps:

Click on any cell within your data range.

Select ‘Data > Data cleanup > Remove duplicates’ from the top menu.

In the ‘Remove duplicates’ window, choose the columns you want to check for duplicates.

If your data includes headers, select ‘Data has header row’.

Click ‘Remove duplicates’.

And that’s it! Google Sheets will then share how many duplicates were found and removed, meaning you’ll instantly know what changed!

If you’re dealing with a large amount of data, duplicates can seriously cause significant issues in datasets, leading to confusion and inaccurate results within your business.

Whether managing customer contacts, tracking inventory levels, or organizing extensive project lists, duplicates can ultimately undermine the reliability and integrity of your data. 

These duplicate entries can lead to miscounts, incorrect calculations, communication errors, and more.

Duplicate customer entries can result in sending multiple marketing emails or invoices, while duplicate inventory records can lead to overstocking or stockouts.

And in project management, duplicate tasks can confuse team responsibilities or deadlines.

But luckily, Google Sheets has your back with simple formulas (that do the heavy lifting for you!).

By implementing these easy yet effective strategies, the data within your spreadsheet will always be accurate.

If you’re looking to efficiently identify and manage duplicate records within Sheetify CRM, Sheetify Bookkeeping, or Sheetify Projects, you’re in luck!

All three Sheetify business toolkits are built entirely with Google Sheets, meaning you can use the same steps above to find duplicates in single or multiple columns.

This allows you to identify whether you have any duplicate contacts, inventory items, project tasks, and more, resulting in a clearer and more accurate database overall.

Sheetify business toolkits are the perfect solution for small and micro teams looking for a spreadsheet-based solution packed full of features they’ll actually use.

Find out more about my three Google Sheets small business templates here.