How to Track Income and Expenses in Excel (4 Suitable Examples)

To demonstrate our methods, we’ll use the following sample dataset that contains Income and Expenses in different Categories.

Example 1 – Using Formulas

We can combine the SUMIF and SUM functions to track income and expenses.

Steps:

Select cell C13 (the cell to show the summary of Income).
Enter the following formula:

=SUMIF(B6:B8,B13,C6:C8)

In the function, B6:B8 is the range, B13 is the criteria, and C6:C8 is the sum_range. The function sums the values from sum_range that match the criteria within the selected range.

Press ENTER.

The summed value for that particular Category is returned.

Drag the Fill Handle to copy the formula to the other cells.

The summed value for all the categories is returned.

Select cell C16 (the cell to calculate Total Income).
Enter the following formula:

=SUM(C13:C15)

C13:C15 is the number range to sum.

Press ENTER to return the Total Income.

Follow the exact same process to derive the Expense summary.

Select cell C17 (the cell to show the summary of Expense).
Enter the following formula:

=SUMIF(D6:D10,B17,E6:E10)

In the function, D6:D10 is the range, B17 is the criteria, and E6:E10 is the sum_range. This function sums the values from sum_range that match the criteria within the selected range.

Press ENTER to return the summed value for that particular Category.

Drag the Fill Handle down to copy the formula to the other cells.

The summed values for all the categories are returned.

To calculate Total Expense, select cell C22.
Enter the following formula:

=SUM(C17:C21)

Here, C17:C21 is the number range to sum.

Press ENTER to return the Total Expense.

Now we calculate the Balance.

In cell C24 enter the following formula:

=C16-C22

This formula subtracts Total Expense from Total Income to get the Balance.

Press ENTER to return the Balance.

In cell C25 enter the following formula:

=C22/C16*100%

This formula will divide the Total Expense by the Total Income and then multiply it by 100% to return the Expense Percentage.

Press ENTER.

Example 2 – Using Pivot Table

We can also use the Pivot table to track income and expenses.

Steps:

Select the whole dataset.
Go to the Insert tab.
Select Table.

A dialog box will appear on the screen.

Check if the table selection is correct and adjust if necessary.
Mark My table has headers.
Press OK to return the table.

Select the table again.
Go to the Table Design tab.
Select Summarize with PivotTable.

A dialog box will appear on the screen.

Select the location for the Pivot Table.
Click OK.

PivotTable Fields list will appear on the right of the screen.

Select and drag the Income column to the Values area.

The PivotTable will display the sum of values in the Income column.

Select and drag the Expense column to the Values area.

The PivotTable will display the sum of values in the Expense column.

Click Update.

The Sum of Income and Sum of Expense are now displayed.

Now we calculate the Balance.

In cell C15, enter the following formula:

=GETPIVOTDATA("Sum of Income",$B$12)-GETPIVOTDATA("Sum of Expense",$B$12)

Here, we subtract the Sum of Expense from the Sum of Income to get the Balance.

Press ENTER to return the Balance.

In cell C16, enter the following formula:

=GETPIVOTDATA("Sum of Expense",$B$12)/GETPIVOTDATA("Sum of Income",$B$12)*100%

This formula will divide the Sum of Expense by the Sum of Income and then multiply it by 100% to return the Expense Percentage as result.

Press ENTER to return the Expense Percentage.

Example 3 – Using a Graph

Steps:

In cell H5, enter the following formula:

=SUM(C6:C8)

Here, C6:C8 is the number range to be summed.

Press ENTER to return the Total Income.

Now calculate the Total Expense by following the same steps:

In cell H6, enter the the following formula:

=SUM(E6:E10)

Here, E6:E10 is the number range to be summed.

Press ENTER to return the Total Expense.

Now, we calculate the Balance.

In cell H8, enter the following formula:

=H5-H6

Here, we subtract the Total Expense from the Total Income to get the Balance.

Press ENTER to return the Balance.

In cell H9, enter the following formula:

=H6/H5*100%

This formula will divide the Total Expense by the Total Income and then multiply it by 100% to return the Expense Percentage as result.

Press ENTER to return the Expense Percentage.

Now we insert a graph for Total Income and Total Expense.

Select the rows to graph. Here, Total Income and Total Expense.
Go to the Insert tab.
Select Column or Bar Chart.

Select the type of chart. Here, Clustered Column from 2-D Column.

A graph like the image below is created.

Example 4 – Using “Personal Monthly Budget Template”

Excel provides a Personal Monthly Budget template to track income and expenses.

Steps:

Go to the File tab on the Ribbon.

Select the Personal Monthly Budget template as follows:

Select Create.

The template will appear.

Edit the template according to your dataset.

This template will automatically calculate Total monthly income and Total Expense. It uses the SUM function to calculate Total monthly income and the SUBTOTAL function to calculate Total Expense.