How to Use AI Agent Mode in Microsoft Excel (Step-by-Step)

If you think you know Excel throughout the rule  book, everything just changed. For decades,   we've been the ones doing the heavy  lifting in Excel. We write the formulas,   we build the pivot tables, and we debug  the errors. Copilot helped a little,   answering questions one at a time. But what if  you didn't have to ask the questions? What if   you could just delegate a massive multi-step  project to an AI co-worker and watch them   do it right in front of your eyes? Today, I'm  showing you the biggest update to Excel in years,   AI agent mode. It plans, it executes, and even  corrects its own mistakes. Let's get started. Hi everyone, Jamie here at Teachers Tech. Today  we're looking at agent mode in Microsoft Excel.    Right now, this is in preview. You might not see  it in your desktop app unless you're part of the   Windows Insider beta channel. The easiest  way to try this today is using Excel for   the web with a specific addin. You'll need a  Microsoft 365 copilot license for this to work.

I'm logged in to my Microsoft 365 Copilot account  here. I'm just going to go to my all apps and go   to Microsoft Excel and we're going to open up  a sheet and get this installed right away. So,   I have some uh demo data that I'm going to open  up here and I can show you how agent mode works.    But once you have it open here, I'm going to go  to add-ons right here or add-ins and I'm going to   type in agent mode. And you will see once you do  the search, it's this one right here, Excel Labs.    And I'm going to click add. And then after a few  seconds, it just pops open right here. So make   sure you have that. And then I'll put a link to my  practice data down below. So if you want to open   that up and follow along with me, go right ahead.   Now, if you're on enterprise versus a personal,   it might look a little bit different. Where I  just showed you, I installed that on my business   account, my enterprise account. If you have a  personal account, uh you can go ahead and install   it. But the other thing you can do, I'm going to  drop down on under Copilot here and I'm going to   turn on Frontier features. And I'm just going to  turn on this with preview features. The difference   now is when I go to Copilot. So if I just drop  down and I'm just going to go to chat. Then right   here, if I hit the tools, you're going to see  agent mode frontier. So, when I click on this,   you're going to get a few more options than  the first way. So, depending on if you have   your personal or enterprise account, depending  what you're doing, and I'm going to show you this   later on in the video. But here, I can try claude.   And the other thing I can do with this is uh I can   select sources from the web. So, make sure you  uh stay tuned to that because I'll be showing   you from both ways. Before we run it, you need to  understand the difference. Standard co-pilot is

chatty. You ask it how do I calculate growth and  it gives you a formula to copy and paste. It's   passive. Agent mode is autonomous. You give it  a goal and it breaks that goal down into steps,   executes them sequentially, checks its work,  and iterates. It's like having a junior data   analysis sitting next to you. Let's go ahead and  open agent mode. You'll see it right here. Should

say Frontier. I'm going to click on open. Okay.   Let's say I have this raw sales data here.    And usually maybe your boss asks you for a Q4  report and you might spend an hour cleaning   this up and calculating columns or total revenue,  building pivot table and making charts. Let's ask   the agent to do this whole job at once. So what  I'm going to go is this. I'm going to say analyze   the sales data. I need you to first add a column  calculating total revenue for each. Then create   a new worksheet with a pivot table summarizing  total revenue by region and item. Finally, on   that same new sheet, create a clustered bar chart  visualizing the top five selling items by revenue.    I'm going to go and send this off. Notice I'm not  asking how to do it. I'm telling it the outcome   I want. Okay, 42 seconds later after sending  that prompt off, it says it's done. It added   total revenue column for sheet one. I can see  it right here and simple formula right in here,   but it did it for all the way down. It created a  revenue pivot sheet and I can see it right here   and I'll check that out in a moment and inserted a  cluster bar chart with the top five items. So if I   click on this, I'm going to go and just close this  off here. And look at this. So we have our pivot   table here. If I click in it, the pivot table  fields open up and I can go ahead and adjust this   just like creating a pivot table. But if you've  never created one before, all I had to do was ask.    And I like how it just put the chart exactly what  I was asking. I can see 1 2 3 4 5. Uh, and this,   remember, this was all in 42 seconds. All right,  I just want to try something with this. I, you   can see I have 47 lines here, entries. I want to  know if this can create more uh, data for me. So,   this is all just a practice demo. So, I need  more data for this demo. Please generate 50 more   rows of random sales data following the exact  same pattern. You kind of get the gist of what   I'm after. I'm going to send that off. Now, that  did think a little bit longer for 76 seconds,   but I can scroll down and I can see that I now  have the 50 uh 50 plus entries added to it. So, it   appended 50 new random sales, but it also updated  everything. So, it kept total revenue dynamic,   updated the pivot table on pivot on revenue  pivot, and made the top five items by revenue   block dynamic. So everything updated at once even  after I got it to add those that new data to it.

