To build a budget from your own spending, paste three months of bank statements into one sheet, tag each row with a category, total each category across the three months with SUMIFS, divide by three, and round up. Keep one-off and yearly items out of that average and cover them with a monthly sinking-fund amount instead.
How do I set up the three-month sheet?
- Open a tab named Data. Paste the rows from all three statements under these headings: Date in A, Narration in B, Debit in C, Category in D, Exclude in E.
- Use the same date format throughout. Delete the opening balance, closing balance and any credit rows you do not need.
- Add a Month column in F with =TEXT(A2,"yyyy-mm") so you can check each month on its own.
- Fill Category from a dropdown list kept on a Budget tab, and put Yes in Exclude for rows that do not belong in a monthly average.
Which rows should be excluded?
- Payment of a credit card bill from this bank account. The card statement already lists the purchases, so counting both double-counts them.
- Transfers between your own accounts.
- One-off purchases, such as a ₹6,500 repair, which will not repeat monthly.
- Yearly or quarterly bills, such as an insurance premium.
If you paste both the bank and card statements, this first point is the biggest source of inflated budgets.
Why not use AVERAGEIFS?
AVERAGEIFS averages the rows, not the months. A category with 20 small transactions would give you the average size of a purchase, not the monthly spend. To get a monthly figure, add the category total for the three months and divide by three: in B2 on the Budget tab, with the category name in A2, enter =SUMIFS(Data!$C$2:$C$600, Data!$D$2:$D$600, $A2, Data!$E$2:$E$600, "<>Yes")/3. The criterion "<>Yes" skips excluded rows while still counting blanks.
What does a worked example show?
Say Groceries came to ₹11,800, ₹12,600 and ₹12,100 across the three months. The total is ₹36,500, and divided by three it is ₹12,166.67. Round it up to the next hundred with =CEILING(B2,100), giving a budget of ₹12,200.
| Category | Three-month total (₹) | Monthly average (₹) | Budget (₹) |
|---|---|---|---|
| Groceries | 36,500 | 12,166.67 | 12,200 |
| Fuel | 11,400 | 3,800 | 3,800 |
| Eating out | 9,300 | 3,100 | 3,100 |
| Mobile and internet | 3,600 | 1,200 | 1,200 |
Rounding up gives a small cushion without turning the budget into a guess.
Where do yearly bills go?
Say your annual insurance premium is ₹14,400. Excluded from the average, it needs its own line: divide by 12 to get ₹1,200 a month and put that in a Sinking fund category. Do the same for school fees paid termly, festival shopping, annual subscriptions and a car service. Add the monthly sinking-fund amounts to the budget total, so the money is already waiting when each bill arrives.
What do I check before I trust the budget?
- The total of all categories plus excluded rows equals the total of the Debit column.
- No row has a blank category.
- The total budget is below your take-home pay, with room for savings.
- Each excluded row has a reason noted next to it.
If pasting and tagging three statements feels slow, SpendixAI can take statements as PDF, CSV or Excel, ask you to review the rows, and group spending by category, so you can use its reports or CSV for the same averaging step. The thinking in this article stays the same either way.
Finally, put the budget side by side with next month's actual spending in a Variance column, using Budget minus Actual. After one month you will see which lines were set too tightly, and the second version of the budget will be much closer to real life than the first.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


