Personal Finances Excel Template: Free Download & Tutorial

Download and the spreadsheet so you can follow this guided tour.

What's On This Spreadsheet

The spreadsheet is divided into four basic sections, top to bottom:

• Expenses (yellow)
• Useful Information (gray and white)
• Income (green)
• Bank Accounts / Mutual Funds (multicolored)

Enter expenses and finance info into the lighter-colored cells.

Darker-colored cells (usually labelled with "Subtotal" or "Total" in the left-hand column) perform calculations for you, adding up columns or calculating monthly averages.

Using This Spreadsheet

You may change the labels in the left-hand column by clicking them to edit them.

Enter figures into the lighter-colored boxes: monthly expenses, bank account balances, and so on. Dollar signs are optional; it'll put them there for you. Indicate bank withdrawals and fees with a - sign in front of the number.

To enter a string of figures in one cell, for example more than one purchase of groceries, use an equals sign, then separate them by + signs like this:
= 22.44+14.20+5.00
This tells Excel to add up those figures and show the total in that cell.

To delete the values in one cell, double click and edit it.

To delete the values in multiple cells, select them, then choose Edit > Clear... > Contents from the Edit menu. Don't choose "All" or you'll wipe out the colored backgrounds and borders.

About Locked Cells (Protection)

I have set this worksheet to "Protected", then LOCKED all the cells that perform calculations. This lets you enter figures and edit the left-hand labels without disturbing the cells with formulas in them.

However, this protection also prevents you from doing a few other things that you may want to do:

• Insert or delete rows
  
• Insert comments (useful for adding itemized descriptions of expenses)
  
• Change a formula (see "Customizing Formulas" below for help)

When you want to do one of those things, turn OFF protection under the Tools > Protection > Unprotect Sheet... menu, then turn it back on with the same menu when you're done. You don't have to have protection, but I find it's safer so I don't delete a formula by accident.

A lot of expenses happen every month: utilities, shopping, or orthodontist visits for example.

Then there are big expenses that happen only once or twice a year, like property taxes or purchases of major appliances.

I like to get a handle on my regular monthly trends, so I separated out the monthly stuff from the "big, one-time expenses." Hence the three bright orange boxes.

For purchases like shopping, I'll turn off protection and insert a comment (under the "Insert" menu) telling me what I bought.

There's two gray areas where I've singled out other things I want to monitor: my credit card statements and my business expenses. These amounts are already included the monthly expenses above.

When I get my credit card statement, I review it and and enter the individual expenses into the appropriate "monthly expenses" cells in the yellow section above. But I also just want to be able to glance at how much I'm using each credit card, so I enter those figures here. I pay off my credit cards every month; you may want to add an extra row for unpaid balances.

My business expenses, again, are included in the yellow monthly expenses section up above. But I also want to itemize business expenses by adding comments (under "Insert" menu, see example) to help keep track of write-offs.

You may wish to add or subtract these sections to single out important aspects of your monthly expenses that you need to track separately.

Keep track of all the ways you earn income here. My favorite part of the spreadsheet!

Edit the labels at left to name your income sources. As usual, you'll need to turn off protection under the Tools menu to add/delete rows.

Enter your starting balance each month, plus any deposits or additions to your funds.

Enter withdrawals and fees by prefixing the number with a minus sign (-1.50 for example).

The bottom two cells, the end-of-month balance and gain/loss, are LOCKED. They are calculated automatically based on the figures above.

When you enter a starting balance for the next month, make sure it matches the ending balance of last month. If not, then the figures you entered last month for deposits, withdrawals, and fees are missing something. Make sure your withdrawals had a minus sign; that's usually what I forget.

You should be able to use the spreadsheet I gave you as-is.

But what if you want to change the formulas, so they're calculating different things? Let me try to give you a crash course in Excel formulas, plus the functions I created for this spreadsheet. Mostly they're just "add it up" formulas and "take the average" formulas.

First of all, SAVE your spreadsheet. Then "Unprotect Sheet" under the Tools menu so you can access with formulas in them. Now double-click one of them to see the formula.

Tip: If you accidentally change a formula, use Cntrl-. (Command-. on Mac) to escape without implementing the change.

Here I've double-clicked the first cell to the right of "Total":



Notice the little popup below the formula. Excel tries to help you along by telling you what it expects when you start writing a function (one of the building blocks of a formula).

I got a little fancy here. So let's break this down step by step.

Referring to Cells on a Spreadsheet

To say, "grab the value from that cell," you use the letter (the column) plus the row (the number). B71 means column B, which is the second column, row 71. When I was entering this formula, I didn't have to type it -- I just clicked that cell, and Excel wrote "B71".

To say, "grab a bunch of cells," you specify the first and last cell separated by a colon. So B71:B77 means all the values in column B from row 71 to row 77. I could've made the last number be C77, and then it would've included the values from the next column as well.

Perform a Calculation

Use = to start a formula. This tells Excel, "do the calculations in the following formula".

The functions I've used are...

If you just want to add (or subtract) specific cells, you can use plus or minus signs: =B71+B73-C77, for example

If you have a list of numbers to add, or want to add all the numbers in a column, use SUM with parentheses around whatever you want to add.

Separate individual numbers, cell IDs, or ranges with commas, like this:

=SUM(1,C1:C10,B11)

That means, "Add 1 + all the numbers in row 1 to 10 of column C + cell B11".

I use the IF function a lot.

The basic format of this function is:

IF(something,result)

This tells it to return the result, if "something" is true. Optionally, you can add a comma after "result" and an alternate result which will be returned if "something" is false.

Computers are funny. They think of anything greater than zero as "true" and zero as "false". This means you can say, "If it's a number, do this; if it's empty, do that".

Now let's take another look at that formula from above:

=IF(SUM(B71:B77),SUM(B71:B77),"")

So what I've done here is say, "Add up the column. If there's something IN the column, give the total, but if the column is empty, just leave it blank."

Originally, I had just used a plain old SUM function: =SUM(B71:B77). However, when all the cells in Column B were empty, it spit out the total as $0.00. This looked ugly. So I used IF to hide the result when, well, there wasn't one!

The far right-hand column of this spreadsheet is reserved for the monthly average. This is easy:

=AVERAGE(B2:M2)

means "take the average of all the numbers in column B, row 2, through column M, row 2."

Specify the upper left and lower right cell of the area you want Excel to average.

As with SUM, if you want to list individual values, separate them with commas. For example:

=AVERAGE(B2,B4,C6)

or even

=AVERAGE(B2,B4,3)

Excel hates it when you divide by zero, which is easy to do when you take the average of a bunch of cells you haven't filled in yet. If you give it a formula that results in division by zero, it puts an error message in that cell.

To hide these error messages, I've used a special test, ISNUMBER, which returns TRUE (or 1) if it's an actual number, or FALSE (0) if it's not.

For example:

ISNUMBER(AVERAGE(B1:B10))

returns TRUE if there's at least one number in that range (B1-B10), but if the column is empty, AVERAGE returns a division-by-zero error message, and ISNUMBER says, "nope, not a number".

This means that if you want a cell to calculate the average of a bunch of cells, and you don't want to see an error message, do something like this:

=IF(ISNUMBER(AVERAGE(B1:B10)),AVERAGE(B1:B10),"")

There. No more "DIV#0!" error messages.

One of the most common questions with Excel is, "How do I specify the last non-blank cell in a range?" That is, if you're entering data in a table, you may want another cell to show the last number in the table, keeping a running balance.

You'd think there'd be some simple function for that. But no. Here's how I do it. This only works across a single column or row, but so far that's all I've needed to do.

OFFSET lets you specify a starting point (one cell), specify how far down and to the right to move, and then it spits out the result at the new location.

For example, =OFFSET(C2,3,2) means to find the value of the cell that's 3 below and 2 to the right of C2, in other words, E5.

COUNT counts the number of nonblank cells in a range.

Therefore, if I use this formula: =OFFSET(B2,0,COUNT(B2:M2)-1)

That says: "Start at B2. Count how many cells in Row 2 have contents. Move zero down (we want to stay on the same row) and right to the last cell that's got something in it." The subtraction is because it was counting B2, which is one cell too many.

On the Personal Finances Spreadsheet, this is used to post the balance of the most recent month in the "YTD" (Year to Date) column.

Sometimes it's easier to learn from a book, even today. Here's my top pick of the "How to Excel" books on the market.

An index, examples, and templates you can download from Mike Smart's website make this book one of the best ways to take the mystery out of Excel so that you can become an Excel master.

Check out the user reviews on Amazon. A lot of people have found this book to be a real life saver.