Calculation Issues

How to Keep an Excel Running Total After Deleting Transactions

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to maintain an accurate running total for cumulative savings transfers in Excel, but deleting monthly transaction rows removes the underlying source data and breaks the calculation.

Product: Excel
Device & OS: not provided
Scenario: Tracking cumulative monthly savings or transaction totals where old data is routinely cleared from the primary view.
Observed behavior: Deleting old transaction rows removes the referenced source data, destroying the cumulative running total formula.

Before you start

Before modifying your spreadsheet structure, ensure you have a clear understanding of which transactions have already been processed to avoid double-counting.

Solution 1Recommended

Use a Status Column and Filter Instead of Deleting Rows

Rather than deleting old data, add a status column to categorize transactions and use filtering to hide older entries while keeping their values intact for running totals.

Deleting rows permanently removes data that formulas depend on. By utilizing a status column and Excel's filtering feature, you can keep your workspace clean while preserving the integrity of your running totals.

Add a Status Column

Insert a new column next to your transactions and name it 'Status'.

Categorize Your Transactions

Mark completed or transferred transactions with a specific label, such as 'Sent to savings', 'Cleared', or 'Archived'.

Apply a Filter

Select your table headers, go to the 'Data' tab, and click 'Filter'. Click the dropdown arrow on your new Status column.

Hide Old Entries

Uncheck the 'Sent to savings' box and click OK. The old rows will be hidden from view, but your running total formula will continue to calculate them properly.

Data Preservation: Hiding rows instead of deleting them ensures you always have a historical record if you need to audit past transactions.

Solution 2

Calculate Cumulative Totals using SUMIFS

Use the SUMIFS function to dynamically sum only the transactions that meet specific criteria, eliminating the need for a traditional running total formula that breaks when rows are altered.

Solution 3

Summarize Data with a PivotTable

Create a PivotTable to automatically group and summarize your transactions by status or date, which naturally protects against formula errors caused by deleted rows.

Manage Data Efficiently

Easily Track Running Totals with WPS Spreadsheet

WPS Spreadsheet offers powerful functions like SUMIFS, robust PivotTables, and advanced filtering tools to help you manage your financial data effortlessly without accidentally breaking your calculations.

1. Open your ledger: Launch WPS Spreadsheet and open your financial tracking document.
2. Insert a category column: Add a 'Status' column to categorize your monthly transactions instead of preparing to delete them.
3. Filter the data: Use the Filter tool under the Data tab to hide processed rows, keeping your workspace uncluttered.
4. Apply advanced functions: Utilize the built-in SUMIFS function to calculate cumulative totals based on your specific criteria.

Fully compatible with Microsoft Excel formats (.xlsx, .xls).Advanced filtering and PivotTable features for managing large transaction registers.Comprehensive formula support including SUM, SUMIF, and SUMIFS.Free, lightweight, and easy-to-use interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does deleting rows result in a #REF! error in my running total?

Deleting rows permanently removes the actual cells that your running total formula references. When the formula attempts to calculate and can no longer find the original source cell, it displays a #REF! (reference) error.

Can I permanently move old transactions to another sheet instead of filtering?

Yes, you can copy and paste old transactions into an 'Archive' sheet. Before deleting the rows in your main register, update your running total formula on the main sheet to reference the starting balance from the archived data.

How do I prevent others from accidentally deleting rows in a shared tracker?

You can protect the worksheet by navigating to the Review tab and selecting 'Protect Sheet'. You can configure the protection settings to allow users to format or filter cells but restrict them from deleting rows or columns.

Excel & Data Problems