Now all we have left to do is put everything together so it is easy to see our balances quickly. In this section, we are going to create a summary sheet to show the current balance of each of our accounts. This way you can get a “snapshot” of your finances with a quick glance.
Creating a summary sheet:
| Screenshot | Task Description |
|---|---|
| To create a new worksheet to hold our summary information, right click on the left most tab, in this case it is Checking, and select Insert. | |
| From the Insert dialog, select Worksheet and click Ok. | |
| Go ahead and rename the sheet to “Summary” and create the text entries like I have in my screenshot. This is simply to make the data easy to read. | |
| Now we are going to link the current checking balance to our summary sheet. Select the respective cell, B2, and enter an equals (=) symbol. Do not press enter yet, as we have not finished the formula, instead click on the Checking tab. | |
| On the Checking tab, click the cell containing our current balance. In my screenshot, it is cell D2. | |
Now press Enter to accept the formula. On our summary sheet, the value is now linked to cell D2, so if the value in the checking account changes, this cell will automatically update. Another thing to remember is that this value is merely a link to the Checking sheet, so attempting to change the current balance on the summary sheet will have no effect on the current balance of the checking account, in fact it would remove the link we created. Go ahead and repeat these steps for the savings account. | |
Repeat the same steps for the Credit Card tab as well, except make sure you multiply the cell value by negative one (-1). Since a credit card is a liability (i.e. money you have committed to pay but have not paid yet), it counts as a negative total against your asset balances. Revisiting why we created two identical entries when doing a credit card payment, this is how it balances out. We want to display the credit card balance as a positive on the Credit Card sheet, however we want to calculate the credit card balance as a negative on our Summary sheet. | |
| Now we need to make a slight modification to our link formulas. Since Excel is intelligent with its cell linking, if we were to add another row to our sheets, our link would get updated as well. For example, adding another transaction to our checking account would update our link cell to the updated cell location in the Checking tab of D3. To prevent this we can put a dollar ($) symbol in front of the location we want to remain absolute. So place a $ in front of the number 2 in each of the linked cells. Note we could put a $ in front of the column, D, to keep it absolute, but there is no need to in this case. | |
| Now to total everything. Select the cell D5 and then click the formula button (the “fx” button at the top). | |
| In the Insert Function dialog box select SUM and click Ok. | |
| Excel is smart enough to automatically select the cells we want to total for us in the Function Arguments dialog. No need to change this, so click Ok to set the cell value to this formula. | |
| Our total cell is now set as a formula to calculate the totals of our three accounts. |
If you add more accounts, you can simply create another row on this sheet and link the balance cell to the respective sheet’s total.
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.
