4 Effective Methods for Utilizing Structured References in Microsoft Excel

Excel usage often centers around identifying relationships between different sets of data. Yet, when inserting complex formulas, the overuse of both relative and absolute explicit cell references—such as “B7” and its variations—can lead to a cluttered and chaotic formula bar.

Utilizing structured references in Excel can enhance this process by allowing you to assign names to tables and their headers. These designated names serve as implicit cell references, enabling Excel to automatically retrieve and calculate the structured data.

Below are several effective methods for employing structured references in Excel.

1. Performing Calculations Within Tables

Structured references function exclusively with tables, so the optimal method to utilize them is within those types of tables.

For instance, create a straightforward table ranging from B2 to F8 containing sales information for a store. Be sure to label the table as “Sales” (check the “Table Name” in the upper-left corner).

Next, we will calculate the total for each individual sale:

Step 1: Select cell F2 (ensuring the drop-down icon is not clicked). Navigate to “Home,” then “Insert,” and choose “Insert Table Columns to the Right” to automatically include a new column in the table.

Step 2: Designate the header of the new column G as “Total.”

Step 3: In cell G3, enter =[@PricePerUnit]*[@Quantity] and press Enter. Adjust the cell formatting as necessary.

The references “[@PricePerUnit]” and “[@Quantity]” correspond to their respective fields within the columns. The “@” symbol preceding the column names indicates that each result cell will derive its values from the same row of the table.

To clarify, the formula =[@PricePerUnit]*[@Quantity] in G3 effectively corresponds to =$C3*$D3.

2. Retrieving a Range Beyond the Table

When you intend to utilize a structured reference in a cell that lies outside the table, you must start the reference with the table name. For instance, in our earlier example, employing “Sales[Total]” will retrieve the entire range of values under the “Total” column from the “Sales” table. This provides an array of multiple values for further manipulation.

Here’s how this appears in Excel, specifically in cell I3, where ample space is left for the range to expand downward.

3. Summing and Partially Summing a Column

For a quick sum of an entire column, leverage the “Total Row” checkbox located in the “Table Design” settings (within “Table Style Options”). Here’s how to obtain the totals for both the “Quantity” and “Total” columns.

Although the ‘Total’ row cannot be relocated and will consistently be positioned at the end of the table (allowing for additional entries), you can duplicate its result elsewhere:

To calculate the total of every entry within the “Total” column, use =SUM(Sales[Total]).
For obtaining the sum of only visible entries, such as those appearing after filtering the table, apply =SUBTOTAL(109,Sales[Total]). This formula mirrors what the “Total Row” option in Table Format does for its row.

You may also compute a partial sum based on a specific criterion found within the table without needing to format it. For example:

To find the total sales attributed to Mike, use =SUMIF(Sales[Seller]," Mike" ,Sales[Total]). Here, “Mike” is a manually entered string.
To sum all products represented by Product ID “41230,” you would use =SUMIF(Sales[ProductID],41230,Sales[Total]). Note that since the ProductID column is formatted as “General,” you can directly enter the number.

4. Implementing Data Validation from a Table with INDIRECT

Assuming you are working with the previously mentioned Sales table, you can establish custom Data Validation settings to facilitate easier searching through the data. Let’s create a smaller table that allows selections for Product IDs, Dates, or Sellers, enabling you to pick a single item from these sets to display the subtotal.

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

Data Validation from a Table via INDIRECT 1

Step 2: In the popup window that appears, select “List” from the “Allow” dropdown, and input the column values manually into the “Source” field, separated by commas. For this example, we have entered “ProductID, Seller, Date.”

Data Validation from a Table via INDIRECT 2

Step 3: In cell C13, establish another instance of Data Validation. Again, select “List.” For the “Source,” include the formula: =INDIRECT("Sales["&B13&"]").