To split fixed and variable expenses in a sheet, add a Type column to your category list, mark each category as Fixed, Variable or Occasional, look that type up on every expense row, and total each type with SUMIFS. Divide each total by monthly income to see what share of your income is already committed.
How do I tag each category with a type?
On a Settings tab, put categories in A2:A20 and a type in B2:B20.
| Category | Type | Why |
|---|---|---|
| Rent or home EMI | Fixed | Same amount each month |
| School fees paid monthly | Fixed | Set by the school |
| Groceries | Variable | Moves with the month |
| Fuel | Variable | Depends on travel |
| Insurance premium, yearly | Occasional | Large, once a year |
| Festivals and gifts | Occasional | Seasonal |
On the log tab, add a Type column with =IFERROR(VLOOKUP(D2, Settings!$A$2:$B$20, 2, FALSE), "Check"). Any row showing Check has a category missing from the list. If you use Excel 365 or Google Sheets, XLOOKUP is a cleaner alternative: =XLOOKUP(D2, Settings!$A$2:$A$20, Settings!$B$2:$B$20, "Check").
What does the monthly split formula look like?
With the month's first date in H1, the total of each type is =SUMIFS($C$2:$C$2000, $E$2:$E$2000, "Fixed", $A$2:$A$2000, ">="&$H$1, $A$2:$A$2000, "<"&EDATE($H$1,1)). Copy it for Variable and Occasional by changing the word. Share of income is then =H3/Income, formatted as a percentage.
What does a worked example show?
Take a household with ₹80,000 monthly income. The figures are invented. Fixed costs are rent ₹18,000, a loan EMI ₹8,000, school fees ₹6,000, insurance converted to a monthly amount ₹2,000 and society maintenance ₹2,000, which adds to ₹36,000. Variable costs are groceries ₹12,000, fuel ₹4,000, eating out ₹3,500, utilities ₹3,500 and phone ₹1,500, adding to ₹24,500. Occasional costs, set aside at one twelfth of the yearly amount, come to ₹3,000.
| Block | Monthly (₹) | Share of income |
|---|---|---|
| Fixed | 36,000 | 45.0% |
| Variable | 24,500 | 30.6% |
| Occasional (monthly share) | 3,000 | 3.75% |
| Left over | 16,500 | 20.6% |
The four rows add to ₹80,000. The leftover cell is =Income-SUM(fixed, variable, occasional).
If your income varies from month to month, use an average of the last three or six months as Income, or divide each block by the lowest month to see the worst case. Plot the four rows as a simple stacked bar for each month; a fixed block that keeps growing is easier to notice in a picture than in a column of numbers.
Which part can I realistically change?
Fixed costs are hard to change quickly; a rent or EMI revision is a conversation or a decision that takes months. Variable costs are the part you can influence this month. Within them, separate what is essential, such as groceries and utilities, from what is discretionary, such as eating out. Add a column for Essential or Discretionary and run another SUMIFS. In the example, only the eating-out line of ₹3,500 is discretionary, so that is the cuttable portion this month; trimming it by a fifth would free ₹700.
A few cautions
- Some costs sit between types. A phone bill is fixed in amount but you can change the plan. Choose once and be consistent.
- Do not count loan repayments twice if you also have a separate savings line.
- A split by type is a way of seeing your spending, not a verdict on it.
Review the Type column twice a year. A category can move: a gym fee that was variable becomes fixed once you sign up for a year, and a school transport bill becomes fixed once the route is agreed.
Where to go from here
Run the split for three months and compare. If the fixed share keeps rising, look at which contract grew. If you import statements into SpendixAI, you can set up categories with their own colours and use the same Fixed, Variable and Occasional idea in the names you choose, then download a CSV to feed this sheet.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

