Tool 05 · Data lab

Pivot Table Online: Group By and Summarize

GROUP BY :: SUM · COUNT · AVERAGE

Summarize an Excel sheet or CSV file online, like a pivot table without Excel: totals, counts and averages for each region, product or month, with the Excel formula for your own data. Free, and your file never leaves your browser.

100% in your browser: your data never leaves your device

Step 01

Your data

100% in-browser

What does grouping and summarizing do?

It turns a long list into a short table: one row for each value in the column you group by, and a number worked out from the rows in that group. Here are six sales on a sheet called Data. North appears three times:

  • A2:A7 group by Region
  • C2:C7 add up Sales

Grouped by Region, with the sales added up and the rows counted, those six rows become three, plus a grand total. North's 195 is 120 + 45 + 30, the three North rows together:

  • A2:A4 one formula in A2 lists each region once
  • B3 North: 120 + 45 + 30

=SUMIFS(Data!$C$2:$C$7, Data!$A$2:$A$7, A3) →195

How do you group and sum in Excel with formulas?

Two steps. First list each group once: UNIQUE drops the repeats and SORT puts them A to Z, from one formula that spills down by itself:

=SORT(UNIQUE(Data!A2:A7))
✎ In A2 of the summary sheet. Older Excel without UNIQUE: copy the column and use Data → Remove Duplicates.

Then add up each group's rows beside it with SUMIFS, and fill it down:

=SUMIFS(Data!$C$2:$C$7,Data!$A$2:$A$7,A2)
✎ Sum range first, then the column to check and the group to look for. Lock the ranges with $ so they stay put when filled down.

Swap SUMIFS for COUNTIFS, AVERAGEIFS, MINIFS or MAXIFS for the other columns. To group by two columns, add a second pair: the column and the cell to match, as in Data!$B$2:$B$7,B2. The SUMIFS lesson and the COUNTIF lesson teach both, and the UNIQUE lesson covers the group list.

When is a PivotTable better than formulas?

A PivotTable builds the same summary from menus, with no formulas, and is quicker to rearrange: drag a field from rows to columns and the table turns round. Formulas win when the summary has to sit in a fixed layout, feed other formulas, or update without pressing Refresh. The PivotTables lesson walks through one in a spreadsheet in your browser, and this tool shows the formula version under every result.

Revision Q&A

(click a question) Lab module 05
Q1: Is this pivot table tool free?

Yes, free with no sign-up. Paste or upload a table, pick the column to group by and what to work out, and the summary appears straight away. Everything runs in your browser, so your file is never uploaded.

Q2: What can it work out for each group?

The sum, count, average, smallest (min) or largest (max) of any column, several at once. Each one becomes a column of the summary, with a grand total row at the bottom.

Q3: Can I group by two columns?

Yes. Group by one column, then by another, for example region then product. Each combination that appears in the data gets its own row.

Q4: Why is "North" listed twice in Excel but once here?

Usually because one of them has a hidden space at the end. This tool ignores extra spaces and upper or lower case by default and tells you when it put groups together. Excel keeps "North" and "North " apart, so clean the column with TRIM first.

Q5: What happens to text in a number column?

It is left out of sums, averages, min and max, and the tool says how many cells it skipped. Excel's SUMIFS skips text the same way, which is a common reason a total comes out too low.

Q6: How do I do the same in Excel?

List each group once with =SORT(UNIQUE(...)), then put SUMIFS, COUNTIFS, AVERAGEIFS, MINIFS or MAXIFS beside it. The tool writes those formulas for your own columns under the result. A PivotTable does it without formulas, and Excel 365 also has GROUPBY.

Q7: Can I download the summary?

Yes, as an Excel file (the summary plus your data on a second sheet) or as CSV, or copy it and paste it straight into a spreadsheet.

Know someone fighting with a spreadsheet? Send them this page.