Excel Running Balance: Add Deposits and Subtract Withdrawals
Question details
Create a running balance that updates each row by adding deposits and subtracting withdrawals.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A ledger stores deposits in column B and withdrawals in column D, with the running balance calculated in column G.
- Observed behavior
- The user needs a line-by-line balance formula that can be copied down the ledger and optionally generated with a dynamic array.
Confirm that deposits are in column B, withdrawals are in column D, and the balance column is column G. Check the first data row, because the starting cell references must match your worksheet.
Use a row-by-row formula for the running balance
Use this reliable method when your ledger starts in row 2 or when you want each balance to reference the previous row.
The first balance is the deposit minus the withdrawal for that row. Each following balance adds the current deposit and subtracts the current withdrawal from the previous balance.
Select cell G2, or the balance cell on your first data row, and enter =B2-D2. Press Enter to calculate the opening running balance.
Select G3 and enter =G2+B3-D3. This adds the deposit in B3, subtracts the withdrawal in D3, and carries forward the balance in G2.
Select G3, drag its fill handle down the balance column, or copy and paste the formula through the remaining ledger rows.
Create a dynamic running balance with SCAN
Use this option in a modern Excel version when you want one formula to spill the complete balance sequence automatically.
Build a Running Balance in WPS Spreadsheet
WPS Spreadsheet can calculate the same ledger balance with familiar Excel-style formulas. Enter the first deposit-minus-withdrawal formula, reference the previous balance on the next row, and fill the formula down while preserving .xlsx compatibility.
- 1. Open your ledger in WPS Spreadsheet: Launch WPS Office, choose Spreadsheet, and open the workbook containing the deposit and withdrawal columns.
- 2. Enter the first balance formula: Click G2, or the first balance cell in your sheet, type =B2-D2, and press Enter.
- 3. Calculate the next row: Click G3 and type =G2+B3-D3, then press Enter to include the previous balance and the current transaction.
- 4. Fill the balance formula down: Select G3 and drag the fill handle down column G, or copy G3 and paste it into the remaining balance rows.
Frequently Asked Questions
What formula creates the first running balance?
If deposits are in column B and withdrawals are in column D, enter =B2-D2 in G2 when row 2 is the first data row.
How do I calculate the balance on later rows?
In G3, enter =G2+B3-D3. Copy or drag the formula down so each row references the previous balance and its own deposit and withdrawal.
Why are withdrawals subtracted if they are entered as positive numbers?
Ledger withdrawals are often entered as positive amounts for readability. Subtracting column D in the formula converts those amounts into deductions from the running balance.
Can I use a single dynamic array formula instead?
Yes. In a version supporting SCAN, use =LET(transactions,Deposit-Withdrawal,Accumulate,LAMBDA(a,v,a+v),SCAN(0,transactions,Accumulate)) with matching Deposit and Withdrawal ranges.
What causes a #SPILL! error with the SCAN formula?
A #SPILL! error usually means cells in the formula's output range are not empty. Clear the cells below the formula or use the row-by-row method instead.
