Count Unique Text Values in Excel: Formulas & Tips

Formulas & functions · Updated November 22, 2026

To count unique, nonblank text values in Excel for Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,ISTEXT(A2:A100)*(A2:A100<>"")))),0) when the values are in A2:A100. It returns the number of different text entries, ignores numbers and blanks, and counts text without distinguishing uppercase from lowercase.

The Microsoft 365 formula

The formula uses Excel’s dynamic-array functions, available in Microsoft 365 and Excel 2021 or later. Replace A2:A100 with the range containing your data. Keep the range limited to the rows you need; using an entire column can make a calculation needlessly heavy in a large workbook.

With names in A2:A100, enter this formula in any empty cell outside the data range:

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

The result is a single number. IFERROR returns 0 if the range has no qualifying text values, rather than letting FILTER’s empty-result error appear. If you would rather see the unique values themselves, enter this formula in an empty cell:

With the source values in A2:A100, enter =UNIQUE(FILTER(A2:A100,ISTEXT(A2:A100)*(A2:A100<>""))). Excel spills the distinct text entries into the cells below.

Leave enough empty cells beneath a spill formula for the results. If an occupied cell blocks the output, Excel displays a #SPILL! error; clear the obstructing cells or move the formula.

How the formula works

The formula performs three jobs in sequence. Understanding them makes it easier to adapt the calculation instead of treating it as a black box:

  • ISTEXT(A2:A100) marks entries that Excel recognizes as text. A numeric value such as 25 is not included, even if it is displayed with a number format.
  • A2:A100<>"" excludes empty cells and cells whose formula result is an empty string. Multiplying the two conditions keeps only rows that satisfy both tests.
  • FILTER selects those qualifying entries, UNIQUE returns one instance of each value, and ROWS counts the resulting list.

The outer IFERROR handles the case where FILTER finds nothing. In an ordinary list, the formula counts distinct text regardless of how often an entry appears. For instance, “Maple” repeated 40 times contributes one, not 40.

A worked example

Suppose A2:A10 contains North, South, North, a blank cell, West, South, the number 42, and two more entries: North and West. The qualifying text values are North, South, and West, so the formula returns 3. The blank and the numeric 42 do not contribute to the count.

If the range instead contains “North”, “north”, and “NORTH”, Excel’s standard UNIQUE comparison treats those as the same text value, so they contribute one unique value. This is often the expected meaning of “unique” for a list of names or categories, but it matters if capitalization carries meaning in your data.

To see the counted items rather than just their number, use the spill-list formula from above. Comparing that list with the source is a helpful way to check that the formula is excluding the right things before you use its total in a report.

Count all unique values, not just text

Sometimes “unique values” means distinct entries of every type, including numbers and dates. In that case, remove the text-only test. With values in A2:A100, use:

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

This version still excludes blanks and empty-string results, but it can count numbers, dates, and text. Excel stores dates as numbers, so this formula includes dates as values. If a column contains a mix of numbers and text that look alike, Excel may treat them as different value types; decide whether that distinction makes sense for your task.

If you want to count unique text but include a literal text value that looks blank, such as a space, the main formula will include it. A space is text and is not equal to an empty string. That can create a result that appears to contain a blank category, so inspect the source if the count seems unexpectedly high.

Options for older Excel versions

Excel 2019 and earlier do not have the dynamic-array functions UNIQUE and FILTER. A legacy array formula can count unique nonblank text in a fixed range. With values in A2:A100, enter the following formula:

=SUM(IF(FREQUENCY(IF(ISTEXT(A2:A100)*(A2:A100<>""),MATCH(A2:A100,A2:A100,0)),ROW(A2:A100)-ROW(A2)+1)>0,1))

In versions that require traditional array entry, confirm it with Ctrl+Shift+Enter rather than Enter alone. Excel displays braces around the formula in the formula bar when it has been entered as an array formula; do not type those braces yourself. In Excel versions with dynamic arrays, ordinary Enter is sufficient, but using the newer formula is simpler and easier to maintain.

The older formula is less convenient to audit and edit, especially when the range changes. It can also be disrupted by error values in the source range. If the formula returns an error, check the data for errors and make sure the references all cover the intended rows. For workbooks shared with people on older Excel releases, test the formula in the oldest version that must support it.

Clean the source data before counting

