# How to Create a Formula with Structured References in Excel

This tutorial will demonstrate how to create a formula with structured references in excel. When you are dealing with lots of datasets, you need to select certain cells and apply certain formulas or conditions to them. But this can be hectic sometimes. So, using structured references is a very useful alternative to that. Using this can not only ease your work but also help you to develop the work more efficiently with lower mistakes. So, it is very important to learn how to create a formula with structured references in excel.

## What Are Structured References in Excel?

Structured references are also known as table references in excel. If we use references of table names instead of using cells then, it is called structured referencing in excel. Suppose, you are using a formula, where you need to select certain cells. But selecting certain cells can be hectic sometimes. In that case, you can use the name of the particular column as a reference in the formula bar. Excel will then select the data under the name reference and will apply the formula as desired.

## Step-by-Step Procedures to Create a Formula with Structured References in Excel

Weâ€™ll use a sample dataset overview as an example in Excel to understand easily. If you follow the steps correctly, you should learn how to create a formula with structured references in excel on your own. The steps are

### Step 1: Arranging a Dataset

In this case, our goal is to arrange a dataset for creating a formula with structured references in excel. For this instance, we have the Last Name in Column B, Sales in column C, Country, Quarter, and Bonus in sequential columns D, E, and F. We will use this dataset for a better understanding of the next steps.

### Step 2: Converting Dataset into Table

Now, you want to convert the data into the table so that we can use the structured references in excel. The process of this step is.

• First, right-click on the dataset and select the option Get Data from Table/Range.

• Second, in the Create Table window, check if the data range is selected correctly or not. Then press OK.

• Last, you will get the desired table form.

### Step 3: Referencing Table Cells

After creating the table, now we are set to go for referencing the table accordingly. Our aim is to create a formula with structured references in excel. So, at first, we will check if all the names of the columns are written correctly or not and then use the names in the formula bar for reference. In this case, we will use two formulas one is the SUBTOTAL function and the other is the SUM function. In mathematical language, the subtotal is a summation of a set of numbers. But in Excel, the Subtotal feature not only calculates the sum but also computes the average, count, product, and many more operations.
To do so, it uses the SUM, COUNT, AVERAGE, MIN, MAX, and other functions. As the Subtotal feature requires grouping the dataset, you can easily find the desired information. MS Excel provides a most useful function called SUM for summation purposes. Formula with this function automatically updates with the addition or deletion of a value. It also combines the changes made to a current cell range. We will use these formulas and refer them to your desired columns. The step is.

• To begin with, insert the following formula in cell C10.
`=SUBTOTAL(109,[Sales])`

• In addition, press Enter button to get the desired result for the cell.

• Furthermore, insert the following formula in the cell.
`=SUBTOTAL(103,[Quarter])`

• Then, press Enter button to get the desired result for the cell.

• Afterward, insert the following formula into the cell.
`=SUM([Bonus])`

### Step 4: Showing the Final Result

In the final step, we want to represent our final outcome. At the very beginning, we will show the referencing in the same table, and in the second portion, we will show that, if the referencing outside or in another table can be done or not. The step is

• Firstly, press Enter button to get the desired result for the cell.

• Secondly, insert the following formula in cell C12.
`=SUM(Table134[Bonus])`

• Lastly, press Enter button to get the desired result for the cell.

## How to Turn Off Structured References in Excel

In this case, our goal is to turn off the structure references in excel. We can do this by following the below steps.

Steps:

• First, go to the File option in the Menu bar.

• Second, select the Options from More Options.

• Last, in the Excel Options window, go to Formulas > Working with formulas > untick Use table names in formulas > OK.

## Things to Remember

• The table names are the most important things here. If you spell the table name wrong or in the formula you havenâ€™t used them properly, then you wonâ€™t get the desired result.
• The structure of the formulas must be written accordingly.
• In case of referencing first decide which cells you want to use and then refer to them correctly.
• If your structured references in excel are not working then first you have to enable them by going to File>Options>Formulas>Working with formulas>tick Use table names in formulas> OK.