NEW Copilot Agents Build What Used to Take Hours (Automatically)

Microsoft just released co-pilot agent mode in Excel and it can now build entire spreadsheets for you. I'm talking

full models, link sheets, calculations, charts, dashboards, the whole workflow.

It works more like a digital assistant than a standard AI assistant. In this

video, I'm going to show you how I used it to build this retirement planning model with a one-s sentence prompt that simply described what I wanted. We'll

also try changing a key assumption later just to see how well the structure Copilot created holds up. Let's jump in.

Copilot agent is currently in preview and I'll show you how to activate it later in the video. To access it on the home tab, click on the copilot dropown and open the chat pane and then click on

the tools button and choose agent mode frontier. Now this isn't the regular

frontier. Now this isn't the regular copilot pane that explains formulas etc. Agent mode goes a step further. It plans

a workflow and executes it. It reads

your workbook. It builds sheets. It

writes formulas. It can even explain the reasoning behind each step. So, I simply described the outcome I wanted and the agent broke down the problem into steps.

But here's the trick. I didn't write the co-pilot prompt myself. I actually asked Chat GPT to write the prompt for me. And

all I asked it was to develop a prompt for co-pilot instructing it to create a retirement planner that can have various inputs adjusted that would be typical of a retirement planner. And chat GPT

produced a structured prompt that explained what sheets to create, what calculations to perform, how the formulas should reference each other, and how the dashboard should be

organized. So all I had to do was copy

organized. So all I had to do was copy the code and go back to my co-pilot agent and paste it in. Of course, you'd review the prompt using your knowledge of retirement planning and Excel to make

sure it's accurate and thorough, but for the purpose of the demo, let's take a look at what it produced. You can see here it created four sheets. Input where

we control assumptions. Now, straight

away I noticed two small things. The

currency defaulted to GBP even though my region is set to Australia and header font color is too hard to read. But

those are quick formatting issues that we can easily resolve. What matters is the structure. It also defined names for

the structure. It also defined names for each cell. So we can see in the drop

each cell. So we can see in the drop down here and if I arrow through the different cells in my inputs, you can see the different names defined in the name box. This just makes them easy to

name box. This just makes them easy to reference. Then on the projection sheet,

reference. Then on the projection sheet, it used formulas that differentiate pre-retirement growth from postretirement growth. Which means if I

postretirement growth. Which means if I change the inputs, it should automatically update. After that, it

automatically update. After that, it generated a draw down schedule specifically for retirement years.

Again, it uses formulas to calculate the values, which I expect will make it dynamic. We'll see. And finally, it

dynamic. We'll see. And finally, it built the dashboard and selected chart types to visualize savings and withdrawals over time, plus a runout indicator. In building the model, it

indicator. In building the model, it added data validation and we can see that on the input sheet for the state pension, we have a drop-own and contribution frequency. We also saw that

contribution frequency. We also saw that it defined names and if we open the name manager, you can see the list of them here. Plus on the projection sheet, it

here. Plus on the projection sheet, it used some conditional formatting. And

let's open up the formatting manage rules dialogue. We can see it's added a

rules dialogue. We can see it's added a format to column G to highlight if we run out of money. But one of the most impressive parts of Asian mode is that

it doesn't just build the workbook. It

actually explains how it decided what to build. And we can see that in the

build. And we can see that in the co-pilot pane. So this is my prompt in

co-pilot pane. So this is my prompt in gray. If I scroll down, it gives me an

gray. If I scroll down, it gives me an outline of what it plans to do. Then

reason for 144 seconds. And if I expand this, we can see all the steps that it took. You can click on the arrows to get

took. You can click on the arrows to get a bit more information about each step.

And if we scroll down under considering chart creation approach, it came across an issue, it explains what it thinks the cause of the issue is and then what it plans to do to address it. You can see

in the next step, it narrows down the range to ensure there's enough data for the charts. If I scroll further down, we

the charts. If I scroll further down, we can see more detailed steps of how it built the model at a high level, the assumptions it used, and even how to use

it. If I expand show details, you can

it. If I expand show details, you can even see the individual formulas that it's written for you. And at the bottom, it explains the verification steps it's

taken and next steps and customizations that I might want. So, instead of a blackbox spreadsheet, we have a traceable workflow that you can follow

the logic from input to calculation to output. However, currently, if you close

output. However, currently, if you close the co-pilot pane, you're going to lose all of this documentation. So, I

recommend taking a copy of it for reference. And this is where we slow

reference. And this is where we slow down. A spreadsheet looks polished, but

down. A spreadsheet looks polished, but polished does not mean correct. An AI

will never warn you when it's made an error or tell you shortfalls in its assumptions. Copilot accelerates work,

assumptions. Copilot accelerates work, but we must validate assumptions and test the model for accuracy. That's

where human expertise remains essential.

Okay, let's test the model. In the

future, it's expected that we're going to live much longer than ever before.

So, let's change our life expectancy from 90 to 100. Now, if we go to the projection sheet and I scroll to the bottom, you can see it's only calculated

up to age 90. And that's the first issue. It should have used formulas that

issue. It should have used formulas that automatically spilled down because when key assumptions change like lifespan, the model structure should change with it. Static tables in dynamic models are

it. Static tables in dynamic models are a reliability risk. Let's take a look at the drawown sheet and you can see we've got the same problem. It hasn't expanded the formulas. However, if we go to the

the formulas. However, if we go to the dashboard sheet and we scroll down, you can see these formulas were designed to spill and expand. But because they reference the projection sheet, we're

returning errors here. And if we go up to the top and look at the charts, the charts don't update either because it's referencing a fixed range. So

technically nothing breaks, but the model doesn't expand to include the extra years. Now I could ask the agent

extra years. Now I could ask the agent to fix it, but that can turn into a back and forth loop and often it's faster just to update the formulas ourselves.

For example, using sequence to control how many years get calculated and scan to calculate the balance yearbyear. And

this is the professional's role. C-pilot

can help us build the first draft. But

verification and refinement still require us to understand how the formulas work, how ranges expand, and how to adapt the logic when assumptions change. This is not a check the box and

change. This is not a check the box and trust the AI. This is collaboration.

Copilot accelerates the setup. You bring

the judgment, the testing, and the structural improvements. And this

structural improvements. And this requires domain knowledge and Excel skills. Without these, you're 100%

skills. Without these, you're 100% dependent on AI being right. Copilot

Agents is in preview and only available for Microsoft 365 copilot license customers and Microsoft 365 personal, family, and premium subscribers. It's

currently part of the Frontier program, which you need to enable in your 365 admin center. Under Copilot settings,

admin center. Under Copilot settings, find Copilot Frontier, and on the web apps tab, enable it for the use groups that you want. Of course, once co-pilot agents are generally available, you

won't need to enable the Frontier settings. So, keep that in mind if

settings. So, keep that in mind if you're watching this video sometime after it was first published. So, the

takeaway is this. Copilot agent mode is powerful. It can save hours and build

powerful. It can save hours and build solid structure, but it doesn't replace your domain knowledge or your Excel skills. Your ability to check the math,

skills. Your ability to check the math, question assumptions, and shape the model to reflect the real world. That's

where the real value is. If you're

excited about this and you want to keep building your Excel and analysis skills so you can use Copilot effectively, this video is the perfect next step for you.

I'll see you there.