Formula Errors

How to Use Previous Row Values in Excel Table Formulas (Fix Circular References)

Muhammad Talha Oct 1, 2026 868 views

Question details

The user needs a formula to calculate an excess amount in an Excel table based on a running turnover, a fixed amount, and the previous row's result without triggering a circular reference error.

How to Calculate Excel Table Values Using the Previous Row's Result

Product: Excel
Device & OS: not provided
Scenario: Calculating running totals or excess turnover based on values from the previous row in a structured table.
Observed behavior: The user's original formula mistakenly included the table's header row in the mathematical calculation, which caused a circular reference error.

Before you start

Identify the exact cell reference for your table headers and ensure your running total formula starts explicitly on the first data row to prevent referencing text headers as numbers.

Solution 1Recommended

Use an Expanding Range with the SUM Function

Adjust your formula to subtract the sum of previous rows using an expanding range, which avoids referencing the header row directly in the row-by-row math.

When calculating values based on a previous row in an Excel table, directly referencing the cell above can sometimes include the header row, resulting in a circular reference error. Using a locked starting reference combined with a relative end reference allows you to safely deduce the previous row's totals.

Select the first data cell

Click on the first cell of your result column (e.g., cell H3) where the calculation should begin, immediately below the header.

Enter the modified IF formula

Input the formula: =IF([@[Running Turnover]]>[@[Fixed Amount]],[@[Running Turnover]]-[@[Fixed Amount]],0)-SUM($H$2:H2). Ensure that $H$2 refers to the cell immediately above your current cell (your header).

Fill the formula down the table

Press Enter. Because it is an Excel table, the formula should automatically fill down the rest of the column. If it doesn't, click and drag the fill handle at the bottom-right of cell H3 down to the last row.

Use an Expanding Range with the SUM Function

Circular Reference Resolved: By using SUM($H$2:H2), the formula treats the text header as a 0 rather than failing or looping, effectively solving the circular reference issue.

Powerful Spreadsheet Tool

Calculate Complex Table Formulas Easily with WPS Spreadsheet

WPS Spreadsheet provides robust formula processing, allowing you to easily handle structured table references, expanding ranges, and complex logical calculations without triggering unnecessary errors.

1. Open your data file in WPS Spreadsheet: Launch WPS Office, click on 'Spreadsheet', and open your existing .xlsx workbook containing the structured table.
2. Insert the expanding range formula: Navigate to the first cell of your excess column and enter the corrected IF and SUM logic, locking the header cell with absolute references (e.g., $H$2).
3. Apply across the structured table: Press Enter. WPS Spreadsheet will instantly calculate the results and auto-fill the formula down the rest of the table column accurately.

Fully compatible with Microsoft Excel formulas, .xlsx files, and structured table references.Smart error checking to help quickly identify and resolve circular reference errors.Free, lightweight, and features an intuitive interface for managing complex datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel table formula return a circular reference error?

A circular reference error occurs when a formula directly or indirectly refers to its own cell. In structured tables, referencing the entire column or accidentally looping the mathematical calculation through the table header often triggers this error.

How do I calculate a running total in an Excel table?

You can calculate a running total by using an expanding range within the SUM function, such as =SUM($A$2:A2). The first cell reference is absolute (locked with dollar signs), while the second is relative, allowing the range to expand downward as the formula is copied.

Can I reference the previous row in a structured table without using standard cell references?

Yes, you can use the OFFSET function, such as =OFFSET([@ColumnName], -1, 0). However, using expanding ranges with SUM or MAX is generally preferred, as OFFSET is a volatile function that recalculates constantly and can slow down large workbooks.

Excel & Data Problems