Automate Excel Reports with AI After the COPILOT() Retirement

What changed: the COPILOT() function is gone

If you built an Excel report around the in-cell =COPILOT() formula, it has already stopped working. Microsoft's documentation states that the COPILOT function was removed from Excel on September 14, 2026. It only ever existed as a preview in the Frontier and Microsoft 365 Insider programs.

Existing workbooks do not fail loudly. Cells keep their last calculated values, and the first recalculation turns each one into a #NAME? error. A report that looked fine last week can break the next time someone edits the file.

To find affected cells, press Ctrl+F, open Options, set Look in to Formulas, and search for COPILOT(. Replace each hit with a native formula or one of the methods below.

The lesson is broader than one function: never make a business report depend on a feature labelled preview. Everything in this article uses features that are either generally available or fully under your control.

Three ways to automate Excel reports with AI

There is no single best tool. The right choice depends on whether the report is built once or recurs, and where the data lives.

One rule holds across all three: let AI handle words and structure, and let formulas or code handle numbers. The rest of this article shows how to apply it.

Method 1: Copilot in Excel for building and reshaping reports

Copilot in Excel has three modes. Edit mode changes the workbook directly and handles multi-step jobs such as reshaping data, merging sheets or building a report with several elements. Plan mode writes out an approach for you to confirm before anything is touched. Chat mode answers questions without modifying the file.

Copilot builds with Excel's own objects (tables, charts, PivotTables and formulas), so the result stays editable. It can also pull in data from other workbooks, fetch web information with citations, and run custom skills that define how a repeatable task should be done.

Whether you see Copilot at all depends on your Microsoft 365 license and your organization's settings, so check that before planning a workflow around it.

How to prompt it for a report you can trust

• Start in plan mode for any report others will rely on, and read the plan before allowing edits.
• Ask for live objects: Build a weekly summary from the Orders table: revenue by channel and a chart. Use a PivotTable and formulas, not typed values.
• Reconcile one total against the source data yourself before you share the file.

Treat Copilot as the fastest way to build the first version. Because you start it and can stop it at any moment, it is not the tool that produces next Monday's report while you are away.

Method 2: Office Scripts and Power Automate for scheduled reports

Office Scripts turn a repeated manual routine into a script. The action recorder captures your clicks as code, and the code editor lets you add logic the Excel interface cannot express, such as if/else conditions and loops. Microsoft's own example is the daily chore of opening an accounting CSV, deleting columns, formatting a table and building a PivotTable: record it once, run it forever (Office Scripts overview).

Scheduling needs a detail that older tutorials get wrong. Microsoft notes that scheduling from inside Office Scripts is temporarily disabled, and existing schedules keep running. The supported route today is a Power Automate flow that runs your script.

1. Record the manual routine with the action recorder, then open it in the code editor and tidy it.
2. In Power Automate, create a scheduled cloud flow and set the day and time.
3. Add the Excel Online (Business) action Run script and pick the workbook and script. If the script lives in a team SharePoint library, use Run script from a SharePoint library instead.
4. Add a step that emails or posts the finished file or a chart image.
5. Use Run Test to trigger the flow manually before you trust the schedule.

Where AI belongs in this setup

Use Copilot or a chat model to draft or modify the script, then read it, run it on a copy of the workbook and freeze it. A frozen script does exactly the same thing every Monday. An AI prompt re-interpreted each week does not, and that variance is the thing you are trying to remove from a recurring report.

Method 3: Python plus an LLM for reports that run unattended

When data arrives as CSV exports, API responses or database rows, a short Python script is more flexible than any in-Excel tool. The design below has four deliberate choices:

• pandas computes every figure. The model never does arithmetic.
• The model receives a plain-text list of those figures and is told to copy them exactly.
• A guard rejects any commentary containing a number that is not in that list.
• Totals on the Summary sheet are live Excel formulas, not pasted values.

import re
import pandas as pd

NUM = re.compile(r'\d[\d,]*(?:\.\d+)?')


def load(path: str) -> pd.DataFrame:
    df = pd.read_csv(path, parse_dates=['order_date'])
    return df.dropna(subset=['order_id', 'channel', 'revenue'])


def build_facts(df: pd.DataFrame) -> dict:
    by_channel = (
        df.groupby('channel', as_index=False)
        .agg(orders=('order_id', 'nunique'), revenue=('revenue', 'sum'))
        .sort_values('revenue', ascending=False)
    )
    return {
        'total_revenue': round(float(df['revenue'].sum()), 2),
        'total_orders': int(df['order_id'].nunique()),
        'by_channel': by_channel,
    }


def facts_as_text(facts: dict) -> str:
    lines = [
        f"Total revenue: {facts['total_revenue']:,.2f}",
        f"Total orders: {facts['total_orders']:,}",
    ]
    for r in facts['by_channel'].itertuples():
        lines.append(f'{r.channel}: {r.orders:,} orders, {r.revenue:,.2f} revenue')
    return '\n'.join(lines)


def llm(prompt: str) -> str:
    # Replace this body with your provider's chat call.
    return 'Replace llm() with a real model call.'


def unsupported_numbers(text: str, facts_text: str) -> list:
    allowed = {n.rstrip(',') for n in NUM.findall(facts_text)}
    return [n.rstrip(',') for n in NUM.findall(text) if n.rstrip(',') not in allowed]


def write_report(path: str, facts: dict, commentary: str) -> None:
    n = len(facts['by_channel']) + 1
    with pd.ExcelWriter(path, engine='openpyxl') as xw:
        facts['by_channel'].to_excel(xw, sheet_name='By channel', index=False)
        ws = xw.book.create_sheet('Summary', 0)
        ws['A1'] = 'Weekly sales report'
        ws['A3'], ws['B3'] = 'Total revenue', f"=SUM('By channel'!C2:C{n})"
        ws['A4'], ws['B4'] = 'Total orders', f"=SUM('By channel'!B2:B{n})"
        ws['A6'] = commentary


if __name__ == '__main__':
    df = load('orders.csv')
    facts = build_facts(df)
    facts_text = facts_as_text(facts)
    draft = llm('Write 3 sentences for a sales manager. Use only these figures, copied exactly:\n' + facts_text)
    bad = unsupported_numbers(draft, facts_text)
    if bad:
        raise ValueError(f'Commentary contains numbers not in the data: {bad}')
    write_report('weekly_report.xlsx', facts, draft)
    print('wrote weekly_report.xlsx')

The llm() function is a stub, so replace its body with a call to your provider. The guard is intentionally strict: a sentence such as "up 12 percent" fails unless 12 is already in the facts, so compute any ratio you want mentioned and add it to facts_as_text(). A failed check stops the run instead of shipping an invented figure.

One practical note: formulas written by openpyxl have no cached result, so Excel calculates them when the file opens. A previewer that does not calculate may show empty cells.

If you would rather not own and host this pipeline, Netalith builds AI report agents that generate Excel, PDF and PowerPoint files from your data.

How to keep AI-written numbers accurate in Excel reports

Microsoft's guidance for the old COPILOT function was to use native Excel formulas such as SUM or AVERAGEIF for any task that needs accuracy or reproducibility. That advice still applies to every method above, because a language model predicts text and does not calculate.

Before any report goes out, run three checks: reconcile the grand total with the source system, compare each headline figure with the previous period to catch unexplained jumps, and read the commentary against the table it describes.

Which method should you use?

My default is the last row. Prototype with AI while the layout is still changing, and once the report is stable, replace the prompt with code or a script that cannot drift.

Where to go from here

Pick the report you rebuild most often and move it to the method in the table above. If you want it built, scheduled and monitored for you, request a free quote and describe the data source and the people who read the report.