How to use Python in Excel for finance: forecasting, data cleaning and charts

Python in Excel lets an analyst write Python code directly in a worksheet cell, read ranges and tables from the same workbook, and return the result either as a normal Excel value or as a Python object that later cells can reuse. For finance teams the practical gain sits in three recurring jobs: cleaning exports from accounting and banking systems, producing simple statistical forecasts, and drawing charts that standard Excel charts handle awkwardly. The calculation runs in the Microsoft Cloud, so nobody on the team needs a local Python installation.

The feature has a set of documented requirements and restrictions that decide whether it fits a given finance workflow. It is available on Windows, Mac and the web for Enterprise and Business subscriptions, it needs an internet connection, it cannot read files from the local disk, and the code it runs has no network access. The Microsoft support pages describing these points were checked for this piece on 1 November 2026.

AI assistants enter the workflow at the code-writing stage. Many accountants and FP&A analysts can describe a transformation precisely but do not write pandas fluently, and a general-purpose assistant can draft the code from a plain-English request. Microsoft's support article for the former Copilot in Excel with Python experience now displays a retirement notice, so the workflow below treats the assistant as a drafting tool: the analyst pastes the code into a Python cell, runs it, and checks the output against independently known figures.

··········

WHAT PYTHON IN EXCEL DOES FOR FINANCE TEAMS.

A Python cell is started from Formulas, Insert Python, or by typing =PY in a cell and selecting PY from the AutoComplete menu. Inside the cell, the custom xl() function reads workbook data: xl("A1") reads a single cell, xl("B1:C4") reads a range, and xl("MyTable[#All]", headers=True) reads a whole Excel table with its header row treated as column names. Microsoft's documentation states that xl() can also reference names, images and Power Query connections.

The output of each Python cell is set to one of two types. An Excel value is converted to the closest Excel equivalent, so ordinary formulas, charts and conditional formatting can act on it. A Python object stays inside the Python environment and shows a card icon in the cell; this is the right choice for a cleaned DataFrame that later Python cells will reuse. The PY function documentation describes the same choice as a return_type argument of 0 for an Excel value and 1 for a Python object.

Calculation order is the detail that most often trips up first-time users in a finance model. Statements inside one cell run from top to bottom, and Python cells across the workbook run in row-major order, from column A to column XFD and then down to the next row, following worksheet order across sheets. A cell that cleans the data therefore has to sit above or to the left of the cell that forecasts from it, and both have to sit before any cell that charts the result.

Several libraries are imported by default. According to the open-source libraries page, Matplotlib is available as plt, NumPy as np, pandas as pd, seaborn as sns and statsmodels as sm. Other libraries in the Anaconda distribution supplied with the feature, such as scikit-learn, can be imported with an import statement in a Python cell. Data must come from the worksheet or from Power Query, and Microsoft states that external data functions such as pandas.read_csv and pandas.read_excel are not compatible with Python in Excel.

For readers who already use AI inside Excel for planning work, the same disciplines described in how to build a 13-week cash flow forecast in Excel with Copilot and Claude apply here: the model structure stays in the workbook, and every AI-produced number is reconciled to a known control total.

··········

PYTHON IN EXCEL REQUIREMENTS, PLANS AND LIMITS.

The availability page lists different conditions for business and consumer subscriptions. Enterprise and Business users on Windows need Current Channel Version 2408 (Build 17928.20114) or later, Monthly Enterprise Channel Version 2408 (Build 17928.20216) or later, or Semi-Annual Enterprise Channel Version 2502 (Build 18526.20472) or later. On Mac, Enterprise and Business users need Current Channel Version 16.96 (Build 25041326) or later. Family and Personal subscribers have preview access only, and Education users on Windows reach the feature through the Microsoft 365 Insider Program.

Compute capacity is the second consideration. Qualifying subscriptions include standard compute with automatic recalculation. A paid Python in Excel add-on, bought by an administrator, adds premium compute and the manual, partial and automatic recalculation modes. Microsoft notes that add-on licences can take 24 to 72 hours to apply across several computers. A finance team running heavy recalculations during month-end close should decide in advance whether standard compute is enough, because a slow recalculation on the last working day of the close is costly.

........

Item	Documented position
Business plans	Windows, Mac and web for Enterprise and Business
Consumer plans	Preview only for Family and Personal
Mobile	Not supported on iPad, iPhone or Android
Unsupported licences	Device-based, shared computer activation, free and perpetual consumer
Compute	Standard included; premium via paid add-on
Connection	Internet access required
Data sources	Worksheet ranges, tables, names and Power Query
External files	pandas.read_csv and pandas.read_excel not compatible

........

Workbooks that contain Python can still be opened on iPad, iPhone and Android, but Python cells show an error when they recalculate. A model shared with a board member who reviews it on a tablet will therefore display stale or broken Python results unless the outputs are also converted to static values on a separate sheet.

··········

CLEANING A MESSY ACCOUNTS PAYABLE EXPORT WITH PYTHON IN EXCEL.

The first worked example is a vendor invoice export, a typical source of friction before an accounts payable review. The data below is illustrative. It contains the faults that appear in real exports: inconsistent capitalisation and trailing spaces in vendor names, amounts imported as text with thousands separators, a credit note shown in parentheses, and one invoice duplicated by the extraction.

........

Date	Vendor	Invoice	Amount
2026-09-02	Northwind Logistics	NW-1041	1,250.00
2026-09-05	northwind logistics	NW-1042	980.50
2026-09-05	northwind logistics	NW-1042	980.50
2026-09-11	Contoso Office Supply	CO-2210	312.40
2026-09-14	Fabrikam Energy	FE-0915	2,145.00
2026-09-19	CONTOSO OFFICE SUPPLY	CO-2231	(45.00)
2026-09-23	Fabrikam Energy	FE-0930	2,310.75
2026-09-28	Northwind Logistics	NW-1057	1,075.25

........

With the data formatted as an Excel table named APExport, the analyst can ask an AI assistant for the code with a request that names every rule explicitly. Vague requests such as "clean this data" leave the assistant to guess how credit notes and duplicates should be treated, and those guesses are where errors start.

I use Python in Excel. My table is APExport with columns Date, Vendor,
Invoice, Amount. Read it with xl("APExport[#All]", headers=True).
Write pandas code that: trims spaces and converts Vendor to title case;
converts Amount from text to numbers, removing thousands separators and
treating values in parentheses as negative; removes rows that repeat the
same Vendor, Invoice and Amount; returns the cleaned DataFrame.
Use only pandas (pd) and NumPy (np). Do not read any external file.

A correct answer looks like the code below. It uses only the default pandas and NumPy imports and reads the data through xl(), which is the only route into the Python environment.

df = xl("APExport[#All]", headers=True)
df["Vendor"] = df["Vendor"].str.strip().str.title()
amt = df["Amount"].astype(str).str.replace(",", "", regex=False)
neg = amt.str.startswith("(")
amt = amt.str.strip("()").astype(float)
df["Amount"] = np.where(neg, -amt, amt)
df = df.drop_duplicates(subset=["Vendor", "Invoice", "Amount"])
df

Setting this cell's output to Python object keeps the cleaned DataFrame available to a summary cell placed below it, such as df.groupby("Vendor")["Amount"].sum() returned as an Excel value. The expected result, computed independently, is shown in the next table. The cleaned table has seven rows instead of eight, and the total payable is 8,028.90.

........

Vendor	Invoices kept	Net amount
Contoso Office Supply	2	267.40
Fabrikam Energy	2	4,455.75
Northwind Logistics	3	3,305.75
Total	7	8,028.90

........

The control figure is the reconciliation between the raw and cleaned totals. Adding every raw amount as a positive number gives 9,099.40. The difference of 1,070.50 is made up of the duplicated invoice NW-1042 for 980.50 and twice the Contoso credit note of 45.00, once because it was wrongly added and once because it should have been deducted. If the AI-drafted code produces any other total, the parentheses rule or the duplicate rule has been applied incorrectly.

··········

BUILDING A TREND FORECAST WITH NUMPY IN EXCEL.

The second example is a simple revenue trend forecast for the next quarter. The twelve months of revenue below are illustrative and sit in an Excel table named Revenue with the columns Month and Revenue. A linear trend is a deliberately modest model: it is easy to explain to a CFO, easy to audit, and a reasonable baseline against which a driver-based forecast can be compared.

........

Month	Revenue	Month	Revenue
1	412,000	7	438,600
2	398,500	8	461,400
3	431,200	9	474,200
4	425,800	10	469,800
5	447,300	11	488,500
6	452,900	12	503,100

........

The prompt to the assistant again states the method, the inputs and the output format, so the result can be compared with a known answer.

I use Python in Excel. Table Revenue has columns Month (1 to 12) and
Revenue. Fit a straight-line trend of Revenue on Month with np.polyfit,
then return a DataFrame with Month 13, 14 and 15 and the forecast
revenue rounded to whole units. Also return the slope and intercept.

rev = xl("Revenue[#All]", headers=True)
m = rev["Month"].to_numpy()
slope, intercept = np.polyfit(m, rev["Revenue"], 1)
future = np.arange(13, 16)
fc = pd.DataFrame({"Month": future, "Forecast": (intercept + slope * future).round(0)})
fc

The expected output is a slope of about 8,224.83 per month and an intercept of about 396,813.64. The forecasts are 503,736 for month 13, 511,961 for month 14 and 520,186 for month 15. The coefficient of determination of the fitted line is about 0.91, which says the trend explains most of the month-to-month variation in this sample, while leaving visible noise around it.

A three-month moving average is a useful companion series because it shows whether recent months run above or below the fitted line. In this dataset the moving average for months 10 to 12 is 487,133.33, lower than the actual month 12 figure of 503,100, which reflects the acceleration in the final quarter. A forecast that extends a linear trend through such a period should be presented as a baseline, with the commercial assumptions for the coming quarter discussed separately.

For analysts who also use Copilot for model building in the grid, Microsoft Copilot in Excel for spreadsheet analysis, automation and financial modeling covers the formula-based side of the same work.

