Excel GROUPBY Function
Chala mitrano — gROUPBY builds a summary table with a formula. Think PivotTable, but it spills as a dynamic array.
चला मित्रांनो — GROUPBY सूत्राने summary table बनवते. PivotTable सारखे, पण dynamic array म्हणून spill होते.
चलो दोस्तों — GROUPBY सूत्र से summary table बनाता है। PivotTable जैसा, पर dynamic array की तरह spill होता है।
Purpose
Group rows by one or more columns and aggregate values (SUM, COUNT, AVERAGE, and more) — returning a spilled summary array.
Return value
A dynamic array: category labels plus aggregated numbers (and optional totals), spilled into neighbouring empty cells.
Syntax
Arguments
| Argument | Required? | Description |
|---|---|---|
row_fields | Required | Column(s) to group by (city, status, …). |
values | Required | Numbers to aggregate (amount, qty). |
function | Required | Aggregation: SUM, AVERAGE, COUNT, MIN, MAX, etc. (or a LAMBDA). |
field_headers | Optional | Whether headers are present / should show (see Excel’s enums). |
total_depth | Optional | Grand / subtotal depth control. |
sort_order | Optional | Sort groups ascending/descending. |
filter_array | Optional | TRUE/FALSE filter before grouping. |
Exact optional enums can vary by 365 build — start with the three required arguments, then add options.
How to use — Why → What → How
Why: Managers ask “sales by city” every Monday. A Pivot works, but a formula summary refreshes with the sheet and can sit beside FILTER/SORT.
What: GROUPBY groups row_fields and runs an aggregate function on values.
How — numbered steps:
- Put raw data in a table (Ctrl+T) with City and Amount columns.
- Click an empty cell with space below and to the right.
- Type =GROUPBY(CityColumn, AmountColumn, SUM)
- Press Enter and watch the spill.
- Clear blocking cells if you see #SPILL!.
Ravindra Bagale's Tip
Friends, leave empty cells for the spill. GROUPBY needs room, just like UNIQUE and FILTER.
Ravindra Bagale's Tip – मराठी
मित्रांनो, spill साठी रिकाम्या सेल्स ठेवा — UNIQUE/FILTER सारखे.
Ravindra Bagale's Tip – हिंदी
दोस्तों, spill के लिए खाली सेल छोड़ो — UNIQUE/FILTER जैसे।
Worked examples
1) Sum amount by city (₹)
Orders for Ananya Joshi’s Pune desk:
| City | Amount (₹) |
|---|---|
| Pune | 1200 |
| Nashik | 800 |
| Pune | 400 |
| Manchester | 350 |
Result (spill):
| City | Sum |
|---|---|
| Manchester | 350 |
| Nashik | 800 |
| Pune | 1600 |
What it means: Pune rows 1200+400 become 1600; other cities stay single totals. (Sort order may vary by options/build.)
2) Count orders by city
Result idea: Pune 2, Nashik 1, Manchester 1
What it means: Same groups; function counts instead of summing.
3) Average order value
Result idea: Pune average = (1200+400)/2 = 800
4) UK row in £ beside Indian ₹
Keep currencies in separate sheets in real work. For learning, Manchester 350 may be £ — still groups as its own city row.
Result: One Manchester total row appears beside Indian cities.
5) Compare with PivotTable
Why try both: PivotTable is click-friendly; GROUPBY is formula-friendly and spills next to other dynamic arrays.
Common errors and fixes
Related functions
PIVOTBY, UNIQUE, SUMIF, SUMIFS, FILTER, PivotTable (feature)
Practice tasks (10)
- Task 1. Enter the four-row sample and GROUPBY with SUM.Hint: leave spill space empty
- Task 2. Change SUM to AVERAGE and note Pune’s average.Hint: 800
- Task 3. Add a fifth row City=Pune Amount=100; refresh totals.Hint: expand ranges or use a Table
- Task 4. Use COUNTA to count rows per city.Hint: third argument
- Task 5. Explain one benefit of GROUPBY vs a static pasted summary.Hint: formula updates
- Task 6. Force a #SPILL! on purpose, then clear the block.Hint: type in the spill zone
- Task 7. Build the same summary with a PivotTable and compare numbers.Hint: should match SUM
- Task 8. Separate £ Manchester onto another sheet and GROUPBY only ₹ cities.Hint: clean inputs
- Task 9. Write when you would still prefer a PivotTable.Hint: click-heavy exploration
- Task 10. Bonus: read Microsoft’s optional arguments and try a sort_order if your build supports it.Hint: 365 only
Quick quiz
Answers: Q1=True, Q2=False, Q3=SUM (aggregate), Q4=blocked output.