How does excel indicate that a column has been selected in a structured reference?

Structured Reference is a special feature of Excel that references tables.  It makes our formulas easier to understand and automatically adjusts whenever there are changes in our data.  

How to create a structured reference

A structured reference uses table names instead of the usual cell references.  In order to create a structured reference, we follow these steps:

• Select our data and create a table by pressing Ctrl + T

• In the Create Table dialog box, select the check box for “My table has headers” and click OK.  A table will be created, using the default table style.  

• Click anywhere in the table to display the Design tab on the ribbon
• Change the table name to “Production”

• Let’s create a formula referring to our table.  In cell E2, enter “%Rejects”

Note that as we press Enter, the new column is automatically formatted similar to the rest of the table.  

• In cell E3, enter the equal sign “=”, then press D3.  

Instead of D3, Excel returns the structured reference [@Rejects] referring to the column for Rejects.  The symbol “@” means current row.  

• Now type a forward slash “/” then a left bracket “[“.  A list of structured references appear in a drop-down list.

• We want to divide Rejects by the Output.  We complete the formula in E3 by entering:
  =[@Rejects]/[@Output]

As we press Enter, the formula is automatically copied down the cells in column E.  Another important feature when using structured reference is that the formula along a column has exactly the same references “[@Reject]” and “[@Output], since we are referencing the table and columns, not the individual cells.  

Qualified and unqualified structured reference

A formula that includes a structured reference can be fully qualified or unqualified.  When we calculate within the table, we can use an unqualified reference like the example above.  For an unqualified reference, there is no need to indicate the table name.

When we enter a formula outside the table, we use a qualified reference through these steps:

• In cell C9, enter the first part of the formula =sum(  
• Then click the cells C3:C7.  Excel will automatically rename the range C3:C7 as “Production[Output]”.  

The result in C9 is the total output for all lines, as implied by the formula “=SUM(Production[Output])”.  Note that a qualified reference makes use of both the table name “Production” and column name “Output”.  

By using structured references, we are able to better understand the formulas.  They are also very dynamic, wherein the formulas automatically adapt to any change in the table. Inserting a column within the table, or changing the name of column headers won’t break the formulas.

In the example below, we have changed the header name from “Rejects” to “Defects” and inserted a column between them.  The table still works perfectly fine.

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.