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 data should be in a table format — if it's not, Excel will prompt you to convert it

Steps

1. Open your expense data and find Copilot

Open your Excel file with expense data. Click anywhere in the data and press Ctrl+T to format as a table (if not already done). Then click the Copilot icon in the Home tab ribbon.

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

• Make sure your data is in a proper Excel table (Ctrl+T) before using Copilot — it works much better with structured tables
• 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 sparkle icon in the Home tab.