Daily Expense Sheet

• Version
• 20'564 Downloads
• 1.1 MB File Size
• January 3, 2022 Updated
• 1 Number of comments
• ★★★★★

  ★★★★★
  Rating

According to a yearly Planning and Progress Study by Northwestern Mutual, in 2018 “the average personal debt (exclusive of home mortgages and among those with some debt) climbed higher this year, exceeding $38,000...” The study continues with other trends suggesting “that Americans are digging further into – rather than out of – debt….” One of those trends is that “[t]wo in 10 people allocate a staggering 50%-100% of their income towards debt repayment.”

Everyone responds to personal debt differently. Some of us are not concerned and have enough monthly income or savings to cover the debt anytime. Others avoid it at all costs – knowing it could cause a multitude of cascading financial issues in a fragile balance of income and expenses. But, either way, personal financial experts seem to agree on the importance of budgeting – developing a plan that balances your income and expenses. In fact, Financial guru Dave Ramsey states that “budgeting is essential to your financial success.” He uses the term zero-based budgeting to explain “a way of budgeting where your income minus your expenses equals zero.”

Ramsey isn’t encouraging us to spend all our income on consumables but, rather, some of the “expenses” might be savings or investments. The concept is that you account for all of your money and plan for the future.

That brings us to the importance of this free, downloadable Daily Expense Sheet template. Just as creating a budget is important in controlling your personal finances, tracking your expenses is critical in creating a budget. This daily expenses sheet will help you record your expenses and determine which of those expenses are required and which ones you can reduce – or eliminate.

If you’re already in a great place with your personal finances, it’s probably due to purposeful planning and attention to your spending habits. This template has some great tools to drill down deeper on those expenses and help you better understand your spending patterns.

(Note: There are two spreadsheets included in this download. The “Daily Expense Sheet Personal” includes U.S. Dollar formatting for the financial cells. The “Daily Expense Sheet Personal_No Currency” spreadsheet formats the financial cells as standard numbers – no currency. The two worksheets calculate the numeric totals the same. I refer to the “Daily Expense Sheet Personal” throughout this article but the instructions apply to both. See the Changing the Currency section to learn how to format the financial cells for different currency.)

Budgeting

Just to be clear, this template is a “daily expenses spreadsheet” – not a “budget template.” But, since budgeting is an essential part of financial planning and achieving financial goals, I want to provide a budgeting resource for you. Once you’ve tracked your expenses and evaluated them with the expense reports available in this template, there is a free online budget planner provided by EveryDollar. You can use this tool as a monthly budget worksheet. My wife and I use it in our personal budget. After understanding our monthly expenses, this EveryDollar budget tool helped us eliminate many household expenses and, eventually, get out of debt. A great plus for this budget tool is that the company also provides it as a mobile app.

You can also download free household budget templates right here on The Spreadsheet Page.

Quick Overview

Let’s start with an overview to help you quickly evaluate if you need to download this file. In brief, this expenses template allows you to track and categorize your daily spending. Once recorded, you can use the “Dashboard” page to customize how you view, sort, and display your expenses. This customized view of your expense report – including graphs – can help you understand how you’re spending your money. This spreadsheet also has drop-down menus to help you categorize your expenses. You can also change the expense categories so the daily expense sheet meets your personal spending habits.

Template Contents

There are three worksheets to this expenses spreadsheet: A) Expense Capture Worksheet, B) Dashboard, and C) Categories. (Note: The file downloads without any data. In the following screenshots, you’ll see that I’ve added sample data to help you better understand how the worksheets function.)

Expense Capture (A)

This worksheet is the data entry portion of the spreadsheet. Or in terms related to this topic, it’s the daily expense tracker. After setting up the spreadsheet template (see the Setting up the Spreadsheet section below), this is where you enter all of your spending events (see the Expense Capture image). You enter your daily expenses in this worksheet and then filter the list – based on single or multiple months – using the blue “Slice” buttons.

Expense Capture

Dashboard (B)

