How to Make a Spending Spreadsheet in Excel

Use a spreadsheet to track your monthly spending.
Image Credit: David Sacks/Lifesize/Getty Images

Excel spreadsheets can be used to calculate repeatable or large groups of number calculations. For most people, the monthly spending routine involves large groups of numbers that can be divided into different categories, such as food, entertainment and transportation. Once you have your spreadsheet set up, you just enter your spending amounts at the end of each day and the spreadsheet will calculate and show where your spending went for the month.

Step 1

Open a new spreadsheet with your Microsoft Excel software.

Step 2

Type a list of spending categories across the first row, leaving the first cell — A1 — blank. Use categories that make sense with your spending habits. Some category ideas could be House, Medical, Food, Debt Payments, Savings, Dining Out, Entertainment, Personal Care and Utilities. Once you are tracking your spending, you can always add more category columns.

Step 3

Type the current month into cell A1 then type Budget and Monthly Total in the next two cells of column A. You will put your expected or budgeted spending amounts in Row 2 next to Budget and set up Row 3 — Monthly Total — to show how much you have spent in each category as you go through the month.

Step 4

Fill in Column A with the dates of the month, starting with the first of the month in column A4.

Step 5

Use the SUM spreadsheet function to calculate column totals for each of your spending categories. The first column with a total will be Column B. Type the function or use the spreadsheet function helper to end up with the formula: =SUM(B4:B34) in cell B3. Copy the formula to each of the cells in the Monthly Total row. As you copy, the column designation for the formula will change to indicate the correct column. For example, in C3 will be the formula =SUM(C4:C34).

Step 6

Use the Freeze Pane command under the Window menu item to freeze the first three rows of your spreadsheet. Freezing the rows will keep them at the top of your spreadsheet view as you work your way down the rows for the days of the month.

Tip

Each day enter your spending in the appropriate spreadsheet cells for the day of the month and the category. Your total spending for the category will be calculated in the Monthly Total row — Row 3. Use font effects such as bold and colors to make your spreadsheet easier to understand. For a new month, copy last month's spreadsheet to a new sheet, change the dates and delete the entries. Change the name of each sheet to reflect the spending month.

references