Back to Blog
AI Implementation

AI integration with Excel: automating spreadsheets safely

How to add AI to Excel safely: Copilot, Python in Excel and custom agents compared, with the version control and data controls finance teams need first.

K

Klevere AI Team

AI implementation

18 November 202611 min read

Most finance teams already run the business on spreadsheets, so AI integration with Excel is less about adding something new and more about deciding how much control to hand over. The stakes are real: a 2024 literature review led by Professor Pak-Lok Poon and published in Frontiers of Computer Science reported that 94% of spreadsheets used in business decision-making contain errors. AI can catch some of those mistakes, and it can also introduce new ones faster than any analyst could.

This guide covers the main ways AI now works inside and around Excel, what each option does with your data, where the risks sit, and how to put controls in place before a model starts writing to the workbooks your month-end close depends on. It is written for readers in the US, UK, EU and beyond, so where regulation is relevant we cover more than one market.

Quick answer

AI integration with Excel means using tools such as Copilot in Excel, Python in Excel or a custom AI agent to read, analyse and edit workbooks. Built-in Copilot suits individual analysis and formula help. Agents that write to shared finance files need version history, permission limits, review steps and testing before they touch anything a decision depends on.

The main ways to add AI to Excel

There are four practical routes, and they differ mainly in who controls the logic and where the data goes.

  • Copilot in Excel: Microsoft's assistant inside the workbook. According to Microsoft's own guide to getting started with Copilot in Excel, it has an edit mode that can change your workbook directly, a plan mode that proposes steps for you to confirm, and a chat mode that answers questions without making changes.
  • Python in Excel: Python code written in cells with the PY function, which Microsoft runs in an isolated cloud container rather than on your machine, as set out in its page on data security and Python in Excel.
  • Add-ins and connectors: third-party tools that sit in the ribbon and send selected ranges to an external model. Their data handling depends entirely on the vendor, so each one needs its own review.
  • Custom AI agents: software outside Excel that opens workbooks through an API, reads or writes ranges, and connects them to other systems such as your accounting platform or CRM. This is the most flexible route and the one that needs the most governance.
  • Most organisations end up with a mix. Analysts use Copilot for exploration, a data team uses Python for heavier modelling, and a small number of agents handle repetitive, well-defined workflows such as reconciliations or report assembly.

    What Copilot in Excel can do now

    Copilot has moved from suggesting steps to acting on them. In April 2026 Computerworld reported that Agent Mode became the default experience in Word, Excel and PowerPoint for Microsoft 365 Copilot and Microsoft 365 Premium subscribers, with Copilot taking action in documents while users keep control. Microsoft's own documentation describes Copilot updating workbooks using Excel's built-in features, and it gives users a stop button to halt it mid-task.

    In practice, that covers jobs finance and operations teams do every week:

  • Building formulas from a plain-language description, including lookups across sheets and conditional logic.
  • Cleaning and restructuring data, such as splitting columns, standardising dates or removing duplicates.
  • Creating pivot tables, charts and summary sheets from a raw export.
  • Explaining an inherited workbook, including what a complex nested formula actually calculates.
  • Drafting variance commentary for a management pack, which a person then checks against the numbers.
  • Platform coverage is uneven. Microsoft states that Windows, Mac and the web support all three modes, while iPhone and Android support chat only. It also notes that Copilot might not be included in every Microsoft 365 subscription or may be switched off by your organisation's settings, so check licensing before you plan a rollout. If you are deciding between Microsoft's built-in assistant and something tailored to your processes, our comparison of Microsoft Copilot for business and custom agents covers the trade-offs in more depth.

    A note on the COPILOT function

    For a period, Microsoft offered a COPILOT worksheet function that returned AI-generated answers directly in cells. Microsoft's COPILOT function support page now states the function is no longer available, with cells returning a #NAME? error when they recalculate and previous results kept as cached values. If any of your workbooks used it, find those cells now, because a model that silently stops recalculating is exactly the sort of quiet failure that ends up in a board pack. Microsoft recommends describing the task to Copilot in Excel instead.

    Python in Excel: power with clear boundaries

    Python in Excel suits work that formulas handle badly: statistical analysis, forecasting, larger data cleaning jobs and charts beyond Excel's native set. Microsoft's Python in Excel security documentation explains that code runs in a hypervisor-isolated container on the Microsoft Cloud, that it does not have network access, and that it cannot reach your computer, devices or account. Data comes in only through references made with the xl function, and Microsoft states the data is not persisted in the cloud, is not shared with third parties and is not used for AI training by Copilot.

    Those boundaries are useful for governance, because they make Python in Excel easier to approve than an add-in that calls an unknown external service. They also define its limits. Python in Excel cannot pull data from your accounting system or post results back to it, so it is a calculation tool rather than an integration tool. Microsoft also notes that Python formulas do not run when a file opens in Protected View, which matters if you circulate workbooks to people outside your organisation.

    Agents that read and write workbooks

    The bigger shift is AI agents that treat a workbook as one stop in a longer process. A typical finance example looks like this: an agent pulls the month's bank feed and ledger extract, populates a reconciliation template, flags unmatched items with a short explanation, and drafts an email to the controller with the exceptions. Another agent might collect supplier invoices, extract the line items and drop them into a coding sheet for review, which is the territory of AI document processing.

    Agents are valuable because they connect Excel to the rest of your systems. They are risky for the same reason. An agent with write access to a shared drive can overwrite the wrong tab, extend a formula across the wrong range or paste values over a live calculation. Unlike a person, it can repeat that mistake across dozens of files in seconds.

    Good agent design for spreadsheets usually follows a few rules:

  • Write to a new output file or a dedicated output tab, never over the source data.
  • Use named ranges or structured tables rather than hard-coded cell addresses, so a layout change breaks loudly instead of silently.
  • Keep the agent's permissions to the specific folders it needs, not the whole shared drive.
  • Log every change the agent makes, with the file, range, old value and new value.
  • Require a person to approve anything that feeds a filing, a payment or an external report.
  • Agents that read spreadsheets supplied by customers or suppliers also face prompt injection, where text hidden in a cell tries to redirect the model. Our guide to AI agent security explains how that attack works and the controls that limit it.

    Finance team use cases worth starting with

    The best first projects share three traits: high volume, a clear right answer and a person who already checks the output. Strong candidates include:

  • Bank and intercompany reconciliations, where the agent matches transactions and a person reviews the exceptions.
  • Accruals and prepayments schedules that follow a stable pattern month after month.
  • Management pack assembly, pulling figures from several workbooks into one template and drafting first-pass commentary.
  • Budget versus actual variance analysis, with the AI explaining movements above a set threshold.
  • Data imports from accounting platforms into reporting models. If you run Xero, our guide to Xero AI integration covers this workflow specifically.
  • Weaker candidates are anything involving judgement calls without a clear reference point, such as impairment assessments or provisioning decisions. AI can help prepare the inputs, but the decision should stay with a qualified person.

    Accuracy and version control risks

    Spreadsheet error is not a new problem, and AI does not remove it. Raymond Panko's widely cited paper Spreadsheet Errors: What We Know. What We Think We Can Do found errors in 91% of the 54 spreadsheets examined in field audits from 1997 onwards, and reported cell error rates of roughly 1% to 2% in laboratory studies. The same paper found that group code inspection caught around 80% of errors, while individuals working alone found about half. That finding still matters: review works best when more than one set of eyes checks the logic.

    AI changes the shape of the risk in three ways. First, volume: a model can generate hundreds of formulas in the time a person writes one, so errors scale with output. Second, plausibility: generated formulas usually look correct, which makes reviewers less likely to trace them through. Third, traceability: if nobody records which cells a model changed, you lose the audit trail auditors and regulators expect.

    Version control is the first line of defence. When workbooks live in SharePoint or OneDrive, Microsoft's version history overview explains that users can view or restore a previous version, and that the restored version becomes the new current version. That gives you a rollback path if Copilot or an agent edits a file badly. It does not tell you what changed or why, so pair it with change logs, protected sheets for core calculations and a simple rule that AI edits to critical files happen in a copy first.

    Data protection and regulation

    Before anyone uploads a payroll file or customer list into an AI tool, confirm what happens to that data. For Microsoft 365 Copilot, Microsoft's enterprise data protection documentation states that prompts, responses and data accessed through Microsoft Graph are not used to train foundation models, that Copilot respects your existing permissions, and that it inherits sensitivity labels. Microsoft also notes that the specific controls vary by subscription plan, so the protections on a business plan may differ from those on an enterprise agreement. Third-party add-ins and custom agents need the same questions answered in their own contracts.

    Regulated firms have further obligations. In the UK, the Prudential Regulation Authority's SS1/23 model risk management principles came into effect on 17 May 2024 and refer to end-user computing applications and calculators among the quantitative methods banks use, with an expectation that deterministic methods not classified as models are still subject to sound, documented controls. In the US, the Federal Reserve, OCC and FDIC issued SR 26-2 revised guidance on model risk management on 17 April 2026, which supersedes the long-standing SR 11-7 and is most relevant to banking organisations with over $30 billion in assets. Even if your firm is outside these regimes, they are a sensible benchmark for how auditors will look at critical spreadsheets that AI has touched.

    For personal data, the GDPR and its UK equivalent apply whatever tool processes it. That means a lawful basis, data minimisation and a processor agreement with any AI vendor. Strip names and identifiers from workbooks before analysis where you can.

    How Klevere approaches AI integration with Excel

    We start by mapping which spreadsheets actually matter. In most organisations a handful of workbooks drive the close, the forecast and the board pack, while hundreds of others are one-off analysis. The critical few get the strictest controls; the rest can use Copilot freely within your data policy.

    From there we look at where Excel is acting as glue between systems. Those are usually the best places for an agent, because the manual steps are repetitive and well understood. We design agents to write to new outputs, log every change, respect folder permissions and route exceptions to a named reviewer. We test them on historical periods where the right answer is already known before they run on live data.

    Where a team needs more than Copilot can offer, such as pulling from an ERP, reconciling across three systems and producing a reviewed output, we build that as part of our AI automation services. Across more than 500 AI agents deployed and 50+ projects in 12 industries, the pattern holds: the governance work up front is what lets finance teams trust the output enough to stop rechecking every number by hand.

    Frequently asked questions

    Can AI edit Excel spreadsheets directly?

    Yes. Copilot in Excel has an edit mode that changes workbooks directly, alongside a plan mode that asks you to confirm steps first. Custom agents can also read and write workbooks through APIs. For important files, start with plan mode or a copy of the workbook, and keep version history switched on so any change can be reversed.

    Is Copilot in Excel safe for confidential financial data?

    Microsoft states that Microsoft 365 Copilot does not use prompts or responses to train foundation models and respects existing permissions and sensitivity labels. Controls vary by plan, so confirm what your licence includes. Safety also depends on your own setup: if permissions in SharePoint are too broad, Copilot can surface data people should not see.

    What is the difference between Copilot and Python in Excel?

    Copilot is a conversational assistant that builds formulas, cleans data and edits workbooks from plain-language requests. Python in Excel lets you write Python code in cells, run in an isolated Microsoft cloud container with no network access. Copilot suits everyday tasks; Python suits statistical analysis, forecasting and heavier data work that formulas handle poorly.

    Can AI connect Excel to my accounting software?

    Built-in Excel AI features work on the data already in your workbook, and Python in Excel has no network access, so neither pulls data from accounting systems by itself. Connecting Excel to platforms such as Xero or QuickBooks usually needs a custom agent or an integration tool that reads from the accounting API and writes to a controlled output file.

    How do I stop AI from breaking my spreadsheets?

    Keep critical calculations on protected sheets, have AI write to new tabs or copies rather than source data, use structured tables instead of fixed cell references, and store files where version history is enabled. Log what each agent changes, and have a second person review outputs that feed reporting, payments or filings.

    Do I need a Copilot licence to use AI in Excel?

    Copilot features depend on your Microsoft 365 plan, and Microsoft notes they may not be included in every subscription or may be disabled by your organisation. Python in Excel and third-party add-ins have their own availability terms. Custom agents can work with Excel files without Copilot, using the workbook format or Microsoft's APIs directly.

    If you want to know which of your spreadsheet workflows are good candidates for AI and which need tighter controls first, start with a free AI audit. We will map your critical workbooks, the systems around them and the safest place to begin.

    Ready to implement AI in your business?

    Let's discuss how AI agents can transform your operations and reduce costs.