Why @ operator fixes the circular references?

Question

Hi team,

Can someone explain why the @ operator resolves circular references?

We encountered a #CALC! error due to a circular reference:

[Moderator note: Personally Identifiable Information removed]

To understand how the formula is evaluated, I used the Formulas tab (Formula Auditing > Evaluate Formula)

Received a response from filed GitHub issue, which explained that the @ operator forces the formula to return a single value that breaks the circular dependency. However, in this case, the formula is not a spilling range nor reference a spilling range.

Answer 1

Hi @Eunice Patricia Villanueva, 

I’d be glad to share my perspective here based on what I know. 

The key thing to understand is that the @ operator isn’t actually fixing a circular reference directly. What it does is change how Excel interprets that part of the formula by forcing implicit intersection, meaning Excel is told to return a single value instead of treating the expression as something that could result in a range or array. 

In modern versions of Excel with dynamic arrays, the calculation engine is more “array-aware” by default. That means even when a formula doesn’t visibly spill on the worksheet, Excel may still internally treat part of the expression as capable of returning multiple values. When Excel builds its dependency chain under those conditions, it can sometimes end up feeding the formula back into itself, which leads to a circular reference or a #CALC! error. 

This is where the @ operator comes into play. Microsoft documents @ as the implicit intersection operator. Its job is to deliberately narrow a range- or array-capable expression down to a single value, specifically the one that intersects with the formula’s row or column. This closely mirrors how older versions of Excel behaved before dynamic arrays existed, except that the narrowing used to happen automatically and invisibly.  

So in scenarios like yours, adding @ effectively pins the evaluation to a single value. That change can alter how Excel interprets dependencies and may break a calculation path that would otherwise be treated as circular, which can prevent the #CALC! error in some cases. It’s less about fixing the formula’s logic and more about changing Excel’s calculation context so it no longer evaluates the expression in a way that leads to a circular or unsupported result. 

There’s a related document that I’ve found quite helpful as well, and you may want to take a look at it for further reading: Implicit intersection operator: @ - Microsoft Support

Hopefully this explanation will be helpful for your reference, and thank you for raising such an interesting and well-observed question.  

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Excel raises a #CALC! error when the calculation engine detects a circular dependency that it cannot resolve with its supported iteration rules. This is similar in spirit to a classic circular reference (a formula depending on its own result), but in dynamic-array workbooks the dependency graph can be created through array operations and spilled ranges, not only through direct cell-to-cell references.

The @ operator changes how Excel interprets a formula in that dependency graph:

• Without @, Excel evaluates the function in fully dynamic-array mode. The function can conceptually return an array, and Excel tracks dependencies for the entire potential array result. In some cases this causes Excel to see a dependency path that leads back to the same formula (directly or indirectly), so the engine stops and returns #CALC!.
• With @, Excel applies implicit intersection and forces the expression to be treated as a single scalar value at the current cell. That restriction removes the array semantics from the dependency graph. As a result, the formula no longer participates in the circular array dependency Excel previously detected, so the calculation engine can complete and the #CALC! error disappears.

In other words, @ does not “fix” a circular reference by changing the underlying logic; it changes the shape of the calculation (array → single value). That change alters the dependency graph enough that Excel no longer sees an unsupported circular array dependency and therefore no longer returns #CALC!.

---

References:

• How to correct a #CALC! error
• How to avoid broken formulas in Excel
• Remove or allow a circular reference in Excel