Tips & Tricks for my Finance Spreadsheet

Balancing a budget should be FUN. You need JOY and WHIMSY in the mundane aspects of life or you will die. Here's some ways I make my finances spreadsheet super fun!!

My finances spreadsheet is set up with an overview on the first page, then one page for each month (additional pages are added on the bottom toolbar of most software - don't forget to rename them to make navigation easier!).

My Overview: sum of liquid assets and debt, a credit utilization thermometer, miscellaneous notes. A simplified template is shown below:

Liquid assets/debt: Pretty straightforward, just a list of all of the accounts you have, the amounts you have in them, and then in the total line (box B8 in the sample) write =SUM(B4:B7) or if you have more than 4 accounts then change the range in the parentheses.

Credit Utilization Thermometer: This one involves a bit more math. See "Automated Aspects" below for the details of coding it.

Each month: list of set monthly bills and due dates, log of checkbook, notes for how each paycheck will be allocated.

Credit Limit Thermometer

To start, I write the credit limit for each card in the column next to its balance (boxes C12 thru C14) and make the text the same color as the background so it's hidden. In the sample I've got $1000, $2000, and $500 for a total limit of $3500. That's shown in the top line of the thermometer (and it's done with the =SUM function like the assets/debt totals). The next row, total debt, is set equal to box B15. The utilization is set as =F4/F3, the current debt divided by the credit line available.

For the thermometer itself, we first set up the values for each tick mark. We just need to decide how many rows tall it will be - in the sample it's 13 rows. It'll be a two-column setup, put borders around the size you want and add a background color to each column. The top bar of the thermometer's tick mark (right column) will be equal to the full credit line (box F3). The second tick mark will be equal to the box above it minus the total credit line divided by 12 (one less than the total number of rows), shown below:

The dollar signs in the F7 within the parentheses is so that the credit line stays the same value - while the F7 at the start will change when we apply this formula to the rest of the cells. You apply the rule to the rest of the thermometer by highlighting that second box and dragging the right corner down to the bottom row:

Now for the mercury in the thermometer: highlight the entire left column and then in the toolbar click "Conditional Formatting -> Manage Rules..." and hit "New Rule." It's gonna be "use a formula to determine which cells to format" and for the rule, type =F7<F$4$ which is saying that if F4 (our total debt) is greater than F7 (our tick mark), that level of the meter will be filled in. Note that F4 has the dollar signs again, as this box needs to remain constant while F7 will change as each row is compared to the total debt. The overall rule manager is shown below:

And here is a demonstration of the thermometer updating itself with a change in overall debt: And that's the whole thermometer!! If it seems like a long hassle to write all that up, don't worry I'm going to upload the full sample spreadsheet at the bottom of this page (after I finish the monthly sample sheet too!!)

Remaining Bills