11 Steps to Create a Monthly Budget Tracker Using Microsoft Excel

Microsoft Excel is perhaps one of the easiest tools to use to keep up with basic expenses, so long as you’re willing to maintain a steady stream of inputs. Even more importantly, you can get a lot done without much knowledge or experience in Excel. With a basic template or outline made, you can start tinkering to get just the information you need.

Steps to Create a Monthly Budget Tracker in Excel Manually

Step 1: Create a Blank Workbook and Make a Sheet for Income and Expense Sources

If you’re working from scratch, it’s useful to have a list of possible income and expense streams you expect. You can keep the list as general as possible, but you can also use more narrow categories as well.

Here’s a basic overview of some categories you can use. We placed this table in the first sheet “Categories.”

Step 2: Create a Blank Sheet for the First Month

Choose a new sheet in the workbook and rename it to the month and year you want to track.

Step 3: List the Categories

Create two smaller tables to list the income and expenses. This time, the categories will go below one another.

From here on, you have a couple of options. You can either tally your budget for each month and just put the result in, or you can have a slightly more complicated table, with each day or week in the month accounted for.

Step 4: Divide Expenses in Days or Weeks

We’ll use a mixed approach. For example, it’ll be easier to track your spending if you go by week, but some mandatory expenses are only done once a month. Therefore, we’ll expand the “Food” categories to cover specific weeks.

Step 5: Insert Formulas for Weekly or Daily Totals

For every category where you have weekly or daily spending, you need to create a “Total” cell that sums the values of the row. The basic formula is “=SUM(Start_Cell:End_Cell)”

You can apply the formula in the following few steps:

1. Select a cell where you need the total.
2. Insert the equals sign.
3. Enter “SUM” and press “Tab.”
4. Drag your mouse over the cells which need to be summed.
5. Press “Enter.”

You’ll need to repeat the process for every category you have, but you can also click on the Fill Handle of the cell (the bottom-right corner) and drag down from one category below to automatically apply the formula for the next row.

Step 6: Add Subtotals for Every Category

To keep things simple, you can add another row to each subcategory to list its subtotal, which will be used for the final tally.

To insert a row, right-click on the row header below the row you want to add, then select “Insert.” For example, to make a subtotal for the “Salary” category, right-click on row 7 to insert a new one there.

Here’s a small example of how the table should look like.

Step 7: Calculate the Subtotals

Similarly to how you made the subtotals for the weekly tally for food, use the SUM formula to calculate the subtotal for each category.

Step 8: Calculate the Monthly Totals

Make a smaller table that lists all the categories and their subtotals. Use a direct reference to each “Subtotal” category. For example, for the subtotal for the Income, we referenced the cell C7 as “=C7.” You can do this by selecting a cell, entering the equals sign, clicking on the cell you want to reference, then pressing “Enter.”

Note that we applied a merge action for the name of the category to make it more visible. You can get the “Merge” option in the “Alignment” group in the “Home” tab.

Step 9: Calculate the Monthly Total

Use the last sum formula to calculate the total for the month. Note that this time, you’ll need to subtract the sum of all expenses from the income subtotal. To do this:

1. Select the “Monthly Total” cell (in this case, H7).
2. Enter the equals sign.
3. Click on the Income subtotal.
4. Enter the minus sign.
5. Enter “SUM,” then press “Tab,”
6. Select all the other cells in the smaller table.
7. Insert a closed parenthesis “)” and hit “Enter.”

You can see the resulting formula bar of cell H7.

Step 10: Copy the Monthly Sheet for Each Month

To copy a sheet, right-click on its name in the bottom bar and select “Move or Copy.”

In the dialog window that opens, check “Create a copy” and click on “OK.”

Rename the new sheet (Right-click and select “Rename”), then repeat the process until you have as many sheets as you need.

Step 11: Start Entering Data

Your tracker is done and you can enter data as you wish.

The above is the detailed content of 11 Steps to Create a Monthly Budget Tracker Using Microsoft Excel. For more information, please follow other related articles on the PHP Chinese website!