Expense Tracker

Click to See Full Template

Version
5'414 Downloads
490 KB File Size
January 3, 2022 Updated
0 Number of comments
★★★★★
★★★★★
Rating

Download this template for free
Get support for this template

table of content

Budgeting
Quick Overview
Template Contents
How To Use the Expense Tracker Template
Summary

Do you find yourself wondering where all the money goes? In the world today, spending money is as easy as opening your browser (yes, the one on your phone… that’s always with you). Run a search and the first few hits are paid ads, and making a purchase is probably two touches away. After all, your credit card is probably already saved to the phone’s memory. You never even have to leave your seat.

Then there are targeted phone apps. With them, you browse an online store that uses big-data and state-of-the-art algorithms to focus what you see on what you’re most likely to buy. An with these apps, making a purchase is one click away.

Now, don’t get me wrong. I appreciate the no-waiting, gas-saving convenience. My point is only that it’s too easy. If we’re not careful, we can lose track of “where all the money goes.”

This free, downloadable Expense Tracker spreadsheet can help you track your expenses and, if needed, give yourself a personal finance makeover. It also includes a budget worksheet.

Budgeting

Most financial experts list “budgeting” as a critical element for financial stability. Michelle Schroeder-Gardner, the owner of the award-winning financial advice blog Making Sense of Cents agrees. “Budgets are extremely important, and I believe that nearly everyone should have one. Rich, poor, middle-class, no matter where you are financially, a budget can most likely improve your financial situation.”

The beauty of this Expense Tracker spreadsheet is that it also includes a simple “Budget Snapshot” worksheet. Simply defined, a budget is an estimate of revenues and expenses over a period of time, coupled with a plan on how surpluses are to be invested or spent. A good budget plan will always include an allowance for building up your savings and long-term investments.

In a previous article (Daily Expense Sheet), I covered another expense tracking spreadsheet. That spreadsheet is a great first step to managing your spending habits because it allows you to track your expenses plus gives you some great tools to analyze them. Once you’ve evaluated your spending, determined all of your recurring expenses, and created a base-line (your minimum monthly expense projections) then you’re ready to create a monthly budget. Adding budget information is one of the first steps in this Expense Tracker spreadsheet, and I’ll cover it in the Creating the Budget section of this article.

(Note: You can also start with this spreadsheet to track your spending and leave the budget information blank until you get an idea of your expenses. At that point, you can then start to utilize the budget worksheet. In the rest of the article, I’ll assume that you’ve collected some transaction data and have a basic idea of your monthly expenses so that we can start setting up the budget information.)

Quick Overview

Let’s take a quick look at the main worksheet to see if this template will meet your needs. I’ve populated the spreadsheet with some sample data so you can get a better idea of how this template works (see the Overview image). We’ll also use this data throughout the article to discuss specific features. I’ll show you how to enter the expenses in the Adding Data section but, for now, let’s cover the bird’s eye view.

Overview

The explanations below follow the numbering on the Overview image:

Budget Header. This header displays the total budget amount (“BUDGET”) that you set on the budget worksheet (discussed later). It also shows the total amount deducted against the budget (“INCURRED”) and the total amount remaining in the budget (“REMAINING”). In simple math terms, BUDGET = INCURRED + REMAINING.

Incurred Expenses. This highlighted section represents the expenses “incurred” against the budget. The factor that makes these count against the budget is an entry in the “Deduct Against Budget” column. (Notice that all the expenses included in this highlighted section have an entry in that column.)

Remaining Budget Items. I entered this “expense” to purposely match the “REMAINING” budget amount in the budget header. In our sample data, entering $143.70 brings the total expenses to $2,895.00 (4) and matches the “BUDGET” amount in the header. (Notice that this expense does not count against the budget because there is no “Deduct Against Budget” category selected.)

Total Expenses. The sum of all recorded expenses – whether deducted against the budget or not.

Budget Shortcut. Click on this shortcut to take you to the “Budget Snapshot” worksheet.

(Note: There is a “no currency” version of this spreadsheet included in the download. The no currency spreadsheet can be used without money formatting – or see the Changing the Currency section on how to format the cells with the money format of your country.)

Template Contents

This spreadsheet contains three worksheets: Expense Tracker, Budget Snapshot, and Drop Down List.

Expense Tracker (A)

This is the primary worksheet in this spreadsheet and is where you record all your personal expenses. (See the Overview image for a preview of this worksheet.)

Budget Snapshot (B)

We use the “Budget Snapshot” worksheet to control the amounts we assign to our budget categories. As I mentioned earlier, this is the second step to effectively managing your money. The first step is to record previous expenses, create an expense report, and evaluate your spending habits. Once you’ve determined which recurring expenses are required and which are optional, you can then use that evaluation to build your first budget goals. This is where we’ll start on this spreadsheet.

