Microsoft Copilot in Excel is most useful when you know exactly what to ask – and you already know the tool well enough to spot a bad answer. This guide is for experienced Excel users: analysts, operations managers, finance staff, and anyone who currently spends hours on data tasks that should take minutes. Real business scenarios, practical Copilot prompts, measurable time savings.

For many Australian businesses, Excel is still the primary tool for managing data, tracking performance, and building reports. Copilot in Excel brings AI assistance directly into your spreadsheets, making it faster and easier to analyse data, write formulas, and create visualisations – even if you are not an Excel expert.

This guide covers practical ways to use Copilot in Excel for everyday business tasks.

What Copilot in Excel Can Do

Copilot in Excel can assist with a range of tasks, including:

• Analysing data and highlighting trends or outliers
• Writing formulas based on a plain-English description
• Creating charts and visualisations
• Formatting and organising data
• Summarising large datasets
• Suggesting what questions you should be asking about your data

Copilot works within your existing spreadsheet. It cannot access external data sources unless they are already in your workbook, and it does not export data or share it externally.

Requirements

Copilot in Excel requires a Microsoft 365 Copilot licence in addition to your standard Microsoft 365 subscription. Your spreadsheet data also needs to be formatted as an Excel Table (not just a plain range) for Copilot to work most effectively. To convert a range to a table, select your data and press Ctrl + T.

How to Open Copilot in Excel

Open your spreadsheet in Excel. In the ribbon, click the Copilot button, which appears in the Home tab once your licence is enabled. A Copilot chat panel will open on the right side of your screen. You can type your request in plain English and Copilot will respond with analysis, a formula suggestion, or a visualisation.

Practical Example 1: Understanding Your Sales Data

Imagine you have a spreadsheet with monthly sales data across five product categories and twelve months of results. Rather than manually building pivot tables and charts, you can ask Copilot:

“Which product category had the highest total sales this year, and which had the lowest growth rate quarter on quarter?”

Copilot will analyse your table and provide a clear answer, often with a supporting chart it generates automatically. You can then ask follow-up questions like “Show me the months where total sales fell below the average” to dig deeper.

Practical Example 2: Writing Formulas Without Knowing the Syntax

One of the most valuable things Copilot can do is write Excel formulas for you. Instead of searching for the right formula or spending time troubleshooting syntax errors, describe what you need in plain language.

For example: “In column G, calculate the percentage change from column E to column F for each row.”

Copilot will write the appropriate formula and explain what it does. You can then apply it to the full column with one click. This works for simple calculations, complex nested formulas, XLOOKUP, SUMIFS, and many other functions.

Practical Example 3: Spotting Issues in Your Data

If you have a large dataset and suspect there are data quality issues, Copilot can help you find them quickly. You can ask:

“Are there any duplicate values in the invoice number column?” or “Highlight any rows where the total is less than zero.”

Copilot can identify these issues and apply formatting to make them visible, or suggest formulas to flag them systematically.

Practical Example 4: Creating a Chart

Rather than manually selecting a chart type and configuring it, you can describe what you want and let Copilot build it.

“Create a bar chart showing monthly revenue by region for the last six months.”

Copilot will generate the chart based on your table data and insert it into the spreadsheet. You can then edit it further using Excel’s standard chart formatting tools.

Practical Example 5: Summarising a Large Dataset

If you have imported a large data export, such as a customer list, a financial report, or an inventory snapshot, Copilot can give you a quick overview before you start working with it.

Ask: “Give me a summary of this dataset. How many rows are there, what columns are included, and what stands out?”

This gives you orientation quickly and helps you identify the right questions to investigate further.

Tips for Getting Better Results

• Format your data as a table: Copilot works most reliably with properly formatted Excel Tables. Ensure headers are in the first row and there are no blank rows within the data.
• Be specific about what you want: The more context you give, the more relevant the output. Name the columns you want to work with rather than saying “this column”.
• Verify formulas before relying on them: Check that any formula Copilot writes produces the expected result, particularly for complex calculations.
• Ask follow-up questions: Copilot is conversational. If the first answer is not quite right, refine your question rather than starting from scratch.

What Copilot Cannot Do in Excel

Copilot does not have access to data outside your workbook. It cannot pull in live data from external sources on its own, though Excel’s existing data connection features can bring in that data first. Copilot also cannot run macros or VBA code, though it can help you write VBA if you need it.

As with all AI tools, results should be reviewed rather than taken at face value. Copilot will occasionally make mistakes, particularly with complex multi-step analysis requests.

For Australian businesses looking to roll out Copilot across their Microsoft 365 environment or get more from their existing tools, the Otto IT Microsoft 365 services page has more information. To discuss licensing or deployment, contact the Otto IT team.

Summary

Copilot in Excel makes data analysis accessible to everyone on your team, not just those who know their way around complex formulas and pivot tables. Ask it to spot trends, write formulas, create charts, or summarise your data in plain language. Start with a specific question about your actual business data and see how much time it saves you.

If your business needs help getting the most out of Microsoft 365 or keeping your IT running smoothly, talk to the Otto IT team.

Frequently Asked Questions

Does Copilot in Excel work with all file types, including older .xls files?

Copilot in Excel works with files stored in OneDrive or SharePoint and requires the file to be in the modern .xlsx format. Older .xls files will need to be converted before Copilot features are available. Files stored only on a local drive, rather than synced to OneDrive, may also have limited Copilot functionality depending on your Microsoft 365 configuration.

Can Copilot in Excel handle macros and VBA?

Copilot does not write or modify VBA macros. It works within the standard Excel formula and table framework. If your workbooks rely heavily on macro-based automation, Copilot will not interact with that layer. However, Copilot can help you write complex formulas that may reduce your reliance on macros for some use cases, particularly data transformation and conditional calculations.

How accurate is Copilot’s data analysis in Excel, and can I trust the outputs?

Copilot’s analysis is only as accurate as your underlying data. It will not catch data quality issues, duplicate entries, or inconsistent formatting – those are your responsibility before you start. The formulas and summaries it generates should be checked, particularly in high-stakes contexts like financial modelling or compliance reporting. Treat Copilot as a fast first draft, not a final answer, and verify outputs against source data before presenting them.

Which Microsoft 365 subscription tier includes Copilot in Excel?

Copilot in Excel requires a Microsoft 365 Copilot licence, which is an add-on to qualifying Microsoft 365 business or enterprise subscriptions. It is not included in standard Microsoft 365 Business Basic, Standard, or Premium plans by default. As of 2025, Microsoft 365 Copilot is available as a per-user monthly add-on. Check your current licensing with your IT provider before purchasing, as pricing and bundling can change.