How to Use Copilot in Excel
Copilot in Excel is available in Microsoft 365 for subscribers with a Copilot licence. Open a workbook, format your data as a table, then click the Copilot button in the Home tab to open the side pane. You can ask it to write a formula, explain what a formula does, summarise patterns in your data, highlight rows that meet a condition, or suggest a chart. Copilot inserts the result directly into the workbook.
Copilot in Excel can generate formulas, explain existing ones, summarise data, suggest charts, and highlight trends, all from a plain-language prompt in the side pane.
What Copilot Can Do in Excel
Copilot in Excel handles four main tasks. It writes formulas: describe what you want to calculate and Copilot produces the function, with an explanation of how it works. It explains existing formulas: paste a complex nested function and Copilot walks you through what each part does. It analyses data: ask questions about your table and Copilot returns a written summary of patterns, outliers, and totals. It creates visuals: describe the chart you need and Copilot inserts it. For deeper, cross-source analysis, Power BI with its own Copilot integration is a more powerful option.
Requirements to Get Started
You need a Microsoft 365 subscription with a Copilot licence assigned by your IT administrator. Your Excel workbook must be saved to OneDrive or SharePoint (not just locally) for Copilot to be active. Your data must be formatted as an Excel table, not a plain range. Convert it with Ctrl+T and make sure each column has a clear, descriptive header. Copilot uses the column headers to understand what the data represents, so “Revenue EUR” is far more useful to it than “Col_C”. For a broader overview of what Microsoft 365 Copilot is and what it can do across apps, see What is Microsoft 365 Copilot?
Use Case 1: Generating Formulas with a Prompt
Type a prompt like “Write a formula to calculate the year-over-year growth percentage comparing the Revenue column to the Previous Year column”. Copilot returns a formula such as =([@Revenue]-[@[Previous Year]])/[@[Previous Year]] along with a plain-language explanation. Click Insert column and Copilot adds the formula as a new column in your table. You can refine the prompt if the result does not match what you needed.
Use Case 2: Analysing Data with Natural Language Questions
Ask “What are the three product categories with the highest average margin this year?” or “Are there any months where sales dropped more than 20% compared to the previous month?” Copilot reads your table and returns a written answer. For conditional highlighting, you can ask “Highlight all rows where the score is below 50” and Copilot applies conditional formatting rules automatically. These capabilities are most useful for exploratory analysis when you do not yet know what questions to ask.
Use Case 3: Explaining Formulas You Did Not Write
Open a workbook you received from a colleague and find a formula like =IFERROR(INDEX($B$2:$B$100,MATCH(1,(A2=$C$2:$C$100)*(D2=$E$2:$E$100),0)),""). Ask Copilot “Explain what this formula does”. It breaks down each function, explains the array match logic, and describes what the IFERROR wrapper handles. This is faster than reading documentation and more reliable than guessing.
Use Case 4: Creating Charts from a Description
Type “Create a line chart comparing monthly revenue and target for the current year, with revenue in blue and target as a dashed line”. Copilot selects the relevant columns and inserts a chart. It will not always get every formatting detail right, but it creates a usable starting point in seconds. You then adjust formatting manually. This is faster than building a chart from scratch, especially when the correct column selection is not immediately obvious.
Our tip: Before asking Copilot to analyse your data, spend two minutes cleaning up column headers. Descriptive headers like “Monthly Revenue EUR” and “Sales Region” help Copilot interpret your data correctly. Ambiguous headers like “Value” or “Code” lead to vague or incorrect responses.
Frequently asked questions
Does Copilot in Excel work with any spreadsheet?
Copilot in Excel requires your data to be in a formatted Excel table, not a plain cell range. It also works best with clearly labelled column headers and consistent data types in each column. If your spreadsheet has merged cells, irregular formatting, or data spread across multiple disconnected ranges, Copilot will struggle to interpret it. Clean up your sheet structure first: unmerge cells, give columns descriptive headers, and convert the main data range to a table with Ctrl+T.
Can Copilot in Excel access data from other files or external sources?
No. Copilot in Excel works only with the data in the currently open workbook. It cannot query other Excel files, SharePoint lists, or external databases directly. If you need Copilot to work with data from multiple sources, consolidate that data into one workbook first, or use Power BI for cross-source analysis. For more powerful data analysis across multiple sources, Power BI with its AI features is a better tool.
How accurate are the formulas Copilot generates?
Copilot generates formulas that are usually correct for straightforward requests, but you should always verify the result against a few known rows before using the formula on your full dataset. For complex multi-condition formulas or array functions, check the logic step by step. Copilot explains what each formula does, which helps you spot errors. Treat Copilot as a first draft tool, not an infallible formula writer.
Can Copilot help me clean messy data in Excel?
Yes, to a degree. You can ask Copilot to identify inconsistencies in a column, such as 'Find rows where the date format is inconsistent' or 'Show me rows with blank values in the Customer column'. Copilot can highlight those rows or suggest a fix. For large-scale data cleaning, Power Query (available in Excel under Data, Get and Transform Data) gives you more control and handles larger volumes more reliably than Copilot.