To count unique values in a pivot table, add your data to the Data Model when you create the pivot, then change the value field to Distinct Count. In Excel 2013 and later that’s the fastest way, and it needs zero formulas.
But before you jump in, there’s one thing worth clearing up — “unique” and “distinct” are not the same thing, and the method you pick depends on your Excel version. So I’ll show you all three ways I use, and tell you exactly when to reach for each one.
Distinct count
Every different value, counted once. If “Airtel” appears 5 times, that’s still 1 distinct value. This is what people usually mean.
Unique count
Only the values that appear exactly once in the whole column. Anything repeated is excluded entirely.
The pivot table’s built-in feature gives you a distinct count, which is what most reports need. If you truly need values that appear only once, use the COUNTIF method further down.
Using a Data Model with a Pivot Table
The Data Model is one of my favourite things about the newer versions of Excel. If you’re on Microsoft 365, Excel 2021, 2019, 2016, or 2013, you already have it. Here’s how to use it for a distinct count.
- Click any cell in your data, go to the Insert tab, and choose PivotTable.
- In the Create PivotTable dialog box that appears, check your table range and where the pivot will go.
- Tick the box at the bottom, “Add this data to the Data Model”, and click OK. This one checkbox is what unlocks the distinct count — don’t skip it.
- Build your pivot as usual by dragging fields into the areas. It looks like a normal pivot, but the field list behaves a little differently.
- Click the small arrow next to “Count of Service provider” in the Values area.
- Choose Value Field Settings.
- Scroll to the bottom of the list, select Distinct Count, and click OK.
- That’s all — you now have a distinct count of service providers for each region.
So in our data there are only 18 unique service providers across the country.
Using the COUNTIF Function
This method works in every version of Excel, and it’s the one to use if you want a true unique count (values that appear only once) rather than a distinct count.
- Add a helper column next to your data with a header of your choice — I’ll call it “Count No.”
- Enter this formula in cell D2 and drag it down to the end:
=IF(COUNTIF($B$2:B2, B2)>1, 0, 1)
How this formula works
The start of the range is locked with an absolute reference — $B$2 — so it stays put when you drag down. When the formula reaches row 3 it becomes =IF(COUNTIF($B$2:B3, B3)>1, 0, 1).
Read it like this: COUNTIF($B$2:B3, B3) counts how many times the value in B3 has appeared so far. The IF then says: if it has appeared more than once, return 0; otherwise return 1. So every first appearance gets a 1, and every repeat gets a 0.
- Now create a pivot table from your data (including the helper column).
- Put Location in the Rows area and Count No. in the Values area (as Sum).
- That’s all — summing the 1s gives you the unique entries for each location.
Using Power Pivot to Count Unique Values
This is the most powerful option. A Power Pivot measure using the DISTINCTCOUNT DAX function is reusable across pivots and refreshes cleanly — ideal if this is part of a bigger report. First, make sure the Power Pivot tab is showing in your ribbon.
- Confirm the Power Pivot tab is enabled.
- Go to the Data Model and click Manage.
- A window opens — it’ll be blank if this is your first import.
- Click Home → Get External Data.
- Choose From Other Sources (we’re importing a simple Excel file).
- Scroll to the bottom, pick Excel File, and click Next.
- Rename the connection if you like, then Browse to your data file.
- Tick “Use first row as column headers” and click Next.
- The file imports into the Data Model — click Finish.
- Success — all 28 rows imported. Click Close.
- Here’s how the loaded table looks in the Power Pivot window.
- Create a pivot from here with Home → PivotTable.
- Expand Sheet 1 by clicking the small triangle beside it.
- Put Location in Rows and Service providers in Values for a plain total count first.
- In the Power Pivot window, click Measures → New Measure.
- Give the measure a name and start typing the formula.
- As you type, pick DISTINCTCOUNT from the suggestions.
- Choose the column to count. The finished formula looks like this:
=DISTINCTCOUNT(Sheet1[Service Provider])
- Set the category to Number since we’re counting.
- Set the format to Whole Number and click OK. A new column appears in the pivot with your distinct counts.
Which Method Should You Use?
Method | Best when… | Works in | Counts |
|---|---|---|---|
Data Model → Distinct Count | You want the fastest, formula-free answer | Excel 2013 and later | Distinct |
COUNTIF helper column | You’re on an older version, or need a true unique count, or want to keep normal pivot features | Every version | Unique (or distinct) |
Power Pivot (DISTINCTCOUNT) | It’s part of a bigger, refreshable report or model | Excel 2013 and later | Distinct |
The Modern Excel Way (Microsoft 365)
If you’re on Microsoft 365, you don’t even need a pivot for a quick total. The dynamic UNIQUE function paired with COUNTA gives you a distinct count that updates on its own:
To count values that appear only once (a true unique count), combine UNIQUE with its third argument:
These spill automatically and need no Ctrl + Shift + Enter. For a per-category breakdown, though, a pivot is still the cleaner choice.
Frequently Asked Questions
Why don’t I see the “Distinct Count” option?
Distinct Count only appears when the pivot is built on the Data Model. Recreate the pivot and tick “Add this data to the Data Model” in the Create PivotTable dialog. It’s also only available in Excel 2013 and later.
What’s the difference between distinct count and unique count?
Distinct count counts each different value once, even if it repeats. Unique count only counts values that appear exactly once. Excel’s built-in pivot feature gives a distinct count; use the COUNTIF method for a true unique count.
Can I count unique values without the Data Model?
Yes — add a helper column with =IF(COUNTIF($B$2:B2, B2)>1, 0, 1), then sum that column in a normal pivot table. This works in every version of Excel.
Why are some pivot features greyed out after using Distinct Count?
A Data Model pivot is OLAP-based, so features like grouping and calculated fields are disabled. If you need them, switch to the COUNTIF helper-column method instead.
Is there a way to count unique values without a pivot table?
On Microsoft 365, use =COUNTA(UNIQUE(range)) for a distinct count, or =ROWS(UNIQUE(range, , TRUE)) for values that appear only once.
Conclusion
In the end, counting unique values in a pivot table comes down to your Excel version and what you actually need. For a quick distinct count, the Data Model checkbox does the job in seconds. For a true unique count or an older version, the COUNTIF helper column is your friend. And for anything that’s part of a larger, refreshable model, a Power Pivot DISTINCTCOUNT measure is the cleanest way. That’s all.
Puneet GogiaMicrosoft MVP
Puneet is a Microsoft Excel MVP with 15+ years of teaching experience. He founded ExcelChamps to help people work smarter in Excel through clear, practical tutorials.
Last reviewed: July 2026