Count Unique Records in Excel: Formulas and PivotTables

Formulas & functions · Updated November 18, 2026

To count distinct values in a column in Microsoft Excel for Microsoft 365, put this formula in an empty cell: =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0). It counts each nonblank value once in A2:A100. For complete records, use UNIQUE on the full set of columns instead. Before starting, identify which columns define a record and whether blank rows should be ignored.

What You’ll Need

  • An Excel workbook containing the records you want to count. The instructions assume Microsoft Excel for Microsoft 365 on Windows.
  • A clear definition of a record. It might be one value, such as a customer ID, or a complete row made up of several fields.
  • For the formula method, a spare cell for the result. The source data should have a header row and a consistent layout.

Step-by-Step

  1. Decide what counts as the same record. If you are counting customer IDs, compare only the ID column. If a record is defined by a combination such as date, product, and location, compare all three columns together. Excel treats two rows as duplicates only when the values in the columns you include match. Including an irrelevant column, such as a note that differs between rows, can make otherwise similar records count separately.
  2. Check and prepare the source range. Keep one field in each column, use a single header row, and remove completely empty rows from the middle of the data if practical. Check that values which should match have consistent spelling and formatting. For example, an ID stored as text may not match the same-looking ID stored as a number. Do not select the header row as part of the formula range.
  3. Count distinct values in one column with UNIQUE. With the values to count in A2:A100, enter the formula below in an empty cell. This example ignores blank cells and returns 0 if the range has no nonblank values. Replace A2:A100 with the actual data range; keep the start and end rows aligned with your records.

    =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0)

    For example, if A2:A7 contains North, South, North, East, a blank, and South, the result is 3. FILTER removes blanks, UNIQUE returns North, South, and East, and ROWS counts those three results. The formula counts distinct values, not values that appear exactly once.
  4. Count distinct complete rows across several columns. With each record in A2:C100 and column A filled for every real record, enter this formula in an empty cell:

    =IFERROR(ROWS(UNIQUE(FILTER(A2:C100,A2:A100<>""))),0)

    FILTER keeps rows with a nonblank value in column A, and UNIQUE compares the combination of values across columns A, B, and C. If two rows match in all three columns, they count once; if any included field differs, they count as separate records. Choose a column that is always filled for valid records. If column A can be blank in a valid row, use a different reliable field as the filter condition.
  5. Check the result against the records. Verify that the formula range includes every data row and no headers. For a quick spot-check, sort a copy of the data by the field or fields that define a record and inspect repeated entries. You can also display the unique list by entering =UNIQUE(FILTER(A2:A100,A2:A100<>"")) in an empty area, assuming values are in A2:A100 and there is at least one nonblank value. The results spill into cells below the formula. Compare that list with the source before relying on the count.
  6. Use a PivotTable for a one-column distinct count. In Excel for Microsoft 365 on Windows, select a cell in the source data and choose Insert > PivotTable. In the dialog, select the option to add the data to the Data Model, then create the PivotTable. Drag the field you want to count into the Values area. Open its Value Field Settings and choose Distinct Count, then select OK. This option is useful for summarizing a large list without placing a formula beside the source. It counts distinct values in that field; it is not a direct multi-column row-count option.
  7. Use Advanced Filter if you prefer a copied list. In the desktop Excel app, select the data and choose Data > Advanced in the Sort & Filter group. In the dialog, choose Copy to another location, set the List range and Copy to location, and select Unique records only. Include all columns that define a record. Excel copies the unique rows to the destination. Count the copied data rows, excluding the header. This creates a separate list rather than a live count, so repeat the process after the source changes. Menu availability and layout may vary slightly by desktop version.

Common Mistakes

Counting the header: If the range starts in row 1, the column heading is treated as a value and can add one to the result. Start at the first data row, such as A2.

Using the wrong fields: A unique count of order numbers is not the same as a count of unique customers. Likewise, comparing only the customer name may combine two people with the same name. Decide which field or combination of fields identifies a record before choosing the range.

Including blank rows in a multi-column range: A completely blank row can appear as an additional distinct row if you use UNIQUE directly on a rectangular range. Filter out empty records first, as in the examples, using a column that every valid record has filled in.

