Calculation Issues

Fix Incorrect Excel Check Register Running Total or AutoSum

Maira Mehtab Sep 20, 2026 869 views

Question details

The running total or AutoSum in an Excel check-register template is calculating incorrectly, either displaying the wrong amount or repeating the same value down the entire column.

Product: Excel
Device & OS: not provided
Scenario: Tracking finances and transactions using a check-register template in Excel.
Observed behavior: The running total formula responds but outputs incorrect sums or continuously repeats the same balance in every subsequent row instead of calculating the dynamic running total.

Before you start

Before editing your formulas, verify that your check register is formatted as an official Excel Table (by pressing Ctrl+T) and ensure your debit and credit columns contain purely numeric values rather than text.

Solution 1Recommended

Use a Structured-Reference Formula

Replace basic SUM formulas with structured references designed specifically for Excel Tables to ensure dynamic, expanding calculations as new rows are added.

Using a structured formula ensures the running total calculates correctly based on expanding debit and credit ranges, avoiding the common pitfalls of simple AutoSum errors.

Verify table and column headers

Check the exact name of your Table (e.g., CheckRegister) from the Table Design tab, and confirm your column headers exactly match 'Credit (+)' and 'Debit (-)'.

Enter the dynamic formula

Select the first cell in your running total column and input the following formula, adjusting the table and column names if yours differ: =IF(AND([@[Debit (-)]]="",[@[Credit (+)]]=""),"",SUM(CheckRegister[[#Headers],[Credit (+)]]:[@[Credit (+)]])-SUM(CheckRegister[[#Headers],[Debit (-)]]:[@[Debit (-)]]))

Apply formula to the column

Press Enter. The Table format should automatically fill this formula down the entire column. If it does not, double-click the fill handle in the bottom-right corner of the cell.

Dynamic Expansion: Because this formula uses structured table references instead of fixed cell addresses, it will automatically adapt and calculate correctly whenever you add a new transaction row at the bottom.

Solution 2

Troubleshoot Repeating Values and Formatting

If the total repeats the exact same value down the entire column, the issue is typically caused by absolute references or incorrect calculation settings.

WPS Spreadsheet Feature

Create and Manage Check Registers Easily in WPS Spreadsheet

WPS Spreadsheet provides seamless support for structured formulas and intelligent Table formatting, making it incredibly easy to track financial records accurately without calculation errors.

1. Open a financial template: Launch WPS Office, open Spreadsheet, and search for 'Check Register' in the template library to get a pre-formatted financial tracker.
2. Format as an intelligent table: Highlight your data range and press Ctrl+T to convert it into a Table, which automatically applies structured logic to your new entries.
3. Apply dynamic formulas: Enter your running total formula. WPS Spreadsheet will automatically auto-fill the logic down the column and adjust dynamically as you log new transactions.

Full compatibility with Microsoft Excel formulas, tables, and structured references.Hundreds of free, built-in financial and check-register templates ready for immediate use.Lightweight, fast, and completely free to download and use on any operating system.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel formula copy the exact same number down the column?

This commonly occurs if your Calculation Options are set to 'Manual' instead of 'Automatic', or if your formula uses absolute cell references (containing dollar signs like $B$2) which lock the calculation to a single cell instead of adjusting for each new row.

How do I fix text formatted as numbers in my check register?

Select the affected cells, click the yellow warning icon that appears next to the selection, and choose 'Convert to Number'. Alternatively, you can use the 'Text to Columns' feature under the Data tab and simply click Finish to convert them instantly.

What are structured references in Excel Tables?

Structured references are special syntax used within Excel Tables that use table and column names (e.g., [@[Credit (+)]]) instead of standard cell addresses (like B2). This makes formulas easier to read and ensures they automatically expand when you add new rows of data.

Excel & Data Problems