Excel formulas for beginners: the 12 you need first
The Excel formulas every beginner needs, in the order to learn them: +, SUM, AVERAGE, COUNT, ROUND, percentages, IF and $. Each with an example to practise.
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
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:
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:
=SUMIFS(Data!$C$2:$C$7, Data!$A$2:$A$7, A3) →195
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)) 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) 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.
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.
The Excel formulas every beginner needs, in the order to learn them: +, SUM, AVERAGE, COUNT, ROUND, percentages, IF and $. Each with an example to practise.
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.
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.
Yes. Group by one column, then by another, for example region then product. Each combination that appears in the data gets its own row.
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.
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.
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.
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.