How to create an expense tracker with Google Forms and Sheets
By Michael Ansaldo, PCWorldMar 10, 2015 4:00 am PDT
When your “expense tracking system” consists of pocketsful of crumpled receipts and several more digital ones buried in your email inbox, it’s time for a change. Many apps and services can help you track your expenses on the fly, but it’s just as easy to create a simple one that you can customize using Google tools.
This method of using Google Forms with Google Sheets was ostensibly designed to gather and analyze survey data, but with a few tweaks it works just as well to record your financial transactions and keep a running balance of your spending. Here’s how to do it.
1. Create a spreadsheet
Go to Google Drive and select New > Google Sheets. Give the spreadsheet a descriptive name like “Expenses” or “Spending Tracker” and include the year.
2. Create a form
In the spreadsheet menu, go to Tools > Create a form. Google’s form creator will open in a new browser tab. You’ll see three sections, but we’re primarily concerned with the second one, where you’ll be adding form fields for the items you want to record.
Click the arrow next to the Add Item button, and select Text from the drop-down menu. In the field labeled Question Title, type Date. Then click Done.
Repeat the above steps, but this time in the Question Title field type Payee, then click Done.
Repeat those steps again, and type Amount in the Question Title field. Click Done.
Click the arrow next to the Add Item button again, but select Choose from a list from the drop-down menu. In the Question Title field, type Payment Method. In the field below this that says “Option 1,” type in a payment method, such as Visa. Once you enter your first payment method, click each succeeding field and add another one until you’ve included all your options. For this example, we’re using Visa, Mastercard, Debit Card, Checking Account, and Cash as our methods. When you’ve entered all of yours, click Done.
Repeat the steps above and type Category in the Question Type field. In each of the options fields, enter a different spending category, such as meals, office supplies, and so on. Then click Done.
Finally, select Text from the Add Item drop-down menu and type Description in the Question Title field. This is where you’ll enter a description of the expense in the finished form. Click Done.
3. Email the form to yourself
Now that you’ve created the form, you need to send it to your email account, where you’ll be able to access it easily from your phone, tablet, or computer.
At the bottom of the form creator, click Send Form. In the field labeled Send form via email, enter your email address. Make sure the “Include form in email” box is checked. Click Send.
Go to your email client and open the message. You should see a form that looks like this:
Keep the email with the form starred or otherwise bookmarked, and you’ll easily be able to record your transactions while you’re out and about.
When you do, your Expenses spreadsheet will automatically update. You can keep your balance updated by using the spreadsheet’s Sum formula.
Once you master this trick, you can use it to track just about anything. Leave your ideas in the comments.