Now that we have set up our “skeleton” sheet, we are going to start entering data and making the data dynamic utilizing Excel formulas. In my example, I am going to demonstrate entering the data with the most recent transactions first. This means we will be inserting rows at the top of our sheet for each new entry.
Creating and using our data entry sheet:
| Screenshot | Task Description |
|---|---|
| On our first data entry column, enter a starting balance. This will be the basis for our current balance calculation. | |
To make an entry into the system, insert a row above our starting balance row by selecting row 2, right clicking the row heading and selecting the Insert option. Row 2 will now be blank and our current balance column will be moved down one row. If you do not have the entire row selected when invoking the Insert option, a dialog box will be presented asking you how you want to shift the data. Using this dialog you could choose to move only the selected column data down, but in this case we want to move the entire row down to preserve our data integrity. | |
| Now we will set the current balance formula to be calculated as the starting balance plus whatever the amount of this transaction is. To do this, select the cell D2 and then type an equals (=) symbol. Now click the cell containing the starting balance amount (D3) and notice a colored box appears around it. The text D3 appears with the same color in your cell text. This is Excel letting you know you are entering a formula as the value of this cell. Next type a plus (+) symbol and then click the amount cell for this new row we are creating. Again, you will see the cell outlined and text appear in the cell text with matching colors. Finally, press the Enter key to set the formula for the cell. | |
| For this entry, let’s assume we received a paycheck for $750. Fill in the values appropriately and notice that after you enter the value in the Amount column, your Balance is automatically calculated. Applying the formula in the step above made the D2 cell value a “dynamic” value, so if the dependency cell values (in this case the amounts in D3 and C2) change, the value in cell D2 will automatically be updated appropriately. | |
| Now to enter a new transaction, insert a blank row above the row we just created using the same procedure as before. Excel is smart enough to preserve all of our formulas so they will not break when the data is shifted down. So relative to the formula we just set up, which is now cell D3, it is calculated as D4 + C3. | |
| A paint brush icon may appear when inserting a new row. Clicking the down arrow next to it will present a dialog asking how we would like the new row formatted. Since we do not want the new row to look like our header row with bold text and underlining, choose the option to format the new row like the row below. | |
| For this transaction, we are going to enter an ATM withdrawal of $50. Be sure your withdrawal is entered as a negative number (-50). You will notice the Balance column is not automatically updated. This is because the formula which calculates this value has not been applied to our new row. Since we just want to take the formula we set up before and apply it to this new row, we can do this easily by copying the formula template. Simply click on cell D3 which contains the formula to copy, move the cursor to the bottom right of the cell until the cursor looks like a “+”. | |
| Now to copy the formula to our new row, click and drag the cursor to our target cell D2. | |
Release the mouse button and the formula is applied to our previously blank cell and the value is automatically calculated. Just to recap, our Balance column is calculated like so: | |
| To add a bit of organization to our workbook, we are going to name the tabs at the bottom to indicate the data it holds. Right click on the tab which currently reads “Sheet1″ and select Rename. | |
| You can now type into the tab, so enter “Checking” into the tab and press Enter to complete the rename process. Rename other two tabs “Savings” and “Credit Card”, respectively. |
We have now set up a useful and functional dynamic worksheet to keep track of our checking account. Of course, you can easily enter new transactions at the bottom and apply the same methodology, this was just the method I preferred. By all means use whichever you like best, just keep in mind for the remainder of this article, I will assume you set up your sheet the same way as I did.
The PCMech.com weekly newsletter has been running strong for over 8 years. Sign up to get tech news, updates and exclusive content - right in your inbox. Also get (several) free gifts.
