How to Create Monthly Spending Tracker in Google Sheets

How to Create Monthly Spending Tracker in Google Sheets
How to Create Monthly Spending Tracker in Google Sheets – Sheetaki

You can use Google Sheets to create a monthly spending tracker to easily record and track your monthly expenses.

Keeping track of your monthly expenses can be very intimidating, especially without a proper tool. But don’t worry, Google Sheets is here to make this task easier. With this tool, you can make your own monthly spending tracker to document your finances. So, before spending your dime on fancy apps just to do the job, try creating a monthly spending tracker using Google Sheets first. It’s free, easy, and highly customizable!

In this guide, I’ll teach you how to make a monthly spending tracker from scratch just by using Google Sheets. Let’s get right to it!

What is a Monthly Spending Tracker?

A monthly spending tracker is a financial tool that lets you record and track your monthly expenditures. It comes in many formats, but ideally, a good monthly spending tracker has the following columns:

  • Date – the date when the purchase or transaction was made
  • Category or Type – how the expense is categorized
  • Amount – the actual amount of the expenditure

With these three columns, you can easily gain insights about your spending pattern. You can identify which categories you spend the most or least. Additionally, you’ll be able to determine how frequently you spend on a particular item within a month. Using these insights and other pieces of information, you can just come up with a better plan on how you would allocate and spend your next allowance.

An Example of a Monthly Spending Tracker in Google Sheets

Let’s see how a monthly spending tracker in Google Sheets looks like. Take a look at the example below.

An Example of a Monthly Spending Tracker in Google Sheets

The example spreadsheet features a simple, personalized monthly spending tracker. In the spreadsheet, you can see the detailed expenses for the month of October. You can just determine the type and the actual expenditure for each record at a glance.

If you look at the table on the right part of the example spreadsheet, you’ll see a summary of the total expenses for each category and the overall total expenses.


Summary of the total expenses

A lot of insights can be derived from the monthly spending tracker. By looking at the summary, you’ll be able to identify which type of expense cost the most. On top of that, you can already deduce if the overall total expense is within the allocated budget or not.

If you want to have a copy of my own monthly spending tracker, just click the link below.

Make a copy of the example spreadsheet

So, how can you create a monthly spending tracker like this one in Google Sheets? Let’s find out!

How to Create Monthly Spending Tracker From Scratch

Surely, you can just simply copy my monthly spending tracker using the link above, but why not recreate it yourself? The advantage of creating your own monthly spending tracker is that you can customize it to your preference. Plus, you get to learn some of the amazing features of Google Sheets!

Now, let’s create a monthly spending tracker in Google Sheets from scratch. Don’t worry because I’ll be guiding you every step of the way.

  1. Start by opening a new Google Sheets spreadsheet. There are several ways to do this; the simplest is to type in sheets.new on your browser.

    Create a new spreadsheet for the monthly spending tracker
  2. Once a new, blank spreadsheet has been created, name it as “Monthly Spending Tracker”.

    Name the spreadsheet as Monthly Spending Tracker


    Great! Now we have a spreadsheet that will contain the monthly spending tracker.
  3. This time, we can now create the column labels of our monthly spending tracker. On cell range A1:D1, type in the following labels respectively: ‘Date’, ‘Type’, ‘Remarks’, ‘Amount’. Apply bold emphasis and make sure to center align each label. Once done, your spreadsheet should now look like this:

    Create the column labels of the monthly spending tracker

  4. At this point, let us create a drop-down control for each cell in the Type column. The drop-down control will contain a list of the type of expenses you want to track in your monthly spending. For this guide, we’ll use the following items:

    • House Rent
    • Grocery
    • Miscellaneous
    • Restaurant
    • Toiletries

    Select the entire B column, except the label. To make this possible, simply click the column header of B, then hold Ctrl and then click the column label to exclude it from the selection. If you’ve done it correctly, you should see this selection on your spreadsheet:

    Select the entire column B

  5. With the entire column B now selected, click the Data menu, and then choose Data validation.

    Create a data validation for the monthly spending tracker

  6. The Data validation form should now appear on your screen. Here, set the Criteria as “List of items”. Afterward, enter each of the types of expenses you want to track, separated by a comma.

    Set the types of expenses as the criteria of data validation

  7. Once you’re done with the items in the Data validation form, click the Save button. The column B in your spreadsheet should now look like this:

    Initial version of monthly spending tracker

 

Good job! You can now enter the details of your expenses for the current month in your newly-created monthly spending tracker. But of course, it’s not yet a fully-functional monthly spending tracker since it cannot show you a complete summary of your expenses. Learn how to achieve this in the next section.

Formulas for the Monthly Spending Tracker

To make your monthly spending tracker useful, of course, it needs to show you important details like the total expenses for each category, and the overall total expenses. Budgeting becomes easier with this feature, since it can help you make informed decisions. Let’s now add some formulas to your monthly spending tracker.

  1. With your monthly spending tracker spreadsheet already open, create a data table like the one shown below.

    Create a data table for the summary of expenses in your monthly spending tracker

    This table will contain the formulas needed to perform simple computations on your monthly spending tracker.
  2. Let’s start on the formula of Total Grocery Expenses. In cell G1, type in ‘=SUMIF(B2:B,”Grocery”,D2:D)’.

    Enter the formula for the Total Grocery Expenses


    So what does the formula above do? Basically, we used the SUMIF function of Google Sheets to get the sum of the cells in column D that meet the given condition, which is all those records that have a type of “Grocery” only.
  3. When you’re done with the first formula, proceed with the succeeding fields with the following formulas:
    • Total Miscellaneous Expenses = ‘=SUMIF(B2:B,”Miscellaneous”,D2:D)’
    • Total Restaurant Expenses = ‘=SUMIF(B2:B,”Restaurant”,D2:D)’
    • Total Toiletries Expenses = ‘=SUMIF(B2:B,”Toiletries”,D2:D)’
    • Total House Rent Expenses = ‘=SUMIF(B2:B,”House Rent”,D2:D)’

      Enter the formulas of the other types of expenses

  4. For the Overall Total Expenses, enter a formula that simply gets the total of all the expenses: ‘=SUM(G1:G5)’.

    Enter the formula for the Overall Total Expenses

  5. Finally, for the Remaining (Budget-Overall) field, enter this formula: ‘=G8-G6’.
    Enter the formula for the Remaining(Budget-Overall) field

  6. That’s it! Now you have a fully-functioning monthly spending tracker created in Google Sheets. You can already use this spreadsheet to record your expenses for the current month.

    Final version of the monthly spending tracker created in Google Sheets

Do you want to learn more awesome tips and features about Google Sheets? Check out our other Google Sheets articles.

Stay tuned for more useful articles like this one by signing up to our newsletter.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like