How to Enter Bank Checking Account Transactions in Excel Register

Applies to Excel templates: "Georges Budget for Excel", "Georges Excel Checkbook Register", "Georges Excel Checkbook for Mac"

(back to All Support Topics)

 

What is an Excel checkbook register?

Similar to a paper checkbook register, an Microsoft Excel checkbook register can be used to keep track of your checking account, savings accounts, money market accounts and other bank type deposit accounts. Excel checkbook registers can save you time and potential errors that can occur in a paper checkbook register and as long have you have Microsoft Excel, you can even make your own checkbook register depending on how well you know the Excel checkbook register formulas or you can get an Excel checkbook register template already designed if you don't know Excel formulas well. Excel checkbook spreadsheet software is another option in addition to the popular personal finance software or personal budget software to manage your finances.

 

Entering bank transactions in the Excel checkbook register

• Use the navigation buttons at the top of the Registers to go to a new transaction or different sheet.
• For "Georges Budget for Excel": The New Transaction button at the top of a Register will take you to the location to start entering a new transaction unless there are error(s) noted in the Balance column or all 4000 rows have been used in the Register. Clicking the New Transaction button will first remove any applied Filters in the Register and then go to new transaction.
• For "Georges Excel Checkbook Register" and "Georges Excel Checkbook for Mac": The New Transaction button at the top of a Register will take you to the location to start entering a new transaction unless there are error(s) noted in the Balance column or all 1500 rows have been used in the Register. Clicking the New Transaction button will first remove any applied Filters in the Register and then go to new transaction.
• For "Georges Budget for Excel": When entering a transaction, four fields are always required.  The Date, Description, Category, and Amount fields are always required. If entering a split transaction, "Split" must be entered into the Rec field for the split itemizations.  See "Split Transactions" for more help. The other fields may be used as needed.

 

 

• For "Georges Excel Checkbook Register" and "Georges Excel Checkbook for Mac": When entering a transaction, three fields are always required.  The Date, Description, and Amount fields are always required. The other fields may be used as needed.

 

 

 

• Enter transactions directly into the cells by typing in the necessary information. Transactions should be entered starting from the top of the Register and then going down. As you start to enter transactions from the top of the register and going down, if you skip a row or rows and enter anything (including a blank space) below those skipped rows, than you will get that message "missing data in row?" in the balance column next to those skipped rows letting you know that you need to enter your transactions top to bottom and not skip rows in the checkbook registers.
• For "Georges Budget for Excel": If you want to start using a new register in Georges Budget for Excel that already has a balance in it from a prior account/register that you need to carryforward, then you can create a transaction for that at the top of the register in Georges Budget for Excel and assign it to the category titled  "Not Categorized " that is located in the Other Categories section of the Categories. You can give the transaction a description such as "Opening Balance Adjustment".
• For "Georges Excel Checkbook Register" and "Georges Excel Checkbook for Mac": If you want to start using a new register in Georges Excel Checkbook Register that already has a balance in it from a prior account/register that you need to carryforward, then you can create a transaction for that at the top of the register in Georges Excel Checkbook Register and give it a Description such as "Balance Carried Forward" or "Opening Balance Adjustment".
• The Insert Date at the top a Register will insert a date in the Date field. The default date that is displayed within the Insert Date dialog box is the current date based on your computers calendar. Make sure your computers calendar date and time are correct. The Insert Date dialog box also gives you the option to select another date within 30 days of the current date. Do not edit the dates in the Insert Date dialog box, just choose one of the available dates in the drop down menu which are displayed in the following format: m/d/yyyy. Press the Esc (Escape) key to cancel the Insert Date dialog box.
• Dates may also be entered directly in the cells within the Date column by typing them in using the following sample formats: m-d-yyyy or m/d/yyyy. However, the dates will be displayed as mm/dd/yyyy.
• You can add/change the category of a transaction by typing directly in the Registers Category column.
• For "Georges Budget for Excel": You can also change the category of a transaction directly in the Registers Category column by choosing another category from the drop down menu in the cell. 
• For "Georges Budget for Excel": If you try recategorize to a category name that is not in the Category List, you need to add it to the Category List first. The Category List is located in the Category List & Budget Sheet.
• The amounts in the Amount column are formatted with the US Dollar ($) signs, so it is not necessary to enter the dollar sign. 
• Transaction amounts are entered in the Amount column in the Registers.

