Two fields on the row axis
This page groups by two row fields at once. Region and Product stay in two columns, so East / Desk and East / Chair are different groups, and West / Desk is different again. That is a second level on the row axis. It is not the same layout as one row field plus one column field. The online pivot table asks for one row field and an optional column field, and its aggregation is one of Sum, Average, or Count. This page always shows all three measures, and the column field here is an extra split, not a substitute for the second row field.
Both row fields are required. If the second one is left on “Choose a second row field,” Calculate stops with an error and does not draw a table. The page does not copy the first field into the second, and it does not insert a made-up label such as (all) or (blank) to fill the gap. Clear the error by choosing a different header, then click Calculate again.
Worked example
Input header Region, Product, Channel, Amount. Rows: East Desk Online 10, East Desk Online 5, East Desk Store 7, East Chair Online 8, East Chair Store with a blank amount, West Desk Online 4, West Desk Store n/a, West Chair Online 6, West Chair Online 6, North Desk Store 3, east Desk Online 1.
With Channel left empty, East / Desk is 22, average 7.333333, count 3, because 10, 5, and 7 are the plain numbers and the two Online rows share one group. East / Chair is 8, average 8, count 1; the blank Store amount is ignored. West / Desk is 4, average 4, count 1; n/a is not a number. West / Chair is 12, average 6, count 2. North / Desk is 3, average 3, count 1. The label east is not East, so east / Desk is 1, average 1, count 1. The all-rows row is 50, average 5.555556, count 9. That average is 50 divided by 9. It is not the average of the six group averages.
Choose Channel as the column field and the same file splits each pair by the channel text that actually occurs. East / Desk / Online is 15, average 7.5, count 2. East / Desk / Store is 7, average 7, count 1. East / Chair / Store stays in the table with a blank sum, a blank average, and count 0, because that row is in the file even though its amount is blank. West, Chair, and Store do not appear together, so there is no West / Chair / Store row and that intersection is not filled with zero. Use the example button, then switch the column field and click Calculate to compare both layouts. The download contains the same rows, including the all-rows summary.
Which cells are 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 exponent form 1e2 is the number 100. Words, currency symbols, percent signs, and thousands separators are not numbers. n/a, $5, 5%, and 1,000 are ignored. A blank cell is ignored. Negative zero is stored as zero. A number that is too large to be finite, such as 1e309, is ignored rather than shown as an extreme.
Sum is the total of the plain numbers in the group. Count is how many of those numbers were included, so it is also the denominator of the average. Average is that sum divided by that count, rounded half away from zero to 6 decimal places, with trailing zeros removed. Integer totals inside the safe integer range are exact. A plain number, or a running total, past 9,007,199,254,740,991 stops with an error instead of a rounded substitute. If a group contains no plain number, Sum and Average are left blank. They are not filled with zero. Count is 0 in that case because zero numeric cells were included.
Labels, blanks, and duplicate rows
Empty cells, missing cells on a short row, and null worksheet cells all become the empty string. A row whose second field is empty still belongs to a group: the first label plus that empty second label. The tool does not rename the empty label. Completely empty records, where every cell is blank, are skipped and do not create a group.
Duplicate labels are the normal case. Three East / Desk rows become one East / Desk result. The first time a pair appears sets its place in the table; later repeats stay on that same row. The tool does not sort labels alphabetically. Letter case and spaces stay as they are. 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.
The optional column field
Leave the column field on “No column field” when the question is only the two row dimensions. The result then has one row per observed pair, plus the all-rows row. Choose a column field when you also want each pair split by a third label, such as channel or month. Each result row is then one triple that appears in the file. The column label uses the same exact text as the row labels.
The column field has to be a different header from both row fields. Choosing the same header stops with an error rather than repeating a row label as if it were a new axis. Combinations that never occur are left out. The all-rows row still covers every plain number in the value field once. Its column cell is empty, and its scope is all rows, so it is not another channel.
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. 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 calculate minimum, maximum, or distinct count. Minimum, maximum, and distinct count answers that question for one row field. This tool also does not calculate percent of total or the change from a previous period. Percent of total and period change is a separate page. This page does not draw a chart, and it does not add a second row field to the online pivot table.
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. Empty sum or average 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
What if I do not choose a second row field?
The calculation stops with an error and does not draw a table. This page does not invent a second row dimension, copy the first field, or insert a label such as (all) or (blank).
Does an empty cell in the second row field become a new name?
No. An empty cell, a missing cell on a short row, and a null worksheet cell stay the empty string. That empty label is a real group. It is not renamed.
Do duplicate labels create extra groups?
No. Rows that share the same text in both row fields are one group. The same is true of the column field. East and east stay separate, and a trailing space stays separate, because the label is the exact cell text.
What happens to text and blank amounts?
They are ignored for Sum, Average, and Count. They are not treated as zero. A group with no plain number has a blank Sum, a blank Average, and Count 0.
Does a column field fill every combination?
No. The table lists only combinations that appear in the file. A missing intersection is omitted. It is not filled with zero.
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.