··········

CREATING FINANCE CHARTS WITH MATPLOTLIB IN PYTHON IN EXCEL.

Charts are where Python in Excel adds most for presentation work, because a single cell can overlay actuals, a fitted trend and a forecast with different line styles. Matplotlib is imported by default as plt and seaborn as sns. Placed below the forecast cell, the code below draws the actual series and the trend line, reusing the variables defined earlier because Python cells calculate in row-major order.

plt.plot(m, rev["Revenue"], marker="o", label="Actual")
plt.plot(future, fc["Forecast"], linestyle="--", marker="o", label="Trend forecast")
plt.title("Monthly revenue and linear trend")
plt.xlabel("Month")
plt.ylabel("Revenue")
plt.legend()

Microsoft's documentation explains that plots are returned as image objects by default, previewed through the card icon. Changing the output to Excel value places the image inside the cell, where it scales with the cell size, and the Display Plot over Cells command creates a resizable copy that floats over the grid while the original stays in its cell. Non-Latin labels render as empty squares unless a font is set with excel.FontPath, which matters for groups that report in several languages.

For a board pack, the floating copy is usually the practical choice because it can be sized and positioned like a normal chart. The cell-based image is better suited to a working model, where the chart moves with the rows it describes.

··········

REVIEWING AI-DRAFTED PYTHON CODE IN FINANCE WORKBOOKS.

AI-drafted code for these tasks tends to fail in predictable ways, and the review should look for them first. Assistants sometimes reach for pandas.read_csv or pandas.read_excel to load data, which Python in Excel does not support. They may treat negative amounts in parentheses as positive, drop duplicates on the invoice number alone and so remove legitimate partial payments, or silently convert dates to text. In forecasts, a common slip is to fit the trend on the row index of a table that is not sorted by month.

Four controls keep the output reliable. The first is a control total: the cleaned data must reconcile to the source system total, with every difference explained, as in the 1,070.50 bridge above. The second is a record count, comparing rows in and rows out. The third is an independent recalculation of at least one output, for example the month 13 forecast computed with Excel's own FORECAST.LINEAR function on the same data. The fourth is code review by someone other than the person who prompted the assistant, because the person who wrote the request tends to read the code as doing what was asked.

Decisions remain with people. Which duplicates are true duplicates, whether a credit note belongs in the period, and whether a linear trend is an acceptable basis for a budget discussion are accounting and management judgements. The Python cell documents the method; it does not approve it.

Teams comparing assistants for spreadsheet work may find the evaluation in ChatGPT 5.4 vs Microsoft Copilot for spreadsheet analysis useful when choosing which tool drafts the code.

··········

DATA SECURITY AND PRIVACY IN PYTHON IN EXCEL.

Microsoft's data security page describes the execution environment in specific terms. Python code runs in a secure container on the Microsoft Cloud, isolated at the hypervisor level, with one container per workbook. The code has no network access and no access to the user's computer, devices, account or user token, and it cannot read formulas, charts, PivotTables, macros or VBA in the workbook. Results come back only through the =PY() function.

On retention, Microsoft states that data is not persisted in the Microsoft Cloud, that no data persists between sessions or users, and that containers are destroyed after use. The same page states that data is not shared with third parties, including Anaconda, and is not used for AI training by Copilot. Containers for EU customers run in the EU, and the page lists GDPR and EU Data Boundary compliance.

These statements cover Python in Excel itself. When an analyst pastes sample data into a separate AI assistant to get code drafted, that data is governed by the assistant's own terms and the organisation's AI policy. A safe practice is to describe the table structure and column names to the assistant, or to paste a few anonymised rows, and to keep the real ledger data inside the workbook where the Python cell reads it.

Excel Protected View does not run Python formulas in workbooks downloaded from the internet, and administrators can configure a registry setting that shows a warning or blocks Python functions, which then return #BLOCKED!. Finance teams receiving models from outside parties should leave these protections in place.

··········

PYTHON IN EXCEL FOR FINANCE: WHERE IT FITS AND WHERE IT DOES NOT.

Python in Excel is a documented, supported way to run pandas, NumPy and Matplotlib inside a finance workbook without a local installation, on Windows, Mac and the web for business subscriptions. Its data boundary is the workbook and Power Query, its code has no network or local file access, and Microsoft states that data does not persist after the session. Those properties make it suitable for cleaning exports, reconciling totals, fitting simple forecasts and producing presentation charts.

The limits are equally concrete. Mobile devices show errors on recalculation, consumer plans are in preview, heavy recalculation may need the paid add-on, and calculation order follows the grid, so model layout has to be planned. The Copilot in Excel with Python support article now shows a retirement notice, so AI assistance currently works best as a drafting step outside the cell, followed by review. In both worked examples the correct outputs were known in advance, 8,028.90 for the cleaned payables and 503,736 for the first forecast month, and that habit of fixing the expected answer before running AI-drafted code is the control that makes the method safe to use in month-end work.

··········

FOLLOW US FOR MORE.

·····

DATA STUDIOS

·····

datastudios.org