A distinct count reflects the values Excel sees, not necessarily the categories a person intended. Differences in spaces, punctuation, spelling, and data type can make a list look more varied than it is. For example, “West”, “West ”, and “West Coast” are three different text values; capitalization alone does not make “West” and “west” different in the standard formula.

Before relying on a total, check for common inconsistencies:

  • Leading or trailing spaces, often introduced by copying data from another system.
  • Nonprinting characters that are difficult to spot on screen.
  • Different spellings or abbreviations for the same category.
  • Numbers stored as text alongside genuine numeric values.
  • Formulas that return an empty string, which can look blank even though a formula is present.

For a quick visual check, select the source range and use Home > Sort & Filter > Filter to inspect categories, or display the UNIQUE spill list. If you want to standardize spaces, create a cleaned helper column and count that column instead. With the original value in A2, a helper formula that removes leading and trailing ordinary spaces is:

=TRIM(A2)

TRIM does not remove every kind of nonprinting or nonbreaking space that can arrive from websites or external systems. Also, it changes the value used for the count: “West” and “ West ” become the same after trimming. Keep the original data if you need an audit trail, and confirm the cleaning rule before applying it to important records.

Choose a formula, PivotTable, or Power Query

A formula is usually best for a live count that should update as entries change. Use the dynamic-array formula when you have Microsoft 365 or Excel 2021 and want a result that refreshes automatically. Put the data in an Excel Table if rows will be added regularly; table references expand as the table grows, which helps avoid changing a fixed range by hand.

A PivotTable is useful when you want to summarize categories alongside other measures, such as sales by region. Put the text field in the Rows area to display each category once, then use the resulting row labels as a distinct list. A standard PivotTable does not automatically give you a distinct-count value in every setup; the Distinct Count aggregation is available when the PivotTable is created using the Data Model. For a straightforward count of unique text, the formula is often less setup.

Power Query is a good choice for repeatable cleanup and reporting on larger imported lists. In Excel for Microsoft 365 on Windows, select a cell in the source table and choose Data > From Table/Range to open the query editor. Set up any needed filtering and cleaning, remove duplicates from the relevant column, and use the remaining row count as the distinct total. Be careful with mixed-type data: changing a column to Text can turn numbers into text, so filter out values you do not want counted before converting types. Refresh the query when the source changes.

Excel for the web and Mac support many modern Excel functions, but the exact availability of Power Query features and interface options can vary by platform and release. If a workbook’s formula must work across devices, use functions supported by the oldest Excel version your collaborators have, or provide a separate summary value.

Common problems and checks

If the count is larger than expected, first display the unique list and look for nearly identical entries. Spaces and spelling differences are common causes. If the count is zero, confirm that the source range is correct and that its apparent text entries are actually text rather than formulas returning empty strings or values in another range.

  • #NAME? usually means the Excel version does not recognize one of the functions, such as UNIQUE or FILTER. Use a supported Excel version or the older array formula.
  • #SPILL! applies to a formula that returns a list, not the single-number count formula. Clear cells in the intended spill area.
  • A surprising blank category may be a cell containing spaces. A genuinely empty cell and a cell containing a space are not the same value.
  • A count that does not update may use a fixed range that excludes newly added rows. Expand the reference or use an Excel Table column.

For a quick verification, compare the total with the spilled unique list and confirm that every listed entry is intended to count. If the workbook contains errors, resolve or explicitly exclude them before using an array formula; error values can interfere with filtering and older array calculations.

Frequently Asked Questions

Does UNIQUE count blank cells?
UNIQUE can return a blank when the source includes blanks. The main counting formula filters out empty cells and formula results equal to an empty string before applying UNIQUE.

Does Excel treat uppercase and lowercase text as different?
No. The standard UNIQUE formula is not case-sensitive, so “Blue” and “blue” count as one unique value. A case-sensitive count requires a different approach, such as using EXACT in a carefully designed formula or helper process.

How do I list unique text instead of counting it?
With values in A2:A100, enter =UNIQUE(FILTER(A2:A100,ISTEXT(A2:A100)*(A2:A100<>""))) in an empty cell. The distinct text entries spill into the cells below.

Can I count unique text in a Table column?
Yes. Replace the cell range in the formula with the table column reference, such as Table1[Category]. Structured references automatically include rows added to that table.

Why does my formula return #NAME?
Your Excel version may not support UNIQUE or FILTER. These functions are available in Microsoft 365 and Excel 2021 or later; older releases need an array formula or a different workflow such as removing duplicates in Power Query.

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.