## What Is Structured References In Excel?

Structured References in Excelshow a cell referenced using the Excel table and column names. And these references adjust automatically on data modification in the dataset.

Excel Structured Referencesenables us to create dynamic formulas inside and outside the Excel tables. And they help locate Excel tables in large workbooks quickly.

For example, the table below shows a student’s scores in five subjects. Cell **B8** shows the sum of all the subject scores using the **SUM excel function **with the relative reference to the required cell range.

First, we will convert the above data range into an **Excel table** named **Score_Table**. And then, determine the sum of the subject scores using the **Structured References** to the cell range containing the scores in the **SUM()** in the target cell.

- Enter the formula
**=SUM(B2:B6)**in cell**B8**. - Enter the formula
**=SUM(Score_Table[Score Out Of 100])**in cell**F8**. - Press “
**Enter**” after entering the formula.

The output is “**478**”, as shown above, in cells **B8 **and** F8**. Therefore, in cell **F8,** we entered a formula using the table and column names to create the required **Structured References** in the **SUM()**.And in cell **B8** we entered the formula using the cell references. Cells **B9 **and** F9** are for our reference.

We can enter the function and select the relevant table and column names from the drop-down lists that Excel suggests to perform the required calculations. Otherwise, we can click the Excel table cells we require to reference in the formula. Thus, the **Structured References **do not require us to type the cell references in the formulas.

##### Table of contents

###### Key Takeaways

**Structured References in Excel**use the Excel table and column names for referencing the selected cells in place of the cell addresses.- The
**Structured References**adjust dynamically according to the changes in the Excel table, and one can utilize them inside and outside the Excel table. Thus, the users can use this reference type in dynamic formulas and quickly locate Excel tables in worksheets. - Suppose we enter a formula containing the
**Structured References**in a cell. Then, we can use the**AutoCorrect Options**button to automatically copy the formula in the remaining cells of the same column to perform the same calculations.

### How To Create Structured References In Excel?

The steps to create **Structured References in Excel** are as follows:

First, let us convert the data range into an Excel table. So, click on a cell in the data range (or select the entire data range), and follow the path **Insert** → **Table,** or press the shortcut keys **Ctrl** + **T**.

Next, the steps to obtain the **Structured References in Excel table formulas** are as follows:

- Select the target cell, enter the ‘
**=**’ sign, the required function name, and open brackets. - Enter the reference by selecting the required cell or cell range in the Excel table. Excel will show the respective column names as
**Structured References**to the chosen cell or cell range. - Close the brackets, and press
**Enter**.

[**Note:** When we use the **Structured References in Excel table formulas**, Excel automatically fills the formula entered in one cell in the remaining cells in the column. Otherwise, we can click the **AutoCorrect Options** button, which appears after we enter the formula in one target cell in the Excel table. It will show the option to update the formula in the remaining cells in the column.

Furthermore, the formula in each target cell will remain the same. But Excel will use the corresponding row data to complete the calculation in each target cell.]

On the other hand, if we must enter the formula outside the Excel table. Then, the steps to create **Structured References** in such a scenario are as follows:

- Select the target cell, enter the ‘
**=**’ sign, the required function name, and the opening parenthesis. - We must enter the Excel table name. Once we type the first letter of the name, Excel will show the table names and inbuilt functions starting with the entered alphabet. We can then double-click on the required table name.
- Enter the opening bar bracket, followed by the reference, by selecting the required cell or cell range in the Excel table. Excel will show the respective column names as
**Structured References**to the chosen cell or cell range. - Next, enter the closing bar bracket. Follow steps 2 to 3 each time we wish to create
**Structured References**to a cell or cell range in the formula. - Finally, enter a close parenthesis to complete the formula, and press
**Enter**.

But if we require only one cell range from the table, click on the formula expression after entering the Excel table name, and press the **Tab** key to execute the function.

#### Basic Example

Let us determine the average employee salary in column H and the average monthly salary in row 13 using the **Structured References Excel example**.

The table below contains the monthly salary of ten employees from Jan-Jun.

The steps to form the **absolute Structured References in Excel table formulas **and the **Structured References** outside the Excel table are:

**Select the cell range H2:H11 and B13:G13, and set the data format in Home → Number Format as Currency.****Click on a cell in the data range A1:H11, and follow the path Insert → Table, or press the keys Ctrl + T, to convert the chosen data range into an Excel table.**

The**Create Table**window opens, showing the data range to convert into an Excel table. Ensure the table headers option remains selected.**Click OK to get the required Excel table with the Design tab open. Update the Table Name field in the Design tab as, say, Emp_Salary.****Select cell H2, and entering the AVERAGE() formula =AVERAGE(**

To find the average employee salary, select the cell range from B2:G2, which will create the**Structured References**in the**AVERAGE()**, showing the table and column names for the chosen cell range.*=AVERAGE(Emp_Salary[@[Jan- Salary in $]:[Jun- Salary in $]]*

Next, close the brackets,*=AVERAGE(Emp_Salary[@[Jan- Salary in $]:[Jun- Salary in $]])***Press Enter to view the average salary of Sally Flowers in cell H2.****Hover on the AutoCorrect Options button.**

And click the down arrow in the button and the overwrite option to auto-fill the**absolute Structured References in Excel table formulas**in cells**H3:H11**.

When we enter the formulas containing the**Structured References**in the Excel table, the**Structured References**are absolute by default, as shown below.**Select cell B13, and enter the AVERAGE() formula =AVERAGE(**

Next, as we set the Excel table name as**Emp_Salary**, enter the text “**Em**”. Excel will show a list of table names starting with the entered characters, i.e.,and double-click on the table name*=AVERAGE(Em,***Emp_Salary**to choose it.

And click cell**B13**expression, i.e.,*=AVERAGE(Emp_Salary*

Next, enter the opening bar bracket*=AVERAGE(Emp_Salary[*

Excel will show a list of column headings from the**Emp_Salary**Excel table. And as we require the average monthly salary in Jan, double-click on the**Jan- Salary in $**column heading option.*=AVERAGE(Emp_Salary[Jan- Salary in $*

Next, enter the closing bar bracket and parenthesis to complete the formula.*=AVERAGE(Emp_Salary[Jan- Salary in $])***Press Enter to execute the formula and obtain the average salary in January.****Using the fill handle, drag the formulas to cells C13:G13.**

The formulas in cells**C13:G13**contain relative**Structured References**.

Furthermore, the formulas in the cell range**H2:H11**are the same as the data range to calculate the average salary of each employee in each target cell is the same.

On the other hand, as the column range to determine the average salary for each month differs, the formulas in the target cells in the range**B13:G13**are different.

### Examples

Check out the following illustrations of **Excel Structured References** to use the feature effectively.

#### Example #1

The table below shows the sales data for five branch offices of a firm. We must determine the lowest sales figures based on the given data using **Structured References.**

The steps to enter **Excel Structured References** are as follows:

**Step 1:**Click cell range**A1:B6**, and follow the path**Insert**→**Table**to convert the chosen data range into an Excel table.

The **Create Table** window will open, where we can see the selected data range reference.

Click **OK** to view the Excel table.

**Step 2:**Click a cell in the Excel table to enable the**Design**tab, and update the**Table Name**field as**BO_Sales**.

**Step 3:**Select cell**B8**, and type the**MIN excel formula***=MIN(*

Next, according to the **Structured References Excel definition**, we must type the first or first few letters of the Excel table name, **BO**.

Excel shows the table names starting with **BO** as a drop-down list.

Double-click on the required table name, **BO_Sales**, i.e., *=MIN(BO_Sales*

Next, click cell **B8** expression, and enter an opening bar bracket as *=MIN(BO_Sales[*

Excel will display a drop-down list of column names from the Excel table **BO_Sales** and a few other options to use the **BO_Sales **table data.

As we require the lowest sales figures based on column B data in the **BO_Sales **table, double-click the corresponding column name, **Sales in USD**, to select it.

*=MIN(BO_Sales[Sales in USD*

And then, enter a closing bar bracket and parenthesis to complete the formula.

*=MIN(BO_Sales[Sales in USD])*

**Step 4:**Press**Enter**to view the output in cell**B8**,**$10,000**.

The **MIN()** checks the values in the data range specified by the **Structured References** and returns the lowest value from the list to display in the target cell.

#### Example #2

The following table shows the quantities of fruits delivered each year from 2018-22. We will find the total years of data available for each fruit in column G using **Structured References in Excel**.

The steps to use the **Structured References **are as follows:

**Step 1:**Click on a cell in the given data range, and press**Ctrl**+**T**to open the**Create Table**window.

Clicking **OK** will convert the chosen data range into an Excel table with the **Design** tab enabled.

**Step 2:**Update the**Table Name**field as**Item_Table**in the**Design**tab.

**Step 3:**Select cell**G2**, and type the**COUNT excel formula**as it counts the cells containing numbers in a cell range.*=COUNT(*

Next, select the cell range **B2:F2,** as we must count the total cells containing numbers in the specified range. *=COUNT(Item_Table[@[2018 (Boxes)]:[2022 (Boxes)]]*

Then, close the brackets as, *=COUNT(Item_Table[@[2018 (Boxes)]:[2022 (Boxes)]])*

**Step 4:**Press**Enter**to view the required count in cell**G2**.

**Step 5:**Hover on the**AutoCorrect Options**icon. And using its down arrow, click the option to overwrite the cells in column G with the**COUNT()**.

Choosing the above-mentioned option will auto-fill cells **G3:G6** with the required counts in the respective target cells based on the corresponding rows’ data.

### Problems With Excel Structured References

**Structured References** help understand the formulas quickly. However, users may face issues while using them in practical scenarios. We shall see the two most common problems with **Structured References**.

#### Problem #1

Consider the table below containing monthly expenses and savings data.

The steps to calculate the total expenses and savings at the end of the respective columns, first, we will convert the data range into an Excel table are:

**Step 1:**Click on a cell in the data range and press**Ctrl**+**T**to open the**Create Table**window.

Clicking **OK** will give us the required Excel table, where we must right-click to open the context menu. Select **Table** → **Totals Row**.

**Step 2:**The above-mentioned action will insert a**Total**row at the bottom of the Excel table, with the last column total displayed.

Delete the last column total.

**Step 3:**Select cell**D9**, click the down arrow button, and select**Sum**.

The above step gives the total monthly expense value in cell **D9** by applying the formula:

*=SUBTOTAL(109,[Monthly Expenses in $])*

The formula returns the sum of the values in the visible rows of column D.

On the other hand, entering a row for displaying the total values and applying the formula **SUM([Monthly Expenses in $])** manually in cell D9 will result in a circular reference. And the output will be **$0**. However, manually entering the formula **SUM(D3:D8)** will work and give the same output, **$2,210**.

Furthermore, the **SUM()** will add the data in hidden and visible rows in column D to return the required sum value.

**Step 4:**As we require the total monthly savings in cell**E9,**select cell**D9**and press**Ctrl**+**C**to copy the cell formula.

Next, select cell **E9** and press **Ctrl **+ **V** to paste the cell **D9** formula.

The **Structured References** do not change with the cell when we copy the formula from one cell into another. And we obtain the total monthly expenses value in cell E9 as well. It is an issue with **Structured References**.

On the other hand, this does not happen when we enter a formula with a relative cell reference. We would see the cell references updating with changing target cells.

But the solution to the above problem is to select cell **D9** and drag the excel fill handle to enter the formula in the remaining target cells, in this case, cell **E9**.

We can now see that the **Structured References** are there in the monthly savings column data in the cell **E9** formula. And the function returns the correct total monthly savings, **$3,230**.

#### Problem #2

The table below shows the quarterly sales of a set of products.

The steps to calculate the total sales of **Desktops** in each quarter using **Structured References,** and display the outcome in cells **C15:F15 **are,

**Step 1:**Click on a cell in the data range, and press**Ctrl**+**T**to open the**Create Table**window.

Click **OK** to obtain the required Excel table.

**Step 2:**Clicking a cell in the Excel table or selecting the entire Excel table will show the**Design**tab in the Ribbon. Update the**Table Name**field as**Quarterly_Sales**in the**Design**tab.

**Step 3:**Select cell**C15**and start entering the**SUMIF()**as*=SUMIF(*

Next, to enter the **range**, type **Q** so that Excel shows the drop-down list of table names and functions starting with **Q**. Double-click on the **Quarterly_Sales** table name.

*=SUMIF(Quarterly_Sales*

Enter the opening bar bracket. Excel will show the Excel table column names to choose the required column data. As we must search **Desktop** in the **Product** column, we shall double-click the column name **Product** to select it.

The formula becomes: *=SUMIF(Quarterly_Sales[Product*

Next, we must enter the **criteria**. It is the absolute reference to cell **B15**, which contains the product for which we require the quarterly total sales values.

So, enter a closing bar bracket, a comma, **$B$15**, and a comma as,

*=SUMIF(Quarterly_Sales[Product],$B$15,*

Next, we must provide the **sum_range**. And for that, we should type the letter **Q** to select the table name, as explained earlier, and enter an opening bar bracket.

*=SUMIF(Quarterly_Sales[Product],$B$15,Quarterly_Sales[*

Entering the opening bar bracket will show the drop-down list of columns in the Excel table.

As we require Q1 total sales data, double-click the column name, **Q1- Sales in $**, to select it.

*=SUMIF(Quarterly_Sales[Product],$B$15,Quarterly_Sales[Q1- Sales in $*

Next, enter the closing bar bracket and parenthesis to complete the formula.

*=SUMIF(Quarterly_Sales[Product],$B$15,Quarterly_Sales[Q1- Sales in $])*

**Step 4:**Press**Enter**to view the Q1 total sales of**Desktop**in cell**C15**.

**Step 5:**Using the fill handle, implement the formula in cells**D15:F15**.

But the issue is that the **Structured References** in the formula copied cause the formula to return incorrect values in cells **D15:F15**.

The reason is that the relative **Structured References** in the **range **argument change with each target cell. But we require the **range** argument to remain the same in all the target cells. So, we should provide the absolute **Structured References** to the **Product** column in the **range** argument.

Here is how to modify the cell **C15** formula.

Enter a bar bracket after the table name and a colon (‘**:**’) after the term **[Product]** in the **range** argument. And enter an opening bar bracket, which will show the drop-down list of the Excel table columns. Double-click on the **Product** column name to select it. And enter two closing bar brackets to complete the formula.

The formula is *=SUMIF(Quarterly_Sales[[Product]:[Product]],$B$15,Quarterly_Sales[Q1- Sales in $])*

**Step 6:**Press**Enter**to view the output in cell**C15**.

**Step 7:**Using the fill handle, update the formula in cells**D15:F15**.

Thus, the **range** argument value remains the same in all the target cells. And the column to add the sales values in each quarter for **Desktop **must vary accordingly. So, the **sum_range** argument includes relative **Structured References** to the corresponding column in each target cell formula.

Thus, we get the correct **Desktop’s** total sales value in each quarter in cells **C15:F15**.

For example, the **SUMIF()** in the cell **F15** formula checks for cells containing the product **Desktop** in the **Product** column. The item is in cells **B4, B7, **and** B11.** And thus, the function adds the values in the same rows but in the **Q4- Sales in $** column (cells **G4, G7, **and** G11**) to return the Q4 total sales value of **$6,595**.

### How To Turn Off Structured References In Excel?

The steps to turn off **Structured References** in spreadsheets are as follows:

**Step 1:**Click**File**→**Options**to open the**Excel Options**window.

**Step 2:**Open the**Formulas**tab in the**Excel Options**window.

**Step 3:**Uncheck the option**Use table names in formulas**in the**Working with formulas**section in the**Formulas**tab, and click**OK**, as shown below.

### Important Things To Note

- Ensure to enter the column name twice, separated by a colon, to create an absolute
**Structured References**. - When applying a cell formula containing
**Structured References**in adjacent cells in the same row, drag the fill handle to update the formula in the required cells. - If the Excel table column names include spaces or special characters, then the
**Structured References in Excel**will include an additional bar bracket set to enclose such column names. - Ensure to turn off the
**Structured References**option in the**Excel****Options**window if we wish not to use it in our spreadsheet.

### Frequently Asked Questions (FAQs)

**1. How to use the IF function and Structured References in Excel?**

We can use the **IF** and **Structured References in Excel** in the following way. Let us see the steps with an example.

The below table contains the students’ scores in four subjects.

Suppose the requirement is to determine the pass status of each student based on their aggregate of the four subjects. Assume the cutoff to pass a student is an aggregate of 250, and we must display the status in column F.

Then, here is how to use the **Structured References** in the **IF()** in the target cells and achieve the desired information.**• Step 1: **Click on a cell in the given data range, and press **Ctrl** + **T** to open the **Create Table** window.

Clicking **OK** will result in the following Excel table, with the **Design** tab visible in the Ribbon.**• Step 2:** Let the Excel table remain selected to keep the **Design** table visible, and update the table name in the **Design** tab as **Subject_Scores**.**• Step 3:** Select cell **F3** and start entering the **IF()** formula as *=IF(SUM(*

Next, as we require the aggregate secured by the student in each row, select the cell range **B3:E3** to provide as the **SUM()** input.*=IF(SUM(Subject_Scores[@[Mathematics Score Out Of 100]:[Biology Score Out Of 100]]*

The above-mentioned selection creates Structured References to the chosen cell range.

Next, enter the closing bracket and complete the IF() formula, as shown below.

=IF(SUM(Subject_Scores[@[Mathematics Score Out Of 100]:[Biology Score Out Of 100]])>=250,”Pass”,”Fail”)

• Step 4: Press Enter to view the pass status in the target cell F3, and click the AutoCorrect Options button to update the formula in the remaining target cells F4:F12 automatically.

The **SUM()** finds the aggregate of marks, and the **IF()** checks if the determined aggregate of a student equals or is above the cutoff of 250. And if the condition holds in the target cell, the **IF()** returns the **TRUE** value, **Pass**. Otherwise, the **IF()** output is the **FALSE** value, **Fail**.

**2. Can we use Excel Structured References in conditional formatting?**

We cannot use **Excel Structured References** in conditional formatting.

**3. Why is Excel Structured References not working?**

The Excel **Structured References** may not work due to the following reasons:**• **We would have copied and pasted the formula containing **Structured References** from a cell to the adjacent cells in the same row.**• **We have used relative **Structured References** in place of absolute **Structured References**.

### Download Template

This article must help understand the **Structured References in Excel**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to Structured References In Excel. Here we create & turn off structured references, Excel table, example & downloadable excel template. You can learn more from the following articles –

## Leave a Reply