Spreadsheets

Annual summary sheet that pulls from twelve monthly tabs

Make an annual expense summary in Excel from twelve monthly tabs with direct links, a 3D SUM or INDIRECT, plus totals and a highlighted peak month.

Editorial illustration for Annual summary sheet that pulls from twelve monthly tabs

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.

MethodStrengthWatch out for
Direct linkSimple, transparentTyped by hand twelve times
INDIRECTOne formula fills allBreaks on tab renames, slow when large
3D SUMTotals across all tabs at onceDepends 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.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports