How to Create a Structured Reference in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

While working in Microsoft Excel you will find a structured reference inside an Excel table at the time of applying formulas. This is very helpful for users as you can see the reference header name of the column or row inside a formula instead of cell references. Today I am sharing with you how to create a structured reference in Excel.


Introduction to Structured Reference

A structured reference is a syntax that refers to table names instead of cell references. Using structured references in an excel table you can make your formulas dynamic. This built-in feature of Excel enables a user to understand formulas quickly and simply. After inserting a table, Excel will automatically display structured references in a cell.


How to Create a Structured Reference in Excel: 3 Simple Steps

In the following, I have explained 3 simple steps to create a structured reference in Excel. Stay tuned!


Step 1: Create a Dataset Using Proper Information

  • First, we are going to start with creating a dataset. Suppose we have some product list in our workbook.

Create a Structured Reference

  • Second, we will add some columns to visualize the sales for every month.

  • Now, after inserting the sales volume for every product our dataset is ready.

Create a Structured Reference


Step 2: Create Table from Dataset

  • Before creating the structured reference we have to insert a table.
  • In order to do that, select cells and press Ctrl+T.

Create a Structured Reference

  • A confirmation window will appear confirming to create the table.
  • Hit the OK button to continue.

  • Thus, you will get a table just like the following screenshot.

Create a Structured Reference


Step 3: Create Structured Reference in Excel

  • This time we will create a structured reference. For that, choose a cell (I6).
  • Put the formula down-
=SUM(Table2[@[January]:[June]])

Create a Structured Reference

  • As you can see, while applying the formula excel table automatically created a structured reference from the table instead of cell references.
  • Click Enter to continue.
  • Finally, the total column is filled with the “Total Sales” volume without dragging creating a structured reference.

Read More: How to Reference a Dynamic Component of a Structured Reference in Excel


Things to Remember

  • While applying a formula, you won’t be able to see the cell name instead you will get the reference column name.
  • You can not copy the structured reference formula. But you can drag the formula to a different cell.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

In this article, I have tried to cover all the methods to create a structured reference in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Wasim Akram
Wasim Akram

Wasim Akram, BSc, Industrial and Production Engineering, Ahsanullah University of Science & Technology, has been working with the ExcelDemy project for 10 months. Currently working as as Excel and VBA content developer who provides authentic solutions to different Excel-related problems and writes amazing content articles regularly. He published almost 150 articles and has many more coming. He is very passionate about learning new things about Microsoft office Suite and Data analysis.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo