DEV Community

Cover image for Remove Duplicates and Count Unique Values in Excel: 5 Methods That Work
epifive
epifive

Posted on Originally published at epifive.com

Remove Duplicates and Count Unique Values in Excel: 5 Methods That Work

#tutorial

To remove duplicates in Excel, click inside your data, select Data > Remove Duplicates, choose the columns that define a duplicate and click OK; Excel keeps the first occurrence and deletes the rest. To count unique values without deleting anything, combine the UNIQUE, FILTER and COUNTA functions in Excel for Microsoft 365, Excel 2024 or Excel 2021, or use a PivotTable with Distinct Count. This guide walks through five methods on one sample table, with the exact result each formula returns.

Who this is for and what you will have

This guide is for anyone who keeps lists in Excel, such as orders, contacts or sign-ups, and needs to clear out repeated rows or answer "how many different customers do we have?" By the end you will be able to highlight duplicates, remove them by one or more columns, build a live list of unique values, count unique values with or without a condition, get a distinct count from a PivotTable, and fix the usual reasons Remove Duplicates "does not work".

UNIQUE and FILTER need Excel for Microsoft 365, Excel 2024 or Excel 2021 on Windows or Mac, or the Excel apps for iPad, iPhone and Android; these are the versions Microsoft lists for both functions. The SUMPRODUCT and COUNTIF formulas also work in Excel 2019 and Excel 2016.

The sample data

Every example uses this order list, with headings in row 1 and data in A2:E13.

Order ID Customer Region Product Amount
1001 Contoso East Laptop 1200
1002 Fabrikam West Monitor 300
1003 Contoso East Dock 150
1004 Northwind Traders South Laptop 1200
1002 Fabrikam West Monitor 300
1005 Adventure Works East Headset 80
1006 Fabrikam East Laptop 1200
1007 Tailspin Toys South Monitor 300
1008 West Dock 150
1009 Contoso East Monitor 300
1010 Wingtip Toys West Headset 80
1011 Northwind Traders South Dock 150

Three things in this list are typical of real data. Order 1002 appears twice (rows 3 and 6), which is a true duplicate row. Customers repeat: Contoso and Fabrikam have three orders each and Northwind Traders has two. And order 1008 in row 10 has an empty Customer cell, which changes the result of several formulas.

Before you use any method that deletes data, copy the original data to another sheet or workbook. Microsoft gives this advice because Remove Duplicates deletes rows permanently; Undo is the only way back.

Step 1: Highlight duplicates before deleting anything

Microsoft recommends highlighting or filtering first so you can confirm the result is what you expect. Conditional Formatting changes nothing in the data.

  1. Select A2:A13 (the Order ID column).
  2. On the Home tab, in the Styles group, select the arrow next to Conditional Formatting.
  3. Select Highlight Cells Rules > Duplicate Values, keep Duplicate and the default format, and select OK.

Two cells are highlighted: both entries for order 1002. On B2:B13, eight cells are highlighted: three for Contoso, three for Fabrikam and two for Northwind Traders.

To flag only the second and later copies, which are the rows Remove Duplicates would delete, enter this in F2 and fill it down to F13:

=COUNTIF($B$2:B2,B2)>1
Enter fullscreen mode Exit fullscreen mode

It returns TRUE in rows 4, 6, 8, 11 and 13. Filter column F on TRUE to review those rows. COUNTIF criteria are not case sensitive, so "contoso" and "CONTOSO" count as the same customer.

Method 1: Remove Duplicates (deletes rows, keeps the first)

  1. Click any cell inside the data, or select the range.
  2. On the Data tab, in the Data Tools group, select Remove Duplicates. In Excel for the web, the path is Data > Remove Duplicates.
  3. Under Columns, check the columns that together define a duplicate. Select All and Unselect All are shortcuts.
  4. Select OK. Excel reports how many duplicates were removed and how many unique values remain. Select OK again.

