Google Sheets is my go-to for organizing data, but sometimes duplicates sneak in, making things messy. Over time, I’ve found a few simple ways to spot and remove them. Let me walk you through it step by step.
Why Bother Removing Duplicates?
Duplicates can cause all sorts of issues, like:
- Messing up calculations
- Making data look unorganized
- Wasting space in the sheet
So, cleaning them up is always a good idea!
Method 1: Highlight Duplicates Using Conditional Formatting
This method highlights duplicate values so I can see them easily.
Steps:
Select the data where I want to find duplicates.
Go to Format > Conditional formatting.
In the “Format cells if” section, choose Custom formula is, then enter:
=COUNTIF(A:A, A1) > 1 (I replace A:A with the actual column range if needed.)
Pick a color for highlighting and click Done.
Now, all duplicate values stand out!
Method 2: Use the Built-in “Remove Duplicates” Tool
If I just want to delete duplicates quickly, this is the best method.
Steps:
- Select the data.
- Go to Data > Data cleanup > Remove duplicates.
- Select the columns where I want to check for duplicates.
- Click Remove duplicates and done!
Sheets will delete the duplicates and tell me how many were removed.
Method 3: Use a Formula to Label Duplicates
Sometimes, I don’t want to delete them but just mark them. Here’s the formula I use:
IF(COUNTIF(A:A, A1) > 1, "Duplicate", "Unique")
Steps:
- In a new column, I enter this formula.
- Drag it down to apply to all rows.
- It will label each entry as “Duplicate” or “Unique”.
| Name | Status |
|---|---|
| John | Unique |
| Alice | Unique |
| John | Duplicate |
| Mark | Unique |
| Alice | Duplicate |
Method 4: Use a Pivot Table to Count Duplicates
When I need a quick count of how many times a value appears, I use a pivot table.
Steps:
- Insert a Pivot Table (Go to Insert > Pivot Table).
- Choose where to place it and click Create.
- In the Pivot Table editor:
- Add the column under Rows.
- Add the same column under Values and set it to COUNT.
Now I can see exactly how many times each value appears.
| Name | Count |
|---|---|
| John | 2 |
| Alice | 2 |
| Mark | 1 |
Final Thoughts
Finding duplicates in Google Sheets is easy when I use these methods:
✅ Highlight duplicates using Conditional Formatting
✅ Remove duplicates with the built-in tool
✅ Use formulas to label duplicates
✅ Count duplicates using Pivot Tables
Depending on what I need, I pick the right method. Hope this helps keep your data clean too!
