Bill Payment Spreadsheet – Many companies use Microsoft Excel to keep a record of departmental expenses or overheads. While Excel is now frequently attached to personal computers using the Windows operating system, if you have Excel on your desktop computer, you can use it to keep track of your bills. There are several templates available to keep track of your expenses from Microsoft and other websites and recent versions of Excel include in their installed templates a template to keep track of your bills. You can also set up your own spreadsheet to keep track of your invoices in Excel. Below you will see the instructions for both approaches.
- Work with an Excel spreadsheet
Image titled Track your Bills in Microsoft Excel Step 11
Choose a pre-installed template. Recent versions of Excel include a template for keeping a personal expense record along with templates for common business applications. You can access these templates and use them to keep track of your invoices in Excel.
- In Excel 2003, select “New” from the “File” menu. Select “On My Computer” from the “New Book” task pane to display the “Templates” dialog box.
- In Excel 2007, select “New” from the “Menu” button. This displays the “New Book” dialog box. Select “Installed Templates” from the “Templates” menu on the left side. Select “Personal monthly budget” in the “Installed templates” in the central part and click on “Create”.
- In Excel 2010, click on the “File” tab, then select “New” from the “File” menu. Select “Sample Templates” in the top section of “Available Templates,” then select “Personal Monthly Budget” in the sample templates submitted and click “Create.”
Choose an online template. If the personal budget template that comes pre-installed with Microsoft Excel does not work to keep track of your expenses, you can select an online template. You can download a template from a third party site or you can use Excel to connect to Microsoft Office online.
- For Excel 2003, you can select an appropriate template from the Microsoft Excel online library at http://office.microsoft.com/en-us/templates/. (You can also find templates for more recent versions of Excel.)
In Excel 2007, choose “Budgets” in the Microsoft Office online section of the “New Book” dialog box. You must have a good internet connection to be able to connect to the Office online template library.
- In Excel 2010, choose “Budgets” in the Office.com section of the Available Templates pane. You must have a good internet connection to be able to connect to the Office online template library.
Image titled Track your Bills in Microsoft Excel Step 33
Enter your information in the appropriate cells. The exact information depends on the spreadsheet template you will use.
- Save your spreadsheet. You can use the name that the template gives you for your spreadsheet or change it to something more meaningful. Adding your name and the year of the existing file should be sufficient.
- Design your own expense record spreadsheet
- Enter a name for the spreadsheet in Cell A1. Use a name that makes sense like “Personal Budget”, “Personal expense record” or something similar. (Exclude quotation marks by entering the name – used here only to show names as examples)
- Enter the column titles in Row 2. Some titles and suggested order are “Date”, “Payment category” (or “Payment to”), “Memo”, “Expenses”, ” Or “Deposits”) and “Balance.” Enter these titles in Cell A2 through G2; you may need to adjust the width of the columns to accommodate long titles or long entries.
After setting the spreadsheet title and column headers, use the “Freeze Panels” option to keep these headers at the top of the display as you scroll through your entries. Immobilizing panels can be found in the “View” menu in Excel 2003 and in earlier versions, in the “Window” group in the “View” menu tab in Excel 2007 and 2010.
Image titled Track your Bills in Microsoft Excel Step 84
Enter your first expense in the cells in row 3.
- Enter the balance formula in Cell G3. Since this is the first entry in the balance sheet, the balance will be determined by the difference between the expenses and the income. How you set this depends on whether you want to see the expenses or cash available.
If you set the spreadsheet to see your expenses mainly, the balance formula will be = E3-F3, where E3 is the cell that represents the expense and F3 is the cell that represents the income. Setting up the formula in this way will make your total spend a positive number, making it easier to understand.
- If you set up the spreadsheet to keep track of available cash, the balance formula will be = F3-E3. Setting up the spreadsheet in this way will present the balance as positive when your cash flow exceeds your expenses and negative when you have more expenses than income.
- Enter your second expense in the cells in Row 4.
Image titled Track your Bills in Microsoft Excel Step 117
Enter the balance formula in cell G4. As the second and subsequent entries will maintain a current balance, you will need to add the result of the difference between the expense and the entry to the value of the balance of the previous entry.
- If you set up the spreadsheet to observe your expenses primarily, the balance formula will be = G3 + (E4-F4), where G3 is the cell that represents the previous balance, E4 is the cell that represents the expense and F4 is the cell Which represents income.
- If you set up the spreadsheet to keep track of available cash, the balance formula will be = G3 + (F4-E4).
The parentheses around the cells representing the difference between the expenses and the income are not necessary; Are included just to make the concept of the formula a bit clearer ..
If you want to keep the cell empty with the balance until you have full income, you can use the IF statement in the formula so that the balance does not show a value until the date is entered. The formula for this, for the second entry is = IF (A4 = “”, “”, G3 + (E4-F4)) if you set up the spreadsheet to keep track of expenses and = , “”, G3 + (F4-E4)) if you set up the spreadsheet to keep track of available cash. (You can omit parentheses around cells, which represent expenses and income, but you can not omit parentheses.)
Copy the balance formula into the other cells in Column G (the Balance column). Right click on Cell G3 and select “Copy” from the popup menu; Then drag to select the cells below the column. Right-click the selected cells and select “Paste” from the pop-up menu to paste the formula into the selected cells. (In Excel 2010, choose the option “Paste” or “Paste Formulas” from the popup menu). The formula will automatically update the reference cells to indicate the expense, entry, and date references (if used) from the current row and the balance reference from the previous row to the current row.
- Save the spreadsheet. Make a meaningful name, such as “Expense register.xls” or “Personal budget.xls” or “Personal budget.xls.” As with a budget spreadsheet based on a template, you may want to include your name And the year in the file name. (Again, note that the quotation marks are only used to demonstrate example names and should not be written in. You should not write the suffix of the file, Excel does it for you)
Excel 2003 and earlier versions save spreadsheet files in the old “.xls” format, whereas Excel 2007 and 2010 save spreadsheets in the new XML-based format “.xlsx” but can read and save worksheets Calculation in the old “.xls” format as well. If you have multiple computers and you plan to have this worksheet in all, use the older format if any of your computers have Excel 2003 or an older version and use the newer format if all your computers have at least Excel 2007.
- To distinguish paid invoices from invoices that have not been paid but which are anticipated expenses, you can bold or color the text of the invoices paid or you can shade the cells.
- Use “AutoComplete” in expense and income categories to ensure consistent spelling.
- To avoid accidentally changing a formula or a column header, you can protect those cells from changes. Select the cells you’d like to change (date, payment category, expenses, revenue, and memo values) and unlock those cells, then apply protection to the entire worksheet.