Now asking for one chart is easy but what if your  boss wants a full dashboard? Usually that means   creating three different pivot tables on three  different sheets and inserting three different   charts. Let's see if the agent can handle a  multi-art visualization task in one prompt. Okay,   this is the prompt I'm going to give it. I need  a dashboard overview of this data. Please create   a chart showing the total revenue by date. Chart  showing total units sold by item. Chart showing   the breakout of revenue by uh by country. Please  create those on a new sheet. I wasn't going to   ask it to create it on a new sheet. I just wanted  to see at first if it would do it, but I thought   I'd add it. And the other thing I didn't uh  really clarify with it what type of charts.    I was going to at first say these are the charts  I wanted, but I'm just going to leave that off to   see how it goes about choosing those. Let's send  this off. As we're going to go through this, I'm   just going to open this up here. And you can see  the activity of how it's planning chart creation.    And if I open this up, so I'm planning to create  three charts. It's going to show the step by step   how it's doing this. Now, I can see under this  part where it's going to ch use the line, column,   and pi. These are the ones that I was actually  going to put in it anyway. So, it's probably   just going through and picking the most obvious  ones, but I like how it just breaks everything   down step by step. You can see it reasoned for 11  seconds. The dashboard created. I told it to put   it on new sheets. I'm going to prompt it again in  a moment, but I want to check this out. I want to   see if it'll put everything onto one sheet. Uh,  but we have our revenue by date, units by item,   revenue by country, and where to find them. I can  see that we have the new sheets right down here   with all the different charts to it. Uh what it  did built dynamic helper blocks on each new sheet   using unique sum ifs, sort, sort by and index to  summarize from sheet one. Um again this thought   for 11 seconds to do all this and um yeah you can  just check out all the different things that it   used the sources. Here is the formula that it  used in it. Here is the let one and yeah this   is just great how everything is seen here. So,  what I want to do now, uh, because you can see   each of these are in a new one, and I'm just going  to quickly ask it, can you place all this data,   um, on a new sheet, I'm going to actually  rephrase that. Can you place all these charts   um, on one uh, new sheet? So, let's see if it  can do this. And it reasoned for 31 seconds.    And it created this dashboard, consolidated into  one sheet, created the three different charts.    Charts are bound by the dynamic summaries. I'm  just going to go and zoom out a bit so you can   see it a bit better. Here we can see that we  have our sales dashboard, revenue by country,   and then we have the data, too. So, everything  got moved over. There's the other one here, which   we could move around. We could add slicers uh to  do uh more things to a dashboard just like you   would do before. So, now I've just switched over  to my personal account so I can show you the other   way to use agent mode. So, I'm just going to click  on co-pilot here. And it opens up the chat. And   we just go to tools. And we want agent mode. And  the first thing I just want to point out is this

right here. Try Claude. I'm just going to click on  it. So, why would they do this? So, in my testing,   Claude is sometimes better at complex logic chains  or very intricate data reasoning task. It's great   that we have the choice to see which one gives  us better results for specific spreadsheets. Now,   this is what I'm excited to show you right  here. So make sure you have this turned on.

Now if I look at my data, it's just internal sales  figures. But what if I wanted to compare this to   external market factors? So what I'm going  to ask it to do is this. Using web search,   find the current inflation rate for the US and UK  last year. Create a small table on this sheet with   the data and then add to the text box summarizing  how current inflation might be impacting the sales   regions in my data. So I'm going to send this off  and let's see what we get. I can see right away   that they already created this table and they have  the impact analysis. I'll move over here just to   take a quick look at what they did here. Uh let's  look at what their explanation was. They went   through they found the latest inflation data for  countries. They created the table. Um they fixed   the merge issue. Uh let's see as we keep  going down summary of what was added inflation   data table. They found the information USA, UK,  uh, inflation impact analysis. And as we go down,   it explains the whole process. I can check the  sources where they pulled this information from.    So, this is where they pulled everything from. I'm  just going to close this down and we'll just take   kind of a little bit larger view at it so we can  see what was created uh, on this sheet here. So,   again, that took less than a minute. I gave it  quite some specific instructions, but if I was   going to do that all myself, that would take a  lot longer than 1 minute or so. Okay, real talk

time. This is amazing technology, but it's not  magic, and it's still in preview. Here are my   three rules for using agent mode successfully.   One, be specific about the outcomes. Don't say   analyze this. Say create a pivot table showing  X by Y filtered by Z. The clearer the goal,   the better the plan. Two, treat it like a junior  employee. It's going to make mistakes. Always   verify the formulas it writes and check the  pivot table settings. Don't blindly trust the   output for critical business decisions with  without a quick review. Three, iterate. If it   gets the chart wrong, don't undo everything. Just  type into the chat, that's the wrong chart. Type,   please change it to a line chart showing trends  over time. You can have a conversation with it   to refine the result. AI agents are shifting Excel  from a tool where we do the work to an environment

where we manage the work. Give this a try in Excel  for the web. And let me know in the comments,   is this going to save you hours of work or do you  prefer to do it manually? If this video helped you   stay ahead of the curve, please hit that like  button and subscribe to Teachers Tech for more   practical guides for the latest technology.   Thanks for watching and I'll see you next