How to Fix Structured References and Merged Cells in WPS Office Spreadsheets
Question details
The user needs Excel table formulas to insert structured table references, but merged cells are blocking automatic reference creation and they need a non-merged layout that still looks centered.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Entering a formula in an Excel table and expecting a structured reference such as Table1[Column] to be inserted automatically.
- Observed behavior
- Merged cells are preventing WPS Office Spreadsheets or Excel from automatically inserting structured references to table columns.
Identify the merged cells in the table range and confirm that your formula will be entered from the first cell of that range. Keep the WPS Office Home tab available so you can switch from Merge & Center to Center Across Selection.
Unmerge Cells and Apply Center Across Selection to Restore Structured References
Use this solution whenever merged cells prevent WPS Office Spreadsheets or Excel from automatically inserting structured references in table formulas.
Structured references are inserted from the active cell in a table. Merged cells change the active range and prevent WPS Office Spreadsheets or Excel from safely identifying the table column, so replacing merged cells with Center Across Selection gives the same visual result without breaking table behavior.
Open your workbook in WPS Office Spreadsheets and select the merged range, for example C12:D12, that is affecting the formula.
On the Home tab, click the Merge & Center dropdown and choose Unmerge Cells to remove the merge.
With the range still selected, press Ctrl+1 to open Format Cells. On the Alignment tab, set Horizontal to Center Across Selection and click OK.
Click C12—the first cell of the centered range—and start the formula. Select the table column so WPS Office Spreadsheets inserts the structured reference such as Table1[Column].
Avoid merging cells in table calculation areas because merges can interfere with formulas, sorting, filtering, and automatic structured references.
Use Center Across Selection in WPS Office Spreadsheets to Keep Structured References Working
In WPS Office Spreadsheets, unmerge the problem cells and apply Center Across Selection instead. This keeps the centered visual layout while allowing Excel-compatible structured references to be inserted normally.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, open the .xlsx workbook, and go to the worksheet containing the Excel table.
- 2. Select and unmerge the problem cells: Select the merged range, click the Home tab, open the Merge & Center dropdown, and choose Unmerge Cells.
- 3. Apply Center Across Selection: With the range selected, press Ctrl+1, click the Alignment tab, set Horizontal to Center Across Selection, and click OK.
- 4. Enter the formula from the first cell: Select the first cell in the range, enter your formula, and select the table column to insert the structured reference automatically.
- 5. Save in Microsoft Excel format: Save the file as .xlsx to keep the table and structured references compatible with Microsoft Excel.
Frequently Asked Questions
Why do merged cells stop Excel from inserting structured references?
A merged range changes the active cell and prevents the table logic from recognizing a normal single-cell reference, so selecting the first cell after unmerging restores automatic insertion.
Can I keep the same visual layout without merging cells?
Yes. Select the range, press Ctrl+1, go to Alignment > Horizontal, choose Center Across Selection, and click OK. The text stays centered across the cells without creating a merged cell.
Does Center Across Selection work in WPS Office Spreadsheets and Microsoft Excel?
Yes. The option is available in both applications and produces an Excel-compatible layout, so the workbook behaves consistently in .xlsx files.
Which cell should I use for the formula after applying Center Across Selection?
Start the formula from the first cell in the range—for example, C12 in C12:D12—so WPS Office Spreadsheets or Excel can insert the correct structured table column reference.
