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.

Dataset to Create a Formula with Structured References in Excel


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.

Right Clicking on the table to Create a Formula with Structured References in Excel

  • 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


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])

Inserting formula to Create a Formula with Structured References in Excel

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

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

Inserting Fomrula to Create a Formula with Structured References in Excel

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

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

Inserting Formula to Create a Formula with Structured References in Excel

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.

Final Result to Create a Formula with Structured References in Excel

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

Inserting Formula to Create a Formula with Structured References in Excel

  • 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

Zehad Rian Jim

Zehad Rian Jim

Hi there! I am Zehad Rian Jim. I graduated with a bachelor's degree in engineering from BUET. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include watching movies, tv series, and meeting new people. I also enjoy sports. My favorite sports are Cricket (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo