Summarize Data Without a Pivot Table

You can summarize data without a pivot table by using two formulas: UNIQUE to list each category once, and SUMIFS to total each one. The result looks like a pivot table, but it updates the moment the data changes and stays exactly where you put it. This guide builds a one-way summary, adds counts, averages and percentages, then builds a two-way table, with a method for older Excel too.

Why summarize with formulas instead of a pivot table?#

A pivot table is quick to build, but it has habits that get in the way of some reports. It doesn't update until you refresh it, its layout changes when you drag fields, and other formulas that point into it can break. A formula summary:

• Updates instantly when you edit or add data.
• Keeps a fixed layout, so charts and other formulas can rely on it.
• Shows its logic in the formula bar, which is easy to check.

The comparison of pivot tables vs SUMIFS covers the trade-offs in detail. The examples use this sales list in A1:D9:

Step 1: List each category with UNIQUE#

In F2, type:

=SORT(UNIQUE(B2:B9))

UNIQUE returns each region once, and SORT puts them A to Z: North, South, West. The list spills into F2:F4. If a new region appears in the data and the range covers it, the list grows by itself. The UNIQUE function guide explains its options, such as listing values that appear only once.

UNIQUE and SORT need Microsoft 365, Excel 2021 or Excel 2024.

Step 2: Total each category with SUMIFS#

In G2, type:

=SUMIFS(D2:D9,B2:B9,F2#)

SUMIFS adds the Sales column for rows where the Region matches the criteria. The criteria is F2#, the spill range of the UNIQUE list, so SUMIFS runs once for each region and spills the answers beside it:

North is 1,400 + 600 + 820 = 2,820. Because both columns use spills, adding a region adds a row to both. The SUMIFS function guide shows how to add more conditions, such as a date range.

Add counts, averages and percentages#

Use the same pattern with other functions, each in its own column:

Format the share column as a percentage. A pivot table does this with Show Values As, explained in percent of total in a pivot table.

For a grand total, put =SUM(G2#) above the summary or to the side, not directly under it. A cell under a spill blocks it from growing and causes a #SPILL! error. Here the grand total is 8,140.

Build a two-way summary like a pivot table#

A pivot with Region in Rows and Product in Columns is a cross-tab. Formulas can build it too:

1. Row labels in F2: =SORT(UNIQUE(B2:B9)).
2. Column labels in G1: =TRANSPOSE(SORT(UNIQUE(C2:C9))). TRANSPOSE turns the list sideways, so Laptop, Phone and Tablet spill across row 1.
3. Values in G2:

=SUMIFS(D2:D9,B2:B9,F2#,C2:C9,G1#)

SUMIFS pairs every row label with every column label and spills a grid:

South sold no tablets, so its cell shows 0. A pivot would show a blank there.

How to summarize data in older Excel#

Without UNIQUE and spills, build the same summary with fixed lists and filled-down formulas:

1. Copy the Region column to F1, select it and use Data > Remove Duplicates. Then sort it A to Z.
2. In G2, type =SUMIFS($D$2:$D$9,$B$2:$B$9,F2).
3. Fill it down to the last region (Windows: Ctrl+D; Mac: ⌘D).

The $ signs lock the data ranges, while F2 moves to F3 and F4. The totals update when the data changes, but the region list doesn't, so add new categories by hand.

Data > Subtotal is another option in every version. Sort the data by Region, choose Data > Subtotal, pick At each change in: Region, Use function: Sum and tick Sales. Excel inserts a total row after each region, with outline buttons (1, 2, 3) to collapse the detail.

Newer options: GROUPBY and PIVOTBY#

Recent Microsoft 365 versions add two functions built for this job. =GROUPBY(B2:B9,D2:D9,SUM) returns the regions and their totals in one formula, with a grand total row. PIVOTBY adds column fields for a cross-tab. They aren't in Excel 2021 or 2024, so share files that use them only with Microsoft 365 users.

When a pivot table is still better#

Choose a pivot table when you are exploring the data and want to try many layouts quickly, need grouping by month in a couple of clicks, or want slicers for others to filter. If you're new to them, start with the beginner's guide to pivot tables. Ratios such as margin can be added there with a calculated field. Interviewers often ask how you'd do the same summary both ways, so it's worth knowing the answers to the most common pivot table interview questions.

Practise it#

A UNIQUE list with SUMIFS beside it is one of the most useful patterns in modern Excel. Practise a pivot-style summary with formulas in a live spreadsheet: you build the sorted list, then fill SUMIFS and COUNTIFS beside it, with instant checks. Then practise UNIQUE on its own. Try the SUMIFS exercise on this page first.