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.


Download Practice Workbook


4 Simple Ways to Track Income and Expenses in Excel

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.

4 Simple Ways to Track Income and Expenses in Excel


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.
=SUMIF(B6:B8,B13,C6:C8)

How to Track Income and Expenses in Excel

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.

Using Formulas to Track Income and Expenses in Excel

  • 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.

Using Formulas to Track Income and Expenses in Excel

  • 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.
=SUM(C13:C15)

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.
=SUMIF(D6:D10,B17,E6:E10)

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.

Using Formulas to Track Income and Expenses in Excel

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

Using Formulas to Track Income and Expenses in Excel

  • 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.
=SUM(C17:C21)

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.
=C16-C22

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

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

Using Formulas to Track Income and Expenses in Excel

  • 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.
=C22/C16*100%

Using Formulas to Track Income and Expenses in Excel

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.

Using Formulas to Track Income and Expenses in Excel

Read More: How to Make an Expense Spreadsheet in Excel (2 Suitable Ways)


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.

How to Track Income and Expenses in Excel by Using Pivot Table 

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.

How to Track Income and Expenses in Excel by Using Pivot Table

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

How to Track Income and Expenses in Excel by Using Pivot Table

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.

How to Track Income and Expenses in Excel by Using Pivot Table

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.
=GETPIVOTDATA("Sum of Income",$B$12)-GETPIVOTDATA("Sum of Expense",$B$12)

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.

How to Track Income and Expenses in Excel by Using Pivot Table

  • 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.
=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.

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

How to Track Income and Expenses in Excel by Using Pivot Table

Read More: Daily Income and Expense Sheet in Excel (Create with Detailed Steps)


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.
=SUM(C6:C8)

Using Graph  to Track Income and Expenses in Excel

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.
=SUM(E6:E10)

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.

Using Graph  to Track Income and Expenses in Excel

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.
=H5-H6

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

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

Using Graph  to Track Income and Expenses in Excel

  • 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.
=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.

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

Using Graph  to Track Income and Expenses in Excel

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.

Using Graph  to Track Income and Expenses in Excel

  • 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.

Using Graph  to Track Income and Expenses in Excel

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.

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

  • After that, select the Personal Monthly Budget template.

  • Next, select Create.

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

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.

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

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.

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

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.
=C7-H5

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.
=H5/C7*100%

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

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.

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

Read More: How to Make Monthly Expenses Sheet in Excel (With Easy Steps)


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.

How to Track Income and Expenses in Excel


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

Mashhura

Mashhura

Hey! Welcome to my profile. Right now, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo