What does the symbol mean in Excel formula outside a table

0 votes
I recently opened an Excel file that I got from a coworker and noticed that several of the formulas start with the symbol @. I'm using the most recent O365 version of excel, but the file was saved using an earlier version. What does the @ sign imply, and how can I get rid of it?

Please take note that I am aware that the @ sign is used in Excel tables to indicate structural references. However, this does not appear to be the same, as the formulas are not in a table.
Nov 26, 2022 in Others by Kithuzzz
• 27,940 points
62 views

1 answer to this question.

0 votes

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

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

answered Nov 26, 2022 by narikkadan
• 51,600 points

Related Questions In Others

0 votes
0 answers

What does the suspend function mean in a Kotlin Coroutine?

I'm reading Kotlin Coroutine and know that ...READ MORE

May 26, 2022 in Others by Harshitha
• 120 points
81 views
0 votes
0 answers

What is the formula to keep first two words in a cell over excel?

I want to maintain the first two ...READ MORE

Nov 2, 2022 in Others by Kithuzzz
• 27,940 points
60 views
0 votes
1 answer

Text with a dash in the cell but not in the formula bar (Excel)

What you have got here is called ...READ MORE

answered Nov 13, 2022 in Others by narikkadan
• 51,600 points
81 views
0 votes
1 answer
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
206 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
374 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,640 points
168 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 22,940 points
150 views
0 votes
1 answer

Display a table (in the html/Excel spreadsheet meaning) in a iOS app in Swift

Here is the answer to your question: How ...READ MORE

answered Oct 29, 2022 in Others by narikkadan
• 51,600 points
91 views
0 votes
1 answer

MS Excel showing the formula in a cell instead of the resulting value

Make sure that... There's an = sign before the formula There's ...READ MORE

answered Nov 8, 2022 in Others by narikkadan
• 51,600 points
118 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP