Did you ever think about how you can apply table names in formulas rather than regular cell references?
Well, Excel structured reference is the one thing that lets you convert table names into well-defined formulas. In a given table, you can use structured references that permit you to work with cell references and formulas.
For instance, you can refer to the cell range as Sales instead of referring to a cell range reference such as F2:F52. It helps you in understanding what your formula pretends. So, basically, when you use Excel structured reference in a formula, it generally opens up many other different factors. For instance, special items, column header names, and table name that refers to areas of the table.
Key Points of Excel Structured References
As we already have mentioned that table references give you a wide variety of advanced features in comparison with standard cell references. Let’s see what structured reference offers:
Easily Formed
The table cells are used when you need to insert a structured reference to the formula. For this, you don’t need to have a special syntax.
Flexible and Updated By Default
Whenever you make changes in a column such as renaming the column, you will see automatic updates in the new name while keeping the formula as it is. Furthermore, when you intend to add new rows in a table, you can add them instantly to the existing references, and thus you can easily manage to calculate the entire dataset.
Therefore, you don’t need to worry about structured references update while making changes in the Excel tables.
Useful Inside and Outside a Table
Formulas used inside and outside a table need structured references and it purely helps in finding tables in large workbooks.
Auto-Filled Formula
When you need to perform the same calculation in each table row, you don’t need to enter the formula repeatedly. You will add a formula only in one cell and you will notice all the other cells are filled by default.
Structured Reference Syntax
To add structured references in formulas, you don’t need to know anything about their syntax. On the other hand, you can get an idea about what a formula is intended to do by using this syntax. Generally, a string is used to signify a structured reference that starts with a table name and ends with a column specifier.
Suppose we need to work on the formula that adds the total of the South and North columns in the table named Regions:
Below are three components used in reference:
- Table name
- Item specifier
- Column specifier
Choose the formula cell and click on the formula bar to check what cells are intended to do. The referenced table cells will be highlighted in Excel.
In the table name, you will find only data except for total rows and header rows. Click on the Table Design tab when you need to add a name. On the other hand, the column specifier indicates the corresponding column except for the header row and total row. You will see the column name appears between brackets like [D2] and in the case when you are using syntax it would be as [D2]:[D3].
You can use an item specifier when you need to highlight specific parts of a table. Below is a list of variables you can use:
- [#All]: Entire table
- [#Data]: Data range
- [#Headers]
- [#Totals]: Total row
- [@Column_Name]: Current row
Reference operators
Using the following operators you can easily merge different specifiers as well as add them with resilience to structured references.
Range operator (colon)
A colon is used for normal range references that refer to two or more adjacent columns in a table. For instance, the following formula will add the numbers given in all the columns between South and East.
=SUM(Regions[[South]:[East]])
Union operator (coma)
Using commas, you can separate the column specifiers that refer to non-adjacent columns. For instance, below is the syntax to sum the data rows given in the South and West columns.
=SUM(Regions[South],Regions[West])
Intersection operator (space)
While intersecting a particular row or column, you can use the intersection operator to a cell. For instance, you can return a value of the Total row and West column at the intersection by using the following reference:
=Regions[#Totals]Regions[[#All],[West]]
Wrapping Things Up
So basically, Excel structured reference is a well-known thing to you because the entire post is loaded with data related to this specific approach. This post hopefully clears out the following things related to structured references:
- For formula creation, we don’t need special syntax.
- The formula does not get affected while modifying the header or table name.
- The formulas can be used inside as well as outside of the table.
