How To Create a Structured Reference in Excel (2 Methods)

Converting spreadsheet data to an Excel table has many benefits, including maintaining formula consistency and keeping column headers visible as you scroll. Another benefit of tables is their compatibility with structured references. By referring to parts of tables, you can quickly copy your data to other areas of your spreadsheet and create useful formulas. In this article, we explain how to create a structured reference in Excel via two methods.

Related: How To Create a Dashboard in Excel in 5 Steps (With Tips)

What is a structured reference in Excel?

A structured reference in Excel is when you refer to a table or part of a table in a formula. Structured references require the presence of Excel tables, though you may create them inside or outside of tables. With structured references, you can easily copy data to other parts of your spreadsheet. They're also useful for quickly creating formulas and calculating lots of values without dragging formulas that contain individual cell references to other cells.

Related: How To Sort Tables in Excel in 4 Steps (With Tips)

How to create a structured reference in Excel

Here are two methods for creating structure references in Excel:

How to create a structured reference within an Excel table

Here's how to create a structured reference within an Excel table:

1. Convert your data to a table

Structured references require your data to be in a table instead of just in a spreadsheet. You can convert your data into a table by ensuring the data has headers and selecting any cell in the range. Go to the "Insert" tab and select "Table" in the "Tables" group. In the "Create Table" dialog box, ensure that the program identified the correct range and check the box "My table has headers." Click "OK."

2. Create a new column

Structured references refer to entire columns rather than an individual cell, so it's important to create a new column. You can insert a new column by right-clicking on the letter of the cell to the right of where you want the new column to appear. Click "Insert" to add the new column.

3. Use structured reference syntax

Select the first cell in the column you just created. Type an equal sign and select any cell in the column you want to reference. This action causes "[@column_name]" to appear next to the equal sign. Type any other functions or operational signs to create your desired formula. You can also include additional column references by selecting the cell in the corresponding column or typing "[@column_name]". When you complete the formula, press "Enter" so that the program produces fills all cells in the column with the corresponding results.

Related: How To Merge Two Spreadsheets in Excel (With Steps and Tips)

How to create a structured reference outside of an Excel table

Here's how to create a structured reference outside of an Excel table:

1. Create a table

Though you can include structured references outside of a table, it's necessary to put the data you're referencing in a table. Create a table by adding headers to your table and selecting any cell in your data range. Open the "Insert" tab, go to the "Tables" group and select "Table."

Verify that the program identified the correct range in the "Create Table" dialog box, check the box next to "My table has headers" and click "OK." Go to the "Table Design" tab and select the "Table Name" field in the "Properties" group. Remember the program's default name for the table or create a new name.

2. Reference the table

Select any cell outside of the table and type an equal sign. Type the name of the table and select it from the drop-down list. Type two opening square brackets and use one of the following structured specifiers to indicate what part of the table you want to reference:

• #ALL – the entire table, including header and total rows

• #DATA – data rows

• #HEADERS – header row only

• #TOTALS – total row only

• #THIS ROW – cell from the same row that the formula is in

3. Add optional arguments

You may end the formula with a structured specifier. For instance, if you want to reference the "Sales" table's entire dataset outside of the table, you could type this formula: "=Sales[[#DATA]]". Alternatively, you can include an optional second argument to indicate a specific part of the structured specifier. After the first set of square brackets, type a column and include the component of the table you want to reference in a second set of square brackets. For instance, if you wanted to externally reference the "Representatives" column in the "Sales" table, you would type "=Sales[[#Data], [Representatives]]".

Related: What Is an Absolute Cell Reference and How To Create One in Excel

Examples of structured references in Excel

Here are some examples of structured references in Excel:

Example 1

A budget manager uses the "Expenses" table below to display estimated and actual expense costs. The manager wants to determine the difference between the estimated and actual costs to determine if the company stayed within its budget. The manager types the following formula into D2: "=[@Estimated]-[@Actual]". When the manager presses "Enter," the difference between the estimated and actual costs for each expense appears in column D.

	A	B	C	D
1	Expense	Estimated	Actual	Difference
2	Computer equipment	$5,000	$6,400	-$1,400
3	Insurance fees	$2,000	$1,900	$100
4	Travel costs	$7,000	$6,000	$1,000

Related: How To Use a Cell Reference in Excel (Instructions and FAQs)

Example 2

Using the same table in Example 1, the manager wants to reference only the values from the "Actual" column. They select a cell outside of the table and type the formula "=Expenses[[#Data], [Actual]]". When the manager presses "Enter," this formula displays the three values from the "Actual" column in the designated portion of the spreadsheet.

Please note that none of the companies mentioned in this article are affiliated with Indeed.