Four Ways to Use Structured References in Microsoft Excel

Quick Tips

• Structured references in Excel only work on tables formatted as such in the program, not on data ranges.
• Using structured references makes formulas more human-readable and dynamic.
• When formatting a table in Excel, change its name to something meaningful. Otherwise, Excel will name it Table[number], which can become confusing.
• Structured references work both inside and outside tables, can be used inside other functions, and will automatically update if headers receive new names.

Working in Excel typically revolves around finding connections between various data points. However, when inserting complicated formulas, repeatedly using relative and absolute explicit cell references (like “B7” or its variations) can only get you so far before the formula bar becomes an unreadable mess.

Structured references in Excel allow you to streamline that work by assigning names to tables and their headers. Those names can then be used as implicit cell references so Excel can automatically fetch the structured data and calculate it.

Here are some of the most common ways to use structured references in Excel.

1. Calculating Inside Tables

Since structured references only work on tables, the best way to utilize them is inside those same tables.

For example, we’ll create a simple table from B2 to F8 with sales data for a store. Note that we named the table “Sales” (see the “Table Name” in the top-left).

Let’s calculate the total for each sale:

Step 1: Click on F2 (but not on the drop-down icon). Go to “Home” then to “Insert,” and select “Insert Table Columns to the Right.” This will automatically add a new column to the table.

Step 2: Name the column G header “Total.”

Step 3: In G3, insert =[@PricePerUnit]*[@Quantity] and hit Enter. Format the cell output as needed.

The “[@PricePerUnit]” and “[@Quantity]” are references to the corresponding fields in those columns. The “@” argument before the column names means each result cell will use the references from the same table row.

To translate, the formula =[@PricePerUnit]*[@Quantity] in G3 is essentially the same as writing =$C3*$D3.

2. Fetching a Range Outside of the Table

When you want to use a structured reference in a cell outside the table, you need to preface the reference with TableName. In our previous example, using “Sales[Total]” will fetch the entire range under the header “Total” of the table “Sales.” This means you’ll get multiple values in an array that you can manipulate.

Here’s how this looks inside Excel in cell I3, provided you leave enough room for the range to spill down.

3. Summing and Partially Summing a Column

To quickly sum an entire column, you can use the “Total Row” checkmark in the “Table Design” options (under “Table Style Options”). Here’s an example of getting the totals for the “Quantity” and “Total” columns.

While the “Total” row by itself can’t be moved and will be placed at the end of the table (allowing for insertions), you can duplicate its result elsewhere:

• To get the sum of all the rows in the “Total” column, use =SUM(Sales[Total]).
• If you want to get only the sum of the visible columns, such as after filtering the table, use =SUBTOTAL(109,Sales[Total]). This formula is what the “Total Row” option in Table Format actually does in its row.

You can also get a partial sum based on a specific variable found inside the table without formatting it. For example:

• To get the sum of all the sales by Mike, you can use =SUMIF(Sales[Seller],”Mike”,Sales[Total]). In the formula, “Mike” is a string that was manually input.
• To get the sum of all products with the ID “41230”, use the following =SUMIF(Sales[ProductID],41230,Sales[Total]). Note that since the ProductID column has a “General” format, you can input the number directly.

4. Data Validation from a Table via INDIRECT

Suppose you have the previously used Sales table. You can create custom Data Validation options to make searching through the table easier. Let’s make a smaller table that will allow you to select between ProductIDs, Dates, or Sellers, and then choose any single item from those subsets to display the subtotal.

Step 1: In cell B13, create Data Validation (Data tab > Data tools > Data Validation).

Step 2: In the popup, choose “List” from the “Allow” options, then insert the values of the columns manually in the “Source” box, separated by commas. In this case, we inserted “ProductID, Seller, Date.”

Step 3: In cell C13, create another Data Validation. Again, choose “List.” For “Source,” insert the following formula: =INDIRECT("Sales["&B13&"]").

Step 4: In cell D13, use the following formula: =SUMIF(INDIRECT("Sales["&B13&"]"),B14,Sales[Total]).

You can now choose the options from the two data validation lists, and the subtotal will be displayed in D13.