Summary
-
Double brackets appear in structured references in Excel when column headers contain spaces or special characters.
-
They are also inserted into formulas when you reference header and total rows or multiple columns in a formatted Excel table.
-
When you type a formula, brackets and other signs and symbols are added automatically when you select table columns with your mouse.
When creating a reference in a Microsoft Excel formula, two pairs of square brackets sometimes appear. If, like me, you like Excel formulas to be neat and concise, these double brackets can look unsightly and cause frustration. However, there are good reasons why this happens.
Direct cell references and structured references
Before explaining why your formula has double brackets, it’s important to understand the difference between direct and structured references.
Direct cell references use column and row headers to locate and reference a cell or range of cells. For example, typing:
=SUM(D2:D6)
in cell F2 adds the values in and between cells D2 and D6.
On the other hand, structured references use table and column names. After formatting cells A1 through D6 as a table, type:
=SUM(
in cell F2 and selecting all cells in the Total column of table T_Game_1 creates a structured reference to the entire column:
=SUM(T_Game_1[Total]
Notice how the column header is enclosed in single brackets. This is to differentiate this type of reference from direct cell references and to make the formulas more readable.
Close the rounded parentheses and press Enter to complete and validate the formula.
Do not manually enter brackets in structured references. They appear automatically when you start typing a formula and use your mouse to select the relevant columns in a table.
The table name (T_Game_1) is included in the cell reference in the example above because the cell where the formula is entered is not within the table boundaries. However, here the structured reference does not include the table name, because the cells containing the formula are in the same table as the columns it references:
=SUM([@Score]+[@Bonus])
Again, since the formula refers to column headers, they are enclosed in single brackets.
THE has The (@) sign, also called the implicit intersection operator, is used in Excel table formulas to reference a cell on the same row. When you select table columns as references in your formula, the operator is added automatically to tell Excel to repeat the calculation on each row as a separate formula, rather than trying to produce a dynamic table of results from a single formula.
However, sometimes, instead of seeing single pairs of brackets in structured references, you see double pairs. If you find yourself in this situation, don’t worry, you haven’t done anything wrong! Let me explain to you what’s going on.
The three causes of double brackets in structured references
Before I fully understood structured references in Excel, I often saw double brackets appear seemingly randomly in my formulas. However, there is nothing random about this behavior.
Column headers contain special characters
The first reason you may see double brackets in a structured reference is because the column headers they refer to contain a special character. In these cases, the extra pair of brackets is Excel’s way of “escaping” these characters to avoid ambiguous references.
Special characters in Excel include most characters that are not numbers or letters, such as punctuation, currency, or anything else on your keyboard.
In this example, the structured reference to the Score column contains a single pair of square brackets because the column header does not contain any special characters. However, when I add the Bonus column to the formula, the structured reference contains double brackets because the column header contains asterisks:
=SUM([@Score]+[@[*Bonus*]])
A space is also considered a special character. Here, the structured reference to the Items Sold column contains double brackets due to the space between the two words in the header:
=[@[Items Sold]]*[@Price]
Formula references multiple columns
Another reason why structured references in Excel sometimes contain two pairs of brackets is because the formula references multiple columns.
This formula adds the values of columns Wk1 to Wk4 for each team in the T_Scores table:
=SUM(T_Scores[@[Wk1]:[Wk4]])
Notice how the outer brackets define the structured reference to each row using the implicit intersection operator, and the nested inner brackets, delimited by a colon, define the first and last column of the selected range.
Additionally, structured references that reference multiple columns also include the table name (in this case, T_Scores), even if the formula is entered within the bounds of the table. This is to add further clarity and avoid ambiguity.
A third and final scenario causing double brackets in Excel references is when you reference cells in the header row or total row of a formatted table.
Let’s say you added a total row to this table by checking “Total Row” in the Table Design tab of the ribbon.
Now you want to use the four-week total in cell F7 to calculate the annual projection. To do this, you need to divide the total by four and multiply the result by 52. So, in cell H2, type:
=SUM(
Next, select cell F7. Here’s what you get:
SUM(T_Scores[[#Totals],[Total]]
When you selected cell F7, the structured reference is wrapped #Totals in square brackets to identify the total line, and Total in separate brackets to identify the Total column. Then he enclosed the entire reference in another pair of brackets, while delimiting nested references with a comma, to identify the intersecting cell.
THE chop The (#) sign, also known in this context as a special item identifier, ensures that Excel understands that the referenced row is not a standard data row, but rather a header or total row.
Now divide this structured reference by four, close the parentheses and multiply the result by 52:
=SUM(T_Scores[[#Totals],[Total]]/4)*52
Tips for Using Structured References in Excel
In summary, double brackets in Excel allow the program to ensure that structured references can adapt to various environments and are easy to read. Here are some tips for using structured references effectively in Excel:
-
Keep table column headers short to ensure that structured references are not too long.
-
Always name formatted Excel tables (via the Properties group on the Table Design tab), so that the formulas that reference them are easy to follow.
-
If possible, avoid spaces and special characters in table headers to keep structured references tidy.
-
If a table column header must contain two or more words, use an underscore (_) rather than a space to separate them. Excel treats underscores as standard characters in headers, so they do not require escaping with double brackets.
-
You don’t need to waste time trying to manually enter brackets, implicit intersection operators, or special element identifiers. Simply use your mouse to select cells when building your formula, and Excel will do the technical work for you.
One way to ensure that you can use structured references in Excel effectively is to ensure that your data sets are structured correctly. For example, avoid using more than one row as column headers and ensure that each cell contains only one data point. Additionally, each column must contain a consistent data field and each row must be a record containing different types of data about an item.
- Operating system
-
Windows, macOS, iPhone, iPad, Android
- Free trial
-
1 month
Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1TB of OneDrive storage, and more.
