﻿ Use structured references in Excel table formulas

# Use structured references in Excel table formulas

When you create an Excel table, Excel assigns a name to the table, and to each column in the table. When you add formulas to an Excel table, those names can appear automatically as you enter the formula and select the cell references in the table instead of manually entering them. Here's an example of what Excel does:

 Instead of using explicit cell references Excel uses table and column names =Sum(C2:C7) =SUM(DeptSales[Sales Amount])

That combination of table and column names is called a structured reference. The names in structured references adjust whenever you add or remove data from the table.

Structured references also appear when you create a formula outside of an Excel table that references table data. The references can make it easier to locate tables in a large workbook.

To include structured references in your formula, click the table cells you want to reference instead of typing their cell reference in the formula. Let’s use the following example data to enter a formula that automatically uses structured references to calculate the amount of a sales commission.

 Sales Person Region Sales Amount % Commission Com mission Am ount Joe North 260 10% Robert South 660 15% Michelle East 940 15% Erich West 410 12% Dafna North 800 15% Rob South 900 15%
1. Copy the sample data in the table above, including the column headings, and paste it into cell A1 of a new, blank Excel worksheet.

2. To create the table, select cell A1, and press Ctrl+T.

3. Make sure the My table has headers box is checked and click OK.

4. In cell E2, type an equal sign (=), and click cell C2.

In the formula bar, the structured reference [@[Sales Amount]] appears after the equal sign.

1. Type an asterisk (*) directly after the closing bracket., and click cell D2.

In the formula bar, the structured reference [@[% Commission]] appears after the asterisk.

1. Press Enter.

Excel automatically creates a calculated column and copies the formula down the entire column for you, adjusting it for each row. For more information about formulas and calculated columns, see Use formulas in an Excel table.

## What happens when I use explicit cell references?

If you enter explicit cell references in a calculated column, it can be harder to see what the formula is calculating.

1. In your sample worksheet in Excel, click cell E2

2. In the formula bar, enter =C2*D2 and press Enter.

Notice that while Excel copies your formula down the column, it doesn't use structured references. If, for example, you add a column between the existing columns C and D, you'd have to revise your formula.

## How do I change a table name?

When you create an Excel table, Excel creates a default table name (Table1, Table2, and so on), but you can change the table name to make it more meaningful.

1. Select any cell in the Excel table to show the Table Tools on the ribbon.

2. On the Design tab, in the Table Name box, type the name you want, and press Enter.

In our example data, we used the name DeptSales.

Use the following rules for table names:

• Use v alid characters Always start a name with a letter, an underscore character (_), or a backslash (\). Use letters, numbers, periods, and underscore characters for the rest of the name.

You can’t use "C", "c", "R", or "r" for the name, because they’re already designated as a shortcut for selecting the column or row for the active cell when you enter them in the Name or Go To box.

• Don’t use c ell references Names can’t be the same as a cell reference, such as Z\$100 or R1C1.

• Don’t use a s pace to separate words Spaces can’t be used in the name. You can use no space or enter the underscore character (_) and period (.) as word separators. For example, DeptSales, Sales_Tax or First.Quarter.

• Use no more than 255 characters A table name can have up to 255 characters.

• Use unique table names Duplicate names aren’t allowed. Excel doesn’t distinguish between upper and lowercase characters in names so if you enter “Sales” but already have another name called “SALES" in the same workbook, you’ll be prompted to choose a unique name.

## Structured reference syntax rules

You can also enter or change structured references manually in the formula but to do that, it’ll help to understand the syntax of a structured reference. Let’s go over the following formula example:

=SUM(DeptSales[[#Totals],[Sales Amount]],DeptSales[[#Data],[Commission Amount]])

This formula has the following structured reference components:

• Table name:   DeptSales is a custom table name. It references the table data, without any header or total rows. You can use a default table name, such as Table1, or change it to use a custom name.

• Colum specifier:   [Sales Amount] and [Commission Amount] are column specifiers that use the names of the columns they represent. They reference the column data, without any column header or total row. Always enclose specifiers in brackets as shown.

• Item specifier:   [#Totals] and [#Data] are special item specifiers that refer to specific portions of the table, such as the total row.

• Table specifier:   [[#Totals],[Sales Amount]] and [[#Data],[Commission Amount]] are table specifiers that represent the outer portions of the structured reference. Outer references follow the table name, and you enclose them in square brackets.

• Structures reference:   (DeptSales[[#Totals],[Sales Amount]] and DeptSales[[#Data],[Commission Amount]] are structured references, represented by a string that begins with the table name and ends with the column specifier.

To create or edit structured references manually, use these syntax rules:

• Use brackets around specifiers    All table, column, and special item specifiers need to be enclosed in matching brackets ([ ]). A specifier that contains other specifiers requires outer matching brackets to enclose the inner matching brackets of the other specifiers. For example: =DeptSales[[Sales Person]:[Region]]

• All column headers are text strings    But they don’t require quotes when they’re used in a structured reference. Numbers or dates, such as 2014 or 1/1/2014, are also considered text strings. You can’t use expressions with column headers. For example, the expression DeptSalesFYSummary[[2014]:[2012]] won’t work.

Use brackets around column headers with special characters    If there are special characters, the entire column header needs to be enclosed in brackets, which means that double brackets are required in a column specifier. For example: =DeptSalesFYSummary[[Total \$ Amount]]

Here’s the list of special characters that need extra brackets in the formula:

• Tab

• Line feed

• Carriage return

• Comma (,)

• Colon (:)

• Period (.)

• Left bracket ([)

• Right bracket (])

• Pound sign (#)

• Single quotation mark (')

• Double quotation mark (")

• Left brace ({)

• Right brace (})

• Dollar sign (\$)

• Caret (^)

• Ampersand (&)

• Asterisk (*)

• Plus sign (+)

• Equal sign (=)

• Minus sign (-)

• Greater than symbol (>)

• Less than symbol (<)

• Division sign (/)

• Use an escape character for some special characters in column headers    Some characters have special meaning and require the use of a single quotation mark (') as an escape character. For example: =DeptSalesFYSummary['#OfItems]

Here’s the list of special characters that need an escape character (‘) in the formula:

• Left bracket ([)

• Right bracket (])

• Pound sign(#)

• Single quotation mark (')

Use the space character to improve readability in a structured reference    You can use space characters to improve the readability of a structured reference. For example: =DeptSales[ [Sales Person]:[Region] ] or =DeptSales[[#Headers], [#Data], [% Commission]]

It’s recommended to use one space:

• After the first left bracket ([)

• Preceding the last right bracket (]).

• After a comma.

## Reference operators

For more 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 is cell range: =DeptSales[[Sales Person]:[Region]] All of the cells in two or more adjacent columns : (colon) range operator A2:B7 =DeptSales[Sales Amount],DeptSales[Commission Amount] A combination of two or more columns , (comma) union operator C2:C7, E2:E7 =DeptSales[[Sales Person]:[Sales Amount]] DeptSales[[Region]:[% Commission]] The intersection of two or more columns (space) intersection operator B2:C7

## Special item specifiers

To refer to specific portions of a table, such as just the totals row, you can use any of the following special item specifiers in your structured references.

 This special item specifier: Refers to: #All The entire table, including column headers, data, and totals (if any). #Data Just the data rows. #Headers Just the header row. #Totals Just the total row. If none exists, then it returns null. #This Row or @ or @[Column Name] Just the cells in the same row as the formula. These specifiers can’t be combined with any other special item specifiers. Use them to force implicit intersection behavior for the reference or to override implicit intersection behavior and refer to single values from a column. Excel automatically changes #This Row specifiers to the shorter @ specifier in tables that have more than one row of data. But if your table has only one row, Excel doesn’t replace the #This Row specifier, which may cause unexpected calculation results when you add more rows. To avoid calculation problems, make sure you enter multiple rows in your table before you enter any structured reference formulas.

## Qualifying structured references in calculated columns

When you create a calculated column, you often 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 Commission Amount, that calculates the amount of commission in dollars, you can use the following formulas:

 Type of structured reference Example Comment Unqualified =[Sales Amount]*[% Commission] Multiplies the corresponding values from the current row. Fully qualified =DeptSales[Sales Amount]*DeptSales[% Commission] Multiples the corresponding values for each row for both columns.

The general rule to follow is this: If you’re 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

Here are some ways to use structured references.

 This structured reference: Refers to: Which i s cell range: =DeptSales[[#All],[Sales Amount]] All the cells in the Sales Amount column. C1:C8 =DeptSales[[#Headers],[% Commission]] The header of the % Commission column. D1 =DeptSales[[#Totals],[Region]] The total of the Region column. If there is no Totals row, then it returns null. B8 =DeptSales[[#All],[Sales Amount]:[% Commission]] All the cells in Sales Amount and % Commission. C1:D8 =DeptSales[[#Data],[% Commission]:[Commission Amount]] Just the data of the % Commission and Commission Amount columns. D2:E7 =DeptSales[[#Headers],[Region]:[Commission Amount]] Just the headers of the columns between Region and Commission Amount. B1:E1 =DeptSales[[#Totals],[Sales Amount]:[Commission Amount]] The totals of the Sales Amount through Commission Amount columns. If there is no Totals row, then it returns null. C8:E8 =DeptSales[[#Headers],[#Data],[% Commission]] Just the header and the data of % Commission. D1:D7 =DeptSales[[#This Row], [Commission Amount]] or =DeptSales[@Commission Amount] The cell at the intersection of the current row and the Commission Amount column. If used in the same row as a header or total row, this will return a #VALUE! error. If you type the longer form of this structured reference (#This Row) in a table with multiple rows of data, Excel automatically replaces it with the shorter form (@). They both work the same. E5 (if the current row is 5)

## Strategies for working with structured references

Consider the following when you work with structured references.

• Use Formula AutoComplete    You 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.

• Decide whether to generate structured references for tables in semi-selection    By default, when you create a formula, clicking a cell range within 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.

• Use workbooks with external links to Excel tables in other workbooks    If a workbook contains an external link to an Excel table in another workbook, that linked "source" workbook must be open in Excel to avoid #REF! errors in the "destination" workbook that contains the links. If you open the destination workbook first and #REF! errors appear, they will be resolved if you then open the source workbook. If you open the source workbook first, you should see no error codes.

• Convert a range to a table and a table to a range    When 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 doesn’t automatically change any cell references to this range to their equivalent table names and column references.

• Turn off column headers    If 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 aren’t affected, and you can still use them in formulas.

• Add or delete columns and rows to the table    Because 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 the data cells in a table, and you then add a row of data, the cell reference automatically adjusts.

• Rename a table or column    If 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.

• Move, copy, and fill structured references    All 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.

 If the fill direction is: And while filling, you press: Then: Up or down Nothing There is no column specifier adjustment. Up or down Ctrl Column specifiers adjust like a series. Right or left None Column specifiers adjust like a series. Right or left Ctrl Column specifiers adjust per relative reference. Up, down, right, or left Shift Instead of overwriting values in current cells, current cell values are moved and column specifiers are inserted.

Applies To: Excel 2013

﻿