If the result is wrong, press Ctrl+Z straight away. What gets deleted depends entirely on the columns you check:

Columns checked Rows removed Rows left What you get
All five columns 1 (second 1002) 11 Exact duplicate rows removed
Customer only 5 (rows 4, 6, 8, 11, 13) 7 One row per customer
Customer and Region 4 (rows 4, 6, 11, 13) 8 One row per customer per region

Two rules from Microsoft explain these results:

  • The first occurrence is kept. Later identical rows are deleted. To keep a different row, such as each customer's largest order, sort by Amount from largest to smallest first so that row is on top.
  • Whole rows are deleted. The checked columns are only the matching key. With Customer checked, Contoso's Dock and Monitor orders disappear along with their amounts.

Only cells inside the selected range or table are affected, so select every column, or click inside the data, before you start.

Method 2: Advanced Filter (unique records without deleting)

To get a clean copy and keep the original intact, use Advanced Filter. Microsoft notes that filtering for unique values only hides duplicates temporarily; it does not delete them.

  1. Select A1:E13, including the heading row.
  2. Select Data > Sort & Filter > Advanced.
  3. Choose Copy to another location and enter G1 in Copy to.
  4. Check Unique records only and select OK.

Excel copies the headings and 11 records starting at G1, leaving out the second 1002 row. For unique customers only, select B1:B13 in step 1 instead.

Microsoft's own counting method builds on this: count the copied list with ROWS, leaving out the heading. With the records in G2:G12, this returns 11:

=ROWS(G2:G12)
Enter fullscreen mode Exit fullscreen mode

The copy is static. When the source changes, run the filter again or use UNIQUE.

Method 3: The UNIQUE function (a live list)

UNIQUE returns the distinct values from a range and spills them into the cells below:

=UNIQUE(array,[by_col],[exactly_once])
Enter fullscreen mode Exit fullscreen mode
  • array is the range to check.
  • by_col is optional. FALSE or left out compares rows; TRUE compares columns.
  • exactly_once is optional. FALSE or left out returns every distinct value; TRUE returns only values that occur exactly once.

List unique values and ignore blanks

In an empty cell to the right of your data, such as M2, enter:

=UNIQUE(B2:B13)
Enter fullscreen mode Exit fullscreen mode

It spills seven cells: Contoso, Fabrikam, Northwind Traders, Adventure Works, Tailspin Toys, 0 and Wingtip Toys. The 0 comes from the empty cell in row 10. To drop it, let FILTER remove empty cells first; the condition B2:B13<>"" is TRUE for every cell that is not empty:

=UNIQUE(FILTER(B2:B13,B2:B13<>""))
Enter fullscreen mode Exit fullscreen mode

This returns six customers. Add SORT, as Microsoft does in its UNIQUE examples, for an alphabetical list that starts with Adventure Works and ends with Wingtip Toys:

=SORT(UNIQUE(FILTER(B2:B13,B2:B13<>"")))
Enter fullscreen mode Exit fullscreen mode

Unique combinations of two columns

Give UNIQUE two columns and it compares whole rows:

=UNIQUE(B2:C13)
Enter fullscreen mode Exit fullscreen mode

This spills eight customer and region pairs, the same eight rows Remove Duplicates kept with Customer and Region checked. Fabrikam appears twice, once for West and once for East.

Values that appear exactly once

Set the third argument to TRUE, which Microsoft describes as the database concept of unique:

=UNIQUE(FILTER(B2:B13,B2:B13<>""),,TRUE)
Enter fullscreen mode Exit fullscreen mode

Result: Adventure Works, Tailspin Toys and Wingtip Toys. This answers "remove duplicates and keep none": every customer with more than one order is left out.

Compare across a row

If values run across a row, for example East, West, East, South, West, East in A20:F20, set by_col to TRUE. The result spills to the right as East, West, South:

=UNIQUE(A20:F20,TRUE)
Enter fullscreen mode Exit fullscreen mode

