How to Create an Excel Check Register Balance Formula
Question details
The user needs a formula to calculate a running check register balance that adds deposits and subtracts expenses, and wants to know how to keep the balance cell blank until a new transaction is entered.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up a personal or business financial check register spreadsheet to track ongoing deposits and expenses.
- Observed behavior
- The standard running balance formula carries the last balance down through all empty rows, cluttering the spreadsheet when prepared in advance.
Ensure your spreadsheet is organized with dedicated columns for your Previous Balance, Deposits, and Expenses, and identify the exact cell references for your starting data.
Calculate a Basic Running Balance
Use simple addition and subtraction to continuously update your balance based on new deposits and expenses.
This is the most straightforward method for calculating a running balance. It takes the previous row's balance, adds any new deposit, and subtracts any new expense.
Type your initial starting balance into the first cell of your balance column (for example, cell F2).
Click on the balance cell in the next row down (e.g., F3). Type the formula: =F2+D3-E3 (assuming D3 is your deposit column and E3 is your expense column).
Press Enter to calculate the new balance for that transaction row.
Click the bottom-right corner of cell F3 and drag the fill handle down the column to apply this formula to subsequent rows. Excel will automatically adjust the row references.
Hide Future Balances in Empty Rows
Use the IF and COUNTA functions to prevent your check register from displaying repetitive balances in empty future rows.
Easily Create a Check Register in WPS Spreadsheet
WPS Spreadsheet provides powerful and intuitive formula calculation tools. It handles complex financial tracking effortlessly and allows you to build clean, automated check registers.
- 1. Set up your columns: Open WPS Spreadsheet and create headers for Date, Description, Deposit, Expense, and Balance.
- 2. Enter the starting balance: Input your initial account balance in the top row of the Balance column.
- 3. Apply the running balance formula: In the next row down, type =IF(COUNTA(D3:E3)=0,"",F2+D3-E3) and press Enter.
- 4. Fill the column: Use the fill handle to drag the formula down the column, keeping your future rows perfectly blank until needed.
Frequently Asked Questions
Why does my running balance formula show an error when I copy it down?
Errors usually occur if your column references shift incorrectly or if your deposit/expense columns contain hidden text or spaces instead of being truly blank. Ensure you entered relative references (like F2, not $F$2) for the previous balance so it updates properly as you drag the fill handle.
Can I use an Excel Table for my check register instead of copying the formula manually?
Yes. If you format your check register data as an Excel Table (select your data and press Ctrl + T), any formula you enter in the balance column will automatically be applied to new rows as you type in new transactions, eliminating the need to drag the fill handle.
How do I highlight negative balances in red automatically?
Select your Balance column, navigate to the Home tab, and click Conditional Formatting > Highlight Cells Rules > Less Than. Enter '0' in the box and select a red text or fill format from the dropdown menu to instantly flag overdrawn balances.
What formula should I use if I log deposits and expenses in a single column?
If you use a single column (e.g., Column C) where deposits are entered as positive numbers and expenses as negative numbers, your formula becomes much simpler. You just need to add the transaction to the previous balance: =F2+C3.