Confusing distinct values with values that occur once: UNIQUE counts a repeated value once by default. It does not mean “show only values that have no duplicates.” In Microsoft 365, the optional exactly_once setting changes that behavior; do not use it when your goal is to count every distinct value.

Assuming lookalike entries match: Extra spaces, inconsistent punctuation, different spellings, and text-versus-number formatting can make entries appear the same while Excel treats them as different. Conversely, trimming or standardizing data can merge entries that should remain separate. Review the source and the rules for your records before cleaning it.

Tips

Turn the source into an Excel Table by selecting a cell in the data and choosing Insert > Table. Tables expand when you add records, which helps avoid a formula range that stops at the old last row. If you use a fixed range in a formula, update its ending row when new data is added.

Keep the unique list visible while checking a result. The list makes it easier to find a spelling variation or a blank that has been counted unexpectedly. If the formula spills into occupied cells, move it to an area with enough empty rows and columns; do not overwrite neighboring data.

UNIQUE, FILTER, and dynamic array formulas are available in Microsoft 365 and Excel 2021 or later. Excel for the web supports these functions, although some desktop tools and dialogs differ. The Data Model distinct-count workflow described above is for Excel desktop on Windows; if the option is unavailable in your Excel edition, use the formula method. On Mac, use the same formula approach when the functions are available, or use a desktop filtering workflow supported by your version.

For repeatable reporting, keep the definition of a record consistent from one update to the next. If you later add a field that should distinguish records, include it in the range; if you add a descriptive field that should not distinguish them, leave it out. A count is only as useful as the comparison rule behind it.

Troubleshooting

The formula returns #NAME? Your Excel version may not include UNIQUE or FILTER, or a function name may have been mistyped. These dynamic array functions require Excel 2021 or Microsoft 365. If you are using an older desktop edition, use Advanced Filter to copy unique records, then count the copied rows, or use a PivotTable where distinct count is available.

The result seems one too high. Check for a header included in the range, a blank value, or a spelling or spacing variant. The formulas above exclude blank cells in the chosen filter column, but they do not remove spaces from text. Inspect the spilled unique list to see which entry is being counted.

The result is zero even though the cells look filled. Confirm that the formula points to the right sheet and rows, and that the filter column contains actual values rather than formulas returning empty text. If your data is in another column, replace both range references with the correct matching ranges. Check for an accidental extra space in a sheet name if you edit a cross-sheet reference.

You see #SPILL! when showing the unique list. The cells where the results need to appear are not empty, or the formula is inside an Excel Table that prevents the array from spilling. Clear the obstructing cells or move the formula to an open area outside the Table. The count formula using ROWS returns a single result and usually does not need a spill area.

The formula returns an error after the data changes. Check for error values in the source, which can flow through FILTER or UNIQUE, and confirm that the ranges in the formula have compatible dimensions. For the multi-column formula, the data range and the filter range must cover the same rows. Correct the source error or adjust the ranges before interpreting the count.

Frequently Asked Questions

Does UNIQUE count duplicates once or count only values that appear once?
By default, UNIQUE returns each distinct item once, even if it appears many times in the source. The optional exactly_once setting is for items that occur only once. For a normal distinct count, leave that option out.

Can I count unique records based on two or more columns?
Yes. Apply UNIQUE to the rectangular range containing the fields that define the record, then count its rows with ROWS. Filter out records that are blank in a reliable key column first. Two rows count separately if any included field differs.

Does the formula update when I add more records?
It updates when a new record is inside the formula’s referenced range. A fixed reference such as A2:A100 does not automatically extend past row 100. Use an Excel Table with structured references or expand the range, then check that the multi-column and filter ranges remain aligned.

How do I count unique values while ignoring blanks?
Use FILTER to remove blank cells before UNIQUE, then count the results with ROWS. The single-column formula in this guide wraps the calculation in IFERROR so an entirely blank range returns 0 rather than an empty-array error.

Can a PivotTable count distinct values?
Yes, in Excel desktop for Windows when you add the source to the Data Model during PivotTable creation. Put the field in Values and select Distinct Count in Value Field Settings. If that option is missing, use the UNIQUE formula or a supported alternative in your Excel version.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.