Critical Review of Python in Excel: Limitations, Use Cases, and Recommendations

Critical Review of Python in Excel

On August 22, 2023 Microsoft released a preview of "Python in Excel". The author, intrigued by the feature, investigated its practical value beyond marketing claims.

Key Observations

Python in Excel is positioned as a replacement for the Excel formula language rather than VBA.

Embedding Jupyter notebook cells directly into the spreadsheet grid is considered a design mistake.

The feature is unsuitable for Python beginners and for interactive data analysis.

Significant restrictions exist: custom packages cannot be installed and network APIs are inaccessible.

Current Use Cases

Compute‑intensive tasks such as Monte Carlo simulations.

AI‑related workloads using built‑in packages (scikit‑learn, nltk, statsmodels, imbalanced‑learn, gensim).

Advanced visualisation with Matplotlib or Seaborn.

Time‑series analysis, a known blind spot for classic Excel.

Data wrangling and analysis, though Power Query often provides a simpler solution.

User Requirements

Early user requests (2015) on UserVoice called for a scripting language and user‑defined functions—features still missing in the current implementation.

Design Flaws

Jupyter‑Cell‑in‑Grid Issue

Traditional spreadsheets calculate cells based on dependency graphs, ignoring physical location. Python cells in Excel, however, are evaluated strictly left‑to‑right, top‑to‑bottom, breaking this core rule.

Code Duplication

Office Scripts store code centrally (SharePoint/OneDrive), allowing reuse across workbooks. Python scripts are stored inside each workbook (xl/pythonScripts.xml), forcing copy‑paste for reuse.

Magic and Hidden Behaviour

Switching a cell from object mode to value mode can silently change formulas (e.g., =PY("...",1) becomes =PY("...",0) ), making debugging difficult.

Non‑Interactive Data Analysis

Unlike Jupyter notebooks, Excel does not display cell outputs directly. Users must convert cells to values or open a preview pane, which is cumbersome and often loses context (e.g., #N/A vs. np.nan).

Diagnostic Panel Issues

All print() output and errors appear in a separate diagnostic panel, detaching results from the originating cell and making it hard to trace which output belongs to which cell.

Cloud Dependency

Python runs on an Azure container instance, incurring extra Microsoft 365 subscription costs and quota limits. Outages or quota exhaustion can render Python cells unusable, which is unacceptable for critical workflows.

Missing Glue‑Language Capabilities

Python’s strength as a "glue" language is lost because external packages, web APIs, and database connections are unavailable. Users are forced to rely on Power Query for data ingestion, contrary to the original intent of using Python.

Code Examples

Accessing multiple sheets without a proper Excel object model requires repetitive code:

<code>jan = xl("Jan[#All]", headers=True)</code>
<code>feb = xl("Feb[#All]", headers=True)</code>
<code>mar = xl("Mar[#All]", headers=True)</code>
<code>...</code>
<code>df = pd.concat([jan, feb, mar, ...])</code>

A proposed custom converter to obtain NumPy arrays or plain lists:

<code>def myconverter(x, headers=False, convert=None, **kwargs):
    if convert is None or convert == pd.DataFrame:
        return excel.convert_to_dataframe(x, headers=headers, **kwargs)
    elif convert == np.array:
        return np.array(x)
    elif convert == list:
        return x
    else:
        raise ValueError(f"{convert} is not supported.")

excel.set_xl_array_conversion(myconverter)</code>

Using the converter:

<code>df = xl("A1:B2", headers=True)</code>
<code>arr = xl("A1:B2", convert=np.array)</code>
<code>mylist = xl("A1:B2", convert=list)</code>

Alternative Directions

The author suggests that integrating a full Jupyter notebook experience into Excel would be more beneficial than the current cell‑based approach, and notes that the feature is still in preview, so future improvements are expected.

Conclusion

Python in Excel is a promising but premature feature; its current limitations hinder both novice and power users. Better integration with the Excel object model, support for custom packages, clearer diagnostics, and reduced cloud dependency are essential for broader adoption.