In Excel 365 builds that already have the new Dynamic Array formulas, all formulas are treated as array formulas by default. The @ sign is used to prevent the new default array behavior of a function if it is not wanted in that particular formula.

If the same workbook is opened in a non DA version of Excel, it will not be visible.

If the @ sign is entered into non DA versions of Excel, it will silently be removed when the formula is confirmed into the cell.

Edit: The @ sign as a prefix to an Excel function should not be confused with the @ sign for Lotus compatibility. These are two different things.

Consider the following screenshot:

It was taken in Excel with Dynamic Arrays enabled. The formula in B2 is =ROW(1:4) and it has simply been confirmed with Enter. The formula is treated like an array formula and the results automatically "spill" into the next rows.

If this behaviour is not wanted, the function can be preceded with an @ sign and then it will behave like a non-array formula in the old Excel without Dynamic Arrays. In old Excel, I would have to select 4 cells, type the formula and confirm with Ctrl-Shift-Enter to get the formula to return the values into four cells.

Excel has recently introduced a huge feature called Dynamic arrays. And along with that, Excel also started to make a "substantial upgrade" to their formula language. One such upgrade is the addition of @ operator which is called 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 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.

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

An apostrophe before a cell value forces Excel to interpret the value as text. This is mostly useful for values that look like a number or date.

For example, Dec-1 would ordinarily be converted to a date in December. If you want the literal text Dec-1, enter it as 'Dec-1.

In formulas that refer to another sheet whose name contains spaces, the sheet name is enclosed in apostrophes. For example:   ='Other Sheet'!A1