'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Keep Structured References in Excel Table Formulas
Question details
The user wants to ensure that Excel retains structured references (table names) instead of converting them to standard cell references when formulas are copied into a new table row.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Copying or expanding formulas into new rows within an Excel table.
- Observed behavior
- Excel occasionally inserts ordinary cell references rather than keeping the expected structured table references.
Verify that your data range is actually formatted as an official Excel Table (using Ctrl+T or Insert > Table) before troubleshooting structured references.
Enable 'Use table names in formulas' Option
Adjusting Excel's formula settings will force the application to use structured references by default when interacting with table data.
Excel handles references based on your application-level settings. If the software is defaulting to ordinary cell references inside tables, the feature that automatically generates table names has likely been disabled.
Launch Microsoft Excel and click on the 'File' tab in the top-left corner of the ribbon. Select 'Options' located at the bottom of the left-hand menu.
In the Excel Options dialog box that appears, click on 'Formulas' in the left-hand sidebar to view calculation and formula preferences.
Scroll down to locate the 'Working with formulas' section. Check the box next to 'Use table names in formulas'.
Click 'OK' to save the settings. Try creating or copying your formula into a new table row to ensure it now retains the structured reference format.
Use Structured References Easily in WPS Spreadsheet
WPS Office Spreadsheet provides full compatibility with Microsoft Excel tables, allowing you to use and retain structured references effortlessly. Simplify your data management with a lightweight, high-performance office suite.
- 1. Open Your File in WPS: Launch WPS Office and open your existing .xlsx workbook containing the table data.
- 2. Format as Table: Select your data range, navigate to the 'Home' tab, and click 'Format as Table' to ensure your data is recognized as a structured table.
- 3. Input Your Formula: Click on a cell within the table and start typing your formula. Click on other table columns to automatically generate structured references rather than standard cell addresses.
- 4. Auto-Fill Rows: Press Enter. WPS Spreadsheet will automatically apply the structured formula to the entire table column without losing the table reference format.
Frequently Asked Questions
What is a structured reference in Excel?
A structured reference is a special syntax used in Excel tables that uses table and column names (e.g., Table1[@Sales]) instead of standard cell addresses (like C2). This makes formulas much easier to read and maintain.
Why did my structured reference turn into a regular cell reference?
This typically happens if the 'Use table names in formulas' option is disabled in your Excel settings, or if the formula is accidentally dragged or copied outside the boundaries of the designated table format.
Do structured references update automatically when new rows are added?
Yes, one of the main advantages of structured references is that they automatically include new data added to the table, expanding the calculation range dynamically without requiring manual formula updates.
Can I use structured references in WPS Office Spreadsheet?
Yes, WPS Spreadsheet fully supports Excel's structured referencing system, ensuring cross-platform compatibility and seamless formula behavior when working with formatted tables.
