Many companies use Microsoft Excel to monitor departmental or company-wide costs. Currently Excel has become the default program in computers with Windows operating systems. So you can use this program to monitor your bills. There are a number of templates (prints) from Microsoft and other sites that you can use to track expenses. Even in the latest versions of Excel, these templates are already available in the program. You can also create your own worksheets to monitor invoices in Excel. This article will discuss both ways.
Step
Method 1 of 2: Using an Excel Template
Step 1. Select the default Excel template
The latest version of Excel has templates for monitoring personal expenses and templates for general business operations. You can access and use this template to monitor expenses in Excel.
- In Excel 2003, select " New " from the " File " menu. Select "On my computer" from the "New Workbook" task pane to bring up the "Templates" dialog box.
- In Excel 2007, select " New " from the " File " menu to bring up the " New Workbook " dialog box. Select the "Installed Templates" option from the "Templates" menu in the left pane. Select "Personal Monthly Budget" from "Installed Templates" in the middle pane and click "Create."
- In Excel 2010, click the "File" label, then select "New" from the "File" menu. Select "Sample templates" from the top of the "Available Templates" panel, then select "Personal Monthly Budget" from the sample templates view and click "Create."
Step 2. Choose an online template
If Microsoft Excel's built-in personal budget template can't keep track of your expenses, try using templates available on the internet. You can download templates from third-party sites, or use Excel to connect with Microsoft Office Online.
- In Excel 2003, you can select the appropriate template from the Microsoft Office Online library at https://office.microsoft.com/en-us/templates/. (Here, you can also find templates for versions of Excel above.)
- In Excel 2007, select "Budgets" from the Microsoft Office Online section of the "New Workbook" dialog box. You must have an internet connection to connect to the online Office template library.
- In Excel 2010, select "Budgets" from the Office.com section of the Available Templates pane. You must have an internet connection to connect to the online Office template library.
Step 3. Enter the information into the appropriate cells
The information entered depends on the worksheet template used.
Step 4. Save your worksheet
You can use the name of the worksheet provided by the template or enter a name as desired. Or, you can simply write down the name and year of manufacture of the working paper.
Method 2 of 2: Create Your Own Worksheet to Monitor Expenditures
Step 1. Open Excel
Step 2. Enter a name for the worksheet in cell A1
Use a clear name, such as “Personal Budget”, “Personal Expenditure Monitoring”, or something similar. (Do not use quotation marks when writing names. Here, quotation marks are used to indicate examples).
Step 3. Enter row 2 column headings
Here are some names and sequences you can use, “Date”, “Paid To”, “Memo”, “Expense”, “Income” (or “Earning”, or “Deposit”) and “Balance”. Enter these headings into cells A2 through G2, and adjust the column width so that all the text is visible.
After setting the column headings and headers, use the " Freeze Panes " feature so that the column headers are at the top when you scroll down the page. The Freeze Panes option is on the " View " menu for Excel 2003 and later, and in the " Window'" group on the " View " menu ribbon for Excel 2007 and 2010
Step 4. Enter the first expense in the cell row 3
Step 5. Enter the balance formula in cell G3
The balance will be determined by the difference between income and expenses as this is the first balance entry. The way it is set depends on your desire to see the budget from the expense side or the cash you have.
- If you want a custom worksheet to show expenses, your balance formula is =E3-F3, where E3 is the cell that contains the expense balance, and F3 is the income balance cell. This way, if your expenses are greater than your income, your total expenses will have a positive number, making it easier for you to understand.
- If you want a custom worksheet to show the money you have, the balance formula is =F3-E3. Thus, the working paper will display positive cash flow if cash on hand is more than expenses, and negative if expenses are greater than income.
Step 6. Enter the second cost entry in cell row 4
Step 7. Enter the balance formula in cell G4
Since the balance of the second and subsequent entries will record the current balance, you need to add the result of the difference between expenses and income with the value of the balance in the previous entry.
- If you want the worksheet to display expenses specifically, the balance formula is =G3+(E4-F4), i.e. G3 is the cell containing the previous balance, E4 is the cell containing expenses, and F4 is the cell containing revenue.
- If you want the worksheet to display the amount of cash on hand, the balance formula is =G3+(F4-E4).
- The parentheses in the formula that show the difference between costs and revenues are not actually required. We wrote it so that the concept of the formula became clearer.
- If you can leave the balance value cell blank until all entries have been made, please use the IF word into the formula so that when you enter the date, the balance cell does not display the value. The formula for this method for the second entry is =IF(A4="", "", G3+(E4-F4)) if you want the worksheet to monitor the load, and =IF(A4="", "", G3+(F4-E4)) if you want a working paper to monitor cash flow at hand. (The parentheses surrounding expenses and income may be left out, but the outer brackets may not be omitted.)
Step 8. Copy the balance formula to another cell in column G (Balance column)
Right-click cell G3 and select "Copy" from the popup menu. After that, scroll down to the cell below it. Right-click the selected cell, and select "Paste" from the popup menu to paste the formula in the selected cell. (In Excel 2010, choose the "Paste" or "Paste Formulas" option in the popup menu.) The formula will update the cell references automatically to indicate the cost, revenue, and date references (if used) of the current row and the reference balance of the row above the current row.
Step 9. Save the worksheet
Give your worksheet a clear name, for example “Cost Monitoring.xls” or “Personal Budget.xls”. You can enter the name and year of the file, the same as the method for creating a worksheet with the. (Again, you don't need to use quotes as we wrote them just to clarify the example file names. You don't even need to type the file extension type as Excel will write it down automatically.)
Excel 2003 and earlier versions save worksheets in the ".xls" format, while Excel 2007 and 2010 save worksheets in the more recent XML-based format ".xlsx". However, the latest Excel also has the option to save files in the ".xls" format. If you have multiple computers and plan to save this worksheet on all of them, use the old format if your computer has Excel 2003 or earlier. Use the latest format if all your computers have at least Excel 2007
Tips
- Use " AutoComplete " in the expense and income categories so that the spelling entered is correct.
- You can use bold, text color, or cell shading to distinguish between unpaid, paid, and future costs.
- To prevent formulas or column headings from accidentally changing, we recommend protecting cells from changing. Select the cells you want to replace (date, payee category, expense, income, and memo), and unlock those cells, then apply protection to the entire worksheet.
