Use Microsoft Copilot in Excel to Analyze Your Spend Data

What This Does

Copilot in Excel lets you ask plain-English questions about your purchase order data (categorizing spend, surfacing your top vendors, identifying consolidation opportunities) without writing a single formula.

Before You Start

• You have Microsoft Excel open (desktop or web)
• You're logged into a Microsoft 365 account with Copilot access (the paid Microsoft 365 Copilot add-on, or the free basic entitlement some organizations have)
• You have a PO export from your ERP loaded in Excel
• Time needed: 10-15 minutes
• Cost: The Copilot add-on is paid per user on top of your Microsoft 365 plan; check with IT

Steps

1. Open the Copilot panel

Click the Copilot icon in the lower-right corner of the Excel window. A side panel opens on the right side of your screen. Microsoft moved Copilot off the Home ribbon in 2026, so this corner button is now the entry point. Pressing F6 cycles focus to it, and the newer Alt + C (Windows) and Cmd + Ctrl + I (Mac) shortcuts are rolling out. If you don't see the button, your organization may not have Copilot enabled yet. Check with IT.

2. Check your data layout

Copilot reads plain ranges as well as tables, as long as there is one header row with clear, unique headers and no merged cells, subtotals, or blank rows. Formatting as a table is still worth doing because it keeps ranges stable: select your spend data range, go to Insert → Table, check "My table has headers," and click OK.

3. Ask your first question in plain English

Click in the Copilot text box and type your question. Try: "Which vendors have the most purchase orders in the last 6 months?" Copilot will generate a pivot or chart summarizing the results.

4. Categorize your spend

Type: "Group these line items by spend category and show me the total amount per category." If your data has a description column, Copilot will attempt to classify items. Review the categories it creates. You may need to correct a few, but it handles most standard categories accurately.

5. Find consolidation opportunities

Type: "Show me vendors where we're buying similar items. Highlight any where we could consolidate." This identifies where you may be splitting spend that could get volume pricing if consolidated.

6. Export your findings

Copilot-generated tables and charts can be saved as a new sheet. Click Insert Sheet in the response, or copy the output to a new tab for your monthly report.

Real Example

Scenario: You've just pulled a 3-month PO export (1,200 rows) and need to identify your top 10 spend categories for a management review.

What you type: "Summarize total spend by category and identify the top 10 categories by dollar amount. Flag any categories where we have more than 5 active vendors."

What you get: A ranked table of your top spend categories with total amounts and vendor counts. The "more than 5 vendors" flag immediately shows you where consolidation work could generate savings.

Tips

• If Copilot gives unexpected results, make sure your column headers are descriptive (e.g., "Vendor Name", "Amount USD", "Category") not generic ("Column A", "Column B")
• For large datasets (5,000+ rows), Copilot may take 20-30 seconds. This is normal.
• Save your Copilot-enhanced file before exiting; the generated tables persist but regenerating them takes time

Tool interfaces change. If a button has moved, look for the Copilot icon in the lower-right corner of the window or similar AI options nearby.