loading, one second please...

Track Your Finances In A Spreadsheet It's Easy!

Joan Young By Joan Young on
Badge: Editor | Level: 34 | Finances Expertise:
Image for Track Your Finances in a Spreadsheet- It's Easy!

You can spend a lot of money on accounting software, but if you have just a little bit of talent with a spreadsheet it’s not difficult to track a basic budget. Here’s how I do mine.

I have two basic sections, which are groups of columns. The first is a running list of income and expenses. Everything I make and spend goes in this. The second is a set of formulas that group the entries by topic.

For the running list I have the following columns A-G:

date

vendor or source of income

description

category (a set list of choices such as rent, insurance, food, entertainment, other, etc)

amount of income

amount of expense (these are separate columns- each entry will appear in one or the other, but not both)

method of payment/ receipt (a code for PayPal, check, cash, EFT, MO, etc)

At the top of this block, so I can easily see it, I total both the income and the expenses. See picture 1

Then I move over a few columns, so that I can visually see that this is a separate section. This section has the following sub sets: income by category, and expenses by category. My income comes from a number of different types of activities, and this allows me to track things such as writing, programming, book sales, etc. You could also track it by source, such as restaurant wages, factory wages, etc. See picture 2

Income columns:

category (these must match the categories used in the first block, whether by type of income or by source)

the total in that category- this is the one space that needs a little expertise. In Excel, use this syntax

=SUMIF($D$7:$D500, ”wages”, $E$7:$E$500) D7 is the first cell that contains a category, and D500 is a cell in that column that is far enough down to include all entries in that list. The word in quotes is the category you want to extract the information about. E7 is the first cell that contains income amounts, and E500 is a cell in that column that will include all those entries. See picture 3

Expense columns:

To track expenses, do the same thing, but in my example in the picture, the columns would be D and F instead of D and E.

For Quattro Pro the syntax is @SUMIF($D$7..$D$500, “wages”, $E$7..$E$500)

Notice that you have now created a double-entry ledger. See picture 4. The amounts in the red circles should be the same, and the amounts in the green circles should be the same.

This is really easy, and so versatile! You can create any custom categories you want to. If you want to know how much you spend on toilet paper in a year, you can just enter it as its own category of expenses. You would have to break that out from your grocery store receipt when you entered it, but once the spreadsheet is set up, this would be the only work you have to do.

You can enter receipts in any order, because you can always just select that block and sort it by date.

I actually keep all of my home business accounting by this method. Since I’m the sole proprietor, I don’t have to keep a lot of extra paperwork, so it even works for that. It would certainly work for personal or family expenses.

Knowing where your money comes from and where it goes is the first step towards keeping your budget in balance!