How to Show a Running Total Only When the Balance Changes in WPS Office Spreadsheet
Question details
The user wants to create a running balance column that stays blank when a row has no transaction or repeats the previous total, and shows the new total only when a charge or credit changes the balance.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- Working with a ledger or account register where charges and credits update a balance column.
- Observed behavior
- The balance column should remain blank for unchanged rows and display a new total only when a transaction changes the running balance.
Prepare your worksheet with an opening balance in a known cell such as H5, charges starting in E6, and credits starting in F6. Confirm that blank transaction rows mean both the charge and credit cells are empty.
Use an IF and SUM Formula to Display a Running Balance Only When Transactions Change
Use this formula when you need a non-dynamic running balance column that works in most spreadsheet versions and leaves blank rows until a new charge or credit appears.
This formula compares the charge and credit cells in the current row. If both are blank, the balance cell remains blank; otherwise it starts from the opening balance and applies all transactions up to that row.
Click the first balance cell where the running total should appear, for example H6, below the opening balance in H5.
Type =IF(AND(E6="",F6=""),"",$H$5-SUM($E$6:E6)+SUM($F$6:F6)) and press Enter.
Drag the fill handle from H6 down through the remaining balance rows to apply the formula to every transaction row.
Use SCAN and VSTACK for a Dynamic Array Running Total
Use this method when your version supports dynamic arrays and you want the balance results to spill automatically without dragging a fill handle.
Create a Conditional Running Balance in WPS Office Spreadsheet
You can complete this running balance task directly in WPS Office Spreadsheet using the same formula structure as Excel. WPS supports IF, SUM, AND, and dynamic array functions, so your workbook stays fully compatible with .xlsx files.
- 1. Open WPS Office Spreadsheet: Launch WPS Office and open the workbook that contains your opening balance, charges, and credits.
- 2. Select the balance cell: Click the first blank balance cell under the opening balance, such as H6.
- 3. Enter the running balance formula: Type =IF(AND(E6="",F6=""),"",$H$5-SUM($E$6:E6)+SUM($F$6:F6)) and press Enter.
- 4. Fill the formula down: Drag the fill handle down through all rows where you want the running total to appear.
- 5. Review the results: Confirm blank rows stay blank and transaction rows display the updated balance.
Frequently Asked Questions
Why does my running balance formula show blank for rows that contain only a credit or only a charge?
The IF(AND(...)) formula is designed to return blank only when both the charge and credit cells are empty in the same row. If either the charge or credit cell has a value, it calculates the balance.
Can I start the running balance from a different opening balance cell?
Yes. Replace $H$5 with the cell that holds your opening balance, and adjust the first E6 and F6 references to match the first transaction row.
Does this formula work in WPS Spreadsheets and Microsoft Excel?
Yes. The IF, SUM, and AND formula works in both WPS Office Spreadsheet and Microsoft Excel. The SCAN and VSTACK method requires dynamic array support.
What should I do if my balance column repeats the previous total instead of staying blank?
Make sure blank transaction rows have truly empty cells and not formulas returning empty strings. The IF condition checks for empty charge and credit cells before leaving the balance blank.
How do I copy the formula without changing the opening balance reference?
The $H$5 absolute reference stays fixed when you fill down, while the E6:F6 references and SUM range adjust relative to each row.
