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:
Open spreadsheet on Google Sheets.
Select the range where you want to search for duplicates.
Click Format tab > Click Conditional Formatting.
Under Format Rules, select Custom Formula is > edit and insert the following formula to match your selection: =countifs([range],[first cell in range])>1.
Set your desired formatting style > And, voila, the sheet will update accordingly!
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.