The # spill reference (the spill range operator) lets a formula point at "whatever size this dynamic array is today". Write E2# and Excel reads the whole spilled list that starts in E2, however many rows it has. That is what lets a category summary, and a chart on top of it, grow when a new category shows up in your transactions.
What does the # after a cell actually do?
Suppose your transactions sit in A2:C500, with the date in A, the category in B and the amount in C. In E2 you type a formula that returns a unique, sorted list of categories:
- E2: =SORT(UNIQUE(FILTER($B$2:$B$500,$B$2:$B$500<>"")))
- The FILTER part drops blank rows, so you do not get an empty entry or a stray 0 in the list.
- Excel spills the answer down from E2. Only E2 holds a formula; the cells below are the spilled result and show greyed formula text if you click them.
If six categories exist today, the list fills E2:E7. Next month you buy a one-off "Pets" item, type Pets in column B, and the list quietly becomes seven rows. Writing E2# anywhere else in the workbook always means "the full current list", not E2:E7 frozen in time. This needs Excel 365 or Excel 2021; older versions have no dynamic arrays.
How do I build a summary that grows beside the list?
In F2 write one formula and let it spill too:
- F2: =SUMIFS($C$2:$C$500,$B$2:$B$500,E2#)
Because the criteria argument is the whole spilled list, SUMIFS returns one total per category and spills the same number of rows as column E. Both columns grow together, and the order always matches because F follows E. You never copy a formula down.
| Cell | Formula | What it gives you |
|---|---|---|
| E2 | =SORT(UNIQUE(FILTER($B$2:$B$500,$B$2:$B$500<>""))) | Category list, A to Z |
| F2 | =SUMIFS($C$2:$C$500,$B$2:$B$500,E2#) | Total for each category |
| H1 | =SUM(F2#) | Grand total of the summary |
| H2 | =ROWS(E2#) | How many categories exist today |
Say your amounts are Groceries 8,400, Fuel 3,200 and Eating out 2,650. H1 shows ₹14,250. Add a first Pets row of ₹1,100 and H1 becomes ₹15,350 without touching any formula.
How do I make the chart grow with it?
A chart cannot take E2# directly in its series box, so go through a defined name:
- Go to Formulas, then Name Manager, then New. Name it CatNames and set "Refers to" as =Sheet1!$E$2#. Make a second name, CatTotals, referring to =Sheet1!$F$2#.
- Insert a column chart on any cell, right-click it and choose Select Data.
- Edit the series values and type =Book1!CatTotals, using your own file name before the exclamation mark (Excel often fills it in for you).
- Edit the horizontal axis labels and type =Book1!CatNames in the same way.
Now a new category adds a new bar. Check this once by adding a test row, then delete it. Behaviour of charts with names can differ between Excel versions, so test it before you rely on it.
Why does it show #SPILL! and what breaks the range?
The spill needs clear space. If any cell in the path already holds something, even a space typed by accident or a note, Excel returns #SPILL! and the whole list disappears until you clear the obstruction. Click the error cell; Excel marks the blocking cell. Common culprits:
- A total row typed directly under the list, in the cells the list needs next month.
- A merged cell in the path (spills cannot go through merged cells).
- Putting the summary inside an Excel Table, which does not allow spills.
- Leaving something under the list because "there are only six categories".
Keep the area under E2 and F2 empty, and place totals to the side, like H1 above.
What next once this works?
- Add a second spilled column that divides each total by H1, then format it as a percentage.
- Wrap the spill in SORTBY(E2#,F2#,-1) elsewhere if you want the biggest category first, and chart that instead.
- Keep the category spelling consistent in column B, because "Grocery" and "Groceries" are two different entries to UNIQUE.
If you would rather not retype categories at all, SpendixAI can categorise imported statement rows and show category totals in its reports, which helps when a sheet becomes more upkeep than insight.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


