A pivot calculated field works on columns, not on the values inside a column. So if one Type column holds Income and Expense, first split the amounts into an Income column and an Expense column in your data. Then add a calculated field named Savings with the formula =Income - Expense, and the pivot shows savings for each month.
Why can't I subtract two items of the same column?
Calculated fields take field names, such as Income and Expense, as their ingredients. A single Amount column with a Type column beside it has only one numeric field. You can show Type across the top and read Income and Expense side by side, but Excel will not let you subtract them as a field. There are two ways around it: change the data, or put a formula beside the pivot.
How do I do it with helper columns in Excel?
- In your Table, add a column named Income with =IF([@Type]="Income",[@Amount],0).
- Add a column named Expense with =IF([@Type]="Expense",[@Amount],0).
- Insert a pivot table from the Table. Put Month in Rows, and drag Income and Expense into Values.
- Click inside the pivot and choose PivotTable Analyze, Fields, Items and Sets, Calculated Field.
- Name it Savings, and in Formula type =Income - Expense. Press Add, then OK.
- Savings appears as a value column. Format it with a thousands separator.
Say a hypothetical month has Income of ₹72,000 and Expense of ₹54,500. The pivot shows Savings of ₹17,500. In a month with income of ₹65,000 and expense of ₹68,200, Savings shows -₹3,200, which tells you spending ran ahead of income.
| Month | Income (₹) | Expense (₹) | Savings (₹) |
|---|---|---|---|
| Apr | 72,000 | 54,500 | 17,500 |
| May | 72,000 | 58,000 | 14,000 |
| Jun | 65,000 | 68,200 | -3,200 |
| Total | 2,09,000 | 1,80,700 | 28,300 |
How do I do it in Google Sheets?
Create the pivot with Month in Rows. Under Values, click Add and choose Calculated field. Sheets asks for a formula built from your column headings; it shows an example in the box, so follow its pattern with the names of your own Income and Expense columns, subtracting one sum from the other. Sheets can also read Type in Columns, which shows Income and Expense side by side, after which a formula beside the pivot gives the difference. Because the pivot sits on its own sheet, keep the formula outside its area so it does not get overwritten.
What is the SUMIFS alternative?
A SUMIFS table beside the pivot is often simpler. With month start dates in column G, income in H and expense in I, these formulas fill each row: income =SUMIFS($C$2:$C$500,$B$2:$B$500,"Income",$A$2:$A$500,">="&G2,$A$2:$A$500,"<"&EDATE(G2,1)), and the same with "Expense" for column I. Savings in J2 is =H2-I2. These rows react to new data without a refresh, while a calculated field needs the pivot refreshed.
Which route suits you? If you only look at savings once a month, the pivot with a calculated field is quick and tidy. If you want a savings column that sits in a report next to a target, or you share the sheet with someone who will not press Refresh, the SUMIFS table is steadier. You can also keep both and compare them: the two Savings totals should agree to the rupee, which is a good test that your Type column has no stray spellings.
What should I check?
- The Savings total equals total Income minus total Expense.
- Transfers between your own accounts are excluded, or Income and Expense each include them once, so they cancel out.
- Credit card bill payments are not counted on top of the card purchases themselves.
- The pivot is refreshed after you add rows.
Matching income to spending is simple in principle and fiddly in practice. SpendixAI keeps each transaction against the account you picked at import, which helps when separating card payments from purchases, and it has budgets and savings goals on Pro if you want to look forward. For a first look at monthly savings, the pivot above is enough.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


