Structured references make it much easier and more intuitive to work with table data when you are using formulas that reference a table, either portions of a table or the entire table. They are especially useful because table data ranges often change, and the cell references for structured references adjust automatically. This minimizes the need to rewrite formulas as rows and columns are added and deleted in a table, or when external data is refreshed.

You are watching: Which of the following is an unqualified structured reference

This structured reference is easier to understand:Than this cell reference:=SUM(DeptSales)=Sum(C2:C7)
In this article The Department Sales table example Components of a structured reference Table names and column specifiers Reference operators Special item specifiers Qualifying structured references in calculated columns Examples of using structured references Working with structured references Structured reference syntax rules

The Department Sales table example

The following is an example, referenced throughout this article, of a table that is based on sales in a department of six employees with the latest sales amounts and commissions.A Department Sales table
*
The entire table (A1:E8) The table data (A2:E8) A column and column header (D1:D8) A calculated column (E1:E8) The Totals row (A8:E8)

Components of a structured reference

To work with tables and structured references effectively, you need to understand how to create the syntax of structured references when you are creating formulas. The components of a structured reference are illustrated in the following example of a formula that adds up total sales amounts and commission amounts:
*
A table name is a meaningful name that you provide to reference the actual table data (excluding the headers row and totals row, if any). A column specifier is derived from the column header, enclosed in brackets, and references the column data (excluding the column header and total, if any). A special item specifier is a way to refer to specific portions of the table, such as the Totals row. The table specifier is the outer portion of the structured reference that is enclosed in square brackets following the table name. A structured reference is the entire string beginning with the table name and ending with the table specifier.

Table names and column specifiers

Each time that you insert a table, Microsoft Office Excel creates a default table name (Table1, Table2, and so on) at the global workbook level or scope. You can easily change the name to make it more meaningful to you. For example, to change Table1 to DeptSales, you can use the Edit Name dialog box. (On the Design tab, in the Properties group, edit the table name in the Table Name box.)A table name refers to the entire range of data in the table with the exception of the header and total rows. In the Department Sales table example, the table name, DeptSales, refers to the cell range A2:E7.Similar to table names, column specifiers represent references to the entire column of data with the exception of the column header and total. In the Department Sales table example, the column specifier, , refers to the cell range B2:B7, and the column specifier, , refers to the cell range D2:D7.

Reference operators

For added flexibility in specifying ranges of cells, you can use the following reference operators to combine column specifiers.This structured reference:Refers to:By using the:Which, in the Example, is cell range:=DeptSales<:>All of the cells in two or more adjacent columns: (colon) range operatorA2:B7=DeptSales,DeptSalesA combination of two or more columns, (comma) union operatorC2:C7, E2:E7=DeptSales<:> DeptSales<:>The intersection of two or more columns(space) intersection operatorB2:C7

Special item specifiers

For added convenience, you can also use special items to refer to various portions of a table, such as just the Totals row, to make it easier to refer to these portions in formulas. The following are the special item specifiers that you can use in a structured reference:This special item specifier:Refers to:Which, in the Example, is cell range:=DeptSales<#All>The entire table, including column headers, data, and totals (if any).A1:E8=DeptSales<#Data>Just the data.A2:E7=DeptSales<#Headers>Just the header row.A1:E1=DeptSales<#Totals>Just the total row. If none exists, then it returns null.A8:E8=DeptSales<#This Row>Just the portion of the columns in the current row. #ThisRow cannot be combined with any other special item specifiers. Use it to force implicit intersection behavior for the reference or to override implicit intersection behavior and refer to single values from a column. For more examples, see Examples of using structured references.A5:E5 (If the current row is 5)

Qualifying structured references in calculated columns

When you create a calculated column, you commonly use a structured reference to create the formula. This structured reference can be unqualified or fully qualified. For example, to create the calculated column called, ComAmt, that calculates the amount of commission in dollars, you can use the following formulas:Type of structured referenceExampleCommentUnqualified=*Multiplies the corresponding values from the current row.Fully qualified=DeptSales*DeptSalesMultiples the corresponding values for each row for both columns.
The general rule to follow is this: If you are using structured references within a table, such as when you create a calculated column, you can use an unqualified structured reference, but if you use the structured reference outside of the table, you need to use a fully qualified structured reference.

Examples of using structured references

There are a number of ways that you can use these special items and combine them with table names and column references as the following information shows:This structured reference:Refers to:Which, in the Example, is cell range:=DeptSales<<#All>,>All the cells in the SaleAmt column.C1:C8=DeptSales<<#Headers>,>The header of the ComPct column.C1=DeptSales<<#Totals>,>The total of the Region column. If there is no Totals row, then it returns null.B8=DeptSales<<#All>,:>All the cells in SaleAmt and ComPct.C1:D8=DeptSales<<#Data>,:>Just the data of the ComPct and ComAmt columns.D2:E7=DeptSales<<#Headers>,:>Just the headers of the columns between Region and ComPct ComAmt.B1:E1=DeptSales<<#Totals>,:>The totals of the SaleAmt through ComAmt columns. If there is no Totals row, then it returns null.C8:E8=DeptSales<<#Headers>,<#Data>,>Just the header and the data of ComPct.D1:D7=DeptSales<<#This Row>, >The cell at the intersection of the current row and the ComAmt column.E5 (if the current row is 5)

Working with structured references

Consider the following when you work with structured references.Using Formula AutoCompleteYou may find that using Formula AutoComplete is very useful when you enter structured references and to ensure the use of correct syntax. For more information, see Use Formula AutoComplete.Deciding whether to generate structured references for tables in semi-selectionBy default, when you create a formula, clicking a cell range within a table semi-selects the cells and automatically enters a structured reference, instead of the cell range in the formula. This semi-selection behavior makes it much easier to enter a structured reference. You can turn this behavior on or off by selecting or clearing the Use table names in formulas check box in the Working with formulas section of the Formulas category in the Excel Options dialog box.Converting a range to a table and a table to a rangeWhen you convert a table to a range, all cell references change to their equivalent A1 style references. When you convert a range to a table, Excel does not automatically change any cell references to this range to their equivalent table names and column references.Turning off column headersIf you turn off table column headers (On the table Design tab, in the Table Style Options group, clear Header Row), structured references that use these headers are not affected, and you can still use them in formulas.Adding or deleting columns and rows to the tableBecause table data ranges often change, the cell references for structured references adjust automatically. For example, if you use a table name in a formula to count all of the cells of data in the Department Sales table, such as =COUNTA(DeptSales) in The Department Sales table example, the returned value is 30 because the data range is A2:E7. If you then added a row of data, the cell reference automatically adjusts to A2:E8, and the new returned value is 35.Renaming a table or columnIf you rename a column or table, Excel automatically changes the use of that table and column header in all structured references that are used in the workbook.Moving, copying, and filling structured referencesAll structured references remain the same when you copy or move a formula that uses a structured reference.When you fill a formula, fully qualified structured references can adjust the column specifiers like a series as summarized in the following table.

See more: 40% Of 25000 ? What Is 40% Of 25000

If the fill direction is:And while filling, you press:Then:Up or downNothingThere is no column specifier adjustment.Up or downCTRLColumn specifiers adjust like a series.Right or leftNoneColumn specifiers adjust like a series.Right or leftCTRLThere is no column specifier adjustment.Up, down, right, or leftSHIFTInstead of overwriting values in current cells, current cell values are moved and column specifiers are inserted.

Structured reference syntax rules