Abstract
Transcript
In this video, I’ll give a brief introduction to structured references.
Structured references are optional, and can be used with formulas both inside or outside an Excel table.
Let’s take a look at some examples.
I’ll convert this data to anExcel Table,and name the table “states”.
Now let’s build some formulas that refer to the table.
I’ll start with ROW and COLUMN counts.
These functions require only a range of cells.
Notice when I choose the data area of the table, Excel adds the name automatically.
Usually the fastest way to use structured references is to start typing.
Then I can use the arrow key to select, and use TAB to complete.
Both formulas are now using structured references.
Next, let’s add some formulas inside the table.
The table name is not needed here because it’s implied.
The @ symbol indicates “current row”.
You’ll see this symbol often when using formulas inside a table.
I’ll add another column to calculate percent change.
Again, I can start typing and Excel will autocomplete the names I need.
Notice that this reference uses both the table name and the column name.
We give INDEX the state column as the array, and use theMATCH functionto get a column number.
As you might see, structured references are easy to read.
They are also resilient and dynamic.
If I change the name of a column, nothing breaks.
The formulas are automatically updated to use the new name.
We’ll explore structured references in more detail in upcoming videos.
