Keep a Chart Linked to a Fixed Cell After Inserting Rows in WPS Office Spreadsheet
Question details
The user wants a chart series in WPS Office Spreadsheet to remain linked to a specific fixed cell, such as A4, even after inserting rows above or around that cell.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user is editing a worksheet and needs to insert rows without changing which cell feeds the chart series.
- Observed behavior
- Normally, chart series references adjust automatically when rows are inserted, but the user needs the reference to stay fixed on cell A4.
Open the workbook in WPS Office Spreadsheet and note the exact worksheet name and fixed cell reference you want the chart to keep, such as Sheet1!A4. Make sure you can access the Formulas tab and the chart series you need to edit.
Use a Defined Name with INDIRECT to Lock a Chart Series to a Fixed Cell
Use this when you need a chart series to remain pointing to a fixed cell after inserting rows, without manually rebuilding the chart reference.
A defined name that uses INDIRECT stores the cell reference as text, so WPS Office Spreadsheet does not shift the reference when rows are inserted. You can then point the chart series to the defined name to keep it fixed.
Click the Formulas tab, then select Define Name to create a new defined name.
In the Name box, enter Data. In the Refers to box, enter =INDIRECT("Sheet1!A4") and confirm. Replace Sheet1 with your actual worksheet name.
Select the chart series you want to fix, open the series data or SERIES formula, and change the series reference to =Sheet1!Data using the workbook sheet name followed by the defined name.
Insert a new row above the fixed cell and verify the chart still reads from the original A4 cell.
Keep WPS Spreadsheet Charts Fixed to a Cell with Defined Names
WPS Office Spreadsheet can directly lock a chart series to a fixed cell by combining a defined name with INDIRECT and pointing the chart series to that name.
- 1. Open the Formulas tab: In WPS Office Spreadsheet, open your workbook and click the Formulas tab.
- 2. Define a fixed cell name: Click Define Name, enter Data, and set Refers to to =INDIRECT("Sheet1!A4").
- 3. Edit the chart series: Select the chart series and change the series reference to =Sheet1!Data.
- 4. Insert rows and test: Insert rows above A4 and confirm the chart keeps reading from the original cell.
Frequently Asked Questions
Why does my WPS Spreadsheet chart change its cell reference when I insert rows?
By default, chart references are relative and adjust when cells move. A defined name using INDIRECT stores the reference as text, preventing this automatic shift.
Where do I create a defined name in WPS Office Spreadsheet?
Open the Formulas tab, click Define Name or Name Manager, enter a name such as Data, and type the Refers to formula.
How do I edit an existing chart series to use the fixed defined name?
Select the chart series, open the series data or SERIES formula, and change the reference to =Sheet1!Data, using your actual sheet name.
What should I do if my worksheet name contains spaces?
Wrap the sheet name in apostrophes inside the text string, for example =INDIRECT("'My Sheet'!A4").
Does this method work when opening the workbook in Microsoft Excel?
Yes, defined names with INDIRECT are supported in Excel, so the workbook remains compatible when opened in Microsoft Excel.
