I created this spreadsheet to keep track of my every expense and income. From grocery shopping to contract work to coffee at the local café.
The Download
Download Books Template 2015 here
Download Books Template ver. 2 here
The Sheets Explained
[year] income
[year] expenses
These two sheets are itemized lists. They should be newly created every year. The columns here (service, for) need to match an item in the totals sheet. You may not have any need for some of the columns, in which case, feel free to delete them (or add more).
[year] totals
This sheet groups each item from the expense and income sheets and totals it as whole year, and by month. This is your overview that gives you all the data you want to see, easily.
The first column is income 1, 2, 3 … expense 1, 2, 3 … and should match up with the service or for columns of the income and expenses sheets, respectively. I’ve added an expense under food on the spreadsheet so you can understand the process by example, and you’ll most likely have a food expense!
If you’re adding new items (income +1) make sure you add it in the middle of the list, rather than at the end. This tells the respective chart to automatically add the item to the chart because it understands it’s still part of the data series.
[year] charts
This graphically displays incomes and expenses for the year. It should automatically update based on the name changes that you make for new items.
If new items don’t get automatically updated, you’ll need to add it to the data series of the chart.
all time totals
Same as 2014 totals, except it’s broken down by year rather than by month.
all time charts
Visually displays all time charts, broken down by year rather than by month.
The Process
- add item details to expense/income sheets
- if this item is new, add the item name to the totals sheet (if you’ve used up all the default item names make sure to add the new item to the middle of the list)
- make sure the item you just added shows up on the totals sheet and the cross checks all add up the same
- keep good books!
The Disclaimer, explained
You will notice on the totals sheets I’ve created columns and rows that say “_____ check” (something check). These are to cross check the totals that are being displayed. These numbers should all match up with the item they’re checking. If read the formula used for the relevant check, it will show you the difference in how the check is calculated, which will give you a hint as to the discrepancy.
I highly recommend keeping the checks in place, and always making sure they all align and are the same.
Sometimes I notice that the totals page doesn’t recognize the item name, so it doesn’t automatically grab the value from the income/expenses sheets. This will be identified by the check not matching. I suspect this happens because of some funny character code being applied to the item name (perhaps excel version or operating system?). The way to resolve this is to copy the item name on the totals sheet and paste it into the relevant income/expenses sheet item name (or vice-verse). Yes it’s a pain to copy / paste a whole spreadsheet of items, but this is why you’re monitoring the totals as you enter them to catch this difference right away.
The auto-fill feature of excel is your friend. It auto fills the cell with what’s previously been entered and you’ve already checked to make sure it’s working in the totals sheet (right?!).
If you’re adding new items to the totals sheet, don’t add it to the end of the list because it won’t automatically get added to the charts (excel won’t consider it’s part of the same data set usually). If you insert a new row somewhere in the middle of the list excel will automatically recognize it as being part of the same data set and hopefully update the related chart with the new item.
I have found that adding new items doesn’t work quite as nice on the all time sheets. You will probably have to insert a new row into the list in the same manner on the all time totals as you did with the year totals, and confirm that the all time charts has automatically updated.
Finally, this is a free spreadsheet. You can chop it up as you wish and share it with your friends. When the new year comes I’ll update the download with the new sheets. I would greatly appreciate if you’d tell your friends where you got it from! Teition‘s the name.
Please don’t hesitate to contact me or comment with questions below. The more questions, the more powerful I can make this spreadsheet.
Hope this helps!