Getting started

A twelve-month budget planner in Excel: April to March, with formulas

Build an April to March budget planner in Excel with a data table and SUMIFS formulas that total each category by month; steps and syntax are exact.

Editorial illustration for A twelve-month budget planner in Excel: April to March, with formulas

To build a budget planner in Excel, keep every expense as a row in an Excel table named Expenses, then use SUMIFS on a second sheet to total each category for each month from April to March. The formulas below use plain syntax that works in Excel 2010 and later, apart from one marked step for Excel 365 and 2021.

Step one: the Expenses table

On a sheet called Data, type these headers in row 1: Date, Category, Amount, Account. Add a few rows, click inside them and press Ctrl and T to make a table. On the Table Design tab, rename the table to Expenses. Now every formula can refer to Expenses[Amount] instead of cell ranges, and the table grows as you add rows.

Step two: a planner sheet with months across

Call the second sheet Planner. In B2 enter the first month of the financial year, then fill the others across:

  • B2: =DATE(2026,4,1)
  • C2: =EDATE(B2,1), then copy it across to M2 so that M2 is March 2027
  • Format B2:M2 as mmm-yy, and put Total in N2
  • Down column A from A3, type your categories: Rent, Food, Transport and so on.

In Excel 365 or 2021 you could instead fill A3 with =UNIQUE(Expenses[Category]), which lists each category once. In older versions, type the list by hand.

Step three: the SUMIFS formula

In B3, enter the following and copy it right across to M3 and down through your last category:

=SUMIFS(Expenses[Amount], Expenses[Category], $A3, Expenses[Date], ">="&B$2, Expenses[Date], "<"&EDATE(B$2,1))

SUMIFS takes the sum range first, then pairs of criteria range and criterion. The two date conditions select 'on or after the first of this month' and 'before the first of next month', which catches a whole month without worrying about its last day. The dollar signs lock column A and row 2 as you copy. In N3, enter =SUM(B3:M3) for the category's yearly total.

A worked example

Say Food is planned at ₹9,000 a month and the Expenses table holds three April food rows of ₹3,200, ₹2,750 and ₹3,640. The April cell shows 3,200 + 2,750 + 3,640 = ₹9,590, so you are ₹590 over. A second block below, with planned amounts typed in, and a variance cell like =B20-B3 (planned minus actual, with your planned block starting at row 20), shows that at a glance.

CellWhat it holdsExample result
Planned, April, FoodTyped by you₹9,000
Actual, April, FoodSUMIFS formula₹9,590
VariancePlanned minus actual-₹590
Yearly planned, Food9,000 x 12₹1,08,000

How do you make overspending visible?

Select the variance cells, choose Home, Conditional Formatting, Highlight Cells Rules, Less Than, enter 0 and pick a red fill. Add a line =SUM(B3:B14)-SUM(B20:B31) at the foot of each month for the overall variance, adjusting ranges to your layout.

What commonly goes wrong?

  • Dates typed as text. Select the column and check they right-align, or retype one.
  • Category names that differ by a space. A dropdown from Data, Data Validation, List prevents this.
  • A planner range that stops one row short of the last category.
  • Saving the file as CSV by mistake, which keeps only one sheet and no formulas.

A refinement for later: add a Month helper column to the Expenses table with =TEXT([@Date],"mmm-yy") and build a PivotTable from the table if you prefer clicking to formulas. The SUMIFS version above is easier to audit because every number can be traced to its conditions. Either way, check the grand total of the planner against the sum of the Amount column for the year; the two must agree.

When a spreadsheet stops being enough

If the typing becomes the chore, SpendixAI can import statements and categorise them, and you can download reports as CSV and keep the planner for what is special about your household.

SPENDIXAI JOURNAL

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

Explore your reports