Count Unique Values in an Excel Column

Formulas & functions · Updated November 22, 2026

To count distinct, nonblank values in an Excel column, use =COUNTA(UNIQUE(FILTER(A2:A100,A2:A100<>""))) in Excel for Microsoft 365 or Excel 2021 and later. Replace A2:A100 with your data range; the formula counts each different value once, even when it appears repeatedly.

What “unique values” means

In spreadsheet questions, “count unique values” can mean one of two things. Most often, it means counting the distinct entries: if a list contains Red, Blue, Red, and Green, there are three distinct values. Repeated entries contribute only once to that count. The formula above answers this common question.

Sometimes, however, “unique” means a value that occurs exactly one time. In the example, Red occurs twice, while Blue and Green occur once, so only two entries meet that stricter definition. Excel formulas and features may use “unique” differently in conversation, so decide which result you need before choosing a method.

These counts are useful for finding how many customers, product codes, regions, or categories appear in a list. They can also help check imported records, summarize survey responses, and understand the variety in a column. The result is a count of different values, not a count of rows or a list of the values themselves.

Count distinct values with UNIQUE and FILTER

Assume your entries are in cells A2 through A100, with a heading in A1. Enter this formula in an empty cell. It filters out blank cells, returns the distinct remaining entries, and counts how many entries were returned.

With data in A2:A100, use: =COUNTA(UNIQUE(FILTER(A2:A100,A2:A100<>"")))

For example, if A2:A8 contains North, South, North, blank, East, South, and West, the result is 4: North, South, East, and West. The repeated North and South entries do not increase the total, and the blank cell is excluded. If the range contains only blanks, the formula can return a calculation error because FILTER has no matching values. To make that case display zero instead, wrap the calculation in IFERROR.

