How to use Copilot in Excel, from formulas to business insights

A bookkeeper we support in Brisbane recently spent an afternoon building a formula to calculate overdue invoice ages across three columns of data. She got there eventually, but the process cost her about two hours she didn’t have. When she tried the same task with Copilot in Excel the following week, it took about 30 seconds. That gap is what this article is about.

Copilot in Excel lets you ask questions about your data in plain English, write formulas on your behalf, spot trends, and create charts without needing advanced spreadsheet skills. If your team already uses Microsoft 365Copilot in Excel is one of the most practical places to start seeing a return on that investment. This guide walks through what it can actually do, where it fits into everyday work, and a few things to watch out for.

What you need before you start

Copilot works inside the Excel you already use, but two things need to be in place first. Your file must be saved to OneDrive or SharePoint with AutoSave turned on. Local files sitting on a desktop won’t work. Second, you need the right licence. The full Copilot experience inside Excel requires a Microsoft 365 Copilot add-on licence on top of your existing Microsoft 365 plan. Without it, access to Copilot in desktop Excel is either unavailable or throttled for smaller organisations.

If you’re not sure what licences your team holds, that’s worth checking before you try to get started. Our IT consulting team can review your current setup and confirm what’s included. Once the licence and file location are sorted, getting started is straightforward.

Writing formulas without memorising them

This is where most people notice the biggest shift. You type what you want in plain language, and Copilot writes the formula. Ask it to “calculate the number of days between the order date and today” and it builds the formula, drops it into the right column, and shows you what it did. No manual syntax. No hunting through the function library.

It handles common tasks like VLOOKUP replacements, conditional calculations, running totals, and percentage changes between periods. For staff who use Excel regularly but don’t think of themselves as power users, this removes a genuine barrier. They stop needing to ask IT or a colleague every time a formula stumps them.

• Ask: “Show me revenue growth rate month over month” and Copilot creates the formula automatically.
• Ask: “Calculate how many days each invoice has been outstanding” and it builds a DATEDIF or equivalent formula in context.
• Ask: “Flag any rows where the total is more than $10,000” and it adds a conditional column or highlights the data.

Spotting trends and summarising your data

Once your data is in a table, you can ask Copilot to analyse it. “What are the top five products by revenue this quarter?” or “Are there any unusual spikes in this data?” are the kind of questions it handles well. It applies filters, groups records, and surfaces patterns rather than leaving you to dig through rows manually.

Copilot can also build charts on request. Ask for a column chart comparing this year against last, or a pie chart showing sales by category, and it produces one. You can refine the output by asking follow-up questions, though be specific. Vague prompts like “make it look better” don’t land as well as “change the chart to a bar chart and add data labels”.

For business owners who prefer visuals over tables, this is a genuine time saver before a team meeting or board presentation.

Plan mode: reviewing changes before they happen

A newer feature worth knowing about is Plan mode. Before Copilot makes any changes to your workbook, it outlines exactly what it intends to do, which data it will use, and which steps it will take. You review the plan, adjust it if needed, and only then approve the edits.

This matters for anyone working with financial data or records they can’t afford to get wrong. Rather than Copilot making edits you then have to undo, Plan mode keeps you in control of every change. It rolled out in May 2026 and is available in the current version of Excel for Microsoft 365 Copilot subscribers.

Edit with Copilot: multi-step tasks in one go

What Microsoft previously called Agent Mode is now available as “Edit with Copilot” across Windows, web, and Mac. It handles more complex, multi-step tasks inside the workbook. You describe the outcome you want, and Copilot builds tables, writes formulas, produces charts, and checks results in sequence. The steps are visible as it works, and the outputs are editable.

A practical example: “Take the raw sales data on Sheet 1, create a summary table by region and product category, calculate the percentage contribution of each, and generate a bar chart showing the top 10 categories.” That’s a task that might take an experienced Excel user 30-45 minutes. Edit with Copilot can work through it in a fraction of that time.

Copilot in Excel can handle tables up to 2 million cells, so it covers most real-world business datasets without trouble. Very large files may take a little longer to process, but the capability is there.

Python for advanced analysis, no coding required

