How to Highlight Duplicates in Google Sheets and Remove

How to Highlight Duplicates in Google Sheets and Remove

How to quickly highlight cells with duplicate values in Google Sheets using conditional formatting. The duplicate cells can be easily removed from the spreadsheet with Google Script.

The Email Extractor app pulls emails addresses of your contacts from Gmail into a Google Sheet. You can then use Mail Merge or Document Studio to send personalized emails to your contacts from within the sheet itself.

That said, the imported mailing list may sometimes have duplicate email addresses and it is thus be a good idea to clean up the data in your Google Sheet by removing duplicates before sending out the emails.

Highlight Duplicates in Google Sheets

You can use Conditional Formatting in Google Sheets combined with the COUNTIF formula to quickly highlight cells that contain duplicate values.

Highlight Duplicates in Google Sheets

Here’s how you can find duplicates in Google Sheets:

  1. Open the Google Sheet containing your data and go to the Format menu.

  2. Select Conditional Formatting from the expanded menu. This will help us change the colors of cells, rows or columns that meet a specific criteria.

  3. In the Apply to Range input box, add the range of cells that contain possible duplicates. In our case, the email addresses are in column A so we can put A:A to specify the entire A column.

  4. Under the ‘Format cells if’ section, choose “Custom formula is” from the dropdown list as set the formula as =COUNTIF(A:A, A1) > 1

Click the Done button and you’ll instantly notice that all duplicate cells are highlighted as shows in the screenshot below.

Duplicate Cells in Google Spreadsheet

The COUNTIF Function

The COUNTIF function in Google sheets (and Microsoft Excel) essentially counts the number of cells in the range that meet a specific criteria. For instance =COUNTIF(A:A, "apple") will count the number of cells that contain the word apple.

It can accept wildcard characters too so =COUNTIF(A:A, "apple?") will count cells that contain the word apple or apples. Or say =COUNTIF(A:A, "*@gmail.com") and it will highlight all email address that end with a gmail address.

Please note that the COUNTIF function is case-insensitive so values like gmail.com and Gmail.com are seen as duplicates.

google sheets mail merge for gmail document studio archives

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

How to Mail Merge with Gmail and Google Sheets

Send personalized emails with Mail Merge for Gmail add-on. Trusted by 2.5 million Gmail users, developed by Google Developer Expert for GSuite and Google Apps Script.

How to Use Formulas with Google Form Responses in Sheets

How to Use Formulas with Google Form Responses in Sheets - Learn how to add autofill formulas with Google Form responses in Google Sheets. The cell values are automatically calculated when a new Google Form response is submitted.

How to Send Google Forms with Pre-filled Answers

Learn how to prefill answers in Google Forms using data from a Google Sheet and send the pre-populated Google Forms as personalized emails.

How to Enable Third-party Cookies in Google Chrome

The newer versions of Google Chrome block third-party cookies by default. Cookies are considered “bad” as they are mostly used for tracking users on the Internet.

How to Use Template {{Markers}} in Document Studio

Your Document Studio templates can include any variable field that is available as a column title in the Google Sheet. In case of Google Forms, you can use any question title as your marker and the marker should be exactly as your question title enclosed in double curly braces.