Copilot in Excel: Formulas, Charts & Analysis Guide

Finance professionals spend an average of 8-12 hours per week building formulas, cleaning data, and creating charts in Excel. Microsoft 365 Copilot in Excel cuts this time by 60-80% using natural language commands to generate complex formulas, create pivot analyses, and build presentation-ready visualizations. This guide walks you through the exact steps to transform hours of manual Excel work into minutes of AI-assisted analysis.

What You'll Learn

How to use natural language to generate complex Excel formulas including XLOOKUP, SUMIFS, and nested IF statements
Generate instant column formulas for calculations across entire datasets without manual cell references
Create pivot-quality summary tables and charts from raw data using plain English requests
Ask Copilot to identify trends, outliers, and insights in financial data without building manual analyses
Format and highlight data automatically based on business rules using AI-generated conditional formatting
Build what-if scenarios and forecast models by describing your parameters in natural language

Prerequisites

Microsoft 365 Copilot license ($30/user/month add-on or included in Business Premium/E3/E5)
Excel data stored in a formatted Table (Ctrl+T) – Copilot only works with Table objects, not plain ranges
Excel for Microsoft 365 desktop app or Excel on the web (Copilot not available in older perpetual license versions)
Basic understanding of your data structure and the analysis goals you want to accomplish

Step 1

Convert Your Data Range to an Excel Table

Before Copilot can analyze your data, it must be in an Excel Table format. Select any cell in your data range, then press Ctrl+T (or Cmd+T on Mac) to open the Create Table dialog. Ensure 'My table has headers' is checked if your first row contains column names, then click OK. Your range will now have filter dropdowns and a table design – this is required because Copilot can only reference structured Table objects, not plain cell ranges. You'll see the Table Design tab appear in the ribbon, where you can rename your table to something meaningful like 'SalesData' or 'ExpenseReport'.

💡 Tip: Name your table something descriptive in the Table Design tab. Instead of 'Table1', use 'Q1Revenue' or 'ExpenseData' – this makes it easier to reference when you have multiple tables in a workbook.

Step 2

Open the Copilot Pane and Select Your Table

