Summary

• The @ sign in modern versions of Excel forces implicit intersection, returning a single row value instead of spilling an array.
• Excel automatically inserts the sign in structured references in tables to force row-level calculations.
• Tagging collaborators in comments using the @ sign triggers a notification email.

Have you ever seen the @ symbol in a Microsoft Excel formula and wondered why it’s there? Well, wonder no more, because in this guide, I’ll explain everything you need to know about this powerful operator.

Implicit intersection and spill in Microsoft Excel

Before I explain what the @ sign does, let’s take a moment to understand implicit intersection and spill in Excel.

Implicit intersection in Excel refers to the act of reducing an array to a single value. Specifically, it forces Excel to return the value from a cell in the same row as the formula.

In versions of Excel released before 2021, if you entered a formula that referenced a range, implicit intersection would happen “silently” without you knowing it. In other words, by default, it would return a single value according to the row in which the formula was typed.

In this example, typing:

=B2:B5*C2:C5

into cell D2 and pressing Enter only returns one result, even though the formula references multiple cells.

You could then click and drag the fill handle to duplicate the formula in the remaining cells in the column, forcing the same formula to recalculate independently on each row.

Alternatively, you could select cells D2 to D5, type the same formula, and commit it by pressing Ctrl+Shift+Enter to force Excel to produce a legacy CSE formula in curly braces.

However, the introduction of dynamic array behavior means that, in modern versions of Excel, formulas spill results into multiple cells by default, rather than favoring implicit intersection. You know this has happened when a blue line surrounds the affected cells.

In other words, implicit intersection is the opposite of spilling: the former returns a single, row-specific result, while the latter produces many results simultaneously.

The @ sign in formulas referencing table columns

You’re most likely to see the implicit intersection operator in use when a formula references a column in an Excel table.

Here, after typing the equal sign into cell D2, I selected cell B2 to create the first reference in the formula, and the @ sign was inserted into the table column reference automatically.

Then, after typing an asterisk, I selected cell C2 to create the second part of the multiplication, and the @ sign appeared again.

When you type a formula into the first cell of a table column and press Enter, it is repeated in the remaining cells in that column. Crucially, the @ sign forces Excel to apply the formula at the row level.

You don’t need to type the @ sign manually in table formulas that reference other columns in the same table—Excel inserts the operator automatically when you select a cell as you construct your formula.

Understanding how the implicit intersection operator works is crucial when referencing the table in a regular cell. For example, typing:

=SUM(T_Sales[@Total]*52)

into cell F2 multiplies the value in the Total column by 52 at the row level because the @ sign is placed before the column header reference.

Removing the implicit intersection operator, however, multiples all the values in the Total column by 52 before summing the results.

=SUM(T_Sales[Total]*52)

If you add more rows to the table and, thus, more values in column D, since the formula references the table header rather than a single cell, the formula without the implicit intersection operator picks up the extra data.

The @ sign in formulas referencing regular ranges

In older versions of Excel that don’t support dynamic array formulas, to enter an array-enabled formula, you would need to select all the cells where you want the results to be displayed, type the formula, and press Ctrl+Shift+Enter to commit it. At this point, the same formula would appear in all the cells you selected, enclosed in curly braces, performing the calculation respectively on each row. This is known as a CSE or legacy array formula.

However, in modern versions of Excel, all formulas are treated as dynamic arrays by default, so when you use a dynamic array formula that references a range of cells, it returns multiple results without the need to press Ctrl+Shift+Enter.

So, suppose you want a formula in Excel for Microsoft 365 to behave as it would have before this shift, returning only a single value rather than an array. To achieve this, you need to manually add the implicit intersection operator before the cell references to override the array behavior:

=@B2:B5*@C2:C5

You can then autofill or copy the formula into other cells if required, safe in the knowledge that the implicit intersection operator will apply the formula at the row level.

Although use cases for manually-typed implicit intersection operators are limited, they could come in handy if you want to prevent the #SPILL! error from appearing, maintain consistency across versions of Excel, or prefer the older, row-by-row calculation model.

Other times you’ll see or use @ sign in Microsoft Excel

If you’re using a version of Excel released in 2021 or later, including Excel for Microsoft 365, you’ll see the @ sign if the workbook was first created in an older version of Excel and contains an array formula. This ensures the existing formula produces the same single-cell result as before.

In a totally different scenario, you can use the @ sign in Excel for Microsoft 365 and Excel for the web to tag collaborators in a comment. To start a new comment thread, select the relevant cell, and in the Review tab on the ribbon, click “New Comment.”

Then, in the Comment text field, type @ and start typing the person’s name or email address. Then, select the correct person from the list that appears.

The person you are tagging must have permission to access the workbook, either because you’ve shared the file with them or it’s saved in a shared space on SharePoint or OneDrive.

Finally, type your message, and click the “Send” icon (or press Ctrl+Enter).

This triggers the collaborator to receive an email containing a link to the comment in the workbook. You can also tag collaborators in a new comment added to an existing thread using the same method.

---

The @ sign is just one of the many signs and symbols in Excel that are crucial for data manipulation, data analysis, and precise formulas. For example, the hash sign (#) tells Excel to consider all the results in a spilled array, the dollar sign fixes column or row references, and double quotes are crucial when you want to use text in formulas.

Microsoft 365 Personal

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.