#CALC error using xl() function with Python

Question

Hi all,

I have started with Python in excel, which I'm generally quite happy with.

However, from one day to the other I suddently get a #CALC error when I use the xl() function to assign a range to a dataframe. The data range contains formulas and if I copy/paste values of the formulas in the range the #CALC error disappears and everything works. It used to work well, and I have used version history to go back and look at old version that I know for a fact worked well- and they too suddenly show #CALC Error. For clarity, the data range does not include any cells with erros. Does anyone know what the problem could be? Thx

Moved from Microsoft 365 and Office / Excel / For home / Windows

Answer 1

Ok, I think I found out what the potential issue is.

What I have been able to notice is that when using xl() to set up a dataframe i.e "df = xl()", is that the #CALC error only happens when the dataframe is on the larger side. (Also make sure to press F9 to refresh the runtime)

For example:
When selecting just the first row of my datarame the following works:
df = xl("D1:S2", headers=True)

However, if I try to add just one more column to this small dataframe, it no longer works:
df = xl("D1:T2", headers=True) ;This results in #CALC!

For context the entire dataframe that I am trying to use is D1:$W463, which is really not that large when you think about it.
However, each of those cells contains very heavy regular excel functions, and at that point it, surpasses the 100mb limit that each excel-python cell is allowed according to this MSFT #CALC! Article.

The solution here is then to paste-value over your own dataframe so that the dataframe range holds less memory and the cell can actually process it.

Once, I replaced the dynamic dataframe with a static-values-only dataframe, I was able to use python again.

The issue that remains here is why/when did this change, because the workbook I'm referencing was working fine, and then one day it wasn't.

Answer 2

This is really helpful. And basically means Python in Excel is worthless for my needs if I always have to duplicate a dynamic formula-driven cell range into a static range of values. Hopefully this will be rectified in the future.

Answer 3

I am also experiencing the same issue.
I had a fully working excel sheet with PY() logic on it, and it suddenly stopped working and I now get the #CALC error as well.
Python functions like the xl() mentioned above no longer work.

The article listed does not apply to this situation, since it only covers regular Excel functions and not PY() functions.

To my knowledge, nothing changed in my computer to cause such an issue.

Answer 4

I'm seeing the exact same behavior. I recognize that Python in Excel is in preview and subject to change, but assigning a range to a dataframe is as basic as it gets. And the support article you linked has no references to Python related #CALC errors. If I audit a PY cell that does nothing but define a dataframe from a range, the specific error listed is "Unsupported calculation." Below are the complete contents of this particular PY cell (which has worked correctly since the Python preview was released and stopped working this week as far as I can tell). The cell references a named table.

df = xl("tbl1[#All]", headers=True)

Has there been a change in the syntax to define a dataframe?

Answer 5

Hello Jacob,

Thank you for posting here in Microsoft Community.

I understand that you are experiencing an issue with the Python in Excel feature, and are encountering the #CALC! error. I'd be happy to help you with this.

Firstly, I'd like to inform you that Python in Excel is currently in preview and is subject to change based on feedback. Our engineering team is continuously working on improving this feature.

Regarding the #CALC! error, this occurs when Excel's calculation engine encounters a scenario it does not currently support. I would suggest referring to this article on how to address specific #CALC! errors: How to correct a #CALC! error - Microsoft Support

If none of these solutions work, I would suggest sending a report by providing feedback, as our engineering team is gathering data and feedback to improve and fix the flaws of this feature. On your Excel select File or Help > Feedback then select Report a problem.

I hope this information helps!

Best regards,

Arquel C.

Microsoft Moderator