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