How to Create Budget and Expense Tracker in Excel

Looking for ways to create a budget and expense tracker in Excel? Then, this is the right place for you. We can keep track of the budget, actual expenses, and their differences in Excel by going through some simple steps. Here, you will find different ways to create a budget and expense tracker in Excel.

2 Easy Ways to Create Budget and Expense Tracker in Excel

Here, we will show you how you can create a budget and expense tracker in Excel manually and by using Microsoft Excel Templates.

1. Create Excel Budget and Expense Tracker Manually

Suppose, We have a dataset containing the Date, Category, and Expense information of a travel agency in a sheet named Expense Data. Now, we will show you how to create a budget and expense tracker in Excel using this dataset.

Step 1: Insert Headers

In the first step, you will have to insert headers to create a budget and expense tracker and then format them in Excel.

To do that, follow the steps given below.

To begin with, type Category, Budget, Actual Expense, and Difference to create headers in a new worksheet.

Then, select cell range B4:E4.
Next, go to the Home tab >> set 14 as Font Size >> click on Bold.

After that, click on Fill Color.
Now, select any color of your own choice. Here, we will select Blue, Accent 1, and Darker 50%.

Finally, click on Font Color and select any color of your choice. Here, we will select White, Background 1.

Step 2: Create a Table in Excel

Next, we will create a budget and expense tracker in Excel. Go through the steps given below to do it on your own.

Firstly, select cell range B4:E5.
Secondly, go to the Home tab >> click on Borders.

Then, select All Borders.

After that, press the keyboard shortcut Ctrl + T to create a table.

Now, the Create Table box will open.
Here, you will see that the cell range has already been selected.
Afterward, turn on My table has header option.
Finally, click on OK.

Thus, a table will be created.

Similarly, create a table using the dataset in the Expense Data worksheet.

Step 3: Enter Categories for Budgeting and Expense Tracking

Now, we will enter Category names for budgeting and expense tracking in Excel from the Expense Data worksheet.

Here are the steps.

In the beginning, select Cell C4 from the Expense Data worksheet.
Then, press the keyboard shortcut Ctrl + C.

Next, select Cell F4 and press Ctrl + V.

Again, select Cell C4.
After that, go to the Data tab >> click on Sort & Filter >> select Advanced.

Now, the Advanced Filter box will open.
Then, turn on the Copy to another location option.
Next, you will see that the range has already been selected.
Further, insert Cell F4 in the Copy to box.
After that, turn on Unique records only.
Finally, press OK.

Here, you will see that only the unique fields have been copied in the cell range F5:F9.
Next, press Ctrl + C.

Then, go to the Manually Creating worksheet and select Cell B5.
After that, press Ctrl + V.

Thus, you can enter Category names for budgeting and expense tracking in Excel from another worksheet.

Step 4: Set Budget in Excel

In the fourth step, we will set budget values for these fields in Excel.

Firstly, select cell range C5:E9.
Then, go to the Home tab >> click on Number >> click on Number Form.

Next, select Currency.

Finally, insert all the values of the Budget for each Category manually.

Step 5: Calculate Actual Expenses to Create Expense Tracker

Next, we will show you how to calculate Actual Expenses to create an expense tracker using the SUMIF function.

To do that, follow the steps given below.

Firstly, select Cell D5 and insert the following formula.

=SUMIF(Table3[Category],[@Category],Table3[Expense])

Secondly, press Enter to get all the values of Actual Expense.

Here, in the SUMIF function, we inserted the values of Category from the table of Expense Data worksheet as range, values of Category from the table of Manually Creating worksheet as criteria, and values of Expense from the table of Expense Data worksheet as sum_range.

Step 6: Compare Actual Expense and Budget Value

In the last step, you will find how to compare the values of the Budget and Actual Expenses in Excel.

In the beginning, select Cell E5 and insert the following formula.

=[@Budget]-[@[Actual Expense]]

Finally, press Enter to get all the values of Differences between the Budget and Actual Expenses.

Here, in the formula, we subtracted the Budget from the Actual Expense to get the value of the Difference.

2. Use Microsoft Excel Templates to Get Budget and Expense Tracker

You can also use Microsoft Excel Templates to create a budget and expense tracker in Excel. To do that, follow the steps given below.

Steps:

Firstly, click on File.

Then, go to the New options.
After that, type Budget and Expense in the search box.

Now, you will find some templates for Budget and Expense, and can use any of them.
Here, we will use the Expense Budget template. So, we will click on that template.

Further, click on Create.

Then, you will see that a new workbook has opened like the following shown below.

Now, if you want you can change the headings of this template. To do that, click on the heading in cell range B1:F1.

After that, type the new heading you want to give. Here, we will type Budget and Expense Tracker.

Similarly, you can change any other heading in the template.

Additionally, you can also change the values in the existing dataset by just typing in the cells. It will automatically update the value in the drop-down list.

Then, insert your Budget values for each field manually.

Now, you will see that the Total has been calculated automatically using the following formula.

=IFERROR(SUM([Budget]), "")

Here, in the SUM function, it added all the values of Budget in the table. Then, in the IFERROR function, it used the resultant value of this function as value and Blank as value_if_error.

After that, insert the values of Actual Expenses in the table.
Finally, you will see that the columns containing the values of Differences in $ and percentage (%) have automatically been updated.

Download Practice Workbook

You can download the workbook to practice yourself.

Creating Budget and Expense Tracker.xlsx

This is the modified Excel Template for Creating the Budget and Expense Tracker.

Expense Budget.xlsx

Conclusion

So, in this article, we have shown you ways to create a budget and expense tracker in Excel. I hope you found this article interesting and helpful. If something seems difficult to understand, please leave a comment. Please let us know if there are any more alternatives that we may have missed.

Thank you!