1. Enter outflows as negative amounts. Outflow examples: purchases, withdrawals, transfers out, etc.
2. Enter inflows as positive amounts. Inflow examples: deposits, transfers in, returns, etc.

• Important: Transfers / payments from one Register account to another Register account must be manually entered in both Registers.
• The column titled "+/-" displays Green, Yellow, and Red Circle indicators describing the amounts in the column directly to the left of them. You can audit your register transactions by using the filter button in this "+/-" column heading to filter the transactions by the color of the indicator lights, thus quickly getting a list of either all positive transactions, all negative transactions, or all transactions equal to 0. You can then cross reference those transactions to the category and description fields.

1. The Green circle indicator means the amount is >0
2. The Yellow circle indicator means the amount is =0
3. The Red circle indicator means the amount is <0

• For "Georges Budget for Excel": Note: These Green, Yellow, and Red Circle indicators in the Registers function differently compared to other Green, Yellow, and Red Circle indicators in other worksheets.
• For "Georges Budget for Excel": The column titled Cleared will display a Green Check Mark indicator if the column titled Rec in the same row has an "R" in it. If the transaction is outstanding (not cleared), leave the Rec field blank (empty). See "Split Transactions" help section for when to enter "Split" in the Rec field.
• For "Georges Budget for Excel": The column titled Cleared will display an Orange Exclamation Point indicator if the column titled Rec in the same row does not have an "R" or "Split" in it.
• For "Georges Budget for Excel": The column titled Cleared will display "Split" if the column titled Rec in the same row has the word "Split" in it.
• For "Georges Excel Checkbook Register" and "Georges Excel Checkbook for Mac": The column titled Cleared will display a Green Check Mark indicator if the column titled Rec in the same row has an "R" in it. The Green Check Mark indicator in the column titled Cleared is represented by the number 10 in the AutoFilter list of numbers for the column titled Cleared.
• For "Georges Budget for Excel": In each Register, Total Outstanding equals the sum of the amounts in the Amount column that have not been marked with an "R" or "Split" in the column titled Rec.
• For "Georges Budget for Excel": In each Register, Total Cleared equals the sum of the amounts in the Amount column that have been marked with an "R" in the column titled Rec.
• For "Georges Budget for Excel": In each Register, in cell  G1, next to the words "Total Outstanding: " is a count of the total number of transactions Outstanding.  The total number of transactions Outstanding is a count of the number of transactions in each register that have not been marked with an "R" or Split" in the column titled Rec.
• For "Georges Excel Checkbook Register" and "Georges Excel Checkbook for Mac": In each Register, Total Outstanding equals the sum of the amounts in the Amount column that have not been marked with an "R" in the column titled Rec.
• For "Georges Excel Checkbook Register" and "Georges Excel Checkbook for Mac": In each Register, Total Cleared equals the sum of the amounts in the Amount column that have been marked with an "R" in the column titled Rec.
• The Balance column is a Running Balance of the Register.
• For "Georges Budget for Excel": See Account Reconciliation for info on the Filter: Show Outstanding and Clear All Filters buttons at the top of the Registers.
• For "Georges Excel Checkbook Register" and "Georges Excel Checkbook for Mac": See Account Reconciliation for info on the Rec and Clear Rec buttons at the top of the Registers.
• The error message "check balance column" will appear in cell L2 of a Register if there are error(s) in the Register that need to be fixed. The Balance column of the Register will also display the specific error(s) that need to be fixed.

 

 

(back to All Support Topics)