Formula Errors

How to Calculate a Running Balance Formula in Excel and WPS

Chanuka Geekiyanage Sep 30, 2026 868 views

Question details

The user needs a spreadsheet formula to calculate a dynamic running balance by subtracting 'Out' values from 'In' values, carrying forward the previous balance, and keeping the balance cell blank if no new data is entered.

How to Create a Running Balance Formula for In, Out, and Balance Columns

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Tracking daily finances, inventory logs, or bank account balances using structured data columns.
Observed behavior: Without a conditional formula, the balance column repeats the last calculated balance all the way down the sheet, cluttering the view when rows do not yet have data.

Before you start

Ensure your spreadsheet is organized with clear headers in row 1 for 'In', 'Out', and 'Balance', and verify that your data entries will begin in row 2.

Solution 1Recommended

Use the IF, AND, and SUM functions to calculate a dynamic running balance

Combining these functions allows the spreadsheet to calculate the current balance only when data is present, leaving future unused rows perfectly blank for a cleaner look.

The IF and AND functions check if both the 'In' and 'Out' cells are empty. If they are, it outputs a blank string. If either contains a value, the SUM function calculates the previous balance plus the 'In' value minus the 'Out' value.

Select the first balance cell

Click on the first empty cell in your Balance column where you want the calculation to start (for example, cell C2).

Enter the conditional formula

Type the formula: =IF(AND(A2="",B2=""),"",SUM(C1,A2,-B2)) into the formula bar at the top of the screen.

Calculate the first row

Press Enter on your keyboard to apply the formula and calculate the first row's balance.

Fill the formula downward

Click the calculated cell, hover over the bottom-right corner until you see a small black crosshair, and drag it downward to fill the formula into the rest of the column.

Use the IF, AND, and SUM functions to calculate a dynamic running balance

Formula Customization: If your columns are arranged differently, simply replace A2 with your 'In' cell reference, B2 with your 'Out' cell reference, and C1 with the cell directly above your current balance.

Efficient Data Management

Calculate Running Balances Effortlessly with WPS Office

WPS Spreadsheet is a powerful, free tool that supports advanced logical and mathematical formulas. You can easily manage ledgers, track inventory, and calculate running balances with a clean, intuitive interface.

1. Download WPS Office: Get the free WPS Office suite from the official website and install it on your computer.
2. Open your ledger: Launch WPS Spreadsheet and open your existing financial tracker or create a new blank workbook.
3. Set up your columns: Label your columns for 'In', 'Out', and 'Balance' in the first row.
4. Apply the balance formula: Enter the running balance formula into the first data cell and drag the fill handle down to apply it to your entire sheet.

100% compatibility with Microsoft Excel formulas and .xlsx file formats.Built-in function library to help you easily construct complex IF, AND, and SUM statements.Lightweight software that runs quickly and smoothly on both older and newer devices.Free alternative for professional-grade spreadsheet and financial management.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my running balance formula return a #VALUE! error?

This error occurs when the formula attempts to calculate text instead of numerical values. Check that your 'In', 'Out', and previous 'Balance' cells contain only numbers or are completely empty. Avoid typing currency symbols manually; use the built-in cell formatting options instead.

How do I add a starting opening balance to this formula?

To include an opening balance, type your starting amount manually in the first row of your Balance column (e.g., cell C2). Then, input the running balance formula starting from the next cell down (C3), making sure the formula references C2 as the previous balance.

Does this running balance formula work in WPS Spreadsheet?

Yes, WPS Spreadsheet has full support for Excel's IF, AND, and SUM functions. You can copy and paste the exact same formula into WPS Spreadsheet and it will function perfectly without any modifications.

How can I automatically highlight negative balances?

You can use Conditional Formatting for this. Select your entire Balance column, navigate to the Home tab, click Conditional Formatting, choose Highlight Cells Rules, and select 'Less Than'. Type 0 in the value box and choose a red fill color to make negative numbers stand out.

Excel & Data Problems