Automatically Track Expenses with Google Sheets and Google Form

An expense tracking tool is crucial if you are budgeting or need to monitor your spending. In this article, we will guide you on how to create a free and efficient expense tracking tool using Google Form. Subsequently, these expenses will be updated and automatically calculated in Google Sheets. This allows you to stop keeping receipts or trying to remember a charge on your credit card.

Once you have built your expense tracking tool, all you need to do is open it right after spending to record all the essential details and send it to a spreadsheet that you can review later. Here are the steps to implement this.

Build Your Expense Tracking Tool with Google Form

The concept of the expense tracking tool is to monitor what you are spending your money on for which categories. Therefore, you need to identify the main items to track. Then, construct those fields in Google Form.

Decide which expense categories you need to track.

Here are some examples of what you may want to track:

  • Amount: How much is this transaction?
  • Store: Where are you spending money?
  • Description: What are you spending money on?
  • Category: What type of purchase is this (entertainment, food, bills, etc.)?
  • Payment method used: How are you paying (card/bank/app)?
  • Payer: If multiple people are using the form.
  • Note: Do you need to add any additional notes?

Once you have decided what content to include in your expense tracking form, you can start creating a tracking form.

Of course, you don’t have to create overly detailed fields. You can build more general fields, for example, instead of creating fields for breakfast, lunch, etc., you can create a general Food field. Additionally, you can set any field in your expense tracking form to be either mandatory or optional. So, if you want a very specific field for something, just set it to optional, and you won’t have to fill in the information for every transaction.

How to Create an Expense Tracking Form

Here’s a sample expense tracking tool, so you don’t have to replicate it exactly. Customize this form according to your actual needs. Please refer to our beginner’s guide to using Google Forms, where you’ll find that customizing the form is straightforward. Below are the basic steps:

  1. Open Google Form.
  2. Choose the Blank page to start a new form.
  3. Rename your form as desired, for example, Expense Tracker.
  4. Select the first box and change it to Short answer.
  5. Name this box Price – this will be the field for entering the amount spent, so set it as a required field and format it as a number.
  6. Use the plus sign from the menu on the right to add new questions, but this time choose Multiple choice.
  7. Name this field Store, then fill in the answer options with the stores you usually shop at. The Other field is added by default, so if you shop from a different store, you can enter it there.

Continue to create additional questions you need to answer when purchasing something on the form. Use the suggestions above, such as payment method and description questions, to provide as much detail as possible. This will be helpful when we need to analyze your expenses later.

Use the preview button (eye icon) at the top of Google Form to get the URL to your form. You can share this information with anyone you want to use the form together or send the form to yourself for easy access from your phone.

Although not necessary, you can add images, titles, colors, etc., to the form if you wish. A colorful form can make tracking your expenses more interesting. So, if needed, you can click on the color palette icon at the top of your form to change the interface.

Setting Up Automatic Expense Calculations with Google Sheets

All entries from your Google Form are automatically collected in a uniquely formatted spreadsheet in Google Sheets. Select RESPONSES from the top of your form and then click on the spreadsheet icon to rename the sheet (if desired).

Once the spreadsheet is open, create a new tab at the bottom and name it Calculations.

On this tab, you can do anything that Google Sheets supports: sum all your expenses over a certain period, create charts for a visual understanding of your expenses, see who spends the most, identify which store is causing you the most expenses, and more.

The use of these types of formulas in Google Sheets is beyond the scope of this article, but consider something as simple as adding all your expenses to see where you are spending the most. Additionally, you can explore a Google Sheets course from basic to advanced to learn more interesting functions and formulas that you can apply.

Now, let’s see how to calculate the total expenses regardless of who made the transaction with this simple array formula:

=sum('Form Responses 1'!B:B)

Here’s a Google Sheets formula for a more detailed view of the amount each person is spending:

=sumif('Form Responses 1'!G:G,"Jeff",'Form Responses 1'!B:B)

The formula works by using the sumif function and then selecting the column containing names (column G in our example). The next part is specifying the name, Jeff. Finally, select the column header with values to sum when the other column matches the name we are tracking.

Now you have created an expense tracking tool. You can quickly access your Form and Sheets from your phone or create an app from your Google Sheet with the available free tools.

Leave a Comment

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

Shopping Basket
Scroll to Top