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.
You can use Conditional Formatting in Google Sheets combined with the
COUNTIF formula to quickly highlight cells that contain duplicate values.
Here’s how you can find duplicates in Google Sheets:
Open the Google Sheet containing your data and go to the Format menu.
Select Conditional Formatting from the expanded menu. This will help us change the colors of cells, rows or columns that meet a specific criteria.
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.
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.
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 are seen as duplicates.
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 - 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.
Learn how to prefill answers in Google Forms using data from a Google Sheet and send the pre-populated Google Forms as personalized emails.
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.
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.