How to Make an Excel Spreadsheet Calculator Expand Automatically in WPS Office
Question details
The user needs a spreadsheet calculator to automatically include newly added rows when formulas currently use fixed ranges.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Maintaining a calculator where new data rows are added and formula results should update without manual range changes.
- Observed behavior
- Formulas continue referencing the original fixed range and omit newly added rows.
Open the workbook in WPS Office and identify the source range that your calculator formulas currently use. Make sure each data column has a unique header, because table structured references rely on clear column names.
Convert the Source Range to an Excel Table and Use Structured References
Best when you add new rows directly below the existing data and want formulas, formatting, and totals to update automatically.
Structured references such as SalesData[Amount] automatically include the entire table column, so formulas no longer require manual range updates.
Open the workbook in WPS Office, click any cell inside the source range, and press Ctrl+T. In the Create Table dialog, confirm the range and enable the My table has headers option if your data includes column headers, then click OK.
Go to the Table Tools tab, click Table Name, and enter a clear name such as SalesData so formulas are easier to read.
Click the formula cell and replace the fixed range with a structured reference, for example change =SUM(B2:B100) to =SUM(SalesData[Amount]). Press Enter to apply.
Define a Dynamic Named Range and Use It in the Calculator Formula
Use this when you prefer to keep the source data as a normal range and need a formula-based way to auto-expand.
Build an Auto-Expanding Spreadsheet Calculator in WPS Office
WPS Office can directly create a self-updating calculator: convert the source data into an Excel Table and use structured references, or define a dynamic named range. Your totals and other formula results expand with new rows without editing the formula.
- 1. Open your workbook in WPS Office: Launch WPS Office and open the .xlsx or .xls workbook that contains the fixed-range calculator.
- 2. Convert the source data to a table: Click a cell in the source data, press Ctrl+T, confirm the range and header option, then click OK to create the table.
- 3. Switch formulas to structured references: Select the calculator formula cell and enter =SUM(TableName[ColumnName]) to reference the entire table column automatically.
- 4. Verify with a new row: Add a new row of data directly below the table and confirm the calculator result updates immediately.
Frequently Asked Questions
Why does my Excel calculator stop adding new rows?
Because the formulas still point to a fixed range such as B2:B100. WPS Office and Excel do not automatically extend that range when new rows are inserted outside it. Convert the range to a table or use a dynamic named range.
How do I create an Excel Table in WPS Office?
Click inside the source data, press Ctrl+T, enable the My table has headers option if needed, and click OK. You can then rename the table from the Table Tools tab.
What is a structured reference in a table formula?
A structured reference is a formula reference that uses the table and column header name, such as =SUM(Table1[Amount]), so the entire column is included and new rows are added automatically.
Can I use dynamic named ranges instead of a table?
Yes. Open Formulas > Name Manager, create a new name, and use an OFFSET or INDEX formula with COUNTA to create a growing range. Then use that name in your calculator formula.
Will these methods work with .xlsx files from Microsoft Excel?
Yes. WPS Office opens and saves .xlsx and .xls files and supports Excel Tables, structured references, and the Name Manager, so the methods work across both applications.
