Analyze and Format Expense Data with Excel Copilot

What This Does

Excel's Copilot lets you work with expense data using plain English: "categorize by vendor," "sum each category," "flag anything over $500 missing a receipt", without needing to know formulas or pivot tables.

Before You Start

• You have Microsoft 365 with Copilot enabled
• You have your expense data in an Excel file (even a basic list works)
• Your columns have clear, unique headers with no merged cells, subtotals, or empty rows

Steps

1. Open your expense data and find Copilot

Open your Excel file with expense data. Click the Copilot icon in the lower-right corner of the Excel window. Copilot is no longer on the Home ribbon; press F6 to reach the corner button by keyboard, or right-click it and choose Move to ribbon if you prefer the old placement. Formatting the data as a table (Ctrl+T) is optional now, Copilot reads plain ranges too, but a table keeps ranges stable as you add rows.

2. Ask Copilot to categorize and summarize

In the Copilot prompt box, type: "Summarize total spending by category and add a column showing whether each row has a receipt." Copilot adds a summary section and marks rows accordingly.

3. Flag missing receipts or anomalies

Ask: "Highlight rows where Amount is over $200 and the Receipt column is blank." Copilot applies conditional formatting automatically, you immediately see what needs attention.

4. Format for expense report submission

Ask: "Format this table as an expense report with category subtotals and a grand total at the bottom." Copilot restructures the data into a submission-ready format.

Real Example

Scenario: Your executive traveled for 10 days and you have a messy list of 45 transactions from the corporate card statement. You need to prepare the monthly expense report.

What you type: "Group these transactions by category (Meals, Travel, Lodging, Other) and show totals for each. Highlight any transaction over $500."

What you get: A sorted table with category subtotals, a grand total, and the 3 large transactions highlighted for receipt verification. Expense report ready in 20 minutes instead of 90.

Tips

• Give every column a clear, descriptive header before using Copilot. It reads your headers to understand the data, so "Amount" works and "Col_D" does not
• Use Copilot to add a "Notes" column with placeholder text for items that need explanation
• Ask Copilot to "write a formula to flag amounts that don't match the receipt total" for reconciliation tasks

Tool interfaces change, if a button has moved, look for the Copilot icon in the lower-right corner of the window.