Identify Unique Values in Excel or Google Sheets

I often work with large lists of email addresses when helping Mailchimp clients with their email marketing needs. Occasionally I need to identify unique values only in a list. For example a list of 100,000 contacts may have duplicate contacts and I need to quickly identify records where the email address isn’t duplicated.

Fortunately it is quick and simple in Microsoft Excel or Google sheets to identify and extract unique records. The instructions are below.

How to Select Unique Email Addresses in Excel and Google Sheets

Cost: Assuming you have Microsoft Excel or Google Sheets, this will cost you $0

Time: 1 minute

Using Google Sheets or Excel it is very simple to quickly identify unique values in a list using a formula.

Necessary Items:

Microsoft Excel or Google Sheets

Your data open in Microsoft Excel or Google Sheets

Steps

Open your data

Open your data in either a Google Sheets or Microsoft Excel spreadsheet.

Identify the field to analyze

You may have many columns of data. Before continuing identify the column where you want to identify unique values. For example, if this is a list of contacts for import into Mailchimp then the email address field is likely to be the field you want to use.

Create a new column

Create a new column in your spreadsheet. Call the column “Duplicate”.

Enter the spreadsheet formula

Enter the formula

=COUNTIF(A:A, A2)>1

into the first cell in your “Duplicate” column (i.e. immediately under the header titled “Duplicate”). This formula assumes that column A of your spreadsheet contains the data that you want to analyze.

Then, copy the formula entered into all rows that contain data i.e. drag the cell containing the formula down the column. The “Duplicate” column will now show “FALSE” where values are unique.

Filter the data to identify unique values

Create a filter on the header rows of your columns. By filtering the “Duplicate” column to show only FALSE values will show uniques records in your list.