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?

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.

## Other usage of table structured references

**Get the total number of rows in an Excel structured table**

=ROWS(table1)

**Get the total number of columns in an Excel structured table**

=COLUMNS(Table1)

**Sum a column in a structured table in Excel**

=SUM(Table1[Sales])

**Counting a column in a structured table in Excel**

=COUNTA(Table1[Sales])

## Video: Create Structure Reference in Excel

## SAMPLE Files

Below are sample files in Microsoft Excel that you can download for reference if you wish.