Unlock Excel’s Power: A Complete Guide to Python in Excel

1. Introduction to Python in Excel

Python in Excel is a new Microsoft 365 feature that allows users to write Python code directly in Excel, leveraging powerful computation and visualization libraries for enhanced data analysis.

1. Advantages

Built‑in Python computation : Supports popular libraries such as Pandas, Matplotlib, Seaborn, etc.

Cloud execution : Python runs on Microsoft’s cloud, no local environment required.

Tight Excel integration : Cells can be referenced and results returned to the worksheet.

Data visualization : Use Matplotlib and Seaborn to create advanced charts.

2. Supported Versions

Windows Enterprise and Business Microsoft 365 (version 2408+).

Excel on the Web for enterprise and business users.

Personal and Home users on Windows Excel (version 2405+) or Excel on the Web preview.

Mac Excel preview (Beta channel, version 16.95+).

iPad, iPhone, Android Excel are not supported.

2. Enabling Python in Excel

1. Insert Python

Open Excel and create a new workbook.

On the Formulas tab click Insert Python .

In a cell type =PY , then click “Create Python formula”. Enter Python code and press Ctrl+Enter to run.

After running, the Python editor appears in the sidebar.

Test Python availability with a simple expression:

<code>import math; math.sqrt(20)</code>

If enabled, the cell returns 4.4721 .

3. Basic Usage

1. Referencing Excel cells

Use xl('A1') to get the value of cell A1. Example:

<code>xl('A1') + xl('B1')</code>

If A1=5 and B1=10, the result is 15 .

2. Performing calculations

Example of computing a square root:

<code>=PY("import math; math.sqrt(xl('A1'))")</code>

If A1=16, the result is 4 .

You can also select a range, e.g., xl('A1:B9') , useful for Pandas operations.

4. Data Analysis

1. Reading Excel data

<code>import pandas as pd; df = xl('A1:C10', headers=True); df.head()</code>

The result displays a DataFrame.

2. Data cleaning

<code>import pandas as pd; df = xl('A1:B09', headers=True); df.dropna()</code>

3. Statistical metrics

<code>import pandas as pd; df = xl('A1:A100'); mean_value = df.mean()</code>

You can also compute standard deviation with df.std() .

5. Data Visualization

1. Line chart

<code>import matplotlib.pyplot as plt; df = xl('A1:B10', headers=True); plt.plot(df['A'], df['B']); plt.xlabel('X轴'); plt.ylabel('Y轴'); plt.title('Excel 数据可视化')</code>

2. Scatter plot

<code>import matplotlib.pyplot as plt; df = xl("A1:B10", headers=True); plt.scatter(df['A'], df['B']); plt.xlabel('X轴'); plt.ylabel('Y轴'); plt.title('Excel 数据可视化')</code>

3. Seaborn box plot

<code>import seaborn as sns; import matplotlib.pyplot as plt; df = xl("A1:A10", headers=True); sns.boxplot(x=df['A']); plt.title('Seaborn 箱线图')</code>

6. Importing External Data

1. Use Power Query to import CSV and create a connection.

2. Access imported data in Python

<code>import pandas as pd; df = xl('MyTable[#All]', headers=True); df.head()</code>

7. Supported Open‑Source Libraries

The following libraries are available in Python in Excel, covering data analysis, visualization, machine learning, network analysis, and natural‑language processing:

Astropy – astronomy calculations.

beautifulsoup4 – HTML/XML parsing.

Faker – synthetic data generation.

imbalanced-learn – handling imbalanced datasets.

IPython – interactive computing.

Matplotlib – plotting.

mlxtend – machine‑learning utilities.

NetworkX – network analysis.

NLTK – natural language processing.

NumPy – array computing.

pandas – data structures and analysis.

Pillow – image processing.

plotnine – ggplot‑style visualizations.

Prince – multivariate statistics.

PyTables – large‑scale data storage.

PyWavelets – wavelet transforms.

qrcode – QR code generation.

scikit-learn – machine learning models.

SciPy – scientific computing.

seaborn – statistical visualizations.

snowballstemmer – stemming algorithms.

squarify – treemap charts.

statsmodels – statistical modeling.

SymPy – symbolic mathematics.

tabulate – formatted tables.

TheFuzz – fuzzy string matching.

wordcloud – word cloud generation.

8. Common Issues

#PYTHON! error

Check for Python syntax errors.

#BUSY! error

Excel is still calculating; press Ctrl+Alt+Shift+F9 to recalculate.

#CONNECT! error

Excel cannot connect to the Python server; verify network connectivity.

Python in Excel enables efficient data analysis, professional visualizations, and powerful Pandas operations without leaving the spreadsheet.