Excel 365 - Functions preceded by "@" in formulas

That sounds familiar, I just can't place the context

Can you give us some examples of these formulas? Maybe upload a small sample file

Trouble shooting problems in files can be like a visit to the dentist, a long, slow painful process of us trying to “extract” the clues needed to recognize the problem in a back and forth flow of questions and answers so that we can come up with a / “the” solution.

Often it is faster and easier for everyone if we have a “sample file” get "hands on", to look at, and to “play with”.

This next link provides some tips on setting up a sample file and specific instructions for uploading and "sharing" it for us to access:

https://answers.microsoft.com/en-us/windows/forum/windows_other-winapps/trouble-shooting-share-onedrive-file/a231a097-bcbf-4e34-ad6c-a33118baf471?tm=1523189328156

Includes a link to a macro to randomize text in Word.

**************************************

Found it.  It is not new. As I thought (but couldn't find an example when I wanted it, <sigh>) it is part of using "Structured References" in the Excel Table feature.

It means use the value in the column only on this row.

This article demonstrates that:

Using structured references with Excel tables
https://support.office.com/en-us/article/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e
When you create an Excel table, Excel assigns a name to the table, and to each column header in the table. When you add formulas to an Excel table, those names can appear automatically as you enter the formula and select the cell references in the table instead of manually entering them. Here's an example of what Excel does:

Excel tables are a great form of automation.  The structured references replace row/column id, ie A1 with english language column names.

By default, tables use a colored style. It is a quick way if identifying their boundaries. And it looks pretty.

In a table, when you enter a formula once, it is automagically copied to the whole column.

Creating formulas in tables relies on clicking on columns to pick up the correct name syntax.

Here are some links about about tables and the structured reference language/syntax.

@ Avoid Writing Complex Excel Formulas – Use Tables
https://www.myonlinetraininghub.com/avoid-writing-complex-excel-formulas
There comes a time in many Excel users’ careers where we start to write incredibly complex Excel formulas to summarise or extract data from poorly structured workbooks. I’m not talking about relatively simple nested functions like INDEX and MATCH, I’m talking about monsters. the true Excel Guru knows that if the data were in a Tabular format we’d be able to use the amazing tools Excel has available. Tools that make mincemeat of complex formulas like the one above.

@ Basic Demo of Excel Tables to Warn of Upcoming Deadlines 2012 12 13
http://ozdusoleil.com/2012/12/13/basic-demo-of-excel-tables-to-warn-of-upcoming-deadlines/
Quick video to show the use of Excel tables, and how a table…

! Excel Tables at excelcampus-Tutorial Beginners Guide for Windows 2007-2013& Mac 2011.mp4  16min (downloaded) (and example files)
https://www.excelcampus.com/tables/excel-tables-tutorial-video/   October 1, 2013------------------------------- Jon Acampora 
10 Awesome Reasons to Use Excel Tables
  1. Automatic Formatting -----------------------           2. Organizing & Naming Data
  3. Sorting & Filtering ----------------------------           4. Auto Expansion & Navigating
  5 Total Row --------------------------------------           6. Remove Duplicates
  7. Create Unique List ---------------------------           8. Pivot Table Integration
..9. Chart Integration -----------------------------         10. Table Formulas (Structured References)

@ Excel Tables Tutorial & 13 Tips for making you a Data Guru
https://chandoo.org/wp/data-tables/
Excel table is a series of rows and columns with related data that is managed independently. Excel tables, (known as lists in Excel 2003) is a very powerful and super-cool feature that you must learn if your work involves handling tables of data.
.  *  The most important thing after you create a table – Give it a meaningful name
.  *  Change table formatting without lifting a finger
.  *  Add Zebra Lines to Tables without doing Donkey Work
.  *  Tables come with Data Filters and Sort Options by default
.  *  Slice your tables with slicers
.  *  Bye, bye cell references, welcome structured references
.  *  Make Calculated Columns with ease
.  *  Total your Tables without writing one formula
.  *  Export Tables to Pivot Tables
.  *  Push the table data to Sharepoint Intranet Site
.  *  Print Tables Alone, with out all the other stuff around
.  *  Change, reshape or clean your table data with Power Query
.  *  Got multiple tables? Connect them to make a multi-table pivot
ET PT PivotTables Power Query BI.docx

@ Shortcuts for Excel Tables
https://exceljet.net/tips/shortcuts-for-excel-tables
https://www.youtube.com/watch?time_continue=197&v=-K_Z3OACOIA

********************************

@ Introduction to Structured References and Tables  Run time: 3:35
In this video gives a brief introduction to structured references. Structured references is just a fancy name for formulas that use table names instead of normal cell references.

Tables and Structured References                April 4, 2016           Sridhar Belide
Excel Tables arranges data in a structured way in rows and columns. When you have data as output of some analysis then showing it in Excel Table is my suggestion. It makes us easy to work on that data to further process our analysis. In this tutorial Excel expert Oz explains us how to use […]

Structured reference in Excel tables
https://www.ablebits.com/office-addins-blog/2019/02/06/structured-references-excel-tables/
This tutorial explains the basics of Excel structured references and shares some tricks on using them in real-life formulas.
One of the most useful features of Excel tables are structured references. When you have just stumbled upon a special syntax for referencing tables, it may look boring and confusing, but after experimenting a bit you will surely see how useful and cool this feature is.
   •      What are structured references in Excel?
   •      How to create a structured reference in Excel
   •      Excel table reference syntax
   •      Excel structured references - formula examples
   •      Absolute structured references in Excel formulas

Formulas and Structured Data in Excel Tables
https://excelsemipro.com/2011/04/formulas-and-structured-data-in-excel-tables/
I created a data table on a worksheet, then converted it to an Excel Table. Before the conversion, I had entered a formula for Gross Product in the last column. This formula looks like most formulas do in Excel, with cell references. The great thing about converting to a Table is that when I add more data to the table, the formula is automatically copied down for me. If I change this formula, then Excel will automatically copy the change to all rows in the column. And it doesn’t matter if I change the first cell at the top of the column or change a cell in the middle, all rows in the column will be modified. Tables have a structured data format you can see when adding a formula after the Table has been created. In my example I will reenter the Gross Product formula so that it equals Net Product plus Waste Setup plus Waste Run. Instead of cell references, Excel uses column references for the Table.

Thanks very much for clarifying the problem - I wasn't aware of Structured References even though I use tables and range names extensively.  Despite this, some of the formulas that I have developed are very complex and I often resort to adding additional (sorry - tautological, I know!) columns to resolve different portions of the formula.  However, I have two further questions:-

1. Will removing the "@" symbol from all formulas in the workbook, as I have already done, cause any problems?

2. I have been using these workbooks for some time without seeing the "@" symbols previously - why have I only just encountered them now?

#0. Despite this, some of the formulas that I have developed are very complex and I often resort to adding additional (sorry - tautological, I know!) columns to resolve different portions of the formula.  However, I have two further questions:-

1. Will removing the "@" symbol from all formulas in the workbook, as I have already done, cause any problems?

2. I have been using these workbooks for some time without seeing the "@" symbols previously - why have I only just encountered them now?

#0.  Using "helper columns" is a reasonable strategy for breaking formulas down into understandable chunks. I do it myself. 

#1  Oops!  yes, removing the @ will change the formula, change the results.  Instead of using the column value only on the current row, it will use all of the values in that column.  The difference should be pretty noticeable.

#2 I can think of 2 reasons. One is you simply did not notice them. The other is the type of formulas you were using did not require them.

I'm not sure that this applies to my problem, so I have attached a copy of one of the spreadsheets that exhibits it.  I have pasted it into OneDrive as you suggested; the link is shown below (I'm not sure why it isn't hyperlinked).

https://1drv.ms/x/s!AqYle5WR6YzmmQNhzlKFfyu6zzpV?e=mcCXXV

The problem is shown in columns K-T of the "AccomTour" worksheet.

I have been using this workbook for some time but have not seen the "@" symbol anywhere in it previously.  I may not have noticed it but I modify the formulas frequently to accommodate different circumstances and think that it is doubtful that I did not see it.  Also, I copied the same workbook (that I have now shared with you but with the data removed and unnecessary rows deleted) to another computer a week ago and have just checked it.  It definitely does NOT contain the "@" symbol in any of the formulas, which seems to indicate that the problem does indeed stem from the recent Office upgrade.

While I use tables and references to ranges extensively, I have not used Excel to create the tables but have created and named them myself and have not, therefore, used  Structured References in any of my spreadsheets previously.  I am hopeful, therefore, that removing the "@" from the formulas will not have any deleterious consequences.

I would appreciate your comments on the linked spreadsheet and suggestions as to why the "@" appears in some formulas but not all.

OK, something else is going on here. You are not using tables, so that idea is dead.

I pulled this formula out of K25. You did say you see @ in K column formulas, right?

=IF(OR(StartDate="",$B25=""),"",IF($D25=FinDate,"p",IF($F25="TBA","a",IF(LEFT($F25,3)="In ","i",IF(AND($AC25=OFFSET($AC25,-1,0),$F25=IF(ISERR(FIND("; ",OFFSET($F25,-1,0))),OFFSET($F25,-1,0),OFFSET($AJ25,-1,0))),OFFSET($K25,-1,0),IF(AND($AC25=OFFSET($AC25,-1,0),$AF25<>"",$AF25>=OFFSET($AF25,-1,0)),OFFSET(AccRef,$AF25,-3),IF(ISNA(VLOOKUP($AJ25,PrivAccTbl,3,FALSE)),IF($H25="",IF($I25="","?",VLOOKUP($I25,PrePaidTCA_Tbl,3,FALSE)),VLOOKUP($H25,PrePaidTCA_Tbl,3,FALSE)),"p")))))))

ick!  But there is no @ in it. So I can't comment on your problem.

I hope someone else jumps in here ...

I've just had a look at the Excel file that I uploaded to OneDrive and, yes, the "@" symbols have indeed disappeared but they are still there in the original, which I have shown below for your interest:-

=@IF(OR(StartDate="",$B25=""),"",IF($D25=FinDate,"p",IF($F25="TBA","a",IF(LEFT($F25,3)="In ","i",IF(AND($AC25=OFFSET($AC25,-1,0),$F25=IF(ISERR(FIND("; ",OFFSET($F25,-1,0))),OFFSET($F25,-1,0),OFFSET($AJ25,-1,0))),OFFSET($K25,-1,0),IF(AND($AC25=OFFSET($AC25,-1,0),$AF25<>"",$AF25>=OFFSET($AF25,-1,0)),OFFSET(AccRef,$AF25,-3),IF(ISNA(VLOOKUP($AJ25,PrivAccTbl,3,FALSE)),IF($H25="",IF($I25="","?",VLOOKUP($I25,PrePaidTCA_Tbl,3,FALSE)),VLOOKUP($H25,PrePaidTCA_Tbl,3,FALSE)),"p")))))))  

The formula in column P (cell P18) actually has 2 "@" symbols:-

=@IF(OR(StartDate="",$B17=""),"",IF(OR($K17="c",$K17="i",$K17="t"),0,IF($F17=OFFSET($F17,-1,0),OFFSET($P17,-1,0),IF($AF17="",IF(OR($L17="na",$L17="P"),0,IF($L17="n",$AH17,$AG17)),IF($S17=@OFFSET(AccRef,$AF17,-1),OFFSET(AccRef,$AF17,-4),IF($L17="n",$AH17,$AG17))))))

I have also checked whether removing them from the workbook (using Find & Replace) causes any problems and I haven't detected any yet, so I am not going to worry about them in future.  They have not reappeared in any new spreadsheets, so probably it was an intermittent Office program problem (but these don't occur, do they?!).

