A structured reference is a term for using a table name in a formula instead of a normal cell reference. 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. On this sheet, I have population data for 25 states in the US, based on the 2000 and 2010 census data. I’ll convert this data to an Excel 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 select the data area of the table, Excel adds the name automatically. Usually the fastest way to use structured references is to start typing. Once I have an opening parentheses, I can type “st” and Excel will show the table name below. 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. I’ll first add a new column called “change”, then add a formula that subtracts the 2000 population from the 2010 population. Notice Excel automatically picks up the column name when I click a cell, and that column names appear enclosed in square brackets. 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. Now I’ll jump back out of the table and add a formula to return the largest percent change using the MAX function. 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. Finally, I’ll use an INDEX and MATCH formula to return the name of the state with the largest percent change. We give INDEX the state column as the array, and use the MATCH function to get a column number. As you can 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. Even better, if I paste in data for the remaining 25 states, the table expands, and the structured references immediately use the full set of data. We’ll explore structured references in more detail in upcoming videos.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
