September 15, 2020

How to Highlight Duplicates on Google Sheets (in 5 simple steps)

Google Sheet spreadsheet

Suppose you’re organizing a newsletter list (or any other Google Sheets spreadsheet), and your boss wants you to flag--but not delete--emails that occur more than once. Unfortunately, you can’t select the Remove duplicates button (under the Data tab) as that will get rid of certain data completely.

Instead, you’ll want to highlight duplicates, which can be done pretty quickly:

  1. Open spreadsheet on Google Sheets.
  2. Select the range where you want to search for duplicates.
  3. Click Format tab > Click Conditional Formatting.
Spreadsheet named "Animal Newsletter" with Format tab open and Conditional Formatting highlighted
  1. Under Format Rules, select Custom Formula is > edit and insert the following formula to match your selection: =countifs([range],[first cell in range])>1.
  2. Set your desired formatting style > And, voila, the sheet will update accordingly!
Spreadsheet named "Animal Newsletter" with Column C (Emails) selected and duplicates highglighted. Conditional format rules is open with Custom formula to highlight duplicates included
For this selection, my formula was =countifs(C:C,C1)>1.


More Details

  • The formula will work with multiple columns selected as we use countifs as opposed to countif (no ‘s’).
  • Sometimes, users have semicolons set as the argument separator. If that is the case, simply type in the following formula: =countifs(C:C;C1)>1.

Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.