Summary
- Double square brackets appear in structured references in Excel when column headers contain spaces or special characters.
- They’re also inserted into formulas when you reference header and total rows or multiple columns in a formatted Excel table.
- When you’re typing a formula, square brackets—as well as other signs and symbols—are added automatically as 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 square brackets can look unsightly and cause frustration. However, there are good reasons why they happen.
Direct Cell References vs. Structured References
Before I explain why your formula has double square brackets, it’s important to understand the difference between direct and structured references.
Direct cell references use the column and row headings to locate and reference a cell or range of cells. For example, typing:
=SUM(D2:D6)
into cell F2 sums the values in and between cells D2 and D6.
On the other hand, structured references use table and column names. After formatting cells A1 to D6 as a table, typing:
=SUM(
into cell F2 and selecting all the cells in the Total column of the T_Game_1 table creates a structured reference to the whole column:
=SUM(T_Game_1[Total]
Notice how the column header is enclosed in single square brackets. This is to differentiate this type of reference from direct cell references and to make formulas more readable.
Close the rounded parentheses and press Enter to complete and commit the formula.
Don’t type square brackets into structured references manually. 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 typed isn’t within the table’s boundaries. However, here, the structured reference doesn’t include the table name, as the cells containing the formula are in the same table as the columns it references:
=SUM([@Score]+[@Bonus])
Once again, because the formula references column headers, they’re wrapped in single square brackets.
The at (@) sign—also known as the implicit intersection operator—is used in formulas in Excel tables 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 array of results from a single formula.
However, sometimes, instead of seeing single pairs of square 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 what’s going on.
The Three Causes of Double Square Brackets in Structured References
Before I fully understood structured references in Excel, I would often see double square brackets appearing apparently randomly in my formulas. However, there’s nothing random at all about this behavior.
The Column Headers Contain Special Characters
The first reason why you might see double square brackets in a structured reference is that the column headers they refer to contain a special character. In these cases, the extra pair of square brackets is Excel’s way to “escape” those characters to avoid ambiguous references.
Special characters in Excel include most characters that aren’t a number or letter, such as punctuation, currency, or any other sign on your keyboard.
In this example, the structured reference to the Score column contains a single pair of square brackets because the column header doesn’t contain any special characters. On the other hand, when I add the Bonus column to the formula, the structured reference contains double square 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 square brackets due to the space between the two words in the header:
=[@[Items Sold]]*[@Price]
The Formula References Multiple Columns
Another reason structured references in Excel sometimes contain two pairs of square brackets is that the formula references more than one column.
This formula sums the values in 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 inner nested brackets—delimited by a colon—define the first and last columns of the selected range.
What’s more, structured references that refer to multiple columns also include the table name (in this case, T_Scores), even though the formula is typed within the boundaries of the table. This is to add further clarity and avoid ambiguity.
A third and final scenario that causes double square brackets in Excel references is when you reference cells in the header row or total row of a formatted table.
Suppose you’ve added a total row to this table by checking “Total Row” in the Table Design tab on 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(
Then, select cell F7. This is what you get:
SUM(T_Scores[[#Totals],[Total]]
When you selected cell F7, the structured reference wrapped #Totals in square brackets to identify the total row, and Total in separate square brackets to identify the Total column. Then, it surrounded the whole reference in another pair of square brackets, while delimiting the nested references with a comma, to pinpoint the intersecting cell.
The hash sign (#)—also known in this context as a special item identifier—ensures Excel understands that the referenced row isn’t a standard data row, but rather, a header or total row.
Now, divide that structured reference by four, close the parentheses, and multiply the result by 52:
=SUM(T_Scores[[#Totals],[Total]]/4)*52
Advice for Using Structured References in Excel
In summary, double square brackets in Excel are the program’s way to ensure structured references can cope with diverse environments and are easy to read. Here are some tips for the effective use of structured references in Excel:
- Keep table column headers short to ensure structured references aren’t too lengthy.
- Always name formatted Excel tables (via the Properties group of the Table Design tab), so that formulas referencing 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 square brackets.
- You don’t need to waste time trying to type square brackets, implicit intersection operators, or special item identifiers manually. Simply use your mouse to select cells when constructing your formula, and Excel will do the technical stuff for you.
One way to make sure you can use structured references efficiently in Excel is to make sure your datasets are properly structured. For example, avoid using more than one row as your column headers, and make sure each cell contains a single data point. Also, each column should contain a field of consistent data, and each row should be a record containing different types of data about one 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.