2 Simple Tactics for Managing Spreadsheet Duplicates

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 Simple Tactics for Managing Dupes In Your Spreadsheets

  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/table that needs de-duping.
  2. In the Data tab, click the ‘Remove Duplicates’ icon
  3. In the dialog box, indicate which column should be checked for duplicate entries.
  4. Click the Remove Duplicates button.

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

Google Sheets Instructions (Remove Duplicates)

  1. Select the range/table that needs cleansing.
  2. Under the Data menu, click ‘Remove duplicates’
  3. In the dialog box, indicate which column should be checked for duplicate entries.
  4. Click the Remove Duplicates button. Once finished, you’ll see a count for 1) all the duplicate values removed, and 2) all the 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 (Conditional Formatting)

  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. Then, sort or filter by color to get all of your duplicate rows together and organized.

*psst! Here’s a video showing this hack in action!

Google Sheets Instructions (Conditional Formatting)

  1. Highlight the column that contains your duplicates.
  2. Under the Format menu, select Conditional Formatting.
  3. The rules sidebar will open on the right side of your screen. 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.

One caveat with Google Sheets: As of the date of this post, Google Sheets does not let you sort or filter rows by color. So keep in mind that while this helps point out the duplicates in your sheet, it’s the lesser option in terms of then sorting/grouping those rows later on.

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

Share your thoughts!