AI Agents and Excel: Using Spreadsheets as Data Source and Output
In most mid-sized companies, the spreadsheet is the real system of record for a surprising amount of work: price lists, bills of materials, project trackers, inspection logs, cost estimates. If you want an AI agent to help with that work, it has to deal with Excel. The useful question is not whether a model can read a spreadsheet, but how to let it do so without quietly changing the numbers.
What does connecting an AI agent to Excel actually mean?
It means giving the agent a small set of tools that read, check and write spreadsheet data in a controlled way. The language model interprets the request and chooses which tool to call. The tools themselves are ordinary code, so every figure is taken from the cell as it is rather than retyped by the model.
Compare that with dragging a file into a chat window. The assistant converts the workbook into a long block of text and answers from it. For a quick look, that is fine. For a process that has to produce the same kind of output every Monday, it breaks down: results vary from run to run, large workbooks do not fit into the context window, and nobody can trace which cell a statement came from.
Common jobs for an Excel-connected agent:
- Analysis: revenue per customer, open items per project, variance between budget and actuals.
- Consolidation: merging supplier lists that each use different column names into one consistent layout.
- Validation: finding missing mandatory fields, wrong units, duplicates or outliers in a parts list or test log.
- Output: writing a quote calculation, a monthly report or a purchase list into a template people already use.
How does it work? The building blocks
There are three building blocks: a way to reach the file, tools the model may call, and rules about what the agent is allowed to change. The model never edits the grid directly. It calls functions, receives compact results and explains them.
Access. If the workbook sits on a file share or arrives as an email attachment, the agent opens it locally with a library. If it lives in OneDrive for Business or SharePoint, the agent can work on it in place through the Microsoft Graph Excel API without downloading a copy.
Tools. Instead of "read the whole file", the agent gets narrow functions with clear inputs: list sheets, read the header row, filter a column, compute a total, append rows to a named table. Each returns a short structured answer, which keeps the context small and makes every statement traceable to a tool call. If you run several agents, the article on MCP servers for connecting AI agents explains how to expose such tools once and reuse them.
Rules. Reading is low risk; writing is not. A sound default is that the agent never overwrites a source file. It writes into a copy or into a dedicated output table. Which sheets are off limits, and how many changed rows require a human sign-off, belongs in configuration, not in the prompt.
Read through code, not copy and paste
For local files, the usual Python route is pandas. According to the 3.1.0 documentation, read_excel handles xls, xlsx, xlsm, xlsb and ods files and delegates to different readers, for example openpyxl for current Excel files and xlrd for legacy .xls. A tool for the agent might look like this:
import pandas as pd
def read_table(path: str, sheet: str, columns: list[str]) -> dict:
"""Read selected columns from one sheet and return a compact summary."""
df = pd.read_excel(path, sheet_name=sheet, engine="openpyxl", dtype=str)
missing = [c for c in columns if c not in df.columns]
if missing:
return {"error": f"missing columns: {missing}", "available": list(df.columns)}
part = df[columns]
return {
"rows": len(part),
"empty_cells": int(part.isna().sum().sum()),
"preview": part.head(20).to_dict(orient="records"),
}
The function deliberately returns a preview and a few counts, not the full sheet. When the agent needs a sum, it calls a second tool that computes the sum in code and returns one number. Reading everything as text with dtype=str protects part numbers with leading zeros from being turned into integers; numeric columns are converted explicitly afterwards.
Formulas: the formula or its result?
Many workbooks are full of formulas. By default openpyxl returns the formula string; with data_only=True it returns the value stored the last time Excel read the sheet. The documentation is explicit that openpyxl never evaluates formulas. A file generated by another program and never opened in Excel may therefore have no stored result at all.
The practical consequence: the agent needs to know which cells are inputs and which are results. It reads results as stored values, writes only inputs, and leaves recalculation to Excel, either when someone opens the file or in the cloud through the Graph API.
Writing into templates
The safest output is a fixed template with a header block, a table with named columns, total formulas and formatting already in place. The agent only fills input cells. Layout stays exactly what colleagues and customers expect, and the model never has to invent formatting.
from openpyxl import load_workbook
def fill_template(template: str, target: str, lines: list[dict]) -> str:
wb = load_workbook(template) # formulas in the template stay formulas
ws = wb["Lines"]
first_row = 5 # first data row in the template
for i, line in enumerate(lines):
ws.cell(row=first_row + i, column=1, value=line["item"])
ws.cell(row=first_row + i, column=2, value=line["qty"])
ws.cell(row=first_row + i, column=3, value=line["unit_price"])
wb.save(target) # never save over the template itself
return target
The values in lines come from tools that read prices and quantities from a source system, not from the model's free text. Tested with openpyxl 3.1.5 and pandas 3.1.0 on Python 3.12, as of November 2026; pandas 3.1.0 refuses to use openpyxl versions older than 3.1.5.
Workbooks in Microsoft 365 via the Graph Excel API
When the file is stored in OneDrive for Business or SharePoint, Microsoft Graph offers endpoints for worksheets, ranges, tables, charts and even Excel's own functions. The main benefit is that the Excel backend does the calculating on the server and the file never leaves its library. Microsoft specifically suggests non-persistent sessions for apps that only need calculation results.
Points from Microsoft's documentation that matter for agents:
- Only Office Open XML workbooks are supported; .xls files are not, and neither are files in consumer OneDrive accounts.
- For more than one call, Microsoft recommends creating a session with
createSessionand passing its ID in theworkbook-session-idheader. A non-persistent session does not save changes back to the file, which suits pure calculations. - The documentation states that a persistent session typically expires after about 5 minutes of inactivity and a non-persistent one after about 7 minutes.
- The best-practice guide advises against parallel write requests to the same workbook and recommends sending the next request only after the previous one has succeeded.
A tool that appends one row to a named table in a SharePoint workbook:
import os
import requests
GRAPH = "https://graph.microsoft.com/v1.0"
def append_row(drive_id: str, item_id: str, table: str, values: list) -> dict:
headers = {"Authorization": f"Bearer {os.environ['GRAPH_TOKEN']}"}
base = f"{GRAPH}/drives/{drive_id}/items/{item_id}/workbook"
s = requests.post(f"{base}/createSession", json={"persistChanges": True}, headers=headers)
s.raise_for_status()
headers["workbook-session-id"] = s.json()["id"]
try:
r = requests.post(f"{base}/tables/{table}/rows/add",
json={"values": [values]}, headers=headers)
r.raise_for_status()
return r.json()
finally:
requests.post(f"{base}/closeSession", json={}, headers=headers)
Microsoft lists Files.Read for read access and Files.ReadWrite for write access. Grant the agent only what its job needs: an agent that only reports should not hold write permissions.
Use cases: when Excel is the source and when it is the output
Spreadsheets play two roles for an agent: a source of facts and figures, and a destination for results. Many processes need both. Decide the role per file up front so the agent can never modify something it should only read.
Excel as a source. Price lists, discount tiers, lead times, spare-parts lists and project trackers are often maintained by one person in a single workbook. An agent that prepares quotations reads prices and lead times from it; an agent supporting technical service looks up the right part number. Quality of reading depends on the sheet: clear column names, one header row, no merged cells in the data area.
Excel as output. Cost estimates, monthly reports, purchase lists and inspection records often have to end up as spreadsheets because the team keeps working in them. Here the agent fills a template, as shown above, and leaves the template's formulas and formatting alone.
Excel as something to check. Incoming supplier sheets or measurement logs can be validated against rules: mandatory fields, plausible ranges, units, duplicates. The agent writes a short findings report and flags rows; it does not edit the file it received. If the same data arrives as PDFs or scans instead, text extraction comes first, which is covered in making documents, PDFs and scans usable for AI agents.
Excel as a bridge. Often the workbook is just an export from an ERP or CRM. If that system has an API, connecting the agent to it directly is usually more robust. The guide on connecting an AI agent to databases covers that route. The key difference: with a database the agent queries a fixed schema, while with a spreadsheet it first has to work out the layout and cope with variations.
What do you need before you start?
The main prerequisite is not technology but a spreadsheet whose structure does not change every week. An agent can cope with messy data as long as the mess is consistent. Beyond that you need a defined storage location, access rights and one person who owns the sheet's content.
Data. Check your workbooks for these points:
- One header row per table, no headings spread across several rows.
- No merged cells inside the data area.
- One piece of information per column, not "quantity plus unit" in one cell.
- Data ranges formatted as Excel tables (Insert, Table) with meaningful names. The Graph API can address tables by name.
- No subtotal rows in the middle of the data.
Size. A worksheet can hold 1,048,576 rows by 16,384 columns, according to Microsoft's specifications. That is modest for Excel and enormous for a model's context. So the agent never pulls whole sheets into the conversation; it uses tools that filter and aggregate. Microsoft also recommends reading or writing large ranges in several smaller chunks.
Systems. For local files, a machine with Python and read access to the share is enough. For Microsoft 365 files, the agent needs to authenticate through the Microsoft identity platform with appropriate permissions.
Ownership. Agree who maintains the source sheet, who reviews the output and what happens when the agent cannot find a column. Without that, errors stay silent until a customer spots them.
A step-by-step approach
Build up gradually: start with reading only, and enable writing once reading has run cleanly for several weeks. That way you learn where your spreadsheets are inconsistent before the agent changes any data.
- Pick the process and the files. Write down what the agent should do, which files are sources, which are outputs, and where they live.
- Tidy the sheets. Standardise headers, unmerge cells, convert ranges into named Excel tables. This step tends to pay off even without an agent.
- Build read tools. List sheets, read headers, filter, aggregate. Each tool returns a clear error when something is off, for instance "column unit_price not found".
- Add validation tools. Mandatory fields, value ranges, duplicates. The agent reports findings instead of silently fixing them.
- Introduce template output. Create a fixed template, mark input cells, protect formulas, and let the agent write only to copies.
- Run on real files. Process real workbooks for a few weeks and have a domain expert compare the results with the manual version.
- Approve and log. Record source, timestamp and tool calls for every output. Only then consider taking the human out of routine checks, if at all.
In our own operations, step 2 regularly takes longer than building the agent. That is not wasted effort: a clean spreadsheet helps everyone who touches it.
Limits and risks
Spreadsheets are convenient, but they are not the right home for every dataset. Most problems arise when several people and agents write at the same time, when the layout changes without notice, or when the model retypes figures from text instead of taking them from cells through code.
Concurrent writes. Microsoft's guidance notes that parallel write requests to the same workbook commonly lead to throttling, timeouts and merge conflicts. If several agents or people edit one file at once, a database is the sturdier home and Excel becomes an export.
Layout changes. Rename a column or insert a sheet and the agent loses track of the data. Good tools detect this and stop with a readable message rather than carrying on with the wrong column.
Invented numbers. Give the model table contents as text and let it do arithmetic, and it can misread values. The cure is the division of labour described here: arithmetic in code, explanation in the model.
Formulas and macros. openpyxl does not recalculate, and an agent should not run macros in .xlsm files. If current formula results matter, use the Graph API or let Excel recalculate the file.
Personal data. Sheets often contain contact details or employee numbers. Decide which columns the agent really needs and filter the rest out before anything reaches the model. Where the data is processed affects whether the setup can be operated in line with the GDPR and similar laws. Involve your data protection lead for a proper assessment; this article is not legal advice.
When this approach is the wrong one. If the data already lives in an ERP with an API and Excel is merely a manual stopover, connect the agent to the source system instead. A spreadsheet integration makes sense where the workbook genuinely is the authoritative source, or where the result has to be a spreadsheet.
How do you get started?
Start with one read-only process whose output someone already produces by hand, such as a weekly summary from a project tracker. That gives you a direct comparison between agent and manual work. Add template output only once reading is stable.
A short starting list:
- Choose one workbook with a single owner and a stable layout.
- Format the data range as a named Excel table.
- Build two or three read tools that filter and summarise.
- Run them alongside the manual process for a few weeks and compare.
- Then decide: add template output, or plan to move the data into a database over time.
For examples of agents built around specific business tasks, such as sales or knowledge management, see the overview of AI agents for business teams. Guides on connecting other systems, from databases to ERP and mailboxes, are collected in the guides section.
Frequently asked questions
Why not just upload the spreadsheet to a chat assistant?
That works for a one-off question. For a weekly process the model sees flattened text rather than cells, may copy numbers wrongly and produces differently shaped results each time.
Can the agent read legacy .xls files?
Yes, locally: pandas reads .xls through the xlrd or calamine reader. The Microsoft Graph Excel API, however, only accepts Office Open XML workbooks such as .xlsx.
Does the agent recalculate formulas?
Not when it reads files with openpyxl, which returns either the formula or the value Excel last stored. If fresh results matter, work through the Graph Excel API, where Excel itself does the calculation.
At what point should the spreadsheet become a database?
When several people and agents write at the same time, when data is spread over dozens of files, or when you need history over years. Excel often stays on as the import and export format.
Sources
- Microsoft Learn: Working with Excel in Microsoft Graph learn.microsoft.com
- Microsoft Learn: Best practices for working with the Excel API in Microsoft Graph learn.microsoft.com
- pandas 3.1.0 documentation: pandas.read_excel pandas.pydata.org
- openpyxl documentation: Tutorial (data_only) openpyxl.readthedocs.io
- openpyxl documentation: Simple Formulae openpyxl.readthedocs.io
- PyPI: openpyxl pypi.org
- Microsoft Support: Excel specifications and limits support.microsoft.com