Fetch the budget with XLOOKUP and the actual with SUMIFS, side by side in one summary table. XLOOKUP finds each category's planned amount on the Budget sheet, SUMIFS adds up that category's rows on the Expenses sheet for the chosen month, and a third column subtracts one from the other. XLOOKUP needs Excel 365 or Excel 2021.
How should the three sheets be laid out?
- Budget sheet: category in A2:A50, monthly budget in B2:B50.
- Expenses sheet: date in A, category in B, amount in C, one row per transaction down to row 2000.
- Summary sheet: the month you are reporting on, typed as the first of that month, in F1 (for example 01-06-2027), and the table starting at row 2.
What do the lookup and sum formulas look like?
On the Summary sheet, put the category in A2 with =Budget!A2 and fill down. Then:
- B2, budget: =XLOOKUP($A2,Budget!$A$2:$A$50,Budget!$B$2:$B$50,0). The final 0 is what shows if the category is missing from the Budget sheet.
- C2, actual: =SUMIFS(Expenses!$C$2:$C$2000,Expenses!$B$2:$B$2000,$A2,Expenses!$A$2:$A$2000,">="&$F$1,Expenses!$A$2:$A$2000,"<"&EDATE($F$1,1))
- D2, left: =B2-C2
- E2, used: =IF(B2=0,"",C2/B2) formatted as a percentage.
The SUMIFS first argument is always the range being added, then each criteria range comes with its own criterion. The two date lines mean on or after the first of the month and before the first of the next month, which captures every day of the month without worrying whether it has 28 or 31 days.
To report on another month, change only F1. Typing 01-07-2027 makes the first date test mean on or after 1 July and the second mean before 1 August, because EDATE moves the date forward by one month. Nothing in the formulas needs editing. For a quarter or a full April to March year, add a second cell with the end date and replace the EDATE part with that cell, so the test reads before the day after your last date.
What if you add a category later?
Copying A2 down by hand means a new budget row is missed until you copy again. In Excel 365 or 2021, replace the A column with one spilling formula in A2:
=FILTER(Budget!$A$2:$A$50,Budget!$A$2:$A$50<>"")
Then use A2# as the lookup value and the criteria, so one formula fills the whole column: B2 becomes =XLOOKUP(A2#,Budget!$A$2:$A$50,Budget!$B$2:$B$50,0) and C2 becomes the same SUMIFS with A2# in place of $A2. Type a new category on the Budget sheet and all three columns grow by a row.
What does the finished table show?
Say the budget and expenses below are invented for June.
| Category | Budget | Actual | Left | Used |
|---|---|---|---|---|
| Groceries | ₹8,000 | ₹6,420 | ₹1,580 | 80.3% |
| Transport | ₹3,000 | ₹3,450 | -₹450 | 115.0% |
| Dining out | ₹2,500 | ₹1,900 | ₹600 | 76.0% |
| Total | ₹13,500 | ₹11,770 | ₹1,730 | 87.2% |
Check the arithmetic: 6,420 plus 3,450 plus 1,900 is 11,770, and 13,500 minus 11,770 leaves 1,730. Transport is the one row to look at: 3,450 divided by 3,000 is 115 percent.
Next steps
- Put a dropdown on the Expenses category column so spellings always match the Budget sheet.
- Use the same month cell in F1 for every formula so changing it updates the whole table.
- Once a month, run the check cell before trusting the percentages.
A last tip: format the Left column with a custom number format such as #,##0;[Red]-#,##0, so overspending shows in red without any conditional formatting. Keep the Summary sheet read-only in your head, meaning you never type over its formulas; all edits belong on the Budget and Expenses sheets.
If typing every row into the Expenses sheet becomes the chore, SpendixAI can read a bank statement in PDF, CSV or Excel and show the rows for review before saving, with budgets on its Pro plan.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

