In today’s post, I want to go over how to make an Excel expense tracker. This is a simple and effective way to track your everyday expenses throughout the month and keep track of which expense category they belong in. 

Having a place to enter in amounts spent throughout the day will let you see the impact of your spending and where all of your money is going. More information is always better, and as you’ll see later on, this can be used in conjunction with our free Excel budget template.

An important note that I want to cover before beginning. For some of the functionality in this expense tracker to work, you will need the full version of Excel, not the online version. I will go over an expense tracker that will work with the online version in another post if there is interest. 

With that out of the way, let’s get started!

Table of Contents

1. Create a new workbook
2. Set up categories and expense descriptions
3. Create the Excel expense tracker
4. Add a filter
5. Format your Date and Amount columns
6. Total your expenses
7. Make it look nice

Create a new workbook

The first thing you are going to need to do is open up Excel. For this tutorial, I will be using the desktop version of Excel in order to name a list of cells which is needed to create some of the dropdown menus that will be included. 

Once Excel is open you will want to click on “Blank workbook.”

Set up categories and expense descriptions

Create expense categories

The first thing you are going to want to do is create a series of lists that represent all of your expense categories and descriptions. 

Your expense categories are going to be fixed expenses, variable expenses, discretionary expenses, contributions to funds, and contributions to savings. You can add more if you like. The thing to remember is that this is your expense tracker to customize and make your own.

To start, we want to add the expense category names. To make sure there is plenty of room, I’m going to start in column I. 

Click in cell I1 and type “Fixed” and hit the tab key to move to the next cell to the right, cell J1. Now type “Variable” and hit the tab key again. Type “Discretionary” in cell K1 and hit the tab key again. Type “Funds” in cell L1 and hit the tab key again. Type “Savings” in cell M1.

Your titles should look like this:

Adjust your column spacing and title font weight as you would like to help create space and heirachy for your template. I will leave the design up to you.

Below is the column width that I decided on for this tutorial but feel free to adjust for your own preference.

Add expense descriptions

Now you want to add the descriptions of your expenses. For example, for fixed expenses you would add things like rent, car payments, your internet bill, and any other expenses that are more or less the same each month. For this example, I’m going to use “Fixed Expense 1,” “Fixed Expense 2,” and so on. 

Click in cell I2 and type your first expense description. Press the return or enter key and type the next expense description in cell I3. Continue until all of your fixed expenses are listed.

Do the same in each of the other columns under the category names that you created.

These descriptions are going to be used in our expense tracker that we will be building next. 

Side Note:

Name each list of expenses

The reason we are creating this list of expense categories and descriptions is to use it in our expense tracker that we will be building. Our expense tracker will pull information from these lists. If we had a budget on another sheet, for example, instead of creating and naming an expense list to the side, we could name an expense list that we have in our budget. 

Don’t worry, it will make more sense when we get there.  

Select cells I2 through I11. In the name box above column A and row 1, it should say “I2.” We want to change this to “Fixed.”

In the name box, type “Fixed” and press return or enter. 

You will want to do this for each list of descriptions, naming them according to the label/title that you gave them. 

For the next one, select cells J2 through J11 and in the same name box from the last step, type “Variable” and press return or enter. 

Perform this step for the remaining expense descriptions. 

Create the Excel expense tracker

Now that we’ve set up the expense categories and descriptions, we need to create the actual tracker. To do this, we first need to label our columns. 

Click in cell A1 and type “Category” and press the tab key. In cell B1 type “Date” and press the tab key. In cell C1 type “Description” and press the tab key. In cell D1 type “Amount” and press the tab key.

Your worksheet should look like this when you’re done:

Set up your category column

This is where the fun happens. We want to set up the category column with all of the categories that we set up previously in columns I through M. The way we will do this is to select cells A2 through A41 and then go to the Data tab above.

In the Data ribbon, click on the Data Validation drop down and select Data Validation. The button for the has a green check mark and a red circle with a line through it. 

When you select this, a window will open with validation criteria. In the drop down below “Allow:” we want to select “List.”

Once you select this, a box will appear under the title “List:” where we will enter the names of the lists that we created previously.

In this box, we want to type “Fixed, Variable, Discretionary, Funds, Savings” and then press “OK.”

Now, if you click in any of the cells C2 through C41, you will notice a dropdown arrow to the right of the box from which you will be able to select one of the expense categories that we created.

Set up your description column

Now we want to set it up so that our Description column will have a dropdown showing the descriptions of whatever expense category was selected in the Category column. 

Select cell C2 and open up the Data Validation window again by selecting Data Validation in the drop down menu from before. 

We will select List from the “Allow:” drop down and in the “Source:” box we will type the following formula and then press “OK”:

=INDIRECT(A2)

When entering in your =INDIRECT(A2) formula, if there is currently no value showing in column A (your Category column), an error message will pop up warning you that the formula that you just entered into the Source box will evaluate into an error. All this means is that there is nothing in column A at the moment. Hit the Yes button. When there is a value in column A, the formula will work as intended.

Important Note:

In order for the Description column to work correctly and show expense descriptions, there must first be a value chosen in the Category column. For example, in order for descriptions from the Variable expense list that we created earlier to show up in a chosen Description category cell, we must first select the Variable expense category in the Category column. See below:

Next we want to copy and paste this to cells C3 through C41. Do this by selecting cell C2 and pressing command C for Mac or control C for Windows, then select cells C3 through C41 and press command V for Mac or control V for Windows. 

Add a filter

Adding a filter will make manipulating our data easier and will allow us to see only certain categories of expenses, only certain expense descriptions, only certain dates, or only certain amounts. 

To add a filter, select cells A1 through D1. On the Data ribbon above there will be a button that looks like a funnel that says “Filter” below it. Click on this and you will see that you now have a dropdown arrow on the right side of each of the selected cells. 

Format your Date and Amount columns

We want our Date and Amount columns to correctly display the information that we enter. For our Date column, select cells B2 through B41. We want to return to the Home ribbon by clicking on the Home tab at the top of our sheet. 

Then click on the format drop down menu where it says “General” and select either “Long Date” or “Short Date.” I chose “Short Date.”

Next, we want to select cells D2 through D41 and click on the format drop down menu again. This time, select either “Currency” or “Accounting.” I chose “Accounting.”

Total your expenses

Now that our expense tracker is basically complete, we want to be able to total our expenses by category and overall. 

We are going to set up two total sections. One will allow us to see the entire total of our expenses and the other will show only what has been filtered. 

Start off by selecting cell A43 and type “Filtered Total” and press return or enter. 

In cell A44 type “Overall Total” and press return or enter. 

Now we want to add our formulas. In cell B43, type “=SUBTOTAL(109,D2:D41)” and press return or enter. 

This formula is going to only add up the visible cells in this cell range. What this means is that when you filter data, only the amounts that you are filtering will be counted. 

IMAGE

In cell B44, type “=SUM(D2:D41)” and press return or enter. 

Last, we want to format the Total cells. Select cells B43 and B44 and format as either “Currency” or “Accounting” and press return or enter. 

Make it look nice

Now that you’ve made an expense tracker in Excel (good job!), it’s time to make it look nice. How you decide to do this is up to you. I like to play with fonts, font sizes, and make titles/labels bold in the Home ribbon at the top. This will increase readability and make it yours. 

Before that, however, this sheet should be more obvious. We want to know that it is an expense tracker when we open it up.

Start by making the titles bold.

Let’s insert a few rows above all of our tables and data. Right click on the “1” on the far left side of the sheet for Row 1 and select “Insert.”

Do the above step three times.

Select cells A1 through D1 and click on “Merge & Center” above in the Home ribbon. 

Click in the newly merged cells and type “Expense Tracker” and press return or enter.

From here you can do the same thing on the next row down so you can add the month or period that this tracker is for.

You can also go through and choose your favorite font and size for titles/labels and data. It’s all up to you. 

You can even move your data table to another sheet so that your expense tracker is clean and free from excess information. 

You’re done!

I really hope you enjoyed this tutorial and find this expense tutorial useful in your budgeting and financial tracking. If you have any additional tips and tricks, I would love to hear them in the comments or you can email me. Happy budgeting!

DISCLOSURE: THIS POST MAY CONTAIN AFFILIATE LINKS AND/OR PAYED PLACEMENT. PLEASE READ MY DISCLOSURE FOR MORE INFO.