If the source is an Excel table referenced with structured references, Microsoft notes that the result resizes as rows are added or removed. Put the formula outside the table, because spilled formulas are not supported inside Excel tables.

Method 4: Count unique values with a formula

Count distinct values in a column

Wrap UNIQUE in COUNTA or ROWS:

=COUNTA(UNIQUE(B2:B13))
Enter fullscreen mode Exit fullscreen mode 
=ROWS(UNIQUE(B2:B13))
Enter fullscreen mode Exit fullscreen mode

Both return 7, because the empty cell counts as one value. For the number of real customers, filter out blanks first. This returns 6:

=COUNTA(UNIQUE(FILTER(B2:B13,B2:B13<>"")))
Enter fullscreen mode Exit fullscreen mode

To count customers who ordered exactly once, this returns 3:

=ROWS(UNIQUE(FILTER(B2:B13,B2:B13<>""),,TRUE))
Enter fullscreen mode Exit fullscreen mode

Count unique values with criteria

Add the condition to FILTER. Multiplying two conditions means both must be TRUE. With a region name typed in O2:

=IFERROR(ROWS(UNIQUE(FILTER(B2:B13,(C2:C13=O2)*(B2:B13<>"")))),0)
Enter fullscreen mode Exit fullscreen mode

East returns 3 (Contoso, Adventure Works, Fabrikam), West returns 2 (Fabrikam, Wingtip Toys) and South returns 2 (Northwind Traders, Tailspin Toys). Without the (B2:B13<>"") condition, West would return 3 because of order 1008.

IFERROR matters because FILTER returns #CALC! when nothing matches, since Excel does not support empty arrays. Do not swap in COUNTA with FILTER's third argument set to an empty string: COUNTA counts empty text, so a region with no orders would return 1.

The pattern works for any column and condition. This counts the different products Contoso bought (Laptop, Dock, Monitor) and returns 3:

=ROWS(UNIQUE(FILTER(D2:D13,B2:B13="Contoso")))
Enter fullscreen mode Exit fullscreen mode

Excel 2019 and earlier: SUMPRODUCT and COUNTIF

The classic formula weights each value by 1 divided by the number of times it appears, so three copies of Contoso add up to 1. On the Order ID column, which has no blanks, this returns 11:

=SUMPRODUCT(1/COUNTIF(A2:A13,A2:A13))
Enter fullscreen mode Exit fullscreen mode

The caveat is blank cells. Point it at B2:B13 and it returns #DIV/0!: the empty cell in row 10 becomes a criterion that matches nothing, COUNTIF returns 0 for that row, and 1/0 is a division by zero. Microsoft documents the same rule for COUNTIFS, which treats a criterion that refers to an empty cell as 0. The blank-safe version joins an empty string to the criteria and sets the numerator to 0 for empty cells. It returns 6:

=SUMPRODUCT((B2:B13<>"")/COUNTIF(B2:B13,B2:B13&""))
Enter fullscreen mode Exit fullscreen mode

For a condition, use COUNTIFS in the denominator. This returns 3 for East, and 2 if you change it to "West":

=SUMPRODUCT((C2:C13="East")*(B2:B13<>"")/COUNTIFS(B2:B13,B2:B13&"",C2:C13,C2:C13&""))
Enter fullscreen mode Exit fullscreen mode

All ranges must be the same size, or SUMPRODUCT returns #VALUE!.

Method 5: PivotTable Distinct Count

A PivotTable can count distinct values per group without formulas, but Microsoft states that Distinct Count "only works when you use the Data Model in Excel".

  1. Click any cell in A1:E13 and select Insert > PivotTable.
  2. Choose New Worksheet, check Add this data to the Data Model, and select OK.
  3. Drag Region to Rows and Order ID to Values.
  4. Right-click a number in the Values area and select Value Field Settings.
  5. On the Summarize Values By tab, select Distinct Count, the last item, and select OK.