Click the Copilot button in the Excel ribbon (it's on the Home tab, far right side, with the colorful sparkle icon). The Copilot pane opens on the right side of your screen. If you have multiple tables in your workbook, Copilot will prompt you to select which table to analyze – click the table you just created. If you only have one table, Copilot automatically selects it and displays suggested prompts like 'Show data insights' or 'Add a formula column'. This pane is your command center for all AI-assisted Excel operations, and it maintains context about your current table throughout your session.

Step 3

Generate a Formula Column Using Natural Language

In the Copilot pane, type a natural language description of the calculation you need. For example, 'Calculate profit margin as (Revenue minus Cost) divided by Revenue, formatted as percentage' or 'Create a column that shows Yes if Amount is greater than 5000, otherwise No'. Copilot generates the formula, shows you a preview in the pane, and explains what it does. Click 'Insert Column' to add it to your table. The formula is automatically applied to every row, and the new column appears with a suggested name that you can edit. This eliminates the need to manually write XLOOKUP, nested IF statements, or complex calculations – just describe what you want in plain English.

💡 Tip: Be specific about formatting in your prompt. Say 'formatted as currency' or 'rounded to 2 decimals' and Copilot will include number formatting in the generated formula.

⚠ Watch out: Always review the generated formula before inserting. Click 'Show formula' in the preview to verify Copilot interpreted your request correctly, especially for financial calculations where accuracy is critical.

Step 4

Ask Copilot to Identify Trends and Insights

Instead of manually scanning your data, type questions like 'What are the top 5 products by revenue?' or 'Show me any unusual expenses in the last quarter' or 'Which regions have declining sales trends?'. Copilot analyzes the entire table and returns text-based insights in the pane, often with specific numbers and comparisons. These insights are generated from actual calculations Copilot performs behind the scenes – it's not just reading the data, it's analyzing patterns, calculating aggregates, and identifying statistical outliers. Use this to prepare for executive summaries or to quickly spot issues that would take 30+ minutes of manual pivot table work.

💡 Tip: Ask follow-up questions to drill deeper. After Copilot identifies top products, ask 'Why did Product X perform better than Product Y?' and it will compare specific metrics.

Step 5

Generate Summary Charts with Natural Language

Type chart requests like 'Create a column chart showing monthly revenue by region' or 'Make a line chart of profit trends over time'. Copilot generates the chart, inserts it directly into your worksheet, and automatically selects the appropriate chart type based on your data structure. The chart includes proper axis labels, legends, and formatting. If the first attempt isn't quite right, refine your prompt: 'Change that to a stacked bar chart' or 'Show only Q4 data'. This replaces the multi-step process of selecting data ranges, choosing chart types, and manually formatting labels – Copilot handles all of it from a single sentence.

Step 6

Create Pivot-Style Summary Tables Instantly

For financial reports that require aggregated summaries, type requests like 'Summarize total expenses by department and month' or 'Create a table showing average deal size by sales rep and quarter'. Copilot generates a formatted summary table below your source data (or in a location you specify), complete with calculations, subtotals, and proper headers. Unlike traditional pivot tables, these summaries are static tables you can further format or copy into reports. For dynamic analysis, you can still ask Copilot to 'Create a pivot table showing...' and it will build an actual PivotTable object with the correct rows, columns, and values configured.

💡 Tip: Specify the location by saying 'Create this summary starting in cell A50' to keep your source data separate from analysis outputs.

Step 7

Build Conditional Formatting Rules by Describing Them

Instead of manually configuring conditional formatting dialogs, describe the highlighting you want: 'Highlight all expenses over $10,000 in red' or 'Use a color scale to show revenue performance, with highest in green'. Copilot applies the conditional formatting rules to your table columns automatically. For more complex rules, try 'Highlight rows where Status is Overdue and Amount is greater than 5000'. This saves the 5-10 clicks normally required to set up custom conditional formatting rules, and you can describe complex multi-condition rules in plain language that would be tedious to configure manually.

⚠ Watch out: Conditional formatting created by Copilot persists even after you close the session. Review the applied rules in Home > Conditional Formatting > Manage Rules to verify they're working as expected.

Step 8

Generate What-If Analysis and Forecasts

For scenario planning, type prompts like 'Show me projected revenue if we increase prices by 10%' or 'Create a forecast for next quarter based on the last 6 months of data'. Copilot adds formula columns with the calculations or creates a separate forecast table with projected values and confidence intervals. You can refine scenarios by asking 'Now show that same projection with a 15% increase instead' and Copilot updates the analysis. This replaces manual what-if tables, Goal Seek, and forecast sheet creation – especially valuable for budget planning, pricing analysis, and capacity planning where you need to test multiple scenarios quickly.

💡 Tip: Combine forecasts with charts: After generating a forecast, ask 'Create a line chart comparing actual vs. forecasted revenue' to visualize the projection.

Step 9

Clean and Transform Data Using AI Assistance

Data cleanup that normally requires Power Query or complex formulas can be done with prompts like 'Remove duplicate rows based on Customer ID' or 'Split the FullName column into FirstName and LastName' or 'Convert all date formats to MM/DD/YYYY'. Copilot executes the transformation and either modifies your table in place or adds new columns depending on the operation. For text cleanup, try 'Remove extra spaces from all text columns' or 'Convert product codes to uppercase'. This eliminates the need to write TRIM, UPPER, LEFT, RIGHT, or Text-to-Columns operations manually – just describe the cleanup task and Copilot generates the appropriate solution.

⚠ Watch out: Test data transformations on a copy of your workbook first, especially for operations that modify data in place rather than adding new columns. Some operations can't be easily undone if they overwrite original data.

Step 10

Export Copilot Analysis to PowerPoint or Word

Once you've generated charts, summaries, and insights, you need to present them. Select the chart or table Copilot created, copy it (Ctrl+C), then open PowerPoint or Word and paste. The formatting carries over cleanly. For a more integrated approach, open Business Chat in Microsoft Teams or Office.com, type 'Summarize the financial analysis in my Excel file [filename] and create a presentation outline', and Copilot will pull insights from your Excel work to draft slides or documents. This creates a seamless workflow from raw Excel data to presentation-ready deliverables without manual reformatting or retyping numbers.

💡 Tip: Use Copilot in PowerPoint after pasting your Excel charts: In PowerPoint, select the slide and ask Copilot to 'Generate speaker notes explaining this revenue chart' to create talking points automatically.

Summary

You've now transformed manual Excel analysis into an AI-assisted workflow that saves 60-80% of your time on formulas, charts, and data insights. By using natural language commands in Copilot, you've eliminated the need to manually write complex formulas, configure pivot tables, or build charts from scratch. These techniques work across any Excel table – from expense reports and revenue analysis to forecasting and scenario planning – giving you hours back each week to focus on strategic financial decisions instead of spreadsheet mechanics.

Next Steps

Apply this workflow to your most time-consuming recurring Excel report this week and measure the time savings compared to your manual process
Schedule a Copilot training session with Scott Hay to learn advanced techniques for financial modeling, budget variance analysis, and automated reporting workflows specific to your finance role
Explore Copilot in Outlook to draft follow-up emails summarizing your Excel analysis findings and send them to stakeholders in seconds instead of writing from scratch
Combine Excel Copilot with Business Chat to create board-ready presentations by asking AI to pull insights from multiple financial workbooks and draft executive summaries

Ready to Deploy Copilot Across Your Organization?

This guide covers one app. I teach the full MS-4018 curriculum and build custom deployment plans for SMBs—licensing strategy, user training, adoption metrics. 90-day implementations, you own everything we build.

Schedule Copilot Deployment Call

Scott Hay Microsoft Certified Trainer & AI Solutions Architect Microsoft Certified Trainer (MCT) • Delivers 12 Microsoft Copilot courses (MS-4002 through MS-4023) plus Azure AI, Power BI • Azure AI Agents, Semantic Kernel, Power BI (PL-300), Power Platform certified • Former Microsoft and Amazon — 30+ years building production systems • Builds custom AI solutions for SMBs with 90-day delivery