2 Ways to Handle Duplicates in Excel + Google Sheets

We can all agree that duplicates are a pain. But they’re a real downer when you have a spreadsheet chock full of them, and no clue where to start cleaning.

Enter these spreadsheet tips.

Below are 2 options for tackling those duplicates. Instructions below for both Excel & Google Sheets!


2 Ways to Tackle Spreadsheet Duplicates
(Excel + Google Sheets)

  1. Clear duplicates using the ‘Remove Duplicates’ feature
  2. Highlight duplicates using Conditional Formatting

1. Use the ‘Remove duplicates’ feature.

  • Pros: Quick, automatic, retains unique values
  • Not Ideal: If you have to retain any data associated with your duplicate rows
This method is great for instances when you just need a list of unique values (ex. for importing a mailing list)

Remove duplicates is one of the easiest, quickest ways to delete duplicate rows all at once. It’s your best option if you’re safe to delete data, or if you need a quick count of duplicate/ unique values.

Excel Instructions (Remove Duplicates)

1. Select the range that needs de-duping
2. In the Data tab, click Remove Duplicates
3. In the dialog box, choose the column to check against for duplicates
4. Click the Remove Duplicates button.

Watch the 1-minute Excel tutorial here.

Depending on which version of Excel you’re using, you should see two counts: the number of duplicate rows removed & the number of unique values remaining.

  Google Sheets Instructions (Remove Duplicates)

1. Select the range that needs de-duping
2. Under the Data menu, click Remove Duplicates
3. In the dialog box, choose the column to check against for duplicates
4. Click the Remove Duplicates button.

Watch the 1-minute Google Sheets tutorial here.

When finished, you’ll see two counts: the number of duplicate rows removed and the number of unique values remaining.

2. Use Conditional Formatting to identify & sort duplicates.

  • Pros: visual way of bringing attention to duplicates or uniques (using color)
  • Not Ideal: if your file is very large/slow. Also doesn’t actually de-dupe
Gang, what would we do without conditional formatting??

If you’re not familiar with this one, add it to your spreadsheet wizardry toolkit. Conditional formatting is what it sounds like: cells taking on a set format/appearance, based on specific conditions. This is a great one for my fellow visual learners, who need their duplicates to practically jump off the screen in order to be workable!

Excel Instructions (Highlight Duplicates)

1. Highlight the column that contains your duplicates
2. In the Home tab, click Conditional Formatting > Highlight Cell Rules > Duplicate Values
3. In the dialog box, Format only unique or duplicate values is pre-selected,
followed by duplicates. (Change the latter to uniques, if you’d prefer it.)
4. Choose your desired formatting. My go-to is the “yellow fill with dark yellow text” option.
5. Click OK. You should see your duplicates formatted.
6. Sort or filter by color to get all of your duplicate rows together and organized.

Watch the 1-minute tutorial for how to highlight dupes in Excel.

  Google Sheets Instructions (Highlight Duplicates)

1. Highlight the column that contains your duplicates
2. Under the Format menu, select Conditional Formatting. The rules sidebar will open on the right side of your screen.
3. Under the Format rules section (next to “Format cells if:”), select the Custom Formula is option.
4. Underneath that dropdown, enter this formula: =countif(A:A,A1)>1. Replace the column letter A with your specific column letter. (Credit to Lifewire for this workaround!)
5. The column will immediately register your rule with the new format. Make any desired format changes below and click Done when ready.

Hopefully, these spreadsheet tips will make your next de-duping adventure a lot more enjoyable!

Share your thoughts!