Recently I’ve been exploring how to use Microsoft’s own stack to build agents that support common Excel workflows. I’ll be honest: getting all the pieces to work together has not been simple. There are a lot of moving parts. Tools like Claude Code/Cowork can feel more streamlined, and it’s possible some of this will evolve once Copilot Tasks is fully rolled out:
Still, it’s worth understanding what’s possible today.
Before I go any further, I should say that many of these ideas are heavily inspired by Matthew Devaney. His content has genuinely helped me see a practical path toward actually using Copilot Studio and the Power Platform to build real AI agents and workflows. Thanks, Matt!
In this walkthrough, we’ll build a very basic agent that populates a variance highlight deck. The setup includes two files.
First, we have a dataset containing a standard monthly variance table.
We also have a very basic PowerPoint template that serves as the reporting deck.
Realistically, your actual deck will probably be more complex than this example, and as the task scales, the prompting and orchestration will need to scale with it. But you have to start somewhere.
You can download those two exercise files below to follow along:
- https://github.com/summerofgeorge/blog-files/raw/refs/heads/master/VarianceReportTemplate.pptx
- https://github.com/summerofgeorge/blog-files/raw/refs/heads/master/variance_data.xlsx
Be sure both files are uploaded to an accessible location in your OneDrive or SharePoint environment that Power Automate can connect to.
Before diving in, I strongly recommend being comfortable with the fundamentals of Power Automate. You should understand how to create triggers, structure flows, and write basic Power FX expressions. If that foundation isn’t there yet, I suggest starting with my LinkedIn Learning course designed specifically for Excel users before attempting to build workflows like this:
To begin, head to make.powerautomate.com and create a new flow. I’ll set this up as a manual flow for now, though in practice it would likely make more sense to schedule it as part of your standard month-end reporting process.
Next, we’ll add the first action in the flow: retrieving the path to our dataset. We need the file path because it will be passed as a variable into the prompt, allowing the flow to locate and work with the correct file at runtime.
Whether your file lives in OneDrive or SharePoint, choose the action labeled “Get file content using path.” That’s the one we’ll use to reference the template dynamically within the flow.
Next, locate your workbook in the same way. It’s a good idea to rename this step to something like ‘Get variance data workbook using path,’ since we’ll repeat the same action shortly to retrieve the PowerPoint template. Clear naming will make the flow much easier to follow as it grows.
Make sure you now have two steps that look similar to this. That way, both file paths will be available and ready to use in the next step of the flow.
Next, search for the action called “Run a prompt.” As the name suggests, this step allows you to execute a generative AI prompt directly within the flow and then use its output in the steps that follow.
Go ahead and create a new prompt. The full text is included below. We’ll walk through how to configure it so it functions properly within the flow.
Open the PowerPoint template [PowerPoint Template]
Open the uploaded Excel workbook [Excel workbook]
Locate the table named tbl_variance.
Identify the Month column in tbl_variance.
Determine the latest month present in the data:
- If Month is a date field, use the maximum date.
- If Month is text (e.g., "January", "February"), determine the chronologically latest month based on calendar order.
- If Month includes year (e.g., Jan 2026), use the most recent year-month combination.
Set this value as Selected Month.
Filter tbl_variance for Selected Month only.
Aggregate data at the Department level by summing:
- Budget
- Actual
- Forecast
- Prior Year Actual
Calculate:
- Total Budget
- Total Actual
- Total Variance (Actual - Budget)
- Variance %
- Forecast Variance (Actual - Forecast)
Determine:
- Largest favorable department variance
- Largest unfavorable department variance
Rules:
- For Revenue accounts, positive variance is favorable.
- For Expense and COGS accounts, negative variance is favorable.
- Only highlight departments where absolute variance exceeds BOTH:
- 5% of department budget
- $50,000
- If no department exceeds threshold, state performance was within tolerance.
Executive Summary Requirements:
Write the executive summary as 4–6 bullet points.
Formatting rules:
- Each bullet must be a single concise sentence.
- Do NOT write paragraphs.
- Do NOT include numbering.
- Do NOT include introductory text.
- Preserve line breaks so each bullet appears on its own line.
- No emojis.
- Use concise executive tone.
Content requirements:
- One bullet summarizing overall performance vs budget (include total variance and variance %).
- One bullet addressing forecast variance if material.
- One bullet highlighting the largest favorable department (include dollar and % impact).
- One bullet highlighting the largest unfavorable department (include dollar and % impact).
- Optional additional bullet(s) if other material insights exist.
Update the PowerPoint:
Slide 1 shape named "Title":
Variance Report – {Selected Month}
Slide 2 shape named "Variance report":
Insert the bullet-point executive summary exactly as written.
Do not convert bullets into a paragraph.
Do not alter formatting or layout of the slide.
Do not create new slides.
Return the updated PowerPoint file.Before we test the flow, go to Settings and turn on ‘Enable code interpreter.’ This setting is required if you want the prompt to create or modify files. In our case, we need it enabled so the prompt can generate the updated PowerPoint deck.
Next, we’ll configure the inputs at the top of the prompt. Where you see the placeholders like [PowerPoint Template] and [Excel workbook], those need to be connected to the actual files from the flow.
Click into each field, type the / symbol, and choose the option labeled “Image or document.” From there, you can map in the corresponding file content from your earlier steps.
This NEXT part can feel a little confusing.
In order to test the prompt, you need to upload placeholder files directly into the prompt interface. So go ahead and upload your sample deck and workbook here. You can also rename the variables at this stage to make them clearer and easier to follow.
Just remember: these uploads are only for testing inside the prompt editor. They are not part of the actual workflow. When the flow runs in Power Automate, it will use the real files stored in your OneDrive or SharePoint location.
All right, we’re ready to test.
Before running it, make sure the Output setting is configured to “Documents/Images” since the prompt will be generating a PowerPoint file. Once that’s set, click ‘Test’ and see what it produces.
Great. Once you see an output, go ahead and download the file and review it.
You may find that it takes a couple of test runs to get consistent results while working inside the prompt editor. For whatever reason, that seems to happen during testing here. I have not seen the same issue once everything is running inside Power Automate itself.
Amazing. You should see something like this. It’s alive, right? You just generated a PowerPoint variance deck using AI. That’s pretty cool.
But we do not want users going into the prompt builder every time they need a report. The goal is to run this with a single click or on a schedule. So the next step is figuring out how to automatically capture and save the generated file. Those final steps are critical and, honestly, a bit more involved than everything we have done so far.
Amazing. You should see something like this. It’s alive, right? You just generated a PowerPoint variance deck using AI. That’s pretty cool.
But we do not want users going into the prompt builder every time they need a report. The goal is to run this with a single click or on a schedule. So the next step is figuring out how to automatically capture and save the generated file. Those final steps are critical and, honestly, a bit more involved than everything we have done so far.
Go ahead and save your prompt and exit the editor.
Now we need to define the variables that will feed the prompt when the flow runs. Remember, we are not using the placeholder files anymore. We are passing in the actual files stored in OneDrive or SharePoint.
To do that properly, we have to convert the file content into Base64. The prompt runner expects the files in that format, so this step ensures it can correctly interpret and work with them at runtime.
To do this, populate the parameters for your workbook and deck as shown below:
base64(body('Get_variance_data_file'))base64(body('Get_variance_report_deck_template'))Now that the prompt knows where to retrieve the input files, we still need to decide where the generated deck will be saved.
To handle that, add one more action: ‘Create a file.’ First, specify the location where the file should be stored. I’ll borrow a smart approach from Matthew Devaney and use the Copilot Chat Files folder as a convenient place to save outputs like this.
First, we’ll generate a file name for the deck. I’ll call it VarianceDeck and append a random ID so we don’t overwrite prior runs:
concat(concat('VarianceDeck', rand(1000, 9999)), '.pptx')This creates something like VarianceDeck4821.pptx.
If you’d prefer something more structured, you could also incorporate today’s date into the file name instead, which can be helpful for version tracking.
Now we need one more Power Fx expression, and this is an important one.
When the prompt generates the PowerPoint file, it does not return it as a normal binary file. It returns the file encoded as Base64 text. That is essentially a long string representation of the file’s binary content. This encoding makes it safe to transmit through APIs and flow outputs, but it is not directly usable as a downloadable .pptx file.
So before we can save it with the ‘Create a file’ action, we have to convert that Base64 string back into binary.
That is exactly what this expression does:
base64ToBinary(first(outputs('Run_a_prompt')?['body']?['responsev2']?['predictionOutput']?['files'])?['base64_content'])Once converted to binary, the ‘Create a file’ action can write it to OneDrive or SharePoint as a proper, downloadable PowerPoint template.
Yes, there are quite a few steps. But now we’re ready to save the flow and run a full test.
If everything is wired up correctly, you should see a freshly generated deck sitting in your Copilot Chat Files folder, ready to open and review.
Keep in mind, this is generative AI, so results can vary. I have generally seen the numbers come through correctly, but you should absolutely review and validate everything before sending it to anyone.
Still, this gives you a real starting point for automating PowerPoint variance presentations, which is pretty powerful. There is clearly much more that can be built on top of this. I am learning as I go, and hopefully you will experiment as well and share what you discover.