A structured reference is a special syntax for referencing Excel Tables . Structured references work like regular cell references in formulas, but they are easier to read and understand. Structured references are also dynamic, and adjust automatically when data is added or removed from an Excel Table.
Excel will enter structured references automatically when you reference parts of a table with point and click. This behavior is controlled by the preference “Use table names in formulas”.
Examples
To get the total rows or columns in an Excel table:
=ROWS(table)
=COLUMNS(table)
To count or sum items in a column:
=SUM(table[amount]) // sum amount column
=COUNTA(table[item]) // count all in item column
To count visible rows in a filtered table:
=SUBTOTAL(3,table[Item]) // count visible in item column
Current row
Inside an Excel table, you’ll see the following syntax for “current row”:
=[@color] // current row in color column
Absolute references
References to single columns in a table are relative by default - when formulas are copied across columns, you’ll see the column references change.
References to multiple columns are absolute and will not change when formulas are copied.
=table[@[amt]] // relative
=table[@[amt]:[amt]] // absolute
Text to Columns is a feature in Excel that parses the text in one cell/column into many columns using a delimiter than can be adjusted. You can find the Text to Columns button on the Data tab of the ribbon, in the Data Tools group.
In the example shown, the text in column B is separated with a vertical pipe (|) character. In the Text to Columns dialog box, the pipe has been set as the custom delimiter. Notice the preview area at the bottom of the dialog, which allows you to confirm results. Press the Finish button to perform the operation.
Note: it’s also possible to parse delimited text with a formula .
