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

Excel can be extremely useful for tracking your income and expenses. The purpose of this tutorial is to explain how to track income and expenses in Excel.

How to Track Income and Expenses in Excel: 4 Suitable Examples

Here, I have taken a sample dataset that contains Income and Expenses. I have shown the Category and Amount for both  Income and Expense. For you to understand clearly I will be using this same dataset for every method.

1. Using Formulas to Track Income and Expenses in Excel

In this method, I will be using SUMIF and SUM functions to track income and expenses.

Now, let’s see how to track income and expenses in Excel using combined formulas.

Steps:

• Firstly, select the cell where you want to show the summary of your Income. Here, I selected cell C13.
• Secondly, in that cell write the following formula.

Here, I used the SUMIF function, and in the function, I used B6:B8 as range, B13 as criteria, and C6:C8 as sum_range. This function sums the values from sum_range that match the criteria within the selected range.

• Next, press ENTER and you will get the summed value for that particular Category.

• After that, drag the Fill Handle to copy the formula to the other cells.

• Finally, you have got the summed value for all the categories.

• Now, select the cell where you want to calculate your Total Income. Here, I selected cell C16.
• After that, in that cell write the formula shown below.

Here, C13:C15 is the number range that I want to sum. This function will return me the summed value of this range.

• Finally, press ENTER to get your Total Income.

Now, you can follow the exact same process for your Expense summary.

• At first, select the cell where you want to show the summary of your Expense. Here, I selected cell C17.
• Next, in that cell write the following formula.

Here, I used the SUMIF function, and in the function, I used D6:D10 as range, B17 as criteria, and E6:E10 as sum_range. This function sums the values from sum_range that match the criteria within the selected range.

• After that, press ENTER and you will get the summed value for that particular Category.

• Finally, drag the Fill Handle to copy the formula to the other cells.

• Now, you have got the summed value for all the categories.

• After that, select the cell where you want to calculate your Total Expense. Here, I selected cell C22.
• Next, in that cell write the formula shown below.

Here, C17:C21 is the number range that I want to sum. This function will return me the summed value of this range.

• Finally, press ENTER to get your Total Expense.

Now, I want to calculate the Balance.

• Firstly, select the cell where you want the value for Balance. Here, I selected cell C24.
• Secondly, in that cell write the following formula.

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

• Now, press ENTER and you will get your Balance.

• After that, select the cell where you want to calculate your Expense Percentage. Here, I selected cell C25.
• Next, In that cell write the following formula.

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

• Finally, press ENTER and you will get your  Expense Percentage.

2. Tracking Income and Expenses in Excel by Using Pivot Table

In this 2nd method, I will use the Pivot table to track income and expenses. We will use the same data and will calculate the Income & Expense Summary.

Let’s see how to track income and expenses in excel with the use of Pivot Table.

Steps:

• Firstly, select the whole dataset.
• Secondly, go to the Insert tab.
• Thirdly, select Table from the Insert tab.

• After that, a dialog box will appear on the screen.
• Next, check if the table selection is all right.
• Then, select My table has headers option.
• Finally, press OK and you will get your table.

• Now, select the table again.
• After that, go to the Table Design tab.
• Finally, select Summarize with PivotTable from the Table Design tab.

A dialog box will appear on the screen.

• Firstly, select the location where you want your Pivot Table.
• Secondly, press OK.

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

• After that, select and drag the Income column to the Values area. The PivotTable will display the sum of values in the Income column.
• Next, select and drag the Expense column to the Values area. The PivotTable will display the sum of values in the Expense column.
• Finally, select update.

Now, you will see that you have got your Sum of Income and Sum of Expense.

Here, I will calculate the Balance.

• Firstly, select the cell where you want the value for Balance. Here, I selected cell C15.
• Secondly, in that cell write the following formula.

Here, I subtracted the Sum of Expense from the Sum of Income to get the Balance.

• Finally, press ENTER and you will get your Balance.

• After that, select the cell where you want to calculate your Expense Percentage. Here, I selected cell C16.
• Next, In that cell write the following formula.

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.

• Now, press ENTER and you will get your Expense Percentage.

Read More: Daily Income and Expense Sheet in Excel

3. Using Graph to Track Income and Expenses in Excel

In this method, I will show you how to track income and expenses in Excel using a graph. Let’s learn step by step how it is done.

Steps:

• To begin with, select the cell where you want to calculate your Total Income. Here, I selected cell H5.
• Next, in that cell write the formula shown below.

Here, C6:C8 is the number range that I want to sum. This function will return me the summed value of this range.

• Finally, press ENTER to get your Total Income.

You can also calculate the Total Expense by following these same steps.

• Firstly, select the cell where you want to calculate your Total Expense. Here, I selected cell H6.
• Secondly, in that cell write the formula shown below.

Here, I selected E6:E10 as the number range that I want to sum. This function will return me the summed value of this range.

• Finally, press ENTER to get your Total Expense.

Now, I will calculate the Balance.

• Firstly, select the cell where you want the value for Balance. Here, I selected cell H8.
• Secondly, in that cell write the following formula.

Here, I subtracted the Total Expense from the Total Income to get the Balance.

• Finally, press ENTER and you will get your Balance.

• After that, select the cell where you want to calculate your Expense Percentage. Here, I selected cell H9.
• Next, In that cell write the following formula.

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

• Finally, press ENTER and you will get your  Expense Percentage.

Now, I will insert a graph for Total Income and Total Expense.

• Firstly, select the rows you want to make a graph for. Here, I selected Total Income and Total Expense.
• Secondly, go to the Insert tab.
• Thirdly, select Column or Bar Chart.

• After that, select the type of chart you want. Here, I selected Clustered Column from 2-D Column.

Now, you will get a graph like a picture shown below.

From the following picture, you can see that graph helps us to compare the values more easily and clearly.

4. Use of “Personal Monthly Budget Template” to Track Income and Expenses in Excel

In this method, I will use the Personal Monthly Budget template from Excel to track income and expenses.

Let’s see step-by-step how to track income and expenses in Excel with the use of the Personal Monthly Budget template.

Steps:

• Firstly, go to the File tab from the Ribbon.

• After that, select the Personal Monthly Budget template.

• Next, select Create.

The template will appear.

• After that, edit the template according to your dataset. Here, in the following picture, you can see that I have edited the template according to my 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.

Now, I will calculate the Balance.

• Firstly, select the cell where you want the value for Balance.
• Secondly, in that cell write the following formula.

Here, I subtracted the Total Expense from the Total monthly income to get the Balance.

• Finally, press ENTER and you will get your Balance.

I will also calculate Expense Percentage.

• Firstly, select the cell where you want to calculate your Expense Percentage.
• Next, In that cell write the following formula.

Here, I divide the Total Expense by the Total monthly income and then multiplied it by 100% to get the Expense Percentage as result.

• Now, press ENTER and you will get your Expense Percentage.

Things to Remember

• It should be noted that, while working with PivotTable the name of every column must be different.

Practice Section

Here, I have provided a practice sheet for you to practice how to track income and expenses in Excel.

Download Practice Workbook

Conclusion

In this article, I tried to explain 4 different ways in which you can track income and expenses in Excel. I hope this was helpful and after reading this article you have learned how to track income and expenses in Excel. I recommend you practice to learn better. Last but not least if you have any questions feel free to let me know in the comment section below.

Related Articles

• How to Calculate Business Income and Expense in Excel Worksheet
• How to Keep Track of Small Business Expenses in Excel
• How to Create Daily Expense Sheet Format in Excel

<< Go Back to Excel Expense Tracker | Tracker in Excel | Excel Templates