In this tutorial, we will explain the applications of absolute structured references in excel table formulas. One thing you should know is that **structured reference** tables and absolute structured reference tables are not the same. It is more tricky to create absolute structured references in excel table formulas. This term is also known as anchoring or closing the column references.

## Download Practice Workbook

We can download the practice workbook from here.

## What Are Absolute Structured References?

Generally, a **structured reference** is a term that refers to using a table name in an excel formula in lieu of a usual cell reference. If the table name that we are using as a reference does not change when we copy the formula to other cells, it will be considered an absolute structured reference.

## Absolute Structured Reference Syntax

The default syntax for absolute structured reference is:

**Table[[Column_1]:[Column_2]]**

Here, weâ€™ve introduced an additional and identical column reference to generate an absolute structured reference.

The syntax for absolute structured reference refers to the present row inside the table is:

**[@column1]:[@column2]**

Here, we have added the **@ **symbol before the identical column reference to attach a row reference.

## 4 Applications of Absolute Structured References in Excel Table Formulas

In this article, we will show **4 **applications of absolute structured references in an excel table formula. To make you understand better we will use the following dataset to illustrate all the applications. The following screenshot of the dataset contains sales data for **3 **months **January**, **February**, and **March **in different regions.

### 1. Apply Absolute Structured References to Excel Column

First and foremost, we will use absolute structured references to excel columns. We can use absolute structured references either in a single column or in the current row of a column.

#### 1.1 Use Absolute Structured References in a Single Column

In the first method, we will extract the sales amount for the months of **January**, **February**, and **March **only in the region **East**. We will use the absolute structured references to get the sales data in the **East **region in the following dataset.

Letâ€™s see the steps to perform this action.

**STEPS:**

- To begin with, select any cell randomly from the table range.
- In addition, go to the â€˜
**Table Design**â€™ tab and type a name in the â€˜**Table Name**â€™ field. We named the table â€˜**Sales**â€™. You can select any name based on your data. We will use this table name as a reference in the formula.

- Furthermore, select cell
**H7**. Insert the following formula in that cell:

`=SUMIF(Sales[[Region]:[Region]], $G$7, Sales[Jan])`

- Press
**Enter**. - So, with the above command, in cell
**H7**we get the total sales for**January**month in the**EastÂ**region.

- After that, drag the
**Fill Handle**tool from cell**H7**to cell**J7**. - Lastly, we get totals sales of months
**February**and**March**also for the**EastÂ**region.

#### 1.2 Refer Absolute Cell References to Current Row Inside Table

The previous example creates a reference for all the data ranges of the table. But in this application, we will use absolute references only to the current row inside the table. In the following dataset, we will just add the two columns of sales data **Jan **and **Feb **in another column.

Letâ€™s take a look at the steps of this method.

**STEPS:**

- First, select any random cell from the table range.

- Next, go to the â€˜
**Table Design**â€™ tab. Give the table a name as per your choice. We are using the name â€˜**Sales_2**â€™.

- Then, select cell
**E5**. Write down the following formula in that cell:

`=SUM([@Jan]:[@Feb])`

- Hit
**Enter**. - So, the above action returns the total amount of sales of months
**January**and**February**in cell**E5**.

- Finally, drag the
**Fill Handle**tool from cell**E5**to**E10**. This action copies the formula of cell**E5**in other cells. So, we get total sales for**January**and**February**for each region.

**NOTE:**

In the formula that we used in this method, the **@** symbol creates an absolute cell reference for the current row.

**Read More:** **How to Provide Table Reference in Another Sheet in Excel**

### 2. Sum Two Columns in Excel Using Absolute Structured References

In this method, we will use absolute structured references in excel table formulas to calculate the summation of multiple excel columns. In the following dataset, we will calculate the total sales of months **January** & **February** in cell **H8 **and **February** & **March** in cell **I8**.

Follow the below steps to perform this application.

**STEPS:**

- Firstly, select any cell from the table range.
- Secondly, go to the â€˜
**Table Design**â€™ tab. Type a name for the table in the â€˜**Table name**â€™ field. We are using â€˜**Sales_3**â€™ as the name of the table.

- Thirdly, select cell
**H8**. Input the following formula in that cell:

`=SUM(Sales_3[Jan]:Sales_3[Feb])`

- Press
**Enter**. - So, we can see the total amount of sales of months
**January**and**February**in cell**H8**.

- Furthermore, to get the total sales amount of months
**February**and**March**drag the**Fill Handle**tool horizontally to cell**I8**. - As a result, in cell
**I8**we can see the total sales amount of months**February**and**March**.

**Read More:** **How to Sort Multiple Columns of a Table with Excel VBA (2 Methods)**

**Similar Readings**

**How to Refresh Pivot Table in Excel (4 Effective Ways)****Types of Tables in Excel: A Complete Overview****How to Group Pivot Table by Month in Excel (2 Methods)****What is an Unqualified Structured Reference in Excel?****How to Use HLOOKUP with Structured Reference in Excel**

### 3. Use XLOOKUP Function to Create Absolute Structured References in Excel Table Formulas

In the third application, we will use** the XLOOKUP function** to create absolute structured references in an excel table formula. This application is similar to the first application of this article. So, if you have not read that application it will be better if you take a quick review of that.

In the following dataset, we will extract sales amounts of months **January**, **February**, and **March **for the **West **and **South **regions.

Letâ€™s see the steps to perform this application.

**STEPS:**

- In the beginning, select any cell from the table range.
- Next, go to the â€˜
**Table Design**â€™ tab. Type a name for the table in the â€˜**Table Name**â€™ text field.

- Then, insert the following formula in cell
**H7**:

`=XLOOKUP($G7,Sales[[Region]:[Region]],Sales[Jan])`

- Press
**Enter**. - So, in cell
**H7**, the above action returns the sales amount for the month of**January**in the**West.**

- Furthermore, to get the sales amount for
**January**month in the**West**region drag the**Fill Handle**downward from cell**H7**to**H8**.

- After that, drag the
**Fill Handle**tool from cell**H8**to**J8**. - Finally, the above commands return the sales amount of
**3**months for the**West**and**SouthÂ**regions.

**Read More:** **Excel Formula to Lookup Value in Table (3 Suitable Examples)**

### 4. Count Headers with Absolute Structured References in Excel Table

When we work with a table that has thousands of columns itâ€™s not possible to count the number of headers of the table one by one. To fix this problem we can use absolute structured references in excel table formulas. If you know the header of the first column and last column then we can count the number of headers in your table easily. In the following dataset, we will count the number of headers from months **January** to **February**.

Go through the following steps to execute this application.

**STEPS:**

- First of all, select a random cell from the table range.
- Next, go to the â€˜
**Table Design**â€™ tab. - In addition, input a name for the table in the â€˜
**Table Name**â€™ Field. We are using the table name â€˜**Sales_5**â€™.

- After that, select cell
**G7**and insert the following formula in that cell:

`=COUNTA(Sales_5[[#Headers],[Jan]]:Sales_5[[#Headers],[ Mar]])`

- Press
**Enter**. - In the end, in cell
**G7**, we can see that we have a total of**3**headers in our selected range.

**Read More:** **How to Get a Count in Excel Pivot Table Calculated Field**

## Issues with Absolute References in Tables

Meanwhile, there is no way to make an absolute reference directly from the table reference in a formula. When you will copy or move table references the following things can happen:

- Column references modify linking with the next column to the right if you move the formula across columns.
- On the other hand, column references do not change if you copy and paste formulas.

## Conclusion

In conclusion, this tutorial demonstrates four applications of absolute structured references in excel table formulas. Download the practice worksheet contained in this article to put your skills to the test. If you have any questions, please leave a comment in the box below. Our team will try to respond to your message as soon as possible. Keep an eye out for more inventive **Microsoft Excel** solutions in the future.

**Related Articles**

**How to Use IF Function and Structured Reference in Excel****[Fix]: Formulas Not Copying Down in Excel Table (3 Solutions)****How to Make an Excel Table Expand Automatically (3 Ways)****Lock a Structured Reference in Excel (2 Useful Methods)****How to Reference a Dynamic Component of a Structured Reference in Excel****Stop AutoFill Formula in Excel Table (2 Suitable Ways)****How to Remove Table Formula in Excel (With Quick Steps)**