How to Find Duplicates in Google Sheets – Quick Methods

This document provides a detailed, step-by-step process to remove duplicates google sheets.
By following these instructions, users will learn easily pinpoint duplicates and streamline their data management tasks.

Need to spot repeated entries in your spreadsheet? Google Sheets offers a couple of easy ways to detect and manage duplicates—either visually using Conditional Formatting or by filtering them out with the UNIQUE function.

Method 1: Using Conditional Formatting

This method is great for visually highlighting any repeated data in a selected range.

Step-by-step:

1. Select the Data Range
   Click and drag to highlight the cells you want to scan for duplicates (e.g., A1:A100).

2. Open Conditional Formatting
   Go to the top menu and click Format > Conditional formatting.
   A sidebar will open on the right.

3. Use a Custom Formula
   Under “Format cells if”, choose “Custom formula is”.
   Enter this formula (for column A):

4. Choose a Highlight Color
   Pick a fill color to visually mark the duplicates.

5. Click “Done”
   Duplicates will now be highlighted in your selected color.

Method 2: Using the UNIQUE Function

Want a clean list that excludes duplicates? The UNIQUE function is your friend.

Steps:

1. Pick a Blank Column
   Choose a new column (like column D) to output your unique list.

2. Use the UNIQUE Formula
   Type:

   This will list all the unique values from column A, removing any duplicates automatically.

3. Done!
   You now have a filtered list with no repetitions.

Step-by-Step Guide: How To Find Duplicates In Google Sheets

Step 1

Open the spreadsheet and enter the data.

Step 2

Then select the entire set of values you want to check.

Step 3

Go to the menu and click 'Format'.

Step 4

Within this menu, locate and click on the 'Conditional Format' option.

Step 5

You will now see the 'Conditional Format Rules' section. Observe the default applied range,

Step 6

Find the 'Format Rules' option to customize these values,

Step 7

Click on the drop-down menu and search for more options.

Step 8

In the search, look for the 'Custom Formula' option. You will need to input a specific value here to proceed.

Step 9

Enter the formula =COUNTIF(A:A,A1)>1 to highlight duplicates in the spreadsheet.

Pro Tips to remove duplicates in google sheets

1. Use conditional formatting for quick identification – Apply Conditional Formatting > Duplicate Values to highlight duplicates instantly without formulas.

2. Leverage COUNTIF for better accuracy – Use =COUNTIF(A:A, A1) > 1 to flag duplicate entries dynamically without manual checking.

3. Remove duplicates with the built-in tool – Use Data > Remove Duplicates to clean up your dataset while keeping unique values intact.

Common pitfalls and how to avoid them

1. Not selecting the entire range – Applying duplicate checks to a partial dataset may miss some repeated values.

2. Case sensitivity issues – Google Sheets treats apple and Apple as different; use LOWER() or UPPER() to normalize text before searching for duplicates.

3. Accidentally deleting unique data – Always review duplicates before deletion to avoid losing important unique entries.

Common FAQs for removing duplicates google sheets

1. Can I remove duplicates while keeping the first occurrence?
Yes, Google Sheets automatically keeps the first occurrence when using the Remove duplicates tool.

2. How can I highlight duplicates instead of removing them?
Use Conditional Formatting with the formula =COUNTIF(A:A, A1) > 1 to highlight duplicate values.

3. Does Google Sheets automatically remove duplicates?
No, you need to manually use the Remove duplicates tool or set up a script to automate the process.

4. Can I remove duplicates across multiple columns?
Yes, in the Remove duplicates tool, select multiple columns to check for duplicate rows based on all selected columns.

5. How to screen record on mac? 
To screen record on a Mac, you can use Trupeer AI. It allows you to capture the entire screen and provides AI capabilities such as adding AI avatars, add voiceover, add zoom in and out in the video. With trupeer’s AI video translation feature, you can translate the video into 30+ languages. 

6. How to add an AI avatar to screen recording?
To add an AI avatar to a screen recording, you'll need to use an AI screen recording tool. Trupeer AI is an AI screen recording tool, which helps you create videos with multiple avatars, also helps you in creating your own avatar for the video.

7. How to screen record on windows?
To screen record on Windows, you can use the built-in Game Bar (Windows + G) or advanced AI tool like Trupeer AI for more advanced features such as AI avatars, voiceover, translation etc.

8. How to add voiceover to video?
To add voiceover to videos, download trupeer ai chrome extension. Once signed up, upload your video with voice, choose the desired voiceover from trupeer and export your edited video. 

9. How do I Zoom in on a screen recording?
To zoom in during a screen recording, use the zoom effects in Trupeer AI which allows you to zoom in and out at specific moments, enhancing the visual impact of your video content.