The PivotTable shows East 5, South 3 and West 3, with a Grand Total of 11. A plain Count would show West 4 and a total of 12, because it counts the duplicate 1002 row twice. Use Customer or Product in Values to count distinct customers or products per region.

If Distinct Count is missing, the PivotTable was built without the Data Model. Create a new one from the same data and check the box. Microsoft's summary-function steps are written for Excel for Windows, and the Mac and web instructions on its PivotTable page do not include the Data Model option.

Common errors and fixes

Rows that look identical are not removed. Invisible characters count: "Contoso" and "Contoso " (trailing space) are different values. Clean the column in a helper column, then copy it and use Home, the arrow below Paste, Paste Special > Values to paste over the original:

=TRIM(B2)
Enter fullscreen mode Exit fullscreen mode

TRIM removes the ordinary space (character 32) but not the nonbreaking space (character 160) common in text copied from web pages. Microsoft's data-cleaning guidance is to combine TRIM, CLEAN and SUBSTITUTE, using SUBSTITUTE to replace character 160 first:

=TRIM(CLEAN(SUBSTITUTE(B2,CHAR(160)," ")))
Enter fullscreen mode Exit fullscreen mode

Numbers stored as text. An imported Order ID can be text, often marked with a green triangle. Select the cells, select the error indicator and choose Convert to Number, or convert in a helper column and paste the values back. Make the whole column one type before deduplicating:

=VALUE(A2)
Enter fullscreen mode Exit fullscreen mode

Dates shown in different formats. Microsoft notes that comparison depends on what appears in the cell, so 3/8/2006 and Mar 8, 2006 are treated as unique. Apply one date format to the column first.

Nothing is removed with all columns checked. Two orders from the same customer differ in Order ID, so they are not duplicates across all columns. Check only the columns that define a duplicate.

Outlined data or subtotals. Microsoft states that you cannot remove duplicates from outlined data or data with subtotals. Remove both first.

Upper and lower case. COUNTIF is not case sensitive, and Microsoft's pages for Remove Duplicates and UNIQUE do not say how case is handled, so do not rely on either to keep "ACME" and "Acme" apart. EXACT is case sensitive; with ACME in B2 and Acme in B3, this returns FALSE:

=EXACT(B2,B3)
Enter fullscreen mode Exit fullscreen mode

You can also standardize the column with UPPER, LOWER or PROPER before deduplicating.

#SPILL! from UNIQUE. Cells below the formula are not empty, the formula is inside a table, or the spill range has merged cells. Select the error, choose Select Obstructing Cells, and clear what is in the way or move the formula.

#NAME? from UNIQUE or FILTER. The file is open in a version older than Excel 2021. Use Advanced Filter, SUMPRODUCT or a PivotTable instead.

For messier columns, such as inconsistent company names, see cleaning spreadsheet data with AI in Excel and Google Sheets.

Which method to use

Task Method Changes the data?
Delete duplicate rows, keep one Remove Duplicates Yes
Clean copy, original untouched Advanced Filter No
Live list of distinct values UNIQUE No
Count distinct values, with criteria UNIQUE with FILTER No
Count distinct in Excel 2019 or 2016 SUMPRODUCT with COUNTIF No
Distinct count per group PivotTable with Data Model No

Checklist

  • Copy the data before using Remove Duplicates.
  • Highlight duplicates first to see what will go.
  • Clean spaces, text numbers and date formats.
  • Check only the columns that define a duplicate, and sort first if a specific row should stay.
  • Filter out blanks with a not-blank condition before counting.
  • Wrap filtered counts in IFERROR so no matches returns 0.
  • Check Add this data to the Data Model for a PivotTable distinct count.

More Microsoft 365 tutorials cover Excel, Outlook, Teams and the rest of the suite.

References

This guide was researched and drafted with AI assistance and checked against the vendor documentation listed in its References. Read the maintained version and related guides on EpiFive.

Top comments (0)