The Dashboard worksheet is the main user interface for this template. It’s where you can create a visual representation of your expenses and analyze them in detail. In the Dashboard image, the “Pivot Tables” section surrounded by orange controls the “MAIN CATEGORIES” graph. The yellow surrounded “PIVOT TABLES” section affects the “SUB - CATEGORIES” graph. (Note: the yellow surrounded “slicer” buttons – bottom left of the Dashboard image – also controls the “SUB - CATEGORIES” graph; The “Month” slicer buttons affect both graphs). I’ll explain these controls in Using the Spreadsheet section below.

Dashboard

Categories (C)

This worksheet is the control sheet for the drop-down list menus available in the “Expense Capture Worksheet.” Take a look at the Categories image and you can see that each “Main Category” has a “Sub-Category.” You can modify the category and subcategory options and the changes will also show up in the drop-down lists for each expense.

Categories

How To Use the Daily Expense Sheet

Now we’re ready to start working with the spreadsheet. As I mentioned above, the file will not have any data after you download and open it for the first time. Let’s start adding some sample data so we’re working with the same dataset.

Adding Data

Open up the “Expense Capture” workbook and click in cell B8 (the first cell under the “Date” column). Type in “06/10/19” and you’ll see that the “Month” and “Day” columns auto-populate based on the date you just entered. It’s important not to delete or overwrite any cells in columns C or D – they need to contain a formula in order for them to auto-populate. Taking a short rabbit trail, let’s take a quick look at these cells. Click on cell B:8 and you can see the formula in the formula bar (see the Formula Bar image). It does a “calculation” on the Date cell (B8) to return the “Month” in cell C8 (the formula is D8 is similar except it returns the “Day”).

Formula Bar

I’ll explain how this formula works in the Month Formula Explained section below. But, now that you’ve seen it’s important not to overwrite those cells, let’s move on to adding more data. (Note: If you do happen to overwrite a cell in column C or D, you can “undo” the change if you catch it right away or just copy an unedited cell below or above the one you overwrite; it will rebuild the formula.)

In cell E8, the “Description” column, type “Corner Restaurant.” Now, move to the right one cell (F8) and you should see a drop-down list arrow to the right of the cell – the arrow above the cell is a “Filter” arrow (see the Drop-Down Menus image). Click on the drop-down arrow and select “Food” for F8 and “Breakfast” for G8. Hit “Tab” to enter the “Amount” cell and enter “25.42” – the “$” will automatically appear because the column is formatted as currency.

Drop-Down Menus

You’ve now finished the first event. Add the rest of the sample data as shown in the Sample Data image and then we can move on to changing the categories.

Sample Data

Changing the Categories

The “Categories” worksheet of this template allows us to customize the expense worksheet. A lot of pre-built Excel spreadsheets have finite menu options with a few allowing you to rename the menu item. The designer of this template gave us the ability to not only change the name of existing items but to also add or delete main menu items and subcategory items. Let’s start with the most complicated option and add a main and subcategory menu item. Once you understand this procedure, you’ll then understand the simpler options of changing names and removing items.

The template builder confidently added the most common cost categories for us. But, what if we want to get a little more specific for our spending habits. Even though this is a personal expense tracking spreadsheet, what if we had a hobby, let’s say photography, and we’re occasionally asked to take pictures for our full-time job. Some of the equipment that we purchase might be tax-deductible so we’d like to track it. But, since our purchases are infrequent, it’s not worth setting up a business expense spreadsheet to track those purchases. Let’s just create a new category and subcategories to track it in our personal expense tracking spreadsheet. (Note: If the “business expenses” became more frequent then it would be a good idea to separate the tracking.) So, for now, let’s create a “Photography” category and an “Equipment” and “Software” subcategory.

Open the “Category” worksheet. Click in the last cell of the “Main Category” table (currently, B12) and type “Photography” as the new category. Hit the “Enter” key to accept the entry. It’s that simple. Go into the “Expense Capture” worksheet and let’s create a new expense using the new “Photography” category:

• Date = 06/22/2019
• Description = New filter
• Expense Category = Photography (Note: This option should now be available.)
• Sub Category = <Blank> (Note: We haven’t yet created any subcategories for the Photography category so there are no options available.)
• Amount = $35

