7

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?

7

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.

  • Your reply seems better – Bella Aug 14 at 6:04
4

@ sign is popularly knows as Implicit intersection operator to support Dynamic Array formula method. It can be found in Excel 2019 & 365.

@ sign is almost identical to the older spreadsheet versions, except it uses @ to indicate where implicit intersection may be used, where older spreadsheet versions did this silently.

Implicit intersection follows these rules:

  • If cell value is a single item, then return the item.

  • If values are in a range, then return the cell on the same row or column as the formula.

  • If it is an array, then pick the top-left value.

Considering versions like 2013 & 2016, @ can be found with TABLE.

enter image description here

  • Uses the Column header to carry values in the Row.
  • Consider the example below. If the formula begins with @, it doesn't return an error but removes the @sign as soon as it finishes with Enter, and gets the value 100.

=@VLOOKUP(G112,A111:E111,2,FALSE)

enter image description here

N.B.

With an older version of Excel, functions that return multi-cell ranges or an array will be prefixed with @.

1

'@' was the way you indicated the start of a formula in program Lotus. Then, Excel allowed for similar usage in addition to using "=", However,Excel automatically change '@' to '='. For more details: https://www.quora.com/What-does-the-symbol-do-in-Excel-It-keeps-showing-up-as-why-is-this-happening

  • This applies only to versions of Excel that don't have Dynamic Arrays. The new Dynamic Arrays have a different usage of the @ sign. See my answer for updates. – teylyn Aug 14 at 5:18
0

I agree with AbdelAziz AbdelLatef.

I tested the "@" symbol in Excel, and I find Excel will immediately change to the equal sign. It seems that "@" is covered by "="

enter image description here

So this may not be a new feature of Excel. As far as I know, Recently the new functions are released by Excel is only available to Office Insiders at this time. You can review this in the link: https://blog-insider.office.com/2019/06/13/dynamic-arrays-and-new-functions-in-excel/

  • 1
    This is because you don't have an Excel version with Dynamic Arrays. I updated my answer with some more detail. – teylyn Aug 14 at 5:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.