Summary
-
Double square brackets appear in structured links in Excel when column headings contain spaces or special characters.
-
They are also inserted into formulas when you refer to header and total rows or multiple columns in a formatted Excel table.
-
When you enter a formula, square brackets and other characters and symbols are added automatically when you select table columns with the mouse.
When creating a link in a Microsoft Excel formula, two pairs of square brackets sometimes appear. If you, like me, like to keep your Excel formulas neat and concise, those double square brackets can look unsightly and cause frustration. However, there are good reasons why they happen.
Direct cell links and structured links
Before I explain why there are double square brackets in your formula, it's important to understand the difference between direct and structured links.
Direct cell references use column and row headers to locate and reference a cell or range of cells. For example, by typing:
=SUM(D2:D6)
to cell F2 sums the values in and between cells D2 and D6.
On the other side, structured links use table and column names. After formatting cells A1 through D6 as a table, enter:
=SUM(
to cell F2 and selecting all cells in the “Total” column of table T_Game_1, a structured link is created to the entire column:
=SUM(T_Game_1[Total]
Note that the column heading is enclosed in single square brackets. This is necessary to distinguish this type of reference from direct cell references and to make formulas more readable.
Close the parentheses and press Enter to complete and commit the formula.
Do not manually enter square brackets into structured links. They appear automatically when you start typing a formula and use your mouse to select the corresponding columns in the table.
The table name (T_Game_1) is included in the cell reference in the example above because the cell in which the formula was entered is outside 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])
Once again, because the formula refers to the column headings, they are enclosed in single square brackets.
V The (@) sign, also known as the implicit intersection operator, is used in Excel spreadsheet formulas to refer to a cell in the same row. When you select table columns as references in a formula, a statement is added automatically to tell Excel to repeat the calculation for each row as a separate formula, rather than trying to create a dynamic array of results from a single formula.
However, sometimes you see double pairs of square brackets in structured links instead of single pairs. If you find yourself in this situation, don't worry—you haven't done anything wrong! Let me explain what's going on.
Three Reasons to Use Double Square Brackets in Structured Links
Before I fully understood structured referencing in Excel, I often saw double square brackets appearing in my formulas, seemingly at random. However, there is nothing accidental in this behavior.
Column headers contain special characters
The first reason you might see double square brackets in a structured link is because the column headers they refer to contain a special character. In these cases, the extra pair of square 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 any other symbol on the keyboard.
In this example, the structured reference to the Rating column contains one pair of square brackets because the column header does not contain special characters. On the other hand, when I add the Bonus column to the formula, the structured link contains double square brackets because the column header contains asterisks:
=SUM([@Score]+[@[*Bonus*]])
Space is also considered a special character. Here, the structured link to the Items Sold column contains double square brackets because of 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 square brackets is because the formula references more than one column.
This formula sums the values in columns Wk1 through Wk4 for each team in the T_Scores table:
=SUM(T_Scores[@[Wk1]:[Wk4]])
Notice how the outer brackets define a structured reference to each row using an implicit intersection operator, and the inner nested brackets, separated by a colon, define the first and last columns of the selected range.
Moreover, structured links that reference multiple columns also include the table name (in this case T_Scores), even if the formula is entered within the table. This is intended to provide additional clarity and avoid ambiguity.
The third and final scenario that causes double square brackets to appear in Excel references is when referencing cells in the header row or summary row of a formatted table.
Let's say you added general string to this table by selecting the “Total Row” checkbox on the “Table Design” tab of the ribbon.
Now you want to use the four-week total in cell F7 to calculate the annual forecast. To do this, you need to divide the amount by four and multiply the result by 52. So, in cell H2, type:
=SUM(
Then select cell F7. Here's what you get:
SUM(T_Scores[[#Totals],[Total]]
When you selected cell F7, the structured link turned #Results in square brackets to indicate the total line and General in separate square brackets to indicate the “Total” column. The entire reference was then enclosed in another pair of square brackets, and the nested references were separated by a comma to precisely indicate the intersecting cell.
hash The (#) sign—also known in this context as a special element identifier—ensures that Excel understands that the row being referenced is not a standard data row, but rather a header or total row.
Now divide this structured link by four, close the parentheses, and multiply the result by 52:
=SUM(T_Scores[[#Totals],[Total]]/4)*52
Tips for using structured links in Excel
So, double square brackets in Excel are the program's way of ensuring that structured referencing can work in different environments and is easy to read. Here are some tips for using structured referencing effectively in Excel:
-
Keep table column headings short to prevent structured links from being too long.
- Always name formatted tables Excel. (via the Properties group on the Table Designer tab) to make it easy to follow the formulas that reference them.
-
If possible, avoid spaces and special characters in table headings to keep structured references neat.
-
If a table column heading 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 need to be escaped with double square brackets.
-
You don't have to waste time trying to manually enter square brackets, implicit intersection operators, or special element identifiers. Simply use your mouse to select cells when building your formula, and Excel will do all the technical work for you.
One way to ensure that you can use structured referencing effectively in Excel is to ensure that you data sets are properly structured. For example, avoid using more than one row as column headingsand make sure each cell contains one data point. In addition, each column must contain a consistent data field, and each row must be a record containing different types of data about the same item.
- OS
-
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, 1 TB of OneDrive storage, and more.
