An annual summary sheet works best when every monthly tab has the same layout, so one summary formula can read all twelve. You have three ways to pull the numbers: a direct link to each tab, a 3D reference that adds the same cell across tabs, or INDIRECT driven by the tab name in a header cell.
How should the monthly tabs be set up?
Name them Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar in that order, matching the April to March year. On each tab, put category names in A2:A9 and that month's total for each category in B2:B9, with the grand total in B10. Keep the layout identical: same categories, same rows, in every tab. That consistency is what makes the summary possible.
What does the summary tab look like?
On a tab called Summary, write categories in A2:A9 and the twelve month names across B1:M1, exactly as the tabs are spelled. Put Year total in N1. Everything below depends on matching those header names to the tab names.
Which method should I use to pull the numbers?
Direct links
In B2 type =Apr!B2, in C2 type =May!B2, and so on. Plain and fast, never breaks quietly, but you must set each column by hand because filling right would move to the wrong cell reference.
INDIRECT with the tab name
In B2 enter:
- =INDIRECT(B$1&"!B"&ROW())
B$1 supplies the tab name, ROW() supplies the row number, and the result reads as =Apr!B2. Fill right and down, and the headers do the choosing. If your tab names contain spaces or hyphens, wrap them in quotes inside the formula: =INDIRECT("'"&B$1&"'!B"&ROW()). Two drawbacks: INDIRECT recalculates every time anything changes, which slows large workbooks, and it fails if a tab is renamed because the text no longer matches.
3D sum for the annual column
For Year total, there is an even shorter route if the tabs sit in a row between the first and last: =SUM(Apr:Mar!B2). Excel adds B2 on every tab positioned from Apr to Mar. Moving a tab outside that stretch, or adding one in between, changes the answer, so keep any helper tab elsewhere.
| Method | Strength | Watch out for |
|---|---|---|
| Direct link | Simple, transparent | Typed by hand twelve times |
| INDIRECT | One formula fills all | Breaks on tab renames, slow when large |
| 3D SUM | Totals across all tabs at once | Depends on tab order |
To keep the twelve monthly tabs identical, build April once and check it carefully. Then right-click its tab, choose Move or Copy, tick Create a copy, and rename the copy May. Clear the typed entries but leave every formula in place. Copying a finished tab is the surest way to get the same layout twelve times.
How do I highlight the highest month?
Add a Total row at row 10 with =SUM(B2:B9) filled across. Select B10:M10, open Home, Conditional Formatting, New Rule, choose Use a formula, and enter =B10=MAX($B$10:$M$10). Pick a fill colour. The month with the largest spend lights up, and it moves by itself as figures change. For each category row, do the same with =B2=MAX($B2:$M2) applied to B2:M9 to see each category's peak month.
What about a new financial year?
Save a copy of the finished workbook as the new year's file, clear the entries on each monthly tab and keep the formulas. The summary then restarts clean. Resist adding a fresh tab for the thirteenth month; if you need another period, make a new workbook.
Quick checklist
- Twelve tabs, same layout.
- Header spelling equals tab spelling.
- Check cell equal to zero.
- Highlight rule applied.
When keeping twelve tabs becomes tedious, SpendixAI gives you reports across any date range from your imported statements, so a yearly view does not depend on tab discipline.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


