How to use Copilot in Excel (Step-by-Step Guide)

hi I'm Jamie and welcome to teachers Tech  today I want to show you how to use co-pilot   in Microsoft Excel more specifically I want  to show you the potential of using co-pilot   in Excel if you're new to Excel and maybe  you don't know how to create formulas or   add Charts work with pivot tables do  conditional formatting all you have   to do is ask co-pilot to do these things  for you can either type it in or just   use the microphone to talk it in let's get  started on how to use co-pilot in Excel for beginners now co-pilot isn't something  free this is something you do have to add

with a subscription you do need a Microsoft 365  subscription and then you need to add co-pilot   Pro to this now once you add co-pilot Pro this  is going to be not only in Excel but it will   be through your other Microsoft 365 products as  well this is the sample data that I'm going to

be using to show you how co-pilot works today if  you want to follow along and you have co-pilot you   can download this just look down below in the  description and then you can follow along with   each step the first thing that I want to show  you is if we go up to the ribbon and we have   co-pilot right here notice that it's grayed out  that's because this file is saved on my computer   co-pilot doesn't work if the file is local on your  computer you need to upload it to one drive and so   what I'm going to do with this file that I have  have open I can do this in a few different ways   I could just click right up here and hit upload  or I could go to the file and I could go save   as and notice I can go to one drive or to the  SharePoint because at that point it's going to   be uploaded so I'm just going to go and choose  one drive and save it here now that this file is   saved in one drive notice that we have co-pilot  and I can click on it at this point to open up   the side panel I can resize this so this gets a  little bit larger at this point uh and you have   a few more options here where you can move resize  close as well now take a look at the options that   I can't do because it's gray out that's because  co-pilot still needs something else it needs to   turn this data over here into a table now I could  go and insert table up here or do control T but   right here we have convert co-pilot's going to  do this for me so I'm just going to go ahead and   click on convert and just like that it converted  all that range into a table and notice now I have   all these available to me which we'll dive into  in a moment as soon as this turned to a table if   you wanted to do any reformatting it's quick to  do it with any of these and all your options of   filtering or sorting up top now that's probably  why they turn this into table because it makes   co-pilot much easier to work with when this is  in a table format this down here is the prompt

area this is where you're going to ask co-pilot  to perform task based on this data over here now   you may be unsure what to ask it if you're brand  new to this there's a suggestions here if we go   view prompts you'll be able to see that it gives  you some ideas what you might want to ask now they   have different topics that you can go through  is it for creating is it for understanding it   will help you kind of get your prompts off the  ground the other thing I want to point out use   your microphone just talk and ask it a question  you can always undo the prompt that you created   or whatever it performs and then it's going to  just help you understand how to create a good   prompt for co-pilot to do exactly what you want  now what I like to start with is these up here let

me give you an idea so if I click on highlight  it will tell me what this can do for you now   down here this is where it will perform a certain  action do I want it to bold the First Column do I   want to show items with the sales date so if I go  and click on buold the first column it's going to   go through this data and just bold it so it will  format for me so let's go ahead and click on it   so if we take a look at this column over here  now we can see that it formatted it with bold   as I mentioned before we have the undo here now  the other thing I want to point out whenever you   see the refresh besides here if I go and click  on it will give me different ideas based on   the data what it what might think that I want  let's go back up and try something else let's   go add formulas and columns so now it tells me  what it's or suggesting what it can do and we   have add a column that combines product and unit  sold well maybe I don't want that so I'm going   to go ahead and just hit refresh now it's giv me  three more options and I can still go ahead and   hit refresh again notice that it's saying show  total sales revenue by sales date at as a line   chart it will even create a chart and I haven't  even had it to prompt anything actually let's go   ahead and try this one it's done and it created a  line chart that shows the total sales revenue by   sales date and here it is so all I have to do is  Click add as a new sheet which I'll do now and we   have the data that it pulled out here and created  the line chart I can still go through and uh do   some formatting it if I want but you can see how  they have the pivot chart field over here that it   created from the data and I haven't even asked it  to do anything yet it was just suggesting things   that I might want to do now if I go back to sheet  one here this is where I'm going to see co-pilot   again but I can go back and forth between the  different sheets these options up here to learn

are great and it's a great place to start but  let's move down to the prompt area and ask it   a number of questions to perform on the data  let's start with this you know I noticed   that there's a time in here and I don't really  want the time let's see if I can ask co-pilot   to remove this so I'm going to say remove time  from the sale date column let's go ahead and send this now if I take a look at the sale date it is  fixed the time is removed let's go ahead and ask   it to pull out some numbers you know what I'm  really curious about what product has the most   units sold that's kind of a probably an obvious  question that people would want to know let's go   and send that it looks like the winner is widget  B with a total of 563 units and it went ahead   and created the pivot table for me if I move  up a little bit here it is I can add this to   a new sheet just like I showed you before we  have our pivot table here now I'm just going   to move back to sheet one and I want to know  something else and these first few are pretty   simple questions that I'm asking but I'm kind of  curious of what is the toal Revenue let's go   and use our microphone for this one and just go  ahead and ask it this question What's the total revenue so I sent that away and again we have  our total we have a pivot table that we can add   to the sheet just like before now let's see if  we can add a new column so I want a new column

