Spreadsheets

Percentage of income spent on each category: formula and layout

Work out the percentage of income spent on each category in Excel or Sheets: divide the category total by one take-home income cell and format as percent.

Editorial illustration for Percentage of income spent on each category: formula and layout

Divide each category's total by your take-home income, with the income cell locked using dollar signs, and format the result as a percentage. If your income is in H1 and a category total is in C2, type =C2/$H$1 and fill down. A final check that the shares add up to total spend divided by income tells you no category was missed.

What layout should I use?

Put one row per category, with the total in one column and the share beside it. Keep the income in a single labelled cell near the top, so you change it in one place each month. Here is a hypothetical month with a take-home income of ₹80,000.

CategorySpent (₹)Share of income
Rent20,00025.0%
Groceries12,00015.0%
Transport6,4008.0%
EMI10,00012.5%
Everything else6,0007.5%
Total spent54,40068.0%
Left over25,60032.0%

Check the arithmetic: 20,000 plus 12,000 plus 6,400 plus 10,000 plus 6,000 is 54,400, and 54,400 divided by 80,000 is 0.68, or 68 percent. What remains, 25,600, is 32 percent.

How do I get the category totals?

If your log has Date in A, Category in B and Amount in C, list the categories down column E and use SUMIFS for the month. With the month's first date in G1: =SUMIFS($C$2:$C$500,$B$2:$B$500,$E3,$A$2:$A$500,">="&$G$1,$A$2:$A$500,"<"&EDATE($G$1,1)). Put the answer in F3 and fill down. The sum range comes first, then each pair of range and criterion.

What is the formula for the share?

  1. Put take-home income in H1, for example 80000, and label it in G1 or above.
  2. In the next column, say G3, type =F3/$H$1. The dollar signs keep every row pointing at the same income cell.
  3. Select the share column and choose Percent Style, then add one decimal place.
  4. Fill the formula down for every category.
  5. To avoid an error when the income cell is empty, use =IF($H$1=0,"",F3/$H$1).

How do I check the shares are complete?

Add a check cell under the table: =IF(ROUND(SUM(G3:G12)-SUM($C$2:$C$500)/$H$1,6)=0,"OK","Check categories"). The first part adds your category shares. The second takes all spending in the log and divides it by income. If the log has a row with an empty or misspelt category, the category shares fall short and the cell warns you. This catches the most common mistake in these sheets. If your log holds more than one month, the check needs the same date filter as the category totals.

What should I treat carefully?

  • Income that arrives irregularly. A month with a bonus will make every share look smaller, so compare with similar months.
  • Money moved between your own accounts. It is not spending and should not appear as a category.
  • A credit card bill paid from the bank, alongside the card purchases. Count one or the other, not both.
  • Annual costs. An insurance premium paid once a year inflates one month; spread it across twelve if you want a steady picture.
  • Savings and investment transfers. Decide whether they are a category here, and apply the same rule every month.

What do I do with the numbers?

The shares show where your income goes, and the leftover line shows what you kept. No percentage is right or wrong for everyone, since rent in one city and family obligations in another differ widely. Watch how your own shares move from month to month and ask what changed.

If keeping the log current is the weak link, SpendixAI can build it from uploaded statements and categorise rows that you review, then show category totals in reports. Your sheet can still hold the income cell and the share formulas you prefer.

  • One income cell, locked with dollar signs.
  • Share formula copied down, formatted as percent.
  • Check cell shows OK.
  • Left over equals income minus total spent.
SPENDIXAI JOURNAL

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

Explore your reports