Many companies use Microsoft Excel to track departmental or overall business expenses. Since Excel is now often sold alongside personal computers that use the Windows operating system, if you have Excel on your home computer, you can use it to track your bills. There are several templates on Microsoft and other websites for tracking your expenses, and recent versions of Excel include a specific template for tracking your accounts from among the many pre-installed. You can also set up your spreadsheet to track your expenses with Excel. Below you will find instructions for both solutions.
Steps
Method 1 of 2: Working with an Excel Template
Step 1. Choose a pre-installed template
Recent versions of Excel include a template for tracking personal expenses, along with templates for common business applications. You can access these templates and use them to track your expenses with Excel.
- In Excel 2003, choose "New" from the "File" menu. Select "On My Computer" from the "New Workbook" task pane to display the "Templates" dialog box.
- In Excel 2007, choose "New" with the "File" menu button. The "New Workbook" dialog box appears. Select "Installed Templates" from the "Templates" menu in the left pane. Select "Monthly Personal Quote" from "Installed Templates" in the center pane and click "Create".
- In Excel 2010, click the "File" tab, then select "New" from the "File" menu. Select "Sample Templates" at the top of the "Available Templates" box, then select "Monthly Personal Quote" and click "Create".
Step 2. Choose an online template
If the personal quote template pre-installed with Microsoft Excel is not suitable for tracking your expenses, you can select an online template. You can download a template from a third party site or use Excel to connect with Microsoft Office Online.
- For Excel 2003, you can select a suitable template from the Microsoft Office Online library at https://office.microsoft.com/en-us/templates/. (You can also find templates targeting later versions of Excel here as well.)
- In Excel 2007, choose "Budget" from the Microsoft Office Online section in the "New Workbook" dialog box. You must have a working internet connection to connect to the online Office Template Library.
- In Excel 2010, choose "Budget" from the Office.com section of the Available Templates pane. You must have a working internet connection to connect to the online Office Template Library.
Step 3. Enter your details in the appropriate cells
The information you need to enter depends on the spreadsheet template you are using.
Step 4. Save the spreadsheet
You can use the name the template provides for your spreadsheet or change it to something more meaningful. Adding your name and year to the existing file name should suffice.
Method 2 of 2: Design the Spreadsheet to Track Your Expenses
Step 1. Open Excel
Step 2. Enter a name for the spreadsheet in cell A1
Use a meaningful name like "Personal Budget", "Personal Expense Tracking", or something similar. (Exclude the quotation marks when entering the name, here they are used only to show that the names are examples).
Step 3. Enter the column titles in row 2
The suggested titles and order are "Date", "Beneficiary" (or "Paid to"), "Memo", "Expense", "Receipt" (or "Income" or "Deposit") and "Balance". Enter these titles in cells A2 to F2; you may need to adjust the width of the columns to accommodate long titles or entries.
Once you have your spreadsheet title and column headings set, use the "Freeze Panes" feature to keep these titles at the top of the display as you scroll through the entries. Freeze Panes is found on the "View" menu in Excel 2003 and earlier versions and in the "Window" group of the "View" ribbon menu in Excel 2007 and 2010
Step 4. Enter the first expense item in the cells of row 3
Step 5. Enter the formula for the balance in cell F3
Since this is the first item in your budget, the balance will be determined by the difference between your expenses and your income. How to set up your balance depends on whether you want to highlight expenses or available cash.
- If you are setting up the spreadsheet to mainly control expenses, the balance formula could be = D3-E3, where D3 is the cell representing the expense and E3 is the cell representing the income. By setting the formula in this way the expenses are indicated with a positive number, which will be easier to understand.
- If you are setting up the spreadsheet to dynamically check available cash, the balance formula might be = E3-D3. Setting up the spreadsheet in this way will show the balance as positive when revenue exceeds expenses and as negative when the reverse occurs.
Step 6. Enter your second expense item in the cells of row 4
Step 7. Enter the formula for the balance in cell F4
Since the second and subsequent items will need to have an updated balance, you need to add the result of the difference between expenses and income for the previous item.
- If you are setting up the spreadsheet to primarily control expenses, the balance formula might be = F3 + (D4-E4), where F3 is the cell representing the previous balance, D4 is the cell representing the expense, and E4 is the cell representing the entrance.
- If you are setting up your spreadsheet to have a dynamic cash availability card, the balance formula might be = F3 + (E4-D4).
- The parentheses surrounding the cells that represent the difference between expenses and income are not needed; they are included just to make the formula a little clearer.
- If you want to keep the cell with the balance empty until you have entered a complete entry, you can use an IF statement in the formula so that if no date has been entered, the balance cell displays no value. The formula to do this, for the second entry, is = IF (A4 = "", "", F3 + (D4-E4)) if you are setting up the spreadsheet to track expenses, and it is = IF (A4 = "", "", F3 + (E4-D4)) if you are setting up the spreadsheet to check the availability of money. (You can omit the brackets around the cells that represent expenses and revenues, but not the outer pair of brackets that are part of the IF statement).
Step 8. Copy the balance formula to the other cells of column F (Balance column)
Click on cell F3 and select "Copy" from the pop-up menu; then drag to select the column cells below. Right-click on the selected cells, and choose "Paste" in the pop-up menu to paste the formula into the selected cells. (In Excel 2010, choose the "Paste" or "Paste formulas" option in the pop-up menu). The formula will automatically update the cell references to indicate the expenses, income and date (if used) of the current row and the reference to the balance of the row above the current one.
Step 9. Save the spreadsheet
Give the spreadsheet a meaningful name, such as "ExpenseRevision.xls" or "PersonalBudget.xls." As with a pre-configured budget spreadsheet template, it's a good idea to include your name and year in the file name. (Again, note that quotation marks are only used to show example names and should not be typed. The file suffix should also not be typed; Excel completes it for you.)
Excel 2003 and older versions save spreadsheet files in "xls" format, while Excel 2007 and 2010 save spreadsheets in the newer XML format with "xlsx" extension, but they can also read and save spreadsheets. calculation in the old "xls" format. If you have several computers and plan to keep this spreadsheet on all of them, use the older format if any of the computers have Excel 2003 or an earlier version, and the new, newer format if all computers have at least an Excel 2007 version
Advice
- Use the "Autocomplete" for expense and income items to ensure uniform spelling.
- To distinguish paid bills from unpaid bills, you can write in bold or color the text for paid invoices or use cell shading.
- To avoid accidentally changing a column header or formula, it is a good idea to protect these cells from changes. Select the cells you want to change (the date, payee, expenses, income, and values) and unlock them, then apply protection for the entire spreadsheet.
