2

IN A NUTSHELL

Can anyone explain why I am seeing "@" symbols appearing, where they weren't before, in many formulae in many of my Excel spreadsheets?

They are not @mentions, or part of structured reference or table formulae use, nor any of the other usual suspects.

TL;DR DETAILS

(NOTE: System info is at the end of this post)

As I say, they are not @mentions, and I am not using structured references. Also, I am not and never was a Lotus user, so I don't shove @ symbols into formulae the way those guys sometimes seem to be hard-wired to do. In fact, I can't think of any time I've ever used @ in a formula other than as part of the occasional format code for TEST() and that is not what's going on here either.

Example

Here is an example (grabbed at random). This:

=IFS(DX$33="","",ISNUMBER(MATCH(DX$33,$AJ$33:$DR$33,0)),HLOOKUP(DX$33,$AJ$33:$DR$1033,ROWS(DX$33:DX36)+1,0),TRUE,0)

now looks like this:

=@IFS(DX$33="","",ISNUMBER(MATCH(DX$33,$AJ$33:$DR$33,0)),HLOOKUP(DX$33,$AJ$33:$DR$1033,@ROWS(DX$33:DX36)+1,0),TRUE,0)

As I say, that's just a random example. There are many more, lots of which don't have anything as "exotic" as IFS(), so I don't think that's the cause.

Merely a display effect?

Now in the above I say "looks like" because I guess it's possible that the mysterious @'s are not actually in the file itself, and are merely being displayed by Excel. I say that because I have at least one file where I can see from its DropBox version history that there is only a single version -- my (@-free) original. So it started with no @ symbols, and it has not changed (according to DropBox versioning), yet I can now see @ symbols. One explanation is that it's Excel that has changed, not my file, and so the change is merely a display artifact. Oh, and removing the @'s seems to have no effect on the formulae.

@ is a real thing

On the other hand, for grins I tried creating a brand new formula:

=@A1

When I tried to enter that, Excel warned me that "This formula is not supported by some older versions of Excel" and suggested that I have it replaced with the following variation:

=A1

So this use of @ does appear to be an actual thing, and one that hasn't always been around. (Which obviously applies to its use in conjunction with [] in row referencing, but that's not what this is (is it!?))

Anyway, in response to Excel asking me if I wanted to let it remove the @, I threw caution to the wind, I clicked "Mind your own business!" (a.k.a. "No"). Excel obliged by inserting my original @-ified version, and from what I can see, it's fine and behaves exactly like the @-free version Excel had tried to convince me to use instead.

So?

Can anyone explain all this? Has there maybe been a recent update of Excel that has introduced a new piece of @ syntax? And by recent I mean in the last 2-3 months max. I have files I created in mid to late December that I know were @-free back then, and which have now become @-ified. But if so then why is Excel just wandering around willy nilly changing files without asking me, especially given the fact that by its own admission that might create backwards compatibility issues? And how come I have an Excel file that by virtue of it having only one version I know is UNCHANGED, yet by virtue of the arrival of these @ symbols appears to have been CHANGED?

I've been hunting around for clues for over an hour but I can't find anything definitive. One difficulty is that "@" is such a widely used character that a lot of what Google is throwing up is irrelevant chaff.

System info

  • Excel for the Mac, Version 16.34 (20020900), part of Office 365
  • MacOS Catalina 10.5.3

thx.

6

Congratualtion you just got the new Dynamic Formulas. The @ tells the dynamic array formula to only return one value, basically act like the old array versions of the formula.

The new Dynamic Array Formula want to SPILL the values automatically.

For example put random values in A1:A15

enter image description here

then in B1 put:

=INDEX($A$1:$A$15,SEQUENCE(15))

Entered normally. Excel will "spill" the values down.

enter image description here

Now add the @ to the front of the formula in B1. The formula will no longer spill down.

enter image description here

But just like the old style one can select B1:B15 and hit Ctrl-Shift-Enter instead of Enter with the @ formula and it will fill the range just like before.

enter image description here

As to the automatic change, well it would not work without it with the new functions.

But look into the new Dynamic Array Functions they will make many thing much easier. But with anything new backwards compatibility must be thought of when sharing.

  • 1
    Nice summary of the @ sign in formula – gns100 Feb 14 at 17:44
  • Excellent, thanks! I'd heard about this and was looking forward to it arriving. Pity I'd obviously completely forgotten about it again in the meantime! 😬 – tkp Feb 16 at 16:15

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.