Formula Errors

How to Create a Running Debit and Credit Balance Formula in Excel

Adam Davis Oct 1, 2026 870 views

Question details

Calculate an automatic running balance in a spreadsheet ledger using separate columns for debit and credit values.

How to Create a Running Debit and Credit Balance Formula in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking financials in a spreadsheet ledger where positive and negative amounts are recorded in different columns.
Observed behavior: Requires a formula that seamlessly combines the starting balance with new debit entries (additions) and credit entries (subtractions) row by row.

Before you start

Verify that your ledger is organized with distinct columns for Debits (positive values) and Credits (negative values), and establish a dedicated cell for your initial starting balance.

Solution 1Recommended

Use a Direct Addition and Subtraction Formula

This is the most reliable method for calculating a running total. It adds the current row's debit to the previous row's balance and subtracts the credit.

In a standard ledger format, debits increase the total balance while credits decrease it. Setting up a relative reference formula allows you to drag the calculation down the entire ledger to automatically update totals as new entries are added.

Enter the Starting Balance

Input your initial account balance in the first cell of your balance column. For example, enter the starting balance in cell D1.

Input the Running Balance Formula

In the cell directly below the starting balance (e.g., D2), enter the formula: =D1+B2-C2 (assuming Column B contains Debits and Column C contains Credits). Press Enter to calculate the first running total.

Copy the Formula Down

Select cell D2. Click and hold the small square at the bottom-right corner of the cell (the fill handle), and drag it down the column to apply the running balance formula to the rest of your ledger.

Use a Direct Addition and Subtraction Formula

Handling Pre-Formatted Negative Credits: If your credit column already stores values as negative numbers (e.g., -100 instead of 100), you must change the formula to =D1+B2+C2. Subtracting a negative number mathematically adds it to the balance, which will result in incorrect calculations.

Simplify Ledger Management

Calculate Financial Ledgers Effortlessly in WPS Spreadsheet

WPS Spreadsheet provides powerful data tracking capabilities, fully supporting Excel's arithmetic formulas to help you manage running balances, personal budgets, and corporate ledgers with ease.

1. Open Your Ledger: Launch WPS Spreadsheet and open your financial tracking workbook.
2. Select the Target Cell: Click on the cell where you want your first running balance calculation to appear (e.g., D2).
3. Apply the Formula: Type the formula =D1+B2-C2 and hit the Enter key.
4. Drag to Auto-Fill: Use the fill handle in the bottom-right corner of the cell to drag the formula down to the rest of the rows.

100% compatible with Microsoft Excel (.xlsx) formulas and formatting.Easily apply running balance calculations across thousands of rows instantly.Lightweight, fast-loading, and completely free to download.

microsoft office alternative - wps office

Frequently Asked Questions

How do I hide the running balance if the debit and credit columns are blank?

To prevent the balance from repeating in empty rows, wrap your formula in an IF statement. Use =IF(AND(ISBLANK(B2),ISBLANK(C2)),"",D1+B2-C2). This will leave the balance cell blank unless a debit or credit is entered.

Why is my running balance formula returning a #VALUE! error?

This error occurs when the formula attempts to calculate non-numeric data. Check your debit and credit columns to ensure there is no text, hidden spaces, or incorrectly formatted currency symbols in the cells.

Can I use the SUM function to calculate a running balance instead?

Yes. You can use absolute and relative references together with the SUM function. A formula like =SUM($B$2:B2)-SUM($C$2:C2) plus your starting balance works well, though the direct addition/subtraction method is generally easier to troubleshoot.

Excel & Data Problems