For teams with more complex data needs, Copilot in Excel now supports Python as an analysis tool. You can ask for statistical analysis, forecasting, or data modelling and Copilot writes and runs the Python code inside the workbook. You don’t need to know Python. The results appear directly in Excel.

This opens up analysis that previously required a data analyst or a separate tool. A small logistics business, for example, could ask Copilot to forecast demand across the next quarter based on historical patterns. That kind of analysis used to sit outside what Excel alone could do.

Where Copilot in Excel works best

The businesses we support across South-East Queensland that get the most from Copilot in Excel tend to share a few things. Their data is already reasonably clean and structured as a proper table (not a mix of merged cells and ad hoc formatting). They use Excel for something recurring, whether that’s monthly reporting, tracking job costs, or monitoring sales. And they approach it with specific questions rather than vague prompts.

The roles that benefit most include finance staff doing regular reporting, operations managers tracking KPIs, practice managers in professional services, and small business owners who run their numbers personally but lack deep Excel knowledge. If you’re in one of those situations and your files are in OneDrive, the practical value arrives quickly.

For more on getting Copilot working across your whole Microsoft 365 environment, see our Microsoft 365 Copilot page.

What to watch out for

Copilot in Excel is genuinely useful, but a few limitations are worth knowing before you rely on it.

• Your file must be saved to OneDrive or SharePoint with AutoSave on. Local-only files don’t work with Copilot.
• Follow-up corrections can be unclear. If you tell Copilot “that’s wrong”, it may not understand what you mean. Be specific: “remove the column you just added” works better than “undo that”.
• Row-level formatting has limits. Copilot can highlight or format entire columns more reliably than it can format individual rows based on conditions.
• It works best on clean, structured tables. Workbooks with lots of merged cells, irregular layouts, or multiple unrelated data ranges will produce less reliable results.
• Always review the output. Copilot is not infallible. Check formulas before distributing reports, especially for financial or compliance-sensitive data.

FAQ

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

Yes. The full Copilot experience inside Excel requires a Microsoft 365 Copilot add-on licence on top of your existing Microsoft 365 Business or Enterprise plan. Without it, access may be limited or throttled. If you’re unsure what your current licences include, it’s worth confirming before you try to roll it out.

Does my Excel file need to be in a specific location?

Yes. Copilot in Excel only works with files saved to OneDrive or SharePoint, with AutoSave turned on. Files stored locally on a computer or a network drive won’t have Copilot available. Moving existing files to OneDrive is usually straightforward if you haven’t done so already.

Can Copilot make errors in my spreadsheet?

It can. Copilot is an AI assistant, not an accountant. It generally handles common tasks well, but you should review any formula or change it makes before using the data in reports or sending it to clients. Plan mode, which shows you the proposed edits before they’re applied, helps reduce the risk of unwanted changes.

What kinds of businesses get the most from Copilot in Excel?

Any team that uses Excel regularly for reporting, tracking, or analysis will see value. Finance staff, operations managers, practice managers, and small business owners who work with recurring data sets tend to get the clearest return. The more structured and consistent your data, the better Copilot performs.

Is Copilot in Excel the same as Microsoft’s general Copilot Chat?

No. Copilot Chat is a broader AI assistant included with many Microsoft 365 plans. Copilot in Excel is specifically integrated into the spreadsheet environment and can read your data, write formulas, edit your workbook, and build charts. The in-app Excel experience requires the paid Copilot add-on licence for consistent access.

Where do I start if my team wants to try Copilot in Excel?

Check that your Microsoft 365 licences include the Copilot add-on, confirm your files are saved to OneDrive with AutoSave on, and start with a small, clean dataset. Pick one recurring task your team does manually in Excel, such as monthly reporting or invoice tracking, and try prompting Copilot to handle it. The learning curve is low once the setup is right.

How do we get started with Copilot across our business?

If you’d like help assessing whether your current Microsoft 365 licences include Copilot, or you want a hand setting it up across your team, we’re happy to take a look. Get in touch with TTA and we can walk you through the options without any pressure. We work with businesses across Brisbane and South-East Queensland to get practical value from the Microsoft tools they’re already paying for.