Why is it so hard to keep track of spending? Why is it that most people can go a lifetime before figuring out how to get their finances under control (if they ever do)? The answer is because people rarely opt to simplify.
How can simplifying help? The cold, hard truth is that many people are actually quite clever at coming up with very extravagant and well-organized budgets, but few people have the willpower and motivation to log and analyze their actual spending over the long haul.
The following combination of Google Form / Spreadsheet / Pivot Chart will help you quickly log your spending from your PC or your mobile device, and it’ll also let you analyze your spending in some pretty interesting ways.
Creating Your Purchase Tracking Form
The whole idea here is to track your spending for a while, to identify patterns and see where you might be bleeding money without realizing it.
To get started, go to Google Drive, click the New button, and choose Google Sheets.
Once you’ve got your new sheet open, you may want to retitle it to something like “Home Budget”, or “Budget Tracking”. Whatever works for you. Next, go to Tools in the menu, and select Create a form.
An very simple form editor window will pop up. In this wizard, you’re going to add the elements of the log form that you’ll use to type in expenses immediately after you incur them. Remember that you’ll likely be filling out these forms using a mobile Google Forms app, so make the questions simple and few.
First off you’ll want to make sure you log the date of the transaction.
Then of course the person or company you’re paying (or whoever might be paying you, in the case of your paycheck or other income).
The next step is important if you want to track your spending well. Add a multiple choice question, and list every single category you can think of (or categories that may already be in your existing budget.
Don’t worry about forgetting anything. You can always come back into the design mode for this form and add more categories later.
Then, of course, you’ll want to add a text field for the actual dollar amount spent.
You can also play around with some creative questions that might give you a better picture of how or why you spend money when you do. For example, you could create a “scale” entry and ask whether you feel like you really, really need the item you’re buying.
Doing this will force you to stop and think every time you’re making a purchase. “Do I really need this?”
Now that you’ve got your live form created, if you go back to the original spreadsheet, you’ll see that the form creation process automatically inserted columns for each of the entries in the form.
Now that you have your spending log form ready, and the spreadsheet is just waiting to collect data, you can just start using your new system.
Collecting Spending Data
Earlier this year, I described how you can create a Google form to track your fitness habits. The beauty of these tracking systems is that they work so well using a mobile.
You don’t even need a special app, so long as you have Google Drive installed. You can simply open up your Form and use it every time you make a purchase.
After a while, you’ll see all of the transactions piling up in the spreadsheet.
Keep in mind that this system is a short-to-mid-term solution. You have 1000 rows in the Google Spreadsheet by default, but you could always add many more, so the sheet will last you a while.
However, the point here is to – over time – identify the problem areas with your spending and fix them.
The Magic of the Pivot Table
Of course, we’re only half done. Sure, you have a cool and simple transaction logging system going right into your Google Drive account, but what to do with all that data?
Here is where the magic of Pivot Tables comes into play. You’ll find this option in the spreadsheet, under the Data menu item, and you can select Pivot table. First, select the entire sheet holding all of your transaction data, before you select Pivot table from the menu.
Pivot table automatically opens up a brand new sheet for you when you select it, so there’s no need to select the “+” button to manually create a new sheet.
The Pivot Table sheet will be blank when it first opens. It’s up to you to build it, and doing so is very easy. Click on Add field next to Rows. One by one, add all of the fields from your first spreadsheet, except the actual dollar values.
For each field you add, make sure to deselect the Show totals checkbox.
Finally, next to Values, click on Add field and add the Amount column to the Pivot table. Make sure to select to “Summarize by: SUM”.
At this point, your new pivot chart will be an organized collection of all of your purchases from the original spreadsheet.
Analyzing Your Spending
You’ll notice that each of the first few columns has a “+” or “-” symbol next to them. This is a method you can use to collapse the sub-columns so that you can analyze your spending based on on of the first few columns.
Here’s how that works. If you collapse everything so that you just have the Payee column and the Amounts, you can now highlight just those two columns and then select Insert from the menu and choose Chart from the list.
In this case, you might choose one of the column charts so that you can see the total you’ve spent at each store, restaurant, or bill.
If you expand that column back out by selecting all of the “+” signs so that now you have the category column displaying again, you can just highlight the categories and amounts for a whole different analysis of your spending.
Insert another chart, and in this case you might choose to take a look at your spending in each category using a pie chart.
You can insert these charts into a new sheet so that you don’t interfere with the Pivot Table sheet itself.
Here’s another interesting analysis – expand the category column items so that all of the “Do I really need it?” values are displayed. Highlight that column and the total values spent, and insert a chart just like you did above.
However, in this case, on the Chart types tab, click on “Aggregate column D” (or whatever column the rankings are in). Also choose a column/bar chart.
In this case, you can actually visualize the psychology of your spending.
For example, below you can see that when I’m on the fence as to whether or not I really need something, I tend to spend a lot more than I would otherwise. It appears that indecision is expensive for me!
You never know what you’ll learn about your spending habits until you actually take the time to log the information, organize it, and then analyze it carefully.
Going through this easy exercise could help you identify things that might have been holding back your finances for years, you just never realized you were doing it. And the process of logging your expenses to a Google Form on your phone is so simple, you won’t even mind doing it after a while.
Can This Trick Help You?
Do you feel like your expenses are out of control? Do you think logging and analyzing your spending using Google Spreadsheets might give you some insight? Why not try and let us know how it went for you. Tell us about it in the comments section below!