Excel Structure Reference

This tutorial introduces the basics of structured referencing in Excel and shows you how to use structured referencing in a table with examples.

When you use formulas that reference tables, whether the formula references a part of the table or the entire table, structured references make it easier and more intuitive for you to work with table data. Structured reference is very useful because the table data area often changes, structured reference to the cell reference can be automatically adjusted. This minimizes the need to rewrite formulas when adding and deleting rows and columns in a table or refreshing external data.

Table of Contents

What is table structured reference?
The benefits of structured reference in tables
Table Structured Reference Example
Other usage of table structured references
Video: Create Structure Reference in Excel
SAMPLE Files

What is table structured reference?

Some people may ask, what is the difference between a region and a table? What is the difference between table formatting and area formatting, and why do you need to structure table in Excel worksheets? Let’s answer these questions step by step.

A structured reference or table reference is a special method of referencing a table and its parts, which uses a combination of table name and column name rather than cell addresses.

For example, for a normal region reference, if you want to sum the data in cell region B4:B7, you can use the following formula.

=SUM(B4:B7)

If you want to sum the data in the Sales column of Table1, then you can use the following table-structured formula.

=SUM(Table1[Sales])

The benefits of structured reference in tables

Table structured references have the following benefits.

– Easy to create： When you want to enter a structured reference in a formula, you only need to use the same operation as a normal range reference – select the cell, no need to enter complex syntax.

– Automatic update: When you rename the header of a column, the relevant parameters in the structured reference are updated automatically to the new header. Even if you add a row, the relevant references in the calculation formula will be updated automatically to get the complete calculation result.

– Can be used inside and outside of tables: Structured references can be used for formulas inside and outside of tables, which makes it easier to create complex and large data tables.

Table Structured Reference Example

Step1: Enter the table data in cell area A3:B7, and select all cells in the range

Step2: Press the shortcut key Ctrl+T, the Create Table dialog box will pop up, click the OK button

Step3：The selected cell range will be converted into a structured table, and the table name will be entered in the Table Name text box at the top left, such as Table1

Step4: In cell D3, enter the following tabular formula to get the column name of the product column

=Table1[[#Headers],[Product]]

Step5: In cell E3, enter the following tabular formula to get the column name of the Sales column

=Table1[[#Headers],[Sales]]

Step6: Enter the following tabular formula in cell D4 to get the product name in the product column, press Enter, then drag the Auto Fill Handle to cell D7, which will get the rest of the product names.

Step7: Enter the following tabular formula in cell E4 to get the product sales in the Sales column, press Enter and drag the Auto Fill Handle to cell E7, which will get the rest of the product sales.