What does the symbol mean in Excel formula outside a table

Question

I recently opened an Excel file that I got from a coworker and noticed that several of the formulas start with the symbol @. I'm using the most recent O365 version of excel, but the file was saved using an earlier version. What does the @ sign imply, and how can I get rid of it?

Please take note that I am aware that the @ sign is used in Excel tables to indicate structural references. However, this does not appear to be the same, as the formulas are not in a table.

Answer 1

Excel has recently introduced a huge feature called Dynamic arrays. And along with that, Excel also started to make a "substantial upgrade" to its formula language. One such upgrade is the addition of @ operator which is called the Implicit Intersection Operator.

How is it used

1. The @ symbol is already used in table references to indicate implicit intersection. Consider the following formula in a table =[@Column1]. Here the @ indicates that the formula should use the implicit intersection to retrieve the value on the same row from [Column1].

2. With the new Implicit Intersection Operator, you can use this to return a value from the same row in a Dynamic Range - for example: =@A1:A10

---

References

According to their documentation for Implicit Intersection Operator:

Excel's upgraded formula language is almost identical to the old language, except that it uses the @ operator to indicate where implicit intersection could occur, whereas the old language did this silently

Can you remove the @?

Often you can. It depends on what the part of the formula to the right of the @ returns:

• If it returns a single value (the most common case), there will be no change by removing the @.
• If it returns a range or array, removing the @ will cause it to spill to the neighboring cells.

If you remove an automatically added @ and later open the workbook in an older version of Excel, it will appear as a legacy array formula (wrapped with braces {}), this is done to ensure the older version will not trigger implicit intersection.

As mentioned in the question, the use of @ symbol has been available in Excel tables for a very long as part of the Excel Table's Structural Referencing. But the "Implicit intersection operator" is pretty new and came along with Dynamic arrays.