Excel table structured references become #REF! or text in Google Sheets

Excel tables let a formula say Sales[Qty] or [@Price] instead of C2:C100. Google Sheets has no table object and no structured-reference syntax. When the .xlsx opens here, those formulas do not keep working under a different name. The formula bar shows #REF!, or it shows the bracket text as something Sheets will not run. The numbers you still see are whatever the importer could freeze. Adding a row does not extend a table that no longer exists.

Sheet Error is not Microsoft and not Google. Keep the Google Sheets UI in English. A spill that arrived as values, with an empty formula bar, is Excel spilled arrays become static. This page is the bracket syntax.

Recreate it on a blank sheet

1. You need Excel for the import half. In Excel, type headers in A1 qty and B1 price. A2 2, B2 5. A3 4, B3 3. Select A1:B3, then Insert → Table, with the header row checked. Name the table Sales (Table Design → Table Name). In D1 type total_qty. In D2 enter =SUM(Sales[Qty]). D2 shows 6. In C1 type line. In C2 enter =[@Qty]*[@Price]. C2 shows 10 and the calculated column fills C3 with 12. That block is the control in Excel. The formula bar on C2 contains [@Qty], not A2.

Save and open in Sheets. Save the workbook as .xlsx. In Google Sheets, File → Import → Upload, choose the file, and import it. Click the cell that used to be the sum, then the cell that used to be the line total.

Read the formula bar. You are looking for one of these, and the bar wins over the painted number:

• The formula still contains Sales[Qty] or [@Qty]. Sheets does not implement that syntax. The cell is a Formula parse error, #NAME?, or #REF!, depending on how the importer rewrote the brackets. Leave it broken.
• The formula was rewritten to =SUM(#REF!), or the calculated column is #REF!. The table columns were not mapped onto A:B. Leave that #REF! in the bar.
• The formula bar is empty and the cell is the number 6 or 10. The import kept a value and dropped the formula. That snapshot is Excel spilled arrays become static. Do not debug it as a structured reference.

The same syntax typed in Sheets, so you can see the rejection without Excel. On a blank area, in F1 type typed. In F2 enter =SUM(Sales[Qty]). The cell does not become 6. Sheets reports a Formula parse error. Square brackets are not a column operator here. Sales is not an Excel table even if you later create a named range called Sales. Leave F2 broken.

Changing A2 does not update a #REF! sum. There is no table to resize.

What is actually wrong

• Structured references are stored on the Excel table, not as ordinary A1 addresses. Sheets imports the grid. It does not import the table. Sales[Qty] has nothing to bind to, so the importer either leaves text Sheets cannot parse or substitutes #REF!.
• [@Qty] means “this row of the Qty column.” That row context disappears with the table. A calculated column that looked automatic in Excel is one broken formula per row here, or a column of leftover values.
• A named range is not a repair by renaming. Data → Named ranges can call C2:C3 Qty. =SUM(Qty) then works. It still does not grow when you type in row 4, and it does not understand [@Qty]. See named range #REF! / #NAME? if the name itself is broken. This page is the bracket syntax the name cannot replace.
• Locale can pile on. If the imported formula also uses the wrong argument separator, you get a parse error before the bracket matters. See locale parse error. F2’s brackets are enough to fail on a United States sheet with commas.

This is not a deleted cell inside a normal formula. After a real delete, the bar contains #REF! where an address used to be, and the rest of the formula is still A1. See after you delete. Sales[Qty] was never an A1 address.

Fix

Leave the imported #REF! or parse error in place so the broken bar stays visible. Leave F2 broken.

Write A1 formulas on a clear range. In H1 type sum_qty. In H2 enter =SUM(A2:A3). H2 is 6. In I1 type line. In I2 enter =A2*B2. I2 is 10. Fill I2 down to I3. I3 is 12. Change A2 from 2 to 5. H2 and I2 follow. That is the control inside Sheets.

When the column should grow, point SUM at an open range you actually want, such as A2:A, or use FILTER on the rows that are filled. Do not expect a new row to join an Excel table. There is no table.

If the bar is empty, you have values, not a formula to translate. Retype =A2*B2. Do not hunt for a Refresh command. Sheets will not re-run the Excel table.

Do not wrap the #REF! in IFERROR. The brackets are the bug. See IFERROR hides cause.

If the formula bar still shows Sales[Qty] or [@Qty], Sheets is not going to calculate that token. Replace it with A1. If the bar is empty, the import kept a snapshot and you are on the static-spill page.