created and it's going to be called sales tax here  so we'll just do one step at a time for this one   so we'll create a column named sales tag let's  go and send that take a look at this I asked it   to create a column named sales taxs and it said  here's a for formula column to review and insert   I didn't even tell it to do the uh formula yet  and at 5% I was going to get them to do it but   it already jumped ahead and created formula for me  if I like this after the review and I can even go   and take a look at the explanation of it I can  go ahead and hover insert notice as I hover it   shows where it's going to go and the amounts now  I could go ahead and make an adjustment after do   I want this added to the total sales revenue or  do I want it to be uh just by itself as that 5%   I'm just going to leave it as this is separate  just 5% out I'll hit insert now I have that new   column let's see if we can make a little bit more  complex formula here when we add a new column this   is what I want to have happen this time I want to  add a new column called discount and it's going to   look at the units sold and if they see it if it's  above six so you can tell that there's going to   be an if statement in here if it's above six it's  going to give a 7% uh discount and let's go ahead   and add this to our prompt let's see what happens  here so create a new column name discount give a   7% discount if units sold are over six I'm going  to go ahead and send this so here's the formula   to check I can see it has the if statement in here  but right away I'm noticing you know what there's   something I did this wrong I asked it the wrong  question because if I hover over it notice it's   just going to give 0% or 7% I actually want it  to be applied the discount so I want to multiply   the 7% by the sales revenue so I have to change  the question a little bit so I'm just going to   go down here and put this one in so create a new  column named discount give a 7% discount applied   to the sales reeven if units sold are over six so  the main thing what I want to point out is just   go ahead notice if you make a mistake just go and  tweak The Prompt and then get it back to what you   want it let's go and send this one so this one's  looking a little bit better I can see in the if   statement here so it's going to look and this  is the part that was missing in the first one   it was the sales revenue times the 0 7% or give  back zero let's hover over it so now it's giving   me amounts for this and that's what I want now I  could go another step forward and maybe add this   to the sales revenue but I want it separated out  I'm going to go ahead and click insert now I have   that column here with that formula attached  to it at the beginning we created a chart it

was a line chart based on these learning options  and how co-pilot took us to there but we can go   ahead and ask it to create a chart on something  specific so if I say create a bar chart showing   each product total sales so if I go ahead and  send this now let's go ahead and just move up   we should have our bar chart here and we have  our widgets right through here and it's they   place them in the order of the best sales of the  top sales on it and I can add this to a new sheet   like we did before and we have the pivot table  and charts all connected to these and remember   you can do formatting on it just like you can  do in Excel like I've shown in other videos   if I move back here let's go and try a pie chart  this time create a pie chart showing each product   sales of the total and we'll send this one let's  move up a bit here and we should get a pie chart   showing each of these and I can go ahead and add  this new to the new sheet just like I did before   let's finish up with this tutorial about talking  about some formatting options again that we can   do with co-pilot let's try a few different  formatting options and the first thing I notice   is that you know these columns here probably  should have the dollar format here so I think   I see there's probably units price sales revenue  so there's four that should have it let's go ahead   and I could ask it to do uh specific column so I  could say unit sold unit price but what happens   if I ask it use dollar format in the proper  columns will it know let's go ahead and send this now it looks like it missed one of the  columns I can see it found sales revenue sales   tax and discount added the dollars but it didn't  add it to the unit price but I think that's going   to be a quick fix I'm just going to go ahead  and ask it add the format to unit price as well and I'll hit send and there it made the  change since I'm in the same thread it knew

what I asked up here and then it just added that  formatting to it let's try one thing more with   formatting and this time we're going to be looking  at conditional formatting now I want to be able to   look at this over here and if anything's below  seven I want it to be highlighted in red and   if it's above 10 I want it to be in green so  I'm going to go ahead and ask this highlight   in green units sold above 10 and highlight in red  any below seven let's see if it knows how to do this and this looks pretty accurate over here  we have our red if it was under seven and green   if it was above 10 so that's a handy way just  to go ahead and ask it to do those formattings   with whether be typing or using the microphone  I hope you found this video about co-pilot and   Excel helpful understanding the potential behind  it that being said I think it's still important   to understand the basics of Microsoft Excel at  least when you're using co-pilot it's going to   really help you with your prompts and looking  at the data and understanding what's happening   if you're interested in learning about more  co-pilot applications in other Microsoft 365   products I'll put a link to my video for that  up above in the card thanks for watching this   week on teachers Tech I'll see you next  time with more Tech tips and tutorials