Using Excel: Part 1 – Tracking Spending

The general consensus, at least from people on Twitter, was that this might be helpful.  I’m sure there are some of my readers who already know how to use Excel, or who don’t but have no desire to learn.  Please feel free to scroll right past this post!

I’m always baffled when I hear about people who don’t have a budget.  Budgets use Excel, and Excel is hands down my favorite computer application.  I have my own budget file that is continuously changing as I decide to obsessively track something else.

At work, I recently took over a task for someone that involved a big, semi-complex spreadsheet.  I then discovered that the person previously in charge of it (the sweetest old southern man I have ever met) was updating it manually.  Several times per week. For hours.

There were times when he’d need to count the number of items in a certain category, and times when he needed to count on the basis of several categories.  He used filters to make the job easier, but he would still manually count them.  I lasted about an hour the first day I tried to do it.  It was mind-numbing.

I set up the spreadsheet to do it automatically.  It took maybe 2 hours to set it up, but now I only have to spend 5 minutes updating the file each week, and the spreadsheet does the rest.

The point of this long, rambling story is that sometimes it seems that a budget is hard work, and without knowing some of the functions and commands in the program, it can be too much work.

I don’t know if any of my readers have a problem with spreadsheets. (I know that Stacking Pennies joins me in the spreadsheet love.) However, with the popularity of free budget software, I imagine that there are people who don’t feel comfortable making their own.

Many people might consider it worthwhile since they don’t have to worry about entering in numbers or making their own graphs.  If that’s you, cool.  If you enjoy saving money on something you can DIY, or if you’re nervous about an online company having all of your information, however, I’m going to put together a few posts on making your own budget.

Today we’ll start with Part 1: Tracking Spending.

If you have no idea where your money goes, this is a good step to take first.  After all, it’s much easier to set up a budget if you have a realistic idea of how much you’re actually spending.

So, open your spreadsheet software.  These commands should work in all Microsoft Excel applications as well as OpenOffice (which is what I use at home).  If you use something else, try these commands out there too, and let me know how it works!

Your program will open to a blank page:

budget1

First, create a list of all of your spending categories:

budget2

You can have as many or as few as you want.  For example, I lump gas, car maintenance, and insurance in one group (because I am a slacker and do not have renter’s insurance).

A few lines further down (let’s say line 25 – you won’t match the pictures at first, but all of the functions listed below are based on Row 25 being the row with your headers!), enter into the row the following headers:  “Category”, “Item Description”, “Credit” (if the item is a deposit), “Debit” (if the item is a purchase, for example), and “Balance”.

budget3

Now log into your account’s online access and look up your balance.  Enter that into the first row.

budget4

Now you’re ready to track your spending!

Try to remember anything that might still be outstanding – checks, pending charges from a restaurant, a credit card bill that’s set to be paid in a week.  Make sure to include those!

This next part is up to you – you can track your expenses through your online bank, or you can keep receipts and enter those numbers in.  After a few weeks, your spreadsheet might look something like this:

budget5

If you’ve lined up all of your columns exactly as in the image below, you should be able to exactly copy this formula into the “Balance” column (column E).  Enter the formula in the cell right underneath the value of your starting balance.

=E26+C27-D27

budget6

Now, click on the lower-righthand corner of the black box surrounding that cell, and drag the cursor down.  Check the math – this formula should add the numbers in the “Credit” column to the balance and subtract the numbers in the “Debit” column.

budget72

This is a quick and simple version of a check register, except the computer does all the math for you!  Any time you want to add a new item, just drag down from the cell above.  Copying the formula also works; the program will update the formula!

This system will let you keep track of your balance.  What’s nicer is that you can enter in items that might not have gone through yet.  If you wrote a check to your landlord, for example, you could enter in the amount and not worry about overdrafts if you’re spending whatever your balance says you have.

budget9

Every once in a while you can balance this spreadsheet with what your online account balance.  For example, if they differ by $150, I know that Jim and Michelle haven’t cashed my check yet.  A friend of mine color codes these items – anything that hasn’t shown up at her bank yet is colored blue.

So now you’ve stopped overdrafting your account because of trusting your banks’ online balance!  The next step is to know where your money is going.  This is where that first column comes in.  Look at all of the items and assign them to a category.  Make sure these match the categories you’ve listed above!

budget10

Then, return to the list of categories and enter into column B

=SUMIF(A$26:A$90,A1,D$26:D$90)

Again, drag the box down (or copy and past the formula to the other cells).  This will automatically track what you spend in each category.

budget121

Let me know if this was helpful & if you be interested in me continuing the series.  I figured I’d go through setting up a budget, making graphs, and then doing some of the more confusing stuff, like projecting savings balances and figuring out how much you’re paying in interest each month.  If you see any mistakes (there are some I’m already aware of due to me changing my mind halfway through), please leave a comment and I’ll be sure to correct it!

About these ads

