How to use the csv group by summary
- Paste your CSV data with a header row. Commas, semicolons, tabs and pipes all work, and quoted cells are handled.
- Type the group by column name, or several names separated by commas. The names come from the header row.
- Type the value column to total. Leave it empty if you only want to count rows in each group.
- Choose how to sort the groups, then read the table and copy it, print it or download it as a CSV.
Formula
Rows in a group = number of data rows with the same group value
Total = sum of the numeric cells of the value column in the group
Mean = total / number of numeric cells
Smallest and largest = minimum and maximum numeric cell
Cells such as $1,200.50, (45.10) or 7% are read as numbers. Empty and text cells are not added and are counted as skipped.
Units sold by region
The sample has 8 rows with the columns region, product, units and amount. Grouping by region and totaling units gives 4 groups: East has 2 rows and a total of 12 (one of its units cells says n/a, so only one cell is numeric), North has 3 rows and a total of 19 with a mean of 6.3333, South has 2 rows and a total of 10 with a mean of 5, and West has 1 row and a total of 8.
The grand total of units is 49, and one cell was skipped because it was not a number. Group by region and product together to get a finer split, or change the value column to amount to total money instead.
A pivot table in one step
Grouping rows and adding up a column is the most common thing people do with a table of records: sales by region, hours by person, expenses by category. A spreadsheet pivot table does the same thing but takes several steps. Here you paste the data, name two columns and read the answer.
For statistics on a whole column such as the median or standard deviation, use the CSV Statistics tool. To add columns from another table before you group, use the CSV Join Tool, and to see what changed between two exports use the CSV Diff Tool.
Clean data gives clean groups
Groups are formed from the exact text in the cell after trimming spaces, so North and north are different groups unless you tick the box that ignores capital letters. A stray space or a spelling change creates a new group, which is a useful way to find messy data. The (blank) group shows rows with an empty cell.
Numbers can include currency signs and thousands separators. A European style number such as 1.234,56 is not read, so convert it first or use the Change CSV Delimiter tool if the file uses semicolons. Large files are better opened in a spreadsheet.
Assumptions and limits
- The first row holds unique column names, and every row is read as the same number of columns.
- Numbers use a dot as the decimal point and may have commas as thousands separators.
- Up to 100,000 rows are read, and the table shows the first 500 groups.
- Your data stays in this page. Nothing is uploaded, stored or put in share links.
Frequently asked questions
Can I group by more than one column?
Yes. Type the column names separated by commas, such as region, product. Each different pair of values forms its own group, and the table shows one column per group field.
Why were some cells skipped?
Only cells that read as numbers are added. Text such as n/a, empty cells and cells with letters are counted as skipped so you can check the data, and the count of numeric cells is shown beside the mean.
Does the mean include the skipped cells?
No. The mean is the total divided by the number of numeric cells in the group, not by all rows. A group with no numeric cells has no mean.
Is my data uploaded?
No. The CSV is read and summarized in your browser. It is not sent to a server and is not kept after you leave the page.