Formula Errors

How to Change Excel Structured References to Cell References

Nimra Malik Oct 7, 2026 869 views

Question details

The user wants to stop Excel from automatically generating structured table references (e.g., =[@[Header Name]]) and revert to using standard cell references (e.g., =B3).

How to Change Structured References to Normal Cell References in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating or modifying formulas inside an Excel Table where clicking on cells auto-generates table-specific names.
Observed behavior: Excel displays structured table references instead of standard row and column cell references when selecting cells for a formula.

Before you start

Before converting your table to a range, note that doing so will remove table-specific features like automatic formatting and dynamic filtering, though your data and calculations will remain intact.

Solution 1Recommended

Convert the Excel Table to a Standard Range

The most direct way to force Excel to use standard cell references instead of structured references is to convert the formatted table back into a normal data range.

Excel uses structured references as a default behavior for formatted tables to make formulas easier to read and maintain. If you prefer traditional row and column references, removing the table formatting resolves the issue.

Select a cell in the table

Click on any active cell inside the Excel table that you want to convert.

Open the Table Design tab

Look at the top ribbon menu and click on the 'Table Design' tab (this tab only appears when a table cell is selected).

Click Convert to Range

In the Tools group on the left side of the ribbon, click the 'Convert to Range' button.

Confirm the conversion

A prompt will appear asking if you want to convert the table to a normal range. Click 'Yes' to confirm. Your formulas will now use standard references like =B3.

Convert the Excel Table to a Standard Range

Formatting Retained: Converting the table to a range keeps your cell colors and text formatting intact while removing the background table logic.

Solution 2

Disable 'Use Table Names in Formulas' in Excel Settings

If you want to keep the table features but prefer clicking cells to generate standard references, you can change your Excel calculation options.

Manage Spreadsheet Tables Easily with WPS Office

WPS Spreadsheet provides a seamless and user-friendly way to manage data tables, handle formula references, and quickly switch between structured table references and standard cell references.

1. Open your file in WPS: Launch WPS Spreadsheet and open your existing .xlsx document containing the table.
2. Select the table data: Click on any cell within the data table where the structured references are appearing.
3. Access Table Tools: Navigate to the 'Table Tools' tab that dynamically appears on the top ribbon.
4. Convert to Range: Click the 'Convert to Range' icon on the ribbon to instantly switch your table back into standard cells.

100% compatible with Microsoft Excel (.xlsx) files and table formatsEasily convert tables to standard ranges with a single clickFamiliar user interface requiring zero learning curveFree and lightweight software for daily productivity

microsoft office alternative - wps office

Frequently Asked Questions

What is a structured reference in Excel?

A structured reference is a special syntax used by Excel tables (like =[@[Header Name]]) to reference parts of a table intuitively. It replaces standard cell references like =B3 to make formulas easier to understand and automatically adapt when rows or columns are added.

Will converting a table to a range delete my data or formulas?

No. Converting a table to a range only removes the underlying table functionality (like auto-expanding ranges and filter dropdowns). Your data, visual formatting, and calculation results remain completely unchanged.

Can I manually type a standard cell reference inside an Excel table?

Yes. Even if your table is set to use structured references, you can manually type standard references (such as =C4+D4) into the formula bar. Excel will calculate them perfectly without forcing them into the structured format.

Excel & Data Problems