Anyway, thanks for your help and I have learnt something new about Excel Tables.  I will leave the post open as somebody may be able to identify what caused the issue.

I think this issue (@ symbol issue showing up randomly after = in various formulas), is slowly hitting all users since an update in the last few weeks - likely Version 1905 (Build 11629.20246) or Version 1906 (Build 11727.20230)

I also don't think this can be worked around, but is a bug that will have to be addressed by the Office developers.

It was first reported to me on June 20th, and I've since had feedback from at least 5 other users with the same issue.

In summary: @ turning up after the = sign in formulas that certainly never used the @ character before.

I now get this issue myself as of yesterday (probably an auto update).

For me, it mostly affects custom formulas from a particular addin, but the addin is irrelevant since the @ shows up whether it's installed or not - and obviously given OP's description, affects other formulas as well.

So this happens with just a single formula in the sheet, no other complications eg

before: =CTEST($A9)

after:   =@CTEST($A9)

Seems very coincidental (but admittedly technically unrelated) that there's a brand new feature that just happens to feature the @ symbol in Office products - smells like a bug...

https://support.office.com/en-us/article/use-mention-in-comments-to-tag-someone-for-feedback-644bf689-31a0-4977-a4fb-afe01820c1fd

Yes, it is an interesting co-incidence that the new feature uses @.  Is it related, I don't see how.  The new @ function is supposed to be inserted by a user only in a Comment, and the @ is supposed to be followed a corporate email ID.  Frankly I don't see how your problem could be related to the feature.  But, you never know ...

Now that you have independent corroboration of the issue, do submit a feedback to MS identifying this apparent BUG. Share the link to the feedback here and with the other people who have noticed the same problem

“Official” MS does not pay much attention to this forum. However, you now can send your complaints and feature requests directly to MS developers and managers using one of the 4 possible “Feedback” mechanisms described in this link:

https://answers.microsoft.com/en-us/windows/forum/windows_10-windows_install/send-feedback-to-ms-developers/506e7654-8ced-405b-911e-c58d8f253069

Please post a share link to your feedback, back here, so other people who find your question will be able to vote for it and add their comments to it.

Collection of MS “UserVoice” web links:

http://answers.microsoft.com/en-us/windows/wiki/windows_10-desktop/ms-suggestion-box-sites-at-usdervoicecom/7397119c-6d48-49e6-8d94-7bd0a50d214f

MS Feedback article, says basically the same thing:

https://support.microsoft.com/en-us/help/4021566/windows-10-send-feedback-to-microsoft-with-feedback-hub

PS: I appear to have a substantially newer version than you do:

Version 1906 (Build 11727.20230 Click-to-Run)

and have not noticed (granted, have not been looking for) the problem