I’ve been spending more time exploring Copilot Studio and what it can actually unlock for Excel workflows, sparked in part by some really strong content from Matthew Devaney:
In my last post, I walked through how to prompt a user to upload a workbook and have an agent act on it:
Part of that was simply me pressure-testing my own understanding of these workflows. Even that relatively simple scenario turned out to be more complex than expected.
The more I dig into it, the more I’m realizing that for many practical use cases, custom agents directly inside Microsoft 365 Copilot may actually be a better fit than going all-in on Copilot Studio.
So in this post, we’re going to build a PDF expense report agent.
The idea is straightforward: a user uploads PDF receipts, the agent classifies each expense against predefined account codes, and then generates a fully populated Excel workbook. For anyone who travels for work, this could be a real time-saver.
You can download the exercise files below to follow along.
To get started, sign in to your Copilot Pro-enabled Microsoft 365 account and navigate to Agents > New Agent from the sidebar.
Configuring the agent
Details
The first step is to define what this agent actually is.
The name and description should be clear and specific. You want something that makes it obvious what the agent does when you (or someone else) sees it later. I’ll use the name “Expense Code PDF reader.”
And for the description:
Analyzes uploaded PDF receipts and assigns the appropriate expense account based on your configured account mapping.
OK now let’s look at the instructions.
Instructions
The name and description tell Copilot what the agent is, but the Instructions determine how it operates:
Task: Classify ALL Transactions in an Uploaded PDF Receipt and Return an Excel Workbook
You are an expense classification assistant.
When a user uploads a PDF receipt:
1. Extract all readable text from the PDF.
2. Identify and extract EVERY transaction found in the document.
- The PDF may contain multiple receipts or multiple transactions.
- You must process ALL transactions, not just the first one.
For each transaction, identify:
- Transaction Date
- Vendor Name
- Total Amount
Then, for each transaction:
3. Use the connected Expense Account knowledge source to determine:
- Expense Account Code
- Expense Account Name
Rules:
- Only use the connected knowledge source for account codes.
- Do not invent new account codes.
- If no clear match is found, use:
Account Code: 9999
Account Name: Uncategorized
- If Date is missing, leave it blank and add "Date not found on receipt" in Comments.
- If Total is unclear, add "Total unclear" in Comments.
- Format Date as YYYY-MM-DD.
- Format Total Amount with two decimals.
- Convert Vendor Name to Proper Case (capitalize the first letter of each word).
- Each transaction must appear as its own row.
Output Requirements:
- Return the results as an Excel workbook (.xlsx).
- The workbook must contain exactly one worksheet.
- The worksheet must contain exactly one structured Excel table.
- The table must use this exact column structure and order:
Date | Vendor Name | Expense Account Code | Expense Account Name | Total Amount | Comments
- Include one row per transaction found in the PDF.
- Autofit all column widths so that content is fully visible.
- Do not output JSON.
- Do not explain your reasoning.
- Do not return plain text.
- Only return the Excel workbook.At a high level, this instruction set establishes the task, the constraints, and the required output. It directs the agent to extract every transaction from an uploaded PDF, classify each one using a predefined account mapping, and generate a structured Excel workbook. It also imposes strict data rules, requiring the use of the connected knowledge source for account codes, consistent formatting, full transaction coverage, and a defined fallback when no match is found. Finally, it specifies that the result must be a single workbook containing one properly structured Excel table, with no additional commentary or alternate formats.
By clearly defining the objective, the guardrails, and the output format, we minimize variability and shape the agent into a reliable reporting mechanism rather than an open-ended assistant.
Set up the knowledge source
The Knowledge section is where we connect the reference file that governs how expense categories are assigned. In this workflow, the knowledge source functions as the single source of truth for accounting codes. The agent is not allowed to infer categories from general knowledge or make judgment calls. It must look up each classification directly from the structured mapping we provide.
For this example, we will upload ExpenseAccounts.xlsx, which you can download from the repo exercise files. This file contains the approved expense account codes and names the agent is permitted to use. In a production environment where the chart of accounts changes frequently, it would make more sense to store this file in OneDrive or SharePoint so updates can be managed centrally. For demonstration purposes, uploading a static copy keeps the setup simple and controlled.
After uploading the file, it is critical to enable “Only use specified sources.” This setting ensures that the agent relies exclusively on ExpenseAccounts.xlsx when assigning expense categories. If this toggle is left off, the model may draw on its general training data, which introduces variability and risk. By restricting it to the specified source, we guarantee that all classifications trace back to the defined accounting logic and remain consistent across runs.
Set up the capabilities
In the Capabilities section, turn on Create documents, charts, and code. This is required because the agent must generate and return an actual Excel workbook. Without this enabled, it would only be able to respond with text.
Since our output is a structured .xlsx file, this capability is essential. We can leave image creation turned off, as it is not needed for this workflow.
Namely we need to turn on the ability to create documents.
Set the suggested prompts
Optionally, we can add a starter prompt under Suggested Prompts to guide users into the correct workflow.
This makes the purpose clear and ensures the agent runs the receipt classification and Excel report process as intended.
Great work! Go ahead and save your agent.
Testing the agent
Use the starter prompt to begin testing, and be sure to upload your PDF receipts. Click Upload images and files to add them. You can use the sample PDFs in the companion files to test everything.
In my experience, the agent performs best when each receipt is uploaded as its own PDF. You can upload multiple files, just avoid combining them into a single document. If you needed more advanced file handling rules, that’s where a more robust setup like Copilot Studio would come into play.
It shouldn’t take long to receive your results, including a downloadable Excel workbook.
And just like that, we get an Excel table back with the expenses classified.
For the entry missing a date, the agent flags it accordingly and leaves it unclassified. The “market” charge also was not categorized. It might fall under meals & entertainment, but maybe not. This is generative AI, it works probabilistically.
If we want to make this more reliable, we could update the instruction set to explicitly treat “market” purchases as meals & entertainment. That is exactly why the knowledge source matters so much. The clearer and more comprehensive it is, the more consistent the classifications become.
Conclusion
In this post, we built a Microsoft 365 Copilot PDF expense account agent.
What I like about this example is how practical it is. Filling out expense reports after a business trip is one of those tasks I genuinely dread, so having a structured way to automate part of that process feels like a real win.
I’m looking forward to exploring this space further and seeing how far we can push these kinds of focused, workflow-driven agents.