The article will show you how to reference a dynamic component of a structured reference in Excel. Structured Reference allows you to reference one or multiple ranges of an Excel table using the name of Column Header in it. You don’t need to use the **R1C1 **or **A1 **cell reference in a formula while using the structured reference process. Hence, it’s an easy and understandable way to reference the cells that contain data in an Excel table. The structured reference is also easily recognizable to any user when the user works on formulas with structured references in it. This article will show you how to insert a dynamic component of a structured reference and some applications of it.

**Table of Contents**Expand

## How to Reference a Dynamic Component of a Structured Reference in Excel: 4 Ways

In the dataset, you will see the name of some **Salesman **who sold some products in a day. You can also see how much **Sales **they made that day.

We will be discussing how we can reference the dynamic components of this table as a **Structured Reference**** **later in this article.

**1. Referencing a Dynamic Component of a Structured Reference in Excel with At (@) Symbol**

The **At **symbol (**@**) can be used to reference a dynamic component in an Excel table. This example will show you how to make an absolute reference from the table. Suppose, you want to reference a single piece of data from a column. Let’s go through the process below to see how to do this.

**Steps:**

- First, type the following formula in the
**5th**row of any column in your Excel sheet and hit**ENTER**.

`=Table1457[@Sales]`

You can see that the operation shows the value of **D5 **as column **D **represents the **Sales **column and we selected a cell (**F5**) which is in the **5th **row. Here, **Table1457 **is the name of the table, you can change it if you want. To do that, select any cell of the table then go to **Table Design **>> **Table Name **and put any name of your choice in it.

Keep in mind that, you will only get the values according to the row number of your Excel sheet. If you selected any cells of the **6th **row such as **F6 **or **G6 **or **H6 **etc. and used the formula above, you would have seen the value of **D6 **which is **$60.21**. The table data are ranged from **5th **to **11th **row, so selecting any cells outside this range will return a **#VALUE! **(**Error in Value**) error.

This is one way to reference a dynamic component of a structured reference by using the **At (@) **symbol.

**Read More:** **Applications of Absolute Structured References in Excel Table Formulas**

**2. Referencing Dynamic Components of a Structured Reference Without Any Symbol**

If you want to reference a table range, you can do this without any symbol. Let’s take a look at the following steps.

**Steps:**

- Suppose you want to reference the name of the salesmen. To do that, type the following formula first.

`=Table14578[Salesman]`

Here, you can see that we reference the range where the name of the salesmen was stored previously without any symbol. **Table14578 **is the name of the table. You can **change the name of the table**.

- After that, hit the
**ENTER**.

You can see the name of the salesmen after this operation. You don’t need any symbols to do this. Thus, following the process described in this section, you can easily reference multiple dynamic components of a structured reference in Excel.

**3. Structured Referencing Using Pound Symbol**

We can do the same reference which we previously showed in **Method 2** using a **Pound **(**#**) symbol. Let’s take a look at the following steps.

**Steps:**

- First, type the following formula.

`=Table145789[[#Data],[Salesman]]`

Here, you can see that we reference the range where the name of the salesmen was stored previously using the **Pound/Hash **(**#**) symbol. **Table145789 **is the name of the table. The formula will return the stored data of the **Salesman **column as we mentioned **Data **with the **Pound **(**#**) symbol.

- After that, hit
**ENTER**.

You can see the name of the salesmen after this operation.

Thus, you can easily reference multiple dynamic components of a structured reference in Excel using the** Pound** symbol.

**4. Reference Data and Header Simultaneously of a Structured Reference**

In this section, I’ll show you how to insert data from a table and the corresponding column header simultaneously in an Excel sheet. The previous methods showed only how to insert the data component of a table. Let’s go through the process below.

**Steps:**

- First, write down the formula like the following picture.

`=Table14578910[[#All],[Sales]]`

Here, you can see that we reference the range where we stored the **Sales **amount previously. The ‘**[#All]**’ refers to all the data in the table. As we mentioned the **Sales **column in the formula, it will return the data of the **Sales **column with its header. Here, **Table14578910 **is the name of the table.

- After that, hit
**ENTER**.

You can see the **Sales **data after this operation.

In addition, you can also do this similar operation by using the following process. Just type the formula anywhere on your Excel sheet and press the **ENTER **button.

`=Table14578910[[#Headers],[#Data],[Sales]]`

The formula will return the **Sales **data with the heading as we mentioned **#Headers **and **#Data **in the formula.

Thus, you can easily reference dynamic components of a structured reference with the column header.

## Common Applications of Structured Referencing in Excel

In this section, I’ll show you some examples of the application regarding structured referencing. Let’s have a look at the following portions.

**1. Application of SUM Function**

Suppose you want to determine the total **Sales **amount. To do that,

- First, write down the formula below in cell
**D12**and press**ENTER**.

`=SUM([Sales])`

The formula here uses **the SUM function **and calculates the total **Sales **as we referenced the **Sales **column in the formula. There is another advantage of using the data as a table. Click on the drop down icon in the cell where you wrote the formula. You can see other options of using different functions.

**2. Application of COUNTIF and INDIRECT Functions**

You can also use structured reference with the **COUNTIF **and **INDIRECT **functions. Let’s have a look at the following description.

- Type the formula in cell
**B12**.

`=COUNTIF(INDIRECT("Table14["&B4&"]"),"*")`

The formula will count the number of data entries in column **B**. Here we referenced the column header with the **Ampersands **(**&**) and Cell Reference (**B4**).

**Read More:** **How to Use IF Function and Structured Reference in Excel**

**3. Application of Arithmetic Formula**

We can also make an arithmetic formula to calculate the average sales amount per quantity done by the salesman using the structured reference. Let’s get to the process below.

- First, make a column to calculate the average and write down the formula in cell
**E5**.

`=[@Sales]/[@[Sales Qty]]`

The formula will automatically fill the cells in the corresponding column. Here we used the **At **(**@**) symbol to reference dynamic components of the structured reference.

## Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own.

**Download Practice Workbook**

## Conclusion

Suffice to say, you will learn some basic ideas of referencing a dynamic component of a structured reference in Excel after reading this article. If you have any better suggestions or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.

## Related Articles

**How to Lock a Structured Reference in Excel****What is an Unqualified Structured Reference in Excel?****How to Use HLOOKUP with Structured Reference in Excel**

**<< Go Back to Structured Reference | Table Formula | Excel Table | Learn Excel**