Where I work, they’ve recently upgraded Office from 2016 to 365. One difference I’ve encountered in Excel is that “@” is placed in front of some functions. I’ve written formulae in which I’ve been prompted to change with regards to the inclusion of “@” because my formulae (without the “@”) might not work in older versions of Excel. Is there anyone here familiar with the newer versions of Excel that may care to explain what the “@” is about?

Man Idk, I only use Libre now

dv said:

Man Idk, I only use Libre now

At home, I’m still using Office 2010. I’m not keen to upgrade from that. I hated the online-only Help for Office 2016.

It’s associated with array functions, that you used to have to enter with ctrl-shift-enter, but now it returns the whole array when you just press enter.

I forget exactly what the @ does though, I’ll go and look it up.

The Rev Dodgson said:

It’s associated with array functions, that you used to have to enter with ctrl-shift-enter, but now it returns the whole array when you just press enter.

I forget exactly what the @ does though, I’ll go and look it up.

The Internet says:

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.
microsoft excel – @ in the beginning of a formula – Super User
superuser.com/questions/1471013/in-the-beginning-of-a-formula

dv said:

Man Idk, I only use Libre now

A lot of work to transfer all your VBA to Libre Basic though.

The Rev Dodgson said:

The Rev Dodgson said:

It’s associated with array functions, that you used to have to enter with ctrl-shift-enter, but now it returns the whole array when you just press enter.

I forget exactly what the @ does though, I’ll go and look it up.

The Internet says:

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.
microsoft excel – @ in the beginning of a formula – Super User
superuser.com/questions/1471013/in-the-beginning-of-a-formula

So in the latest versions of Excel:

If you want to return the full array – press enter
If you want to return the top left cell only – start the function with =@
If you want to return a selected range – select the range you want, press F2 then Ctrl-Shift-Enter.

The Rev Dodgson said:

The Rev Dodgson said:

The Internet says:

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.
microsoft excel – @ in the beginning of a formula – Super User
superuser.com/questions/1471013/in-the-beginning-of-a-formula

So in the latest versions of Excel:

If you want to return the full array – press enter
If you want to return the top left cell only – start the function with =@
If you want to return a selected range – select the range you want, press F2 then Ctrl-Shift-Enter.

Thanks.

With regards to spreadsheet formulae (not VBA), what are dynamic arrays?

KJW said:

With regards to spreadsheet formulae (not VBA), what are dynamic arrays?

Actually, I have just been reading what Microsoft have written about the topic of dynamic arrays. It represents a major development in what a spreadsheet formula can do: write to other cells… something that not even user-defined functions written in VBA could do in older versions of Excel.

KJW said:

The Rev Dodgson said:

The Rev Dodgson said:

The Internet says:

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.
microsoft excel – @ in the beginning of a formula – Super User
superuser.com/questions/1471013/in-the-beginning-of-a-formula

So in the latest versions of Excel:

If you want to return the full array – press enter
If you want to return the top left cell only – start the function with =@
If you want to return a selected range – select the range you want, press F2 then Ctrl-Shift-Enter.

Thanks.

With regards to spreadsheet formulae (not VBA), what are dynamic arrays?

Any function that returns an array will now return the full array when entered with just the enter key. For instance:

=TRANSPOSE

returns the full transposed array, or (I have just discovered):

=INDEX

returns the first four columns of the second row of the selected range.

KJW said:

KJW said:

With regards to spreadsheet formulae (not VBA), what are dynamic arrays?

Actually, I have just been reading what Microsoft have written about the topic of dynamic arrays. It represents a major development in what a spreadsheet formula can do: write to other cells… something that not even user-defined functions written in VBA could do in older versions of Excel.

They could, you had to select the output range and enter with Ctrl-shift-enter. But the output range was then fixed. It could easily be extended, but there was no easy way to reduce the array size (other than writing a macro to do it).