Pinterest Stumbleupon Whatsapp
Ads by Google

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 How To Start Saving Money And Stop Spending With 4 Easy Habits How To Start Saving Money And Stop Spending With 4 Easy Habits One of the top new year resolutions on many people's lists is to spend less and save more. It's easier said than done, but you can still rely on several apps and tools to help... Read More without realizing it.

To get started, go to Google Drive, click the New button, and choose Google Sheets.

automated-budget1
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.
automated-budget2
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 Use Google Forms To Collect Contact Info On A Mobile Device Use Google Forms To Collect Contact Info On A Mobile Device Using Google Forms, you can collect names, emails, and other contact information on your computer or mobile device that supports mobile browsing. All the data is saved to a spread sheet which can be used... Read More , so make the questions simple and few.

Ads by Google

First off you’ll want to make sure you log the date of the transaction.

automated-budget3
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 Are You In A Rush To Make Money From Home? Spot 7 Work At Home Scams Are You In A Rush To Make Money From Home? Spot 7 Work At Home Scams Wealth, speed, ethics -- when it comes to money, you can only pick two. When it comes to working from home, it's very likely that you won't get a chance to even pick one of... Read More ).
automated-budget4
The next step is important if you want to track your spending How To Use Mint To Manage Your Budget & Spendings Online How To Use Mint To Manage Your Budget & Spendings Online Read More 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.
automated-budget5
Then, of course, you’ll want to add a text field for the actual dollar amount spent.
automated-budget6
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?”
automated-budget7
Now that you’ve got your live form created, if you go back to the original spreadsheet, you’ll see that the form creation Supercharge Your Google Forms and Get More out of Them Supercharge Your Google Forms and Get More out of Them The humble Google Forms has loads of uses. With the availability of add-ons, you can find more ways to tap into its usefulness than ever before. Read More process automatically inserted columns for each of the entries in the form.
automated-budget8
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 Track Key Areas of Your Life In 1-Minute with Google Forms Track Key Areas of Your Life In 1-Minute with Google Forms It is amazing what you can learn about yourself when you take the time to pay attention to your daily habits and behaviors. Use the versatile Google Forms to track your progress with important goals. Read More .

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.

automated-budget9
After a while, you’ll see all of the transactions piling up in the spreadsheet.
automated-budget10Keep 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.

automated-budget22

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.

automated-budget11
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.

automated-budget12
For each field you add, make sure to deselect the Show totals checkbox.

automated-budget13
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”.
automated-budget14
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.
automated-budget15
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.
automated-budget16In 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.

automated-budget17If 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.
automated-budget18
Insert another chart, and in this case you might choose to take a look at your spending in each category using a pie chart.
automated-budget19

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.
automated-budget20
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!
automated-budget21
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!

  1. Aaron Froemming
    September 16, 2015 at 4:50 pm

    I use YNAB(You Need a Budget) and love it. Sometimes Steam and other retailers have it on sale. No access to your bank accounts or anything, but will sync with dropbox so you can sync it to your phone.

    • Ryan Dube
      September 16, 2015 at 6:03 pm

      I love YNAB. One thing that took me a LONG time to get adjusted to is their zero-based budgeting approach. It's very annoying to not be able to plan out the budget into the future -- you can only budget what you have.

      Intuitively it makes perfect sense (and having used it for almost half a year now, it's working quite well), but it takes a lot of time to get used to, for sure. I also love the Dropbox sync feature so mobile logging works!

      As I said below - it's good for someone ready to finally start using a budget. The DIY tool in this article is really to be used as a quick short-time log to get a fast handle on where spending issues appear to be happening.

      • Aaron Froemming
        September 16, 2015 at 6:09 pm

        Yeah, I have to agree on that. It took me a while to get used to budgeting for what I have now, and not being able to plan further out. I could see myself using this method mixed with YNAB.

  2. Nikhil Ghelani
    September 15, 2015 at 7:08 pm

    Or Use "Hello Expense" if you don't trust Apps which asks for Credit card or Bank details.

  3. Richard Fletcher
    September 15, 2015 at 5:50 pm

    Or you could just use Mint.

    • Saturday Delson Sazaran
      September 16, 2015 at 5:12 pm

      Exactly what I was thinking, Richard.

    • Ryan Dube
      September 16, 2015 at 6:01 pm

      Hi Richard - I don't think this simple tool could (or even should) replace any budgeting software. Ideally, it's a quick tool someone can use to take a log of their spending habits via their mobile phone - simply and easily.

      Mint is powerful (I used it for a while before switching to YNAB), but it's also quite complicated for first timers, and without a full awareness of your existing spending problems, it's easy to get bogged down in the complexity if a large tool like Mint, and lose sight of the fact that...well...you have a spending problem and need to quickly identify it. This tool can help.

      • Richard Fletcher
        September 16, 2015 at 6:56 pm

        Hi Ryan,

        I think Mint can be quite simple to use if you just want to track spending, and it is free too, though I do take the point of the commenters who were reluctant to share their bank details with an external site. The advantage is that all transactions in linked accounts are uploaded automatically so there's no requirement to manually log every payment. I have used it for several years with good results and I'm no expert in financial software.

        I'm sure your Google Sheets method would do the job just fine too, but it did look like quite a lengthy process to set it up and it needs to be manually maintained . That's why I suggested using an off-the-shelf solution.

Leave a Reply

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