Excel – @ in the beginning of a formula

microsoft excel

I have seen @ in the beginning of some formulas in a workbook, for example: =@SUM(A1:A10), =@VLOOKUP(3;F9:H12;2;FALSE).

It seems that it is a new feature of Excel.

Does anyone know what this @ means?

Best Answer

  • 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:

    enter image description here

    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.