Create a Running Balance That Stays Blank Until Data Is Entered in WPS Spreadsheets
Question details
The user needs a running balance formula that adds income, subtracts expenditure, and returns an empty cell when both income and expenditure cells are empty.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Keeping a ledger or transaction sheet clean by preventing zero balances from appearing on unused rows.
- Observed behavior
- The balance cell should remain blank until at least one input cell contains a numeric value.
Check the layout of your sheet and confirm which columns hold expenditure, income, and the previous balance. Adjust the cell references in the formula if your data does not start in columns A, B, and C.
Use IF and OR to Leave the Balance Blank Until a Numeric Input Exists
Use this formula for a simple ledger where each row has an expenditure cell, an income cell, and a running balance.
This formula checks whether either the expenditure or income cell contains a number. If neither cell contains a number, the balance cell remains blank instead of showing zero.
Click the cell where the running balance should appear, such as C2, next to the first expenditure and income row.
Type =IF(OR(ISNUMBER(A2),ISNUMBER(B2)),C1+B2-A2,"") and press Enter. Replace A2, B2, and C1 with your actual expenditure, income, and previous balance cells.
Drag the fill handle from C2 down through all rows that may receive data. Each row now checks its own A and B cells before calculating.
Use a Last Non-Blank Balance Formula for Ledger Rows with Gaps
Use this alternative if your ledger skips rows or you want the formula to continue from the last non-empty balance above the current row.
Create a Blank-Until-Data Running Balance with WPS Spreadsheets
Use WPS Spreadsheets to add a running balance formula that remains blank on unused rows. Enter income and expenditure references, copy the formula down, and keep your ledger clean without zero balances.
- 1. Open WPS Spreadsheets: Launch WPS Office, click Spreadsheets, and open the workbook that contains your ledger or create a new blank sheet.
- 2. Arrange the ledger columns: Place expenditure in column A, income in column B, and the running balance in column C, with the opening balance in C1.
- 3. Enter the blank-until-data formula: Click C2 and type =IF(OR(ISNUMBER(A2),ISNUMBER(B2)),C1+B2-A2,""). Press Enter to apply it.
- 4. Fill the formula down: Drag the fill handle from C2 down the balance column so each row calculates only when its inputs are entered.
Frequently Asked Questions
Why does my running balance show zero instead of staying blank?
Excel and WPS Spreadsheets treat empty cells as zero in arithmetic. Use =IF(OR(ISNUMBER(A2),ISNUMBER(B2)),C1+B2-A2,"") or an equivalent IF/ISBLANK formula to test for input before calculating.
Can I use this running balance formula with a different column layout?
Yes. Replace A2 with your expenditure cell, B2 with your income cell, and C1 with the previous balance cell in the column where the running balance is stored.
Will this formula work when I open an Excel file in WPS Spreadsheets?
Yes. The formula is compatible with Excel workbooks, and WPS Spreadsheets can open, edit, and save .xlsx files without changing this calculation.
What should I do if the formula returns #VALUE!?
Check that the income and expenditure cells contain numbers. If text or other non-numeric values are entered in a calculated row, the running balance can return an error.