Budget Snapshot

Drop Down List (C)

The last worksheet in this spreadsheet is called “Drop Down List” (see the Drop Down List image). We can use this spreadsheet to add, remove, or change the budget categories. The categories included in this spreadsheet are a great starting point. Because you can build your budget and track your expenses with these default categories, I’d like to stick with the most important topics first. We’ll address the option to change this worksheet in the Changing the Categories section towards the end of this article.

Drop Down List

How To Use the Expense Tracker Template

Now that you’ve seen the basics of this template, let’s start working with it and take a deeper look at the details.

Creating the Budget

Open the “Budget Snapshot” worksheet. The only cells we need to access this worksheet are those in the “Budget” column (C15:C20). The rest are auto-calculated as we enter expense information.

But, before we enter any information in this worksheet, we must decide on a time span that we want to represent. Personal budgets are most often created for a month at a time, so we’ll consider this a monthly expense tracker and look at our budget in terms of a single month. The sample data covers basic spending patterns for August.

Based on the evaluation of your spending habits, you would enter items in cells C15 to C20 that represent your monthly expenses. If you’d like to follow along with my sample, refer to the Budget Snapshot image presented earlier (the total of the “Budget” column should be $2,895.00). (Note: as you populate the “Budget” column, the “Incurred” and “Remaining” column cells will still be zero until the next step of adding the sample expenses and assigning them a budget category.)

This next feature I’d like to discuss also affects the budget headers in the “Expense Tracker” worksheet. So, if you change it, it’s important to change it back. I also want to point it out in case you accidentally change it – then you’ll know how to change it back. Referring to the Budget Snapshot: Sum Function image, if you select any cell in the “Total” row, a drop-down list arrow will appear (#1). By default, the “Sum” option will be selected (#2).

Budget Snapshot: Sum Function

If you change this (accidentally or on purpose), Excel will apply a different “function” to the data in the column. For example, if you select “Min” the cell will display the lowest, “minimum” value in that column – not the “Sum.” The sum, or total, makes the most sense in this application so, if you notice that the numbers are off on your “Expense Tracker” worksheet, then pop back into the “Budget Snapshot” worksheet and verify that all the “Total” cells are displaying the “Sum.”

Adding Data

Now, let’s move to the “Expense Tracker” worksheet and start entering some expenses. Select the first cell in the “Date of Payment” column (B12) and type in “08/01/9019” – you’ll notice that it changes to the default format of “01-Aug-19”. Tab over to the first cell in the “Expense” column (C12). The cells in this column have a drop-down list that you need to use. Typing anything outside the list will yield an error. This is also true for the “Payment Type” and “Deduct Against Budget” columns (E and F). See the Categories image for a view of these three drop-down lists.

Using the Spreadsheet

Now, with all the sample data entered, let’s look at some practical applications of this Expense Tracker spreadsheet.

First, look at the Budget Header on the “Expense Tracker” worksheet. This header is only affected by expense items that also have a “Deduct Against Budget” category selected. You can test this by deleting the “Expense Amount” in the last row ($143.70). After deleting the amount, the Budget Header stays the same. The only affected total is the sum of expenses in cell G68. (Hit “undo” to revert back to the $2,895.00 total.)

So, unless you don’t want the expense to count towards your budget, it’s important to assign a budget category in the “Deduct Against Budget” column. It should be a rare case, but an example where you might not want a budget category is if you purchase something for your friend. You know he will reimburse you so it shouldn’t come out of your budget. But you are also using this expense tracker to account for all your money so you want a record of this purchase.

In our sample, we want to categorize that last item. The dollar amount ($143.70) is the surplus amount in our budget for this month (see the “REMAINDER” field of the Budget Header). So, instead of spending it, let’s be a good money manager and add it to savings. Well, we don’t have a savings category so highlight cell F27 and choose “Miscellaneous” (we’ll add a “Savings” category in the next section). Once you assign this expense to a budget category, notice in the Budget Header that the “INCURRED” amount now equals the “BUDGET” amount and the “REMAINDER” equals zero. This brings us back to our original math formula: BUDGET = INCURRED + REMAINING ($2,895 = $2,895 + $0). With zero remaining, we have a balanced budget.

A second practical application of this spreadsheet can be demonstrated by looking at the “Budget Snapshot” worksheet (the Budget Snapshot: Balanced image includes an update of the changes we just made).

Budget Snapshot: Balanced

As we take a closer look at the budget, we see that we have some overages and underages. We spent too much in “Food & Drinks” ($5.24) and “Miscellaneous” ($123.26) – hence the red formatting. So, why does our budget balance? Well, we also spent less in a couple of categories: “Transportation” ($123.50) and “Utilities” ($5.00). Those negatives and positives offset themselves giving us a balanced budget. Meaning, that our expenses match our projected budget expenses (the “Remaining” is zero).

From the snapshot, we can see that we might need to adjust the “Transportation” amount and assign that money to another category. But, it’s not obvious how we were so far over our “Miscellaneous” budget of $550 ($123.26 over). That is until we take a look at the details of our “Expense Tracker” worksheet. Once we review the expenses, we see that we spent a lot in savings. But, the last savings entry was added only because we had a surplus. That’s not obvious in our “Budget Snapshot” so let’s fix it...by adding a category called “Savings.”

Changing the Categories

Changing the categories is a two-step process: 1) Modify the “Expense Types” and “Budget Lists” tables in the “Drop Down List” worksheet; and 2) Edit the “Budget Snapshot” worksheet to reflect the new table changes.

1) Drop Down List. The obvious change we need to make is to add (change) items in the “Drop Down List” worksheet. Let’s go right to our scenario and sample data. Open the “Drop Down List” worksheet and click on the cell below “Miscellaneous” in the “Expense Types” table (B12). Now, simply type “Savings” to add the new expense category. Do the same in cell F12 so we can keep our budget categories consistent with the expense categories. See the New Categories image to view the changes.

New Categories

(Note: You can add a “Payment Type” using the same process without moving on to step #2; The next step is only required when you change the “Expense Type” and “Budget Lists” tables.)

2) Budget Snapshot. This step is a little easier to forget – but it’s important if we want to use our new drop-down items in our budget. Move to the “Budget Snapshot” worksheet and highlight the entire 21st row (click on the number “21” to the left). Now, right-click on the row to bring up the shortcut menu and choose “insert.” There should be a new, blank row above the “Total” row. Click on cell C21 (the newly created “Budget Category” cell) and select “Savings” from the drop-down list (if it’s not an option, then revisit step #1 to make sure you created it correctly).

Let’s revisit the reason we created this new “Savings” category – which is to clarify the “Miscellaneous” expenses and move our savings expenses to this new category. So, while still in the “Budget Snapshot” worksheet, select cell D21 and type in $250. Taken from our “Expense Tracker” worksheet, this is the planned savings deposit that we want to make each month (see the Monthly Planned Savings Deposit image).

Monthly Planned Savings Deposit

Now, we need to deduct that amount from our “Miscellaneous” category since that’s how we categorized that expense earlier. Overwrite the $550 entry in cell D20 with $300 ($550 - $250 = $300) – this is our new “Miscellaneous” budget amount. After those two changes, verify that your total budget is still $2,895.00. Before we take a close look at our new “Budget Snapshot”, we need to categorize the savings expenses.

Open the “Expense Tracker” worksheet. Change the “Expense” and “Deduct Against Budget” categories for the two savings expenses – cells C19, F19, C27, and F27 (see the Applying the Savings Category image).

Applying the Savings Category

Let’s see how this affected our budget. Open the “Budget Snapshot” worksheet and you can now see that we actually did well this month (see the Final Budget Snapshot image).

Final Budget Snapshot

With the new “Savings” category, this snapshot shows us that we did pretty good. We only overspent in the “Food & Drink” category by $5.24. The red-highlighted “overspending” in Savings was actually the monthly surplus that we decided to save – that’s a good thing.

Now, applying what we discovered we should change our budget. If we think this month was a good representation of our “Transportation” expense (or, “Gas” according to the details of our “Expense Tracker”), then we should change “Transportation” from $250 to $150 and move that extra $100 to “Savings.”

Changing the Currency

If you deal with any currency other than U.S. Dollar, use the “Expense Tracker No Currency” spreadsheet. This spreadsheet has no currency formatted by default and you can either use it as is or format the financial cells. Highlight any cells that you want to add specific money format and then select the “Home” tab in the menu toolbar. Choose the “Currency” drop-down menu and select “Euro” to match my sample (see the Currency image for the results). You can change to any of the other currencies directly from that drop-down menu or choose the “More Accounting Formats…” for more currency options.

Currency

Summary

Hopefully, this article gave you some ideas on how this Expense Tracker spreadsheet can help you manage your money. Keep in mind that it’s best to enter your daily expenses as they happen. Don’t get behind or it’s hard to catch up. Also, save your receipts (at least for a couple of months) so you can refer back to them if you need to check an expense.

Also, we kept our example to personal finances, but this spreadsheet can also be a business expense tracker. Easily modify the worksheets to match your business expense categories and your good to go.

(Note: This article references the features in Excel from Microsoft Office 365.)