How to build a count distinct pivot table to count unique customers
- Step 1Confirm Developer tier — The run is Developer-gated; Free, Pro, and Pro-media are rejected. Upgrade before processing if you only need a one-off distinct-count pivot.
- Step 2Prepare a flat file with an ID column — You need one row per event (order, signup, session) and a column holding the entity to count uniquely —
CustomerId,Email,SessionId, orSKU. Add aSegmentand/or period column for the dimensions. - Step 3Put the data on the first sheet and upload — Only sheet one is read. Move your data to the first tab, then drop the
.xlsxor.csvonto the tool — parsing happens in your browser. - Step 4Map the dimensions — Type your group into
rowField(e.g.Segment) and your breakout intocolField(e.g.Month). Match the header text exactly, including case. - Step 5Set the ID column as value with COUNT DISTINCT — Type the ID column header into
valueField(e.g.CustomerId) and choosecountdistinctfrom theaggFndropdown. Each cell will count distinct IDs in that bucket. - Step 6Download and read the counts — Open
pivot-table.xlsx. Each cell is a unique count; theGrand Totalrow gives the distinct count across all row groups for each column. Note that, because of how distinct count works, the Grand Total is the distinct count of the merged buckets — not the sum of the cell counts (see the edge cases).
Count vs Count Distinct
Why Excel's plain Count over-counts and what countdistinct does instead, grounded in the aggregation logic.
| Behaviour | `count` | `countdistinct` |
|---|---|---|
| What it tallies | Number of source rows in the cell | Number of unique values in the value column for that cell |
| Customer with 5 orders | Counts 5 | Counts 1 |
| Blank value cell | Still counts the row | Excluded (blank never enters the set) |
Non-numeric IDs (e.g. C-001) | Works (counts the row) | Works (each distinct string counted) |
| Grand Total column | No | No |
| Excel classic pivot equivalent | Count | Needs Power Pivot / Data Model |
Distinct-count input mapping
Map your columns to the three inputs for common analytics questions.
| Question | rowField | colField | valueField |
|---|---|---|---|
| Unique customers per segment per month | Segment | Month | CustomerId |
| Unique SKUs ordered per region per quarter | Region | Quarter | SKU |
| Unique visitors per channel per day | Channel | Date | SessionId |
| Unique accounts per plan per cohort | Plan | SignupCohort | AccountId |
Tier access
Run requires Developer; lower tiers rejected.
| Tier | Pivot run | Excel limits |
|---|---|---|
| Free | Rejected | 5 MB / 10,000 rows |
| Pro | Rejected | 50 MB / 100,000 rows |
| Pro-media | Rejected | 200 MB / 500,000 rows |
| Developer | Allowed | 500 MB / unlimited rows |
Cookbook
Distinct-count scenarios with the exact option text. IDs anonymised.
Unique customers per segment per month
The headline cohort view. Customer C1 ordered twice in Jan but counts once. Note there is no Grand Total column for distinct count.
Input (sheet 1): Segment,Month,CustomerId Pro,Jan,C1 Pro,Jan,C1 Pro,Jan,C2 Pro,Feb,C2 Free,Jan,C3 Options: rowField=Segment colField=Month valueField=CustomerId aggFn=countdistinct Output (pivot-table.xlsx): Segment,Feb,Jan Free,0,1 Pro,1,2 Grand Total,1,3
Blank IDs are not counted
A row with a missing customer ID still belongs to a (segment, month) cell, but its blank value never enters the distinct set — so it doesn't add to the unique count.
Input: Segment,Month,CustomerId Free,Jan,C3 Free,Jan, Free,Jan,C3 Options: rowField=Segment colField=Month valueField=CustomerId aggFn=countdistinct Output (only C3 is unique; blank ignored): Segment,Jan Free,1 Grand Total,1
Grand Total is a distinct count, not a sum of cells
Because the same customer can appear in two row groups, the Grand Total recomputes distinct over the merged buckets for that column — it may be smaller than the sum of the cells above it. Here C1 is in both Pro and Free, so the Jan total is 2 distinct (C1, C2), not 1+1+... naively.
Input: Segment,Month,CustomerId Pro,Jan,C1 Free,Jan,C1 Free,Jan,C2 Options: rowField=Segment colField=Month valueField=CustomerId aggFn=countdistinct Output (Jan cells sum to 1+2=3, but distinct total is 2): Segment,Jan Free,2 Pro,1 Grand Total,2
Case and whitespace make IDs distinct
Distinct count compares stringified values literally. c1, C1, and C1 (trailing space) are three different strings and count as three uniques. Normalise IDs first if that's not what you want.
Input: Segment,Month,CustomerId Pro,Jan,C1 Pro,Jan,c1 Pro,Jan,C1 Options: rowField=Segment colField=Month valueField=CustomerId aggFn=countdistinct Output (treated as 3 distinct strings): Segment,Jan Pro,3 Grand Total,3
Unique SKUs ordered per region
Distinct count works on any string column, not just customers. Count how many different products each region bought.
Input: Region,Quarter,SKU EMEA,Q1,W-100 EMEA,Q1,W-100 EMEA,Q1,G-200 APAC,Q1,W-100 Options: rowField=Region colField=Quarter valueField=SKU aggFn=countdistinct Output: Region,Q1 APAC,1 EMEA,2 Grand Total,2
Edge cases and what actually happens
Same customer in two row groups
ExpectedDistinct count is recomputed for the Grand Total over the merged buckets, so a customer appearing in two segments counts once at the total even though they count in each segment row. The Grand Total can therefore be smaller than the sum of the cell values above it — that's correct distinct-count behaviour, not a bug.
Whitespace or case variation in IDs
Counted separatelyValues are compared as literal strings. C1, c1, and C1 (trailing space) are three distinct entries. If your source has inconsistent casing or stray spaces, the unique count will be inflated. Trim and normalise the ID column with the Whitespace Trimmer or Case Converter before pivoting.
Blank ID cells
ExcludedEmpty value cells are skipped before aggregation, so they never enter the distinct set and don't add a phantom unique. A row with a blank ID still contributes to its (row, column) bucket's existence but adds nothing to the count.
Numeric IDs read as numbers
PreservedNumeric IDs are stringified for distinct comparison, so 1001 and 1001.0 could differ if one cell carried a decimal. Normally identical integer IDs match fine, but watch for trailing .0 from spreadsheet number formatting on large IDs.
Using count when you meant countdistinct
Over-countcount tallies rows, so a customer with five orders counts five times. If your unique counts look suspiciously high, confirm aggFn is set to countdistinct, not count.
Header name typed with wrong case
Invalid fieldField matching is case-sensitive. A mistyped valueField makes every cell read blank, so the distinct set is empty and counts come out 0. Copy the exact header text from row one.
ID column not on the first sheet
Wrong sheetOnly sheet one is read. If your transactions are on a later tab, move them to the first position; otherwise the tool counts distinct values on whatever is on sheet one.
Account below Developer tier
RejectedThe run throws Pivot Table Generator requires Developer tier. on Free, Pro, and Pro-media. Distinct-count pivots are only produced on Developer.
Very high-cardinality column dimension
Wide outputEach distinct colField value becomes a column. Putting a daily date or raw timestamp into colField makes hundreds of columns. Bucket dates to month or quarter (ISO format) for a readable distinct-count grid.
Frequently asked questions
Does Excel's standard pivot support distinct count?
Not on its own. The classic pivot offers Count (which tallies rows) but not Distinct Count — that needs Power Pivot / the Data Model, available only in certain Excel SKUs, and is configured via the Value Field Settings after adding the data to the model. This tool exposes countdistinct directly as a dropdown option, no model required.
How is COUNT DISTINCT calculated here?
For each (row, column) bucket the tool collects every value from the value column, stringifies it, and puts it in a JavaScript Set; the cell value is the Set's size. That gives the number of distinct values, so a customer who appears five times counts once.
Does it handle null or blank customer IDs?
Yes — blank value cells are excluded from the distinct count. An empty CustomerId never enters the Set, so missing data isn't mistaken for a unique customer. The blank row simply doesn't contribute to the count for its cell.
Why is the Grand Total smaller than the sum of the cells above it?
Because distinct count is recomputed over the merged buckets for the Grand Total, not summed. A customer who appears in two segment rows counts once in the total. So the Grand Total can be (and often is) less than the arithmetic sum of the per-cell counts — that's the mathematically correct distinct total.
Will `C1` and `c1` be counted as the same customer?
No. Distinct count compares literal strings, so different casing or stray whitespace makes them distinct. Normalise the ID column first — trim spaces and standardise case with the Whitespace Trimmer and Case Converter — so genuine duplicates collapse.
Can I count distinct non-numeric values like emails or SKUs?
Yes. Distinct count works on any column because values are stringified before comparison. Emails, SKUs, session IDs, and order numbers all work — just point valueField at that column and choose countdistinct.
Is there a Grand Total column for distinct count?
No. The Grand Total column is only added for sum, because a row-wise total only makes sense for additive measures. For countdistinct you get the per-cell unique counts and a Grand Total row, but no right-hand total column.
Why are some unique counts higher than I expected?
Three usual causes: (1) aggFn is set to count (rows) rather than countdistinct; (2) the ID column has casing/whitespace variants that count as separate strings; (3) trailing .0 on numeric IDs from number formatting. Fix the relevant one and re-run.
Does this need Excel installed?
No. The pivot is computed in-browser via SheetJS and downloaded as pivot-table.xlsx, which opens in any spreadsheet app. No Excel, no Power Pivot, no Data Model.
Are customer IDs uploaded anywhere?
No. Parsing and counting happen entirely in your browser; the file never reaches a server. Only an anonymous usage counter is recorded. This matters for PII like emails and customer IDs.
What tier and file limits apply?
The run requires Developer tier; Free, Pro, and Pro-media are rejected. On Developer the excel-family limit is 500 MB and unlimited rows. Both .xlsx and .csv are accepted.
Can I count distinct customers AND total revenue at once?
Not in one pivot — only one valueField and one aggFn per run. Run once with valueField=CustomerId, aggFn=countdistinct and again with valueField=Revenue, aggFn=sum, then place the two pivots side by side or join them with the Sheet Joiner.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.