In Excel 365, =UNIQUE(C2:C500) lists each category in column C exactly once, spilling the result down from the cell you typed it in. Wrap it with SORT for an alphabetical list, add FILTER to drop blank rows, and refer to the spill range as E2# so a SUMIFS beside it totals every category automatically.
How does UNIQUE work?
The syntax is =UNIQUE(array, [by_col], [exactly_once]). With only the range, you get the distinct values in the order they first appear. UNIQUE and the other spill formulas below need Excel for Microsoft 365 or Excel 2021; they do not exist in Excel 2019 or older.
- =UNIQUE(C2:C500): each category once, in order of first appearance
- =UNIQUE(C2:C500,FALSE,TRUE): only categories that appear exactly once, handy for finding stray one-off labels
- =UNIQUE(B2:C500): unique combinations of two columns, such as merchant with category
Why is there a 0 at the bottom of my list?
If your range includes blank cells, UNIQUE returns a blank as one of its values, and it displays as 0. Remove the blanks with FILTER, then sort:
=SORT(UNIQUE(FILTER(C2:C500,C2:C500<>"")))
Read inside out: FILTER keeps the non-empty cells, UNIQUE removes repeats, SORT puts them in order. Type it in E2 and nothing else needs filling in; the list spills downwards by itself.
How do I total each category beside the list?
With the list in E2, type this in F2:
=SUMIFS($D$2:$D$500,$C$2:$C$500,E2#)
The hash after E2 is the spill reference: it means "the whole spilled list, however long it is". SUMIFS accepts that array as its criteria and returns one total per category, spilling alongside. When a new category appears in your data, both columns grow without you touching them.
| Cell | Formula | What it shows |
|---|---|---|
| E2 | =SORT(UNIQUE(FILTER(C2:C500,C2:C500<>""))) | Category list, sorted |
| F2 | =SUMIFS($D$2:$D$500,$C$2:$C$500,E2#) | Total for each category |
| G2 | =COUNTIF($C$2:$C$500,E2#) | Number of transactions in each |
| F1 | =SUM(F2#) | Grand total, to compare with the sheet total |
Say your data has 5 distinct categories. The list spills over E2:E6, the totals over F2:F6 and the counts over G2:G6. If the grand total does not match the sum of column D, some rows have a category the list does not capture, usually because of a typo or a trailing space.
What does a #SPILL! error mean?
It means something is sitting in the cells the result wants to fill. A typed value, a merged cell or a table below the formula can all block it. Clear the obstruction, or move the formula to a column with space beneath it. Excel will outline the blocked area when you select the error.
Can I rank the categories by how much I spent?
A list in alphabetical order is tidy, but a list with the biggest category first is more useful when you are looking for savings. In Microsoft 365, name the pieces with LET and sort one by the other:
=LET(c,SORT(UNIQUE(FILTER(C2:C500,C2:C500<>""))),t,SUMIFS($D$2:$D$500,$C$2:$C$500,c),SORTBY(HSTACK(c,t),t,-1))
Here c is the category list, t holds each category's total, HSTACK places the two side by side and SORTBY orders the pair by the totals, largest first. Everything lives in one cell, so there is nothing to copy down.
What UNIQUE will not fix
UNIQUE treats "Food" and "Food " (with a trailing space) as different values, so a sorted unique list is a quick audit: two near-identical names side by side mean two spellings to merge. Fix the source cells with TRIM, or pick one name and change the other via Find and Replace.
Setup checklist
- Confirm your Excel version supports dynamic arrays
- Put the unique list where nothing sits below it
- Filter blanks before UNIQUE
- Use the spill reference with a hash for the summary columns
- Compare the grand total with the raw column total
When you outgrow sheets and want these totals without maintaining formulas, SpendixAI has a Report builder where you filter and group transactions and download the result as CSV.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

