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.


Download Practice Workbook

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


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.


3 Simple Steps to Create a Structured Reference in Excel

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.

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. We, the ExcelDemy team, are always responsive to your queries. Stay tuned and keep learning.


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo