Use Microsoft Copilot in Excel to Analyze Operational Data

What This Does

Copilot in Excel analyzes your operational spreadsheet data, writes complex formulas for you, identifies patterns and anomalies, and generates plain-language summaries, so you can get more value from your existing data without needing advanced Excel skills.

Before You Start

• You have Microsoft Excel open (desktop app or Excel Online)
• Your organization has Microsoft 365 with Copilot enabled
• Your data has one header row with clear, unique column names (a formatted Table is helpful but no longer required)
• Look for the Copilot icon in the lower-right corner of the Excel window

Steps

1. Give Your Data Clean Headers

Copilot no longer requires a formatted Table. It reads plain ranges too, as long as there is one header row with unique, descriptive column names and no merged cells, subtotals, or empty rows. Formatting as a Table (click anywhere in your data, press Ctrl + T, click OK) is still worth doing because it keeps ranges stable as rows are added.

What you should see: If you made a Table, your data changes to a formatted table with alternating row colors and filter dropdowns on each column.

2. Open the Copilot Pane

Click the Copilot icon in the lower-right corner of the Excel window. A pane opens on the right side of Excel. Microsoft moved Copilot off the Home ribbon in 2026; press F6 to reach the corner button by keyboard, or right-click it and choose Move to ribbon to restore the old placement.

Troubleshooting: If the Copilot button is missing, your Microsoft 365 license may not include Copilot, or Office needs updating.

3. Ask for Insights

In the Copilot pane, type: "Show me insights from this data." Copilot analyzes the table and generates:

• Automatically detected patterns and trends
• Anomalies worth investigating
• Suggested pivots and visualizations

What you should see: A list of 3-5 insights with "Add to sheet" buttons to insert charts or pivot tables.

4. Ask Specific Analysis Questions

Ask operational questions directly: "What's the average throughput by shift?" or "Which weeks had on-time delivery below 95%?" or "Show me labor costs by department sorted highest to lowest."

Copilot creates pivot tables, charts, or filtered views in response.

5. Have Copilot Write Formulas

Type in the Copilot pane: "Write a formula to calculate the on-time delivery percentage for each row, where on-time means Actual Ship Date is on or before Promised Ship Date." Copilot writes the formula and offers to insert it into the table.

Real Example

Scenario: You have a weekly report of all shipments, 2,000 rows, with Order ID, Customer, Promised Ship Date, Actual Ship Date, Carrier, and Status. You need to find which carriers have the worst on-time performance.

What you do: Format as a Table. Open Copilot. Ask: "Which carriers have the lowest on-time delivery rate? Show as a ranked table."

What you get: A pivot table showing Carrier | Total Shipments | On-Time % | Average Days Late, ranked from worst to best. In 30 seconds.

That's the analysis that used to take you 45 minutes of VLOOKUP and PIVOT TABLE building.

Tips

• Start every Copilot session with "Show me insights". It often surfaces patterns you weren't looking for
• Ask "What formula would help me track [specific metric]?" to get formula suggestions tailored to your data
• After Copilot generates a chart, right-click → "Move to new sheet" to keep your reporting template clean

Tool interfaces change, if a button has moved, look for similar AI/magic/smart options in the same menu area.