10 Responses

  1. This is somewhat embarrassing to admit as a pf blogger, but here it is.

    I use Google Docs, and I tracked my spending for nearly a year before figuring out how to make the categories automatically add themselves up as I entered an expense. So I’d enter $25, mark it as groceries, then manually add $25 to the grocery sum.

    I don’t actually ever balance my accounts either.

  2. I don’t use excel, and I never deal with cheques, but you never know maybe one day I might! This was really interesting and if I ever move to using excel to track my money I know thisll be really helpful – thanks!

  3. I HEART EXCEL! And great post! I used to be one of those people that was afraid to use Excel. But with the help of Wikipedia (for math formulas like compound interest, present value, etc.) and the Help function in Excel, I’ve created different kinds of spreadsheets. I have a friend who’s an Excel junkie like me. When she figured out how to force round-up a fractional number, she immediately called me and said, “Only you would appreciate this discovery!” LOL.

  4. Thanks for the how-to! I actually use the GoogleDocs excel sheet, and it has the same formulas except you can access it online.

  5. This is a REALLY fantastic post!!! I just set mine up and can’t wait for the followup posts! I’ve tried several times to put my budget in excel but had no way of knowing how to do it. This is really great! I have only one question – How do you track multiple accounts with all different balances in the same spreadsheet?

  6. So helpful!! I look forward to the next post.

  7. Great post! I also love Excel, but am currently hooked on YNAB. If I ever quit it I will for sure go back to my Excel days.

  8. I, too, am embarrassed to admit that I love the idea of Excel but haven’t ever used it seriously before. Getting started RIGHT NOW.

  9. Just came across your blog. Great post on using excel to track $$. I have created a few of these excel tracking sheets before, but I was just too lazy to enter everything in there every day. So I ended up using Mint. But lately I have been worried about the safety of my bank info on Mint’s website. May be I am being paranoid. I will be going back to using excel soon. So I will look out for more of your tutorials, in case I can learn something I don’t know already.
    Nice blog paranoidasteroid!

  10. Okay, absolutely late to this topic, but I’d love to share my thoughts.

    I also have my own obsessive Excel spreadsheet for my personal budget, but I set mine up a little differently…

    It took several months of trying out different budgets and tracking my spending to come up with a reasonable expectation of where my money needed to go each month. It doesn’t happen overnight, but it’s absolutely worth it to spend the time figuring it out.

    Most importantly, I split all of my expenses up evenly by paycheck, which I save up for each month. Some expenses are predictable and don’t change from month to month. For example, to pay $200 towards my January car payment, I will set aside $100 from both my Dec 15th and Dec 31st paycheck.

    Other expenses require a little more effort to figure out. For example, regular maintenance on my car every few months could cost me anywhere from $30 to almost $200 depending on what is needed. So, I looked at my receipts for the past year, and realized I needed to set aside $40 a month in order to never get surprised by a maintenance bill. Now, when that bill comes, I know I have the money ready to pay for it.

    (I’m on salary now, but I did the same when I worked hourly and got paid biweekly. I simply budgeted for 2 paychecks per month- based on the smallest paycheck I was likely to receive- and considered it a “bonus” when a month with 3 paychecks came along.)

    Also important: everything goes somewhere. Anything leftover after paying bills or setting aside for future expenses goes into one form or another of savings. (Savings for both a rainy day and savings for something fun. Both are important!)

    Now for the actual Excel part…
    My account name (I have 3 tracked on the same sheet this way, separated by grayed out columns) is labeled at the top, underneath which I have my account balance listed. Underneath that, I list anything that any money in the account is currently allocated for, such as 1/2 January Car, $100, or Car Maintenance, $80, . At the top I also have a cell labeled “Unaccounted for”. A simple sum formula of everything in the “Allocated” column, subtracted from my account balance, lets me know right away if there’s a problem (like if the number is red!)

    On payday, I update my account balance, update the allocations according to my budget, and, if necessary, get ready to start paying bills. When I pay a bill, I subtract the amount from my account balance and remove the amount from allocation.

    I always know EXACTLY how much money I have, as opposed to how much it LOOKS like I have, not to mention I never forget to pay a bill because if it’s still listed, it hasn’t been paid yet. Checks can take as long as they please to hit my account, because each one is recorded and has the necessary money allocated towards it. (Though I do have a separate checking account that I write all my checks from, just because I don’t like having checks mixed in with my regular spending. See? A teeny bit obsessive. Heh.)

    I do a million other things with Excel, including several other budget activities, most of which play with the more fun tricks Excel has to offer, but I get more use out of this simple budget spreadsheet than I do out of anything else.

    PS – There’s someone at my workplace who spends hours and hours updating a very tedious spreadsheet and combing the data for expired dates. I attempted to explain to her that Excel could do the “finding expired dates” part with a relatively simple formula – basically subtracting today’s date from the date listed, based on the DAYS360 function, and highlighting the cell if that number was less than 0, with a few polishes to clean it up. I wrote her detailed instructions on how to set it up. I even offered to set it up for her. She still does it manually (and frequently misses expired dates, I might add…)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: