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.

Download Practice Workbook

You can download the practice workbook from here.

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.

Read More: How to Create a Custom Formula in Excel (A Step-by-Step Guideline)

Similar Readings

• How to Create an Excel Formula to Subtract (10 Examples)
• Apply Formula to Entire Column Without Dragging in Excel
• How to Apply Formula to Entire Column Using Excel VBA
• Create a Nested Formula in Excel (3 Easy Examples)
• How to Create a Complex Formula in Excel (with Easy Steps)

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.

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

• Furthermore, insert the following formula in the cell.

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

• Afterward, insert the following formula into the cell.

Read More: How to Create a Formula in Excel for Multiple Cells (9 Methods)

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.

• 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.

Conclusion

Henceforth, follow the above-described methods. Hopefully, these methods will help you learn how to s create a formula with structured references in excel. in excel. We will be glad to know if you can execute the task in any other way. Follow the ExcelDemy website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.

Related Articles

• How to Create a Formula in Excel That Will Place the Word Yes (7 Ways)
• Create a Formula in Excel for Multiple Sheets (4 Methods)
• How to Create a Formula to Calculate Percentage in Excel
• Create a Formula in Excel to Change Date by 1 Year (3 Methods)
• How to Create a Formula in Excel without Using a Function (6 Approaches)