How to Make a Spreadsheet to Keep Track of Expenses
A spreadsheet is an efficient way to keep track of monthly expenses, because it allows you to format it so that sums are totaled automatically after you input expenses data, displaying immediately where your expenses stand against your budget. Despite looking complicated, maintaining a spreadsheet to keep track of expenses is very simple---much simpler than getting out the calculator and pencil and trying to determine where the budget was blown at the end of the month. Having all the information easily available not only helps you keep track of it, it also helps you avoid going over budget.
Other People Are Reading
Instructions
-
-
1
Open a new blank spreadsheet. In cell A1, type the word "Expenses," and then enter each expense category in a separate cell within column A. For example, A2 may be "Groceries," A3 may be "Electricity" and A4 may be "Gas." These should be regular, monthly expenses; if you give yourself an allowance for clothing or dining out, make a cell for each of those expenses in column A as well.
-
2
Type "January" in cell B1, then the names of all the months in subsequent cells along row 1; end with "December" at cell M1.
-
-
3
Enter the words "Expenditure" in cell N1, "Budget" in O1 and "Budget vs. Expenditure" in P1.
-
4
Input the formula "=SUM(B2:M2)" under the "Expenditure" cell, in N2. Copy this formula into each N cell that has an expense item in the corresponding A cell, but change the number as you go down the rows. So the first row, B2 to M2, will show the total for "Groceries" in the "Expenditure" cell, and then enter "=SUM(B3:M3)" in the next cell down, to show the total for "Electricity," if that is the order you have input your expenses in. Continue inputting the formula for each expense; copy and paste it, changing the numbers in each new row.
-
5
Type in the budgeted amount for each category down column O. This is a set amount.
-
6
Input the formula "=O2-N2" into the "Budget vs. Expenditure" cell in P2. Copy this formula into each "Budget vs. Expenditure" cell in column P, changing the numbers to correspond with the rows, as when you inputted the sum formula. This formula shows the budget balance of each category in either a positive or negative figure, depending on how well you adhere to your budget.
-
7
Input the formula "=SUM(N2:N#)" under the "Expenditures" column, where the "#" represents the last row number that needs to be calculated. This formula gives the total of your expenditures for the year.
-
8
Input the formula "=SUM(O2:O#)" under the "Budget" column, where the "#" represents the last row number that needs to be calculated. This formula gives your total budget for the year.
-
9
Input the formula "=SUM(P2:P#)" under the "Budget vs. Expenditures" column, where the "#" represents the last row number that needs to be calculated. This formula gives the total of your budget versus your expenditures. If you have gone over budget, this figure is negative, but if you have stayed within your budget, this figure represents saved or extra cash.
-
1
Tips & Warnings
Color coding the different columns, especially the totals columns and grand totals row, helps you discern the information on the spreadsheet quicker.
References
- Photo Credit number background image by kuhar from Fotolia.com
