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.

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

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.

`=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**.

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

`=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**.

- 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%`

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.

`=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**.

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

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

`=SUM(C6:C8)`

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

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

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

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.

`=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%`

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**