Now let’s create some subcategories for “Photography.” Using a three-step process, we’re going to expand the spreadsheet and add the new categories to the right of “Miscellaneous.”

1. Create the new columns: Return to the “Categories” spreadsheet and highlight the K and L columns by clicking on the “K” column header and dragging your mouse to the “L” column header (The “K” column is narrow so you need to be accurate with your mouse pointer); Right-click and choose “Copy,” then right-click on the “M” column header and choose “Paste (P).” Hit “Escape” to clear your selection and you’ve just added the new columns. (Note: The filter button appears in the new subcategory title but that can be cleared by going to “Data” in the menu bar and then toggling off the “Filter” button.)

2. Retitle the new table and add items: Quadruple-click on the new “Miscellaneous” title cell (N6) to highlight the text and type “Photography” to match our new category. Now, just as we added a new “Main Category” item, we can also add new “Subcategory” items in the last cell of the table. Click on cell N8 and type “Equipment.” Then hit “Enter” and type “Software” in cell N9 (Note: The spreadsheet keeps the alternated cell formatting of blue and white so the tables are easy to read.)

3. Rename the new table: This step is critical in order for the subcategory drop-down to work and is different then step two. We need to rename the default “internal” name assigned to the new table (versus the table “title” as in step two). Open the Formula tab in the menu bar and select the “Name Manager” button. This will bring up a new window titled “Name Manager” and display all the defined names in the workbook. (These friendly “names” define areas in the spreadsheet and can be used in formulas; as a developer, it’s easier to remember names versus cell references.) Take a look at the names and you should see two “Miscellaneous” entries – one will have two numbers after it (such as “Miscellaneous10”). That’s our new subcategory reference name that was automatically created when we pasted the new columns. We need to change the name to “Photography” so that the subcategory lookup knows where to find the list. In the “Name Manager” window, highlight the "MiscellaneousXX” name and choose the “Edit...” button at the top. In the “Name” field, type “Photography,” click “OK,” and close the window.

We’re now finished with adding the subcategories and they should be available as a drop-down menu. Let’s test it. Go back to your new entry in the “Expense Capture” spreadsheet and select “Equipment” for the “Sub Category.” The last entry should now look like the Photography Expense image (also check the total on top to make sure you match).

Photography Expense

(Note: I’ve also customized the “Expense Capture” spreadsheet by adding my name at the top right. Click on cell H1 and add your own name to personalize it.)

Using the Spreadsheet

Now that we know how to add expenses and change the categories, let’s start analyzing the data. We can change the view and filter the data in both the “Expense Capture” and the “Dashboard” worksheet.

Expense Capture Worksheet

Although this worksheet is the data entry interface for this spreadsheet, it also has some great tools for filtering the data. Using the Expense Capture: Filtering image, I’ll cover five filter tools that let you view only the data that you need.

Expense Capture: Filtering

Tool #1: Slicers are Excel buttons that provide a quick, visual option for filtering data. This worksheet creates monthly slicers based on the dataset. In our example, we have dates for June and July so the worksheet automatically creates those monthly slicers. If we were to add an August expense, we would also see an August button. Clicking on a button toggles that slicer on or off – either turning the monthly filters on or off.

Tool #2: The “Multi-Select” button allows you to select multiple slicers. With it toggled off, clicking on a slicer disables the other slicers. With it toggled on, you can select multiple slicers and must click on a slicer to toggle it off.

Tool #3: If you want to display all the data, click on the “Clear Filter” button. It will toggle off the “Multi-Select” button but still select all slicers.

Tool #4: The filter drop-down buttons are the standard Excel buttons used to filter data. Compared to the monthly slicers, they allow you to filter based on any entered data. Let me go over an example: We want to see how much we’ve spent on food in July. First, make sure the “Multi-Select” button is toggled off and then toggle on the “July” slicer. You should now see all of July’s monthly expenses. Now, following the Filter Drop-Down Buttons image: 1) click on the filter drop-down button in the “Expense Category (Main)” column, 2) uncheck “Select All” to clear all checkboxes then select “Food,” and you should now only be viewing all expenses in July that were assigned the “Food” category (compare the filtered total indicated by #3 to make sure we’re on the same page).

Filter Drop-Down Buttons

(Note: In the Filter Drop-Down Buttons image, the circled button indicated by #1 is the view after applying the filter; it’s a standard arrow button before applying the filter.)

Now, to set the worksheet back and display all the data, you just need to reverse the above process. In the “Expense Category (Main)” drop-down filter, choose “Select All” and then click on the “Clear Filter” button.

Dashboard Worksheet

There are two key aspects of this worksheet to keep in mind: 1) There is no need to enter any text. Changing the views and analyzing the data can be done by clicking on slices, buttons, or the drop-down menus. 2) Whenever you add expenses or make changes to the “Categories” worksheet, you need to remember to refresh the pivot tables to pull in the new data (go to the “Data” tab of the menu bar and select “Refresh All”). Beyond those two instructions, the best way to learn this worksheet is to experiment on your own. The buttons are similar to what we just covered in using the “Expanse Capture” worksheet above. But, to get you started, let’s set up a scenario.

In our sample dataset, we spent a lot in food: $599.96 in June and $450.27 in July (determined by using the filters in the “Expense Capture” worksheet). But, we went in the right direction in July by dropping our food expenses by almost $150. Let’s dive into this more deeply to see if we just ate less or if it might have been a purposeful decision. Start by opening the “Dashboard” Worksheet and refreshing the data (Data...Refresh All). We want to analyze our food so, first, click on the “Food” slicer. Our three expense subcategories for June and July are “Breakfast,” “Groceries,” and “Snack” for a total of $1,050.23. So, how does that compare per month? Click on the June slicer and you’ll see our “Snack” subcategory disappear – no snacks in June. Click on the July slicer and the “Snack” subcategory appears but “Breakfast” disappears – no eating out for breakfast in July. A closer look at the “Details” of the expense items shows that the “Snacks” were coffee. It looks like we saved $55.89 by skipping breakfast out and only having coffee ($75.03-$19.14=$55.89). That’s a good trend, let’s keep that up. But what about the “Groceries” expense? We dropped our grocery expenses by $93.80 from June to July ($524.93-$431.13=$93.80). Another good trend. But, since we’re not eating breakfast out anymore will we be able to keep spending less on groceries? We probably need another month of expense gathering to see if we can sustain that grocery expense. Also, if we do need more grocery money, then we should consider dropping the coffee “out” expense and shift that money to groceries. I know...home coffee isn’t as good as a coffee shop. But, getting out of debt will make it totally worth it.

Changing the Currency

Now, let’s take a look at changing the currency away from U.S. Dollars. Open the “Daily Expense Sheet Personal_No Currency” spreadsheet and then the “Expense Capture” worksheet. Highlight the entire “H” column then select the “Home” tab in the menu toolbar. Choose the “Currency” drop-down menu and then select your desired currency. I selected the Euro in my sample (see the Currency image). 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

Now, let’s take a look at changing the currency away from U.S. Dollars. Open the “Daily Expense Sheet Personal_No Currency” spreadsheet and then the “Expense Capture” worksheet. Highlight the entire “H” column then select the “Home” tab in the menu toolbar. Choose the “Currency” drop-down menu and then select your desired currency. I selected the Euro in my sample (see the Currency image). 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

Lastly, open the “Dashboard” worksheet and change the currency format for the entire “K” and “N” columns just as we did above. Now, you’re good to go with the currency of your country.

Advanced

Month Formula Explained

Revisiting the “Month” formula, let’s take a deeper dive into how this formula works.

Month Formula Explained

Corresponding to the numbers in the Month Formula Explained image, the formula follows this logic:

1. It takes a look at the date in the “Date” column of the table within the same row (B:8), then
2. if that date is blank,
3. it leaves it blank, but
4. if that date is not blank, it converts the date in the “Date” column to text, and
5. only enters the month portion (“June” in our data sample).

The “Day” formula (D:8) is similar except it returns the day (“Saturday” in our data sample).

(Note: Depending on your Excel version, or if you open it from the cloud, Excel might auto-convert the “@Date” portion to the actual cell reference “B:8” when you look at the formula bar.)