Free browser tool

Pivot Minimum, Maximum, Distinct Count

Choose a row field and a value field. Each group then shows its lowest number, its highest number, and how many different cell values it contains.

Calculate minimum, maximum, and distinct count

Load a CSV or the first worksheet of an XLSX file, then pick the fields. A selected file is used instead of pasted text. Click Load data again after you change the delimiter. Nothing is uploaded.

Choose a CSV or XLSX file, or paste CSV, to start.

Minimum and maximum skip blank cells and any cell that is not a plain number. Distinct count uses the exact cell text, so an empty cell counts as one value and 5 differs from 5.0. Limits: 10 MB, 100,000 data rows, and 1,000 columns. The preview shows up to 12 groups; the CSV download includes every group and the all-rows row.

What each group reports

This page builds one result row for each distinct text value in the row field you choose. The minimum is the smallest plain number in the value field for that group, and the maximum is the largest. Distinct count is the number of different value-field texts in the group, including values that are not numbers. Rows that share the same row-field text are combined, including repeated labels. Letter case and spaces stay as they are, so East and east are separate groups, and a trailing space does not match the same word without that space.

The result always ends with one all-rows summary. Its minimum and maximum look across every plain number in the file. Its distinct count looks across every value-field text in the file. That summary is marked with the scope all rows. A source group that happens to be named (all rows) stays in the table as a normal group; the scope column tells the two rows apart.

Worked example

Input header Region, Amount. Rows: East 5, East 1, East 5, West 5, West 9, West n/a, North with a blank amount, North 2.

East has minimum 1, maximum 5, and distinct count 2, because 5 and 1 are the only different amounts and the repeated 5 does not add another value. West has minimum 5, maximum 9, and distinct count 3: the texts are 5, 9, and n/a. North has minimum 2, maximum 2, and distinct count 2, because the blank amount and the number 2 are different texts, while only 2 is a number. The all-rows row has minimum 1, maximum 9, and distinct count 6. Adding the group distinct counts gives 7, which is not the file-wide count: the amount 5 appears in both East and West, so it is counted once in the summary. The text n/a and the blank North amount are part of the distinct count and are not part of the minimum or maximum.

Use the example button above to load these eight data rows and compare the table with this list. The download contains the same rows, including the all-rows summary.

Which cells count as numbers

A value is a plain number when trimming spaces from both ends leaves an optional plus or minus, digits, an optional decimal point, and an optional exponent such as 1e2 or -0.5. The minimum or maximum cell shows that trimmed text from the first row that reaches the extreme. Later cells with the same numeric value do not replace it, so 1.0 stays 1.0 when it appears before 1, and both still count as two distinct texts.

Words, currency symbols, percent signs, and thousands separators are not numbers. n/a, $5, 5%, and 1,000 are ignored for minimum and maximum and still count as their own distinct texts. A blank cell is ignored for the two extremes. If a group contains no plain number, minimum and maximum are left blank. They are not filled with zero. A number that is too large to be finite, such as 1e309, is also ignored rather than turned into an invented extreme.

Blanks, spaces, and duplicate labels

Empty cells, missing cells on a short row, and null worksheet cells all become the empty string. Every empty string in a group is one distinct value, no matter how many rows are blank. A cell that holds only a space is not empty. It is ignored for minimum and maximum, and it is distinct from both a blank and a trimmed number such as 5. Completely empty records, where every cell is blank, are skipped and do not create a group.

Duplicate row labels are the normal case for this tool. Three East rows become one East result. The first time a label appears sets its place in the table; later repeats stay on that same row. The tool does not sort labels alphabetically. Headers that repeat, or a blank header, are renamed so each field can be selected: a second Amount becomes Amount (2), and a blank header becomes Column plus its position. Those names are only for the field lists. They do not change the cells underneath.

CSV and XLSX input

Paste CSV or choose a .csv, .tsv, or .txt file. The first non-empty record is the header, and the rows under it are the records. Quoted fields can contain the delimiter and doubled quotes. An unclosed quote stops the load with an error and no result table. Automatic delimiter detection looks at the header line and chooses among comma, semicolon, tab, and pipe. If the columns land in the wrong places, pick the delimiter and click Load data again. CSV text is read as UTF-8.

An .xlsx workbook is read on this page as Office Open XML. After it loads, the worksheet list shows every sheet named in the file, with the first sheet selected. Switch sheets to calculate that sheet instead. Older binary .xls workbooks are not read; save them as .xlsx or CSV first. The reader uses the value stored in the cell. A formula contributes that stored value when the file contains one, and this page does not recalculate the formula. Excel dates that were saved as serial numbers stay numbers, so a date extreme is that serial rather than a formatted calendar string. Text dates stay text and are not treated as numbers unless the cell text itself is a plain number. See Microsoft's file formats supported in Excel for the difference between .xlsx and older workbooks, and the CSV format specification, RFC 4180, for quoting.

What this page does not calculate

This tool does not sum a column, average it, or count records. The online pivot table is a different page, and its aggregations are Sum, Average, and Count. It does not calculate minimum, maximum, or distinct count, and this page does not add those three measures to that pivot. Count unique values in CSV lists how often each text appears in a single column. It does not group by a second field, and it does not report a minimum or maximum. Use this page when the question is, for each group, what is the lowest number, the highest number, and how many different values are present.

The preview lists at most 12 groups plus the all-rows row when the file has more groups than that. The CSV download is the full result: Group, Minimum, Maximum, Distinct count, and Scope. Empty minimum or maximum cells are empty fields in that file, not zeros. The browser keeps the table in memory only. Refreshing or closing the tab clears it. There is no account, and the file is not sent to a server for the calculation. Very large sheets still use memory on your device. Files over 10 MB, tables over 100,000 data rows, or tables over 1,000 columns stop with an error instead of a partial table.

Frequently asked questions

Are blank cells part of the distinct count?

Yes. Empty cells in a group count as one distinct value, the empty string. A cell that contains only spaces is a different value, not a blank.

Do words and blanks change the minimum or maximum?

No. Minimum and maximum use only cells that are plain numbers after trimming. Text, blanks, currency symbols, percent signs, and thousands separators are ignored. If a group has no plain number, those two cells are left blank rather than shown as zero.

Is the all-rows distinct count the sum of the group counts?

No. The all-rows row counts each different value once across the whole file. A value that appears in two groups is still one value in that summary.

Does the online pivot table already calculate these three figures?

No. The online pivot table calculates Sum, Average, and Count. This page is a separate tool for minimum, maximum, and distinct count.

Which Excel sheet is used?

Choose the worksheet after you load an XLSX file. The first sheet is selected to start. Older .xls files are not read. Formula cells contribute the stored value when the file has one, and the formula is not recalculated here.

Is the spreadsheet uploaded?

No. The browser reads the file on your device. Closing the tab clears the working table unless you downloaded the CSV.