Excel users can utilize texts, formulas, and double click adjustments to create a template worksheet for any home, business, or church. We will outline here how to set up a worksheet template in Microsoft Excel. This basic template can then be used for basic record keeping or modified for many other uses.
For this article we will use four words to manage our spreadsheet: Description, Expense, Deposit, and Balance. Enter the word ‘Description' at A1, ‘Expense' at F1, ‘Deposit' at H1, and ‘Balance' at J1. Continue by entering formulas into the text boxes starting with ‘Expense' in F 11, and ‘Deposit' in H 11. For F 11 enter in the formula, =sum(f2:f10) and for H11 enter =sum(h2:h10). Be sure to include the entire formula which starts with the equals sign ‘=' and ends with the last parenthesis ‘)'. A nice feature of Excel is its ability to adjust formulas when copied and pasted into another cell. In other words, if you were to enter the first formula above then copy that cell and paste it into H11, then Excel will automatically adjust the formula from F's to H's. What these formulas will do is take the numbers you will enter and automatically add the value in all cells between H2 and H10 and display the total in H11 as well as the same in the F column. If you need more space you can substitute H11 for any number of slots. For instance H2:H100 will add up from H2-H100. This is also another area where Excel will automatically adjust formulas for you. If you insert rows anywhere between your formulas range (H2 through H10 for example) then your formula in the last cell will automatically change to include all rows using the appropriate cell location. Also, it is important to note that when entering an expense to be sure to include the negative sign so it is subtracted from the total rather than added.
The next step is to create a formula to calculate your total balance of all columns. In the H13 textbox enter the formula =sum(f11:h11), what this will do is total the negative expenses and the positive deposits, creating a grand total amount. You will also want to create a beginning balance (start of the month balance) at J2. If you are using this template for a new project, then your beginning balance will be zero.
               A few last helpful ideas: In order to date your expenses and deposits simply add a column entitled ‘Date' and enter the dates as you enter transactions. You may also get words in your description moving past the edge of the description text box. This is simply cured by double clicking on the line (in between the letters) of the obstructing word. This will automatically adjust the table to fit the text. You should now have a fairly simple worksheet template that is easy to manage and that is easily modified for various record keeping needs.
For this article we will use four words to manage our spreadsheet: Description, Expense, Deposit, and Balance. Enter the word ‘Description' at A1, ‘Expense' at F1, ‘Deposit' at H1, and ‘Balance' at J1. Continue by entering formulas into the text boxes starting with ‘Expense' in F 11, and ‘Deposit' in H 11. For F 11 enter in the formula, =sum(f2:f10) and for H11 enter =sum(h2:h10). Be sure to include the entire formula which starts with the equals sign ‘=' and ends with the last parenthesis ‘)'. A nice feature of Excel is its ability to adjust formulas when copied and pasted into another cell. In other words, if you were to enter the first formula above then copy that cell and paste it into H11, then Excel will automatically adjust the formula from F's to H's. What these formulas will do is take the numbers you will enter and automatically add the value in all cells between H2 and H10 and display the total in H11 as well as the same in the F column. If you need more space you can substitute H11 for any number of slots. For instance H2:H100 will add up from H2-H100. This is also another area where Excel will automatically adjust formulas for you. If you insert rows anywhere between your formulas range (H2 through H10 for example) then your formula in the last cell will automatically change to include all rows using the appropriate cell location. Also, it is important to note that when entering an expense to be sure to include the negative sign so it is subtracted from the total rather than added.
The next step is to create a formula to calculate your total balance of all columns. In the H13 textbox enter the formula =sum(f11:h11), what this will do is total the negative expenses and the positive deposits, creating a grand total amount. You will also want to create a beginning balance (start of the month balance) at J2. If you are using this template for a new project, then your beginning balance will be zero.
               A few last helpful ideas: In order to date your expenses and deposits simply add a column entitled ‘Date' and enter the dates as you enter transactions. You may also get words in your description moving past the edge of the description text box. This is simply cured by double clicking on the line (in between the letters) of the obstructing word. This will automatically adjust the table to fit the text. You should now have a fairly simple worksheet template that is easy to manage and that is easily modified for various record keeping needs.