With data in A2:A100, use this version if the range might be entirely blank: =IFERROR(COUNTA(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0)

UNIQUE and FILTER are available in Microsoft 365 and Excel 2021 or later. The formula uses a fixed range, so extend the end row if records are added below row 100. For a growing dataset, converting the data to an Excel table can make formulas easier to maintain; structured references expand as table rows are added.

Enter the formula and check the result

  1. Identify the data cells, excluding the header. Check whether the column includes blanks and whether you want to include them.
  2. Select a cell outside the source list so the result does not overwrite the data.
  3. Type or paste the formula, updating the range to match the column, then press Enter.
  4. Compare the result with a small sample of the source values. This can reveal unexpected spaces, spelling variations, or entries stored in different formats.

In current Excel for Windows, the formula returns a single count in the cell where you entered it. UNIQUE itself produces a dynamic array, but COUNTA counts that array rather than requiring you to select several output cells. If you want to inspect which entries Excel considers distinct, enter UNIQUE by itself in an empty area and leave room below for its results to spill.

On Excel for Mac and Excel for the web, the same functions and formula syntax generally work in supported versions. If Excel reports that UNIQUE or FILTER is not recognized, your installed version may not include dynamic-array functions. Check the product version or use one of the alternatives below.

Count values that occur exactly once

If your goal is to count entries appearing one time only, use COUNTIF to test how often each cell’s value occurs. The following formula excludes blanks and adds one for each nonblank cell whose value has a count of exactly one.

With entries in A2:A100, use: =SUM((A2:A100<>"")*(COUNTIF(A2:A100,A2:A100)=1))

For the list North, South, North, blank, East, South, and West, this returns 2 because East and West each occur once. North and South occur twice, and the blank is ignored. This formula is intended for current Excel with dynamic-array calculation. In older Excel releases, array formulas may behave differently, so use a helper column or a PivotTable if the formula does not calculate as expected.

To see the distinction clearly, the distinct-value count for that sample is 4, while the exactly-once count is 2. A repeated value can still be a distinct value; it simply does not qualify as occurring exactly once. This difference matters in tasks such as identifying one-off product codes or finding customers with only one transaction.

Handle blanks, spaces, and inconsistent entries

The FILTER condition excludes truly empty cells and formulas that return an empty string. It does not treat every cell that looks empty as blank. A cell containing one or more spaces is text, so Excel can count it as a distinct value. Likewise, entries such as “East” and “East ” may look identical on screen but are different strings because the latter has a trailing space.

If spaces may be present, clean a copy of the data before counting. TRIM removes ordinary leading and trailing spaces and reduces repeated spaces between words. For an important report, avoid changing original source data without keeping a backup or preserving the original column. You can use a helper column to clean entries, then count the cleaned results.

Other differences can create separate values even when they appear similar:

  • Spelling and punctuation differences, such as “St. Paul” and “Saint Paul.”
  • Numbers stored as numbers versus digits stored as text.
  • Dates that include different underlying time values but display the same date format.
  • Nonbreaking spaces or other imported characters that ordinary TRIM may not remove.

Excel’s standard text comparisons in functions such as UNIQUE and COUNTIF are not case-sensitive, so “blue” and “Blue” are generally treated as the same value. If capitalization must distinguish entries, standard distinct-count formulas need a case-sensitive approach. Before adding that complexity, confirm that capitalization represents a real difference in your data rather than inconsistent typing.

Use a table or a PivotTable for recurring summaries

If the source list changes often, format it as a table with Insert > Table, or press Ctrl+T on Windows. On Mac, the equivalent shortcut is Command+T. Give the table column a clear heading. Table references can make a formula easier to read and help it account for new rows as the table grows. For example, if the table is named Records and its column is Category, the formula can refer to that column instead of a fixed cell range.

A PivotTable is another option for a repeatable summary. Select the data, choose Insert > PivotTable, and place the category field in the Rows area. The resulting row labels show the distinct categories; counting those labels gives the distinct total. A regular PivotTable can display those categories, but a normal count of records is not automatically a distinct count. In Excel for Windows, the Distinct Count value option is available when creating a PivotTable with Add this data to the Data Model selected. That option is useful for larger summaries, but it is not present in every PivotTable setup or every Excel edition.

For a simple one-off count, a formula is usually quicker. A PivotTable is useful when you also want category-level totals, filters, or a report that colleagues can refresh. If the data is imported and needs repeatable cleanup, Power Query can remove duplicates from a query and report the remaining row count; this changes the query output, not the original worksheet range.

Common formula problems

A #NAME? error usually means Excel does not recognize one of the function names, often because the installed version lacks UNIQUE or FILTER. A #CALC! error can occur when FILTER finds no values and no empty-result argument is supplied. The IFERROR version above handles an all-blank range by returning zero.

A result that is higher than expected often points to differences in the source values rather than a counting mistake. Check spaces, hidden characters, date-time values, and numbers stored as text. One practical check is to display the distinct list with UNIQUE in a spare area and inspect the output. You can also sort the source list to bring similar-looking entries together.

A result that is too low may mean the formula range stops before the last data row, or the data contains values Excel treats as equivalent, such as text differing only by capitalization. Confirm the range includes every intended row and that the definition of “different” matches the task.

Finally, do not use COUNTA on the original column as a substitute. COUNTA counts nonempty cells, including repeated entries, so it answers “how many populated cells?” rather than “how many different values?” Likewise, COUNT counts numeric cells only; it is not a general distinct-value function.

Frequently Asked Questions

How do I count unique values in Excel without counting blanks?
In Microsoft 365 or Excel 2021 and later, use UNIQUE inside FILTER with a condition that excludes empty cells, then count the results with COUNTA. The opening formula uses this method for A2:A100.

Does UNIQUE count duplicates once or count only values that appear once?
UNIQUE returns one instance of each distinct value by default. It does not mean “appears exactly once.” Use a COUNTIF-based test when you need to count values that occur one time only.

Can I count unique text and numbers in the same column?
Yes. UNIQUE can return distinct text and numeric entries from the same range, and COUNTA counts the returned items. Check for numbers stored as text if entries that look alike are being treated differently.

Why does my distinct count include something that looks blank?
The cell may contain spaces or an invisible imported character rather than being empty. FILTER removes actual blanks and empty strings, but text containing spaces is still a value. Clean or inspect the source column to find it.

How do I count distinct values in an older Excel version?
If UNIQUE and FILTER are unavailable, use a PivotTable to list distinct row labels, or use Power Query to remove duplicates from a copy of the data. Older Excel formulas can also do the job, but the right formula depends on the version and whether the list contains blanks or text.

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.