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:
First, create a list of all of your spending categories:
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”.
Now log into your account’s online access and look up your balance. Enter that into the first row.
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:
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.
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.
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.
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!
Then, return to the list of categories and enter into column B
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.
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!