Python and Excel: Complete Guide (Automation + Python inside Excel)

Introduction

Python and Excel form a powerful combination for automating reports, analyzing data, and creating dynamic dashboards.

Today there are two main ways to work with both:

Python outside of Excel → using libraries like pandas, openpyxl, or xlsxwriter to read and write Excel files.
Python inside Excel (official from Microsoft, 2023) → run Python code directly in an Excel cell using the native integration.

In this guide, we show you how to take advantage of both.

Full automation of reports and spreadsheets.
Large-scale data analysis beyond Excel’s limits.
Advanced charts and visualizations with libraries like matplotlib or seaborn.
Database and API integration.
New official integration → run Python directly in Excel without the need for macros.

To work with .xlsx or .xls files using Python, widely known libraries are used:

Example: reading an Excel file with pandas:

import pandas as pd

df = pd.read_excel("file_excel.xlsx")
print(df.head())

Since 2023, Microsoft announced the integration of Python in Excel. Now you can write directly in a cell:

=PY("import pandas as pd; df = pd.DataFrame({'A':[1,2,3]}); df")

No installation required → works with Microsoft 365.
Uses pandas, matplotlib, seaborn, and scikit-learn inside Excel.
Enables advanced analysis (statistics, machine learning) directly in your sheets.
You can combine Excel functions with Python code.

Practical example:

=PY("import matplotlib.pyplot as plt; import seaborn as sns; sns.histplot([1,2,2,3,3,3,4,4,4,4])")

This generates a histogram directly in Excel.