Excel Formula for Grouping Data by Category

Formulas & functions · Updated November 18, 2026

In Excel for Microsoft 365, use UNIQUE to list each category once and FILTER to return the records for a chosen category; use SUMIF when you want a total for each category instead. Formulas can organize and summarize results, but they do not physically move or collapse the original rows like a PivotTable or Excel’s outline tools.

What “grouping data” can mean in Excel

People use “group data” to describe several different tasks. You might want to collect matching records under category headings, calculate a total for each category, put values into numerical ranges, or collapse detail rows so a sheet is easier to navigate. The right method depends on which result you need: these tasks are related, but they are not interchangeable.

  • List and display matching records: use a category list with UNIQUE and return matching rows with FILTER.
  • Calculate a value for each category: use SUMIF for one condition or SUMIFS for multiple conditions. Other summary functions include COUNTIF and AVERAGEIF.
  • Create number bands: assign each number to a range, such as 20–29 or 30–39, with a formula.
  • Collapse or expand detail rows: use Excel’s outline grouping commands, not a worksheet formula.

Choosing the right interpretation first prevents a common frustration: a formula that calculates correct totals will not create a collapsible report, and a formula that returns matching rows will not automatically subtotal them.

Set up a simple example

Assume your source data has item names in A2:A6, categories in B2:B6, and sales amounts in C2:C6. The five records are Notebook, North, 120; Pen, South, 80; Folder, North, 95; Marker, West, 45; and Binder, South, 70. In other words, column A contains the item, column B its category, and column C its sales amount. Keep one header row above the records and avoid blank rows inside the data range.

With that layout, the category values are North, South, and West. The repeated categories are intentional: they are what the formulas use to identify which records belong together. In a real workbook, you can extend the example ranges to cover your full data set. If your data grows often, converting it to an Excel Table can make formulas easier to maintain because table references expand as you add records.

List categories and return matching records

To create a distinct list of categories, put this formula in E2; it assumes the category values are in B2:B6:

=UNIQUE(B2:B6)

The formula spills its results into the cells below E2, returning North, South, and West once each, in the order they first appear in the source range. Leave the spill area clear, because Excel displays a spill error if another value blocks the results. UNIQUE is available in Microsoft 365 and Excel 2021 or later; it is not available in older perpetual versions such as Excel 2019.

To show records for the category named in E2, enter this in G2; it assumes the full record is in A2:C6 and the category field is in B2:B6:

=FILTER($A$2:$C$6,$B$2:$B$6=E2,"No matches")

If E2 contains North, the result spills out the Notebook and Folder records, including their sales amounts. Change E2 to South to return Pen and Binder instead. You can pair this formula with a category dropdown created through Data Validation, so a user can select a category rather than type it. The displayed result is a live formula output: it does not delete, reorder, or relocate any source records.

Both formulas recalculate when the source values change. For a larger list, adjust the ranges consistently—for example, use B2:B1000 for the category list and A2:C1000 and B2:B1000 in the filter. The ranges supplied to FILTER must have the same number of rows, or Excel returns an error.

Summarize each group with SUMIF

If the goal is a total rather than a list of underlying records, make a category list in E2:E4 and place this formula in F2; it assumes categories are in B2:B6, amounts are in C2:C6, and the category to total is in E2:

=SUMIF($B$2:$B$6,E2,$C$2:$C$6)

Copy the formula from F2 down beside the other category names. It returns 215 for North, 150 for South, and 45 for West. The arithmetic is 120 + 95 = 215 for North, 80 + 70 = 150 for South, and 45 for West. The dollar signs keep the source ranges fixed as you copy the formula; the relative reference E2 changes to E3 and E4.

SUMIF has the basic pattern “range to test, criterion, range to add.” Here it checks each cell in the category range against the category in E2 and adds the corresponding sales value when there is a match. For two or more criteria—such as category and month—use SUMIFS, which places the sum range first, followed by pairs of criteria ranges and criteria. For counts rather than totals, COUNTIF can count matching categories.

A summary formula is often the simplest option for a compact report that needs to update as records change. It is not a replacement for a detailed grouped view: SUMIF returns a number, not the individual rows that contributed to that number.

Group numbers into ranges

Grouping numerical values into bands is another common meaning. For a value in A2, enter this formula in B2; it assumes A2 contains a number and groups values into bands of ten:

=FLOOR.MATH(A2,10)

If A2 is 27, the result is 20, which you can interpret as the 20–29 band. Fill the formula down for the rest of the values. The result is the lower boundary of each band, so values from 20 through 29 share the same result. For a report, you can label that boundary with a separate formula or use a PivotTable to display readable group labels.

Think carefully about boundary rules before choosing a formula. A band of 20–29 is different from a band of 20 up to, but not including, 30, even if the membership is the same for whole numbers. Decimal values, negative values, and values below zero may need explicit rules. FLOOR.MATH rounds down to a multiple of the significance; with negative numbers, “down” is toward the next lower multiple. If your bands have uneven limits, a lookup table or a series of conditional rules may be clearer than rounding.

Choose between formulas and a PivotTable

Use formulas when the output needs to sit in a specific report layout, respond to a selected category, or feed other formulas and charts. The results are visible in ordinary worksheet cells, which makes them easy to reference elsewhere. Formula-based reports also make the calculation logic explicit, though they require sensible ranges and room for dynamic arrays to spill.

Use a PivotTable when you want to rearrange fields, summarize many records interactively, or let readers expand and inspect categories without building a separate formula for each view. To create one in Excel for Microsoft 365 on Windows, select a cell in the source data and choose Insert > PivotTable. Place the category field in Rows and the sales field in Values; Excel generally summarizes numeric values by Sum. Check the Values setting if the source contains blanks or text and Excel chooses Count instead.

A PivotTable is not a worksheet formula, and it may need refreshing after source data changes, depending on the change and workbook setup. A formula such as SUMIF recalculates from its referenced cells. Conversely, a PivotTable makes it easier to rearrange summaries and drill into grouped data. You can also use Data > Group to create collapsible outline groups for selected worksheet rows or columns; that changes the sheet’s outline structure, not the data returned by a formula.

Common problems and version differences

  • Blocked spill range: If UNIQUE or FILTER returns #SPILL!, clear cells in the area where the result needs to appear. Merged cells can also block a spill.
  • No matching records: Check spelling, extra spaces, and whether the selected category truly matches the source values. FILTER returns the text specified in its third argument when there are no matches; without an appropriate if-empty argument, it can return an error.
  • Unexpectedly small totals: Confirm that the amounts are numeric, the sum range aligns with the criteria range, and the criteria text matches the source. Text-formatted numbers may not sum as expected.
  • Formula separator differences: The examples use commas between arguments, as requested for standard English-language Excel syntax. Some regional settings use semicolons as separators, so a formula copied from an example may need that local adjustment.
  • Older Excel version: UNIQUE and FILTER require Microsoft 365 or Excel 2021 and later. In older versions, a PivotTable is often the most practical way to generate category summaries; alternatively, use built-in filtering or a carefully constructed legacy formula.

For frequently updated source data, format the records as a table with Insert > Table. Tables make headers clearer and help keep data ranges current when rows are added. If using formulas with fixed cell ranges, remember to expand those ranges when new records are placed outside them. Avoid sorting only one column of a source list: sort the entire table or range so that each item stays paired with its category and amount.

Frequently Asked Questions

What is the simplest Excel formula to group data by category?
For a distinct category list, use UNIQUE. To display the matching rows for a selected category, use FILTER. For category totals, use SUMIF. The simplest choice depends on whether you need a list, detail records, or a summary number.

Can Excel group rows automatically when a category repeats?
A formula can return records that match a category, but it does not automatically move or collapse the original rows. Use a PivotTable for an interactive grouped summary, or Data > Group to make worksheet outline groups that can be expanded and collapsed.

How do I total sales for each group?
List each category once, then use SUMIF beside it, with the category column as the criteria range and the sales column as the sum range. Copy the formula down. For example, the sample data returns 215 for North and 150 for South.

Why does FILTER show #SPILL!?
The formula needs open cells to display all its results. Clear any values or merged cells in the expected output area, then recalculate. Also verify that the source and criteria ranges contain the same number of rows.

Can I group numbers into ranges such as 0–9 and 10–19?
Yes. A rounding formula such as FLOOR.MATH returns the lower multiple for each value, which can serve as the band’s starting point. Define how to handle decimals and negative values, and use readable labels if people need to interpret the bands quickly.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.