Excel checkbook balance template

Even advanced users may not know about relative named ranges. So, even though the formula may be easy to read, most people are not going to know why or how the formula works.

Named ranges have their own set of complexities and problems , especially when it comes to duplicating or moving worksheets. Relative named ranges are not compatible with Google Sheets. Currently, Google Sheets treats all named ranges as absolute references even though the references don't show dollar signs. This can be tricky. The most important step is the very first one: selecting the correct cell prior to defining the name. That is why we place a dollar sign in front of the F.

It is also why it doesn't matter what cell in row 2 we choose in step 1. Although not necessary for making a robust running balance, you might be interested in making your formula even more readable, as shown in the image below.

The relative named ranges for deposit and withdrawal refer to columns D and E in the current row. A discussion of Scope is beyond the scope of this article, but the articles listed in the references below cover the topic well. Including the exclamation mark without the name of the worksheet is a trick I learned from Mynda Treacy that makes the relative named range work throughout the workbook.

If you have questions about how to apply these techniques or know of other ways they can be applied, feel free to comment below. Thanks for the detailed summary Jon. I like your relative named range solution. Cheers, Kevin Lehrbass. Just started using project planner template excel version. Need to change the quarterly parameters. Tim … will need more information. Please contact me via email on my Contact Us page.

I have a 2 week time sheet on a spreadsheet. I have a column to input comp time earned in that 2 week period and a column to input comp time used in that 2 week period. I have a beginning balance of accumulated comp time of 30 hours. I delete all the data and use the same time sheet for the next 2 week period. In addition to the above, it consists of the summary section. It displays all balances automatically as it contains predefined formulas. Opening Balance: This will reflect the opening balance from the Balance section.

Closing Balance: This will reflect the closing balance from the Balance section. Reconciliation Amount: This will reflect the opening balance from the Balance section.

If the reconciliation of any entry pending, this section will display it in the outstanding column. Current Balance: This cell will display the ending Balance of account as per the data entered by the user. Difference: This cell displays the difference if any. In this section, the user needs to enter the transaction details as and when they occur during the period.

You can select the type of transaction from the drop-down menu. Balance: Balance will show you the balance after each transaction by adding or deducting the deposits and withdrawals. Balance and Outstanding: No entries are to be made. These cells are given predefined formulas. The balance column shows the end balance and the Outstanding column shows the non-reconciled entries.

Click here to download the Printable version of the Checkbook Register. We thank our readers for liking, sharing, and following us on different social media platforms, especially Facebook. Select personalised ads. Apply market research to generate audience insights. Measure content performance. Develop and improve products. List of Partners vendors. Banking Banking Basics. Table of Contents Expand.

Table of Contents. How to Balance Your Account. Add Outstanding Deposits. Subtract Outstanding Payments. Do the Math. Review Transactions. Bonus Steps. By Justin Pritchard. Justin Pritchard, CFP, is a fee-only advisor and an expert on personal finance. Also, column A, which has no data and is just a spacer, should be very narrow. Hover the cursor over the border between columns D and E. The cursor will change from the arrow to the resizing cursor.

The resizing cursor looks like a cross with arrows. When you see the resizing cursor, left-click and drag your mouse to the right to make the column as wide as you want it to be. Center the register labels. Select the entire first row by left-clicking on the number 1 on the left border of the page.

This action centers all of the data in the selected cells. You will notice that the column labels are now centered in their cells. Part 2. Input some test data. In order to see the formatting changes you make, enter four rows of data into the cells.

Begin with the opening balance, and then enter three more transactions. Add three more transactions. Try to have a mix of debits such as checks written or ATM withdrawals and credits such as deposits. Notice the inconsistent formatting of the numbers in the cells.

Formatting will clean all of this up. Format the dates. Make this column display the dates in a consistent way. Excel provides different options for formatting the date. Select the one that you prefer. The data in this column should be centered. Highlight all of column C by clicking on the header for the column. Notice the test data you entered in this column. It should be centered in the cells. This should work well for these columns. Double check the sizing of the columns.

Now that you have some data in these cells, adjust the column width to make the columns wider or narrower if necessary. You can choose to show the dollar sign if you desire. You can also make your debits appear in red font if you want. Select column F. Repeat for columns H and K. To make your debits appear red, click on the header for column F to select the entire column. From the palette, click on red. Part 3. Create a formula to calculate a running balance.

Insert a formula into column K that does the math to calculate the running balance. Note that you did not need a formula in cell K2. This is where you entered your opening balance. Click on cell K3. Now click on the formula bar at the top of the spreadsheet. This is where you will type in the formula that tells the cells to perform a calculation. This tells the spreadsheet to take our opening balance cell K2 , and subtract a debit if one exists cell F3 and add a credit if one exists cell H3.

Copy the formula. Select cell K3. You should see that the running balance has been calculated in column K for all the rows of test data that you entered. Create a conditional formula to clean up the running balance column.

You could copy the above formula into cell K6. To clean this up, create a conditional formula that leaves the cell blank if no transactions have been entered, but displays a balance if they have been entered. But if cell B6 is not blank, then the balance should be calculated.

Extend the formula with AutoFill. Observe the small, dark square in the bottom right corner of the active cell. Hover over it and the cursor will change to the AutoFill cursor, which looks like a thin plus sign.