How to Create an Excel Running Balance Formula to Add Deposits and Subtract Checks
Question details
The user needs a formula to accurately calculate a running balance in a check register by subtracting check amounts and adding deposit amounts across rows.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Setting up an automated financial tracker or check register where each new transaction updates the overall running balance.
- Observed behavior
- The user requires the correct syntax to establish an initial balance on the first row and a continuous running balance on subsequent rows.
Ensure your spreadsheet columns are clearly organized, with check amounts recorded in one specific column (e.g., Column D) and deposits in another (e.g., Column F).
Use the SUM Function to Calculate Running Balance
This solution uses standard cell references to calculate an opening balance and then add subsequent deposits while subtracting checks row by row.
To maintain a running balance, you need two slightly different formulas: one for the very first transaction to establish your starting balance, and a second formula for all subsequent transactions that incorporates the previous row's balance.
Select the first cell in your running balance column (e.g., cell G2). Enter the formula =SUM(F2,-D2) and press Enter. This establishes the opening balance by adding the first deposit and subtracting the first check amount.
Move to the next cell down (e.g., cell G3). Enter the formula =SUM(G2,F3,-D3) and press Enter. This calculates the new total by taking the previous balance from G2, adding the new deposit from F3, and subtracting the new check amount from D3.
Click on cell G3 to select it. Hover your mouse over the bottom-right corner of the cell until the cursor turns into a small plus sign (the fill handle). Click and drag it down the column to automatically apply the running balance calculation to all future transactions.
Manage Your Finances Seamlessly in WPS Spreadsheet
WPS Spreadsheet provides a lightweight, powerful environment for tracking finances. It handles complex formulas just like Excel, making it simple to build reliable check registers and running balances.
- 1. Open WPS Spreadsheet: Launch WPS Office and create a new blank spreadsheet for your financial tracker.
- 2. Input your register data: Organize your layout by placing check amounts in column D, deposits in column F, and leaving column G for the balance.
- 3. Enter the balance formulas: Type =SUM(F2,-D2) in G2 for the initial balance, and =SUM(G2,F3,-D3) in G3 for the running balance.
- 4. Drag to fill the column: Use the fill handle on the bottom-right of cell G3 to drag the formula down to the rest of the rows in your register.
Frequently Asked Questions
Why is my running balance showing a negative number?
A negative running balance indicates that the total checks (subtractions) are greater than the sum of the previous balance and new deposits. Review your input amounts in columns D and F to ensure no data entry mistakes were made.
How do I hide the balance on rows where I haven't entered data yet?
You can use the IF and ISBLANK functions to keep the cell blank until a transaction is entered. For example, enter =IF(AND(ISBLANK(D3),ISBLANK(F3)), "", G2+F3-D3). This leaves the balance visually empty if both the check and deposit cells are blank.
Will inserting a new row in the middle of my data break the running balance?
Yes, inserting a new row can disrupt the cell references for the running balance. If you need to add a transaction in the middle, insert the row, enter your data, and then drag the fill handle from the cell immediately above the new row down through the rest of the column to reset the formula logic.
