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.
Download Practice Workbook
You can download the workbook to practice yourself.
This is the modified Excel Template for Creating the Budget and Expense Tracker.
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, Darker 50%.
- Finally, click on Font Color and select any color of your choice. Here, we will select White, Background 1.
Step 2: Create 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 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.
- Secondly, press Enter to get all the values of Actual Expense.
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.
- Finally, press Enter to get all the values of Differences between the Budget and Actual Expenses.
- How to Prepare Annual Budget for a Company in Excel
- Create a Business Budget in Excel (With Easy Steps)
- How to Do Budgeting and Forecasting in Excel (2 Suitable Ways)
- Create Renovation Budget in Excel (2 Simple Methods)
- Create Retirement Budget Worksheet in Excel (with Easy Steps)
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.
- 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.
- 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.
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. And, visit ExcelDemy for many more articles like this. Thank you!
- How to Make a Family Budget in Excel (2 Effective Ways)
- Make a Household Budget in Excel (2 Smart Ways)
- How to Make a College Budget in Excel
- Make a Budget in Excel for College Students (with Quick Steps)
- How to Make a Wedding Budget in Excel (2 Suitable Methods)
- How to Create an Operating Budget in Excel (with Detailed Steps)