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 enabled (Microsoft 365 Business Standard or higher)
• You have a PO export from your ERP loaded as a table in Excel
• Time needed: 10-15 minutes
• Cost: Included in Microsoft 365 Business ($6-12/mo per user, check with IT)

Steps

1. Open the Copilot panel

Click the Copilot button in the Home tab ribbon (right side, looks like a sparkle/star icon). A side panel opens on the right side of your screen. If you don't see it, your organization may not have it enabled yet. Check with IT.

2. Format your data as a table

Copilot works best with Excel Tables. Select your spend data range, go to Insert → Table, check "My table has headers," and click OK. Your data will get table formatting with filter dropdowns.

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 sparkle icon in the Home or Insert tabs.