How to use Copilot in Excel - The Complete Beginners Course

What if Excel could just clean your  data, build your charts, spot errors,   write formulas, and explain everything  it's doing, all just by asking? Well,   with Microsoft Copilot, it can. And  today, I'm going to show you exactly how. Hi everyone, Jamie here from Teachers Tech. Today,  we're diving into one of the biggest upgrades   Excel has ever received, Copilot. Whether you're  brand new to Copilot or you've tried a little and   felt overwhelmed, this video is going to walk you  through everything step by step. We're going to   start simple and everything I show you today is  going to be beginner friendly. And the file we're   working with will be available so you can follow  along. Copilot is now available in all Microsoft   365 accounts. So you'll see Copilot buttons and  basic AI features no matter which plan you're   on. But for this course, I'm using Microsoft 360  Copilot Premium, which includes the full set of   Excel features like advanced insights, richer app  skills, deeper data analysis, and support for the   newest Copilot capabilities. Everything I demo  here will work on the premium version. So, we're   going to get started by looking where Copilot  lives in Excel and how to use its two different   modes, chat and app skills. I have my Microsoft  Excel desktop app open here and I'm already logged

in to my account and I want to point out under  the home tab and if we look across the ribbon   all the way on the right we have co-pilot and  as I mentioned we have two different ways we can   use this. If I click the dropdown you're going to  see chat and app skills. Let's go ahead and click   on chat first. So this is where you ask copilot  questions about your workbook. You can use this   for insights, summaries, explanations, trends,  recommendations. Think of chat as a conversation   about your data. Let's go back up and click  on app skills. These are your action tools.    So app skills tell Excel to do something inside  the workbook. Things like insert a pivot table,   create a chart, analyze a selected range, generate  a formula column, apply conditional formatting. So   a simple way to remember chat analyzes and app  skills act. Now, when this panel is open, you   can also resize this. If I just move my mouse in  between, you get the double-headed arrow. You can   see how you can stretch it out and get the size  exactly as you want. And if you want to close it,   just go ahead and hit close. Another way you can  quickly access either chat or app skills is when   you click on any cell, you're going to see the  shortcut to copilot right here. So, if I go ahead   and click on it, you'll see here's the app skills.   If I have data, these would be showing highlighted   where I could use them. And if I go and click  on chat with co-pilot, it's going to open up   the side panel. The other thing I want to point  out, if you rightclick on any of these cells,   you're going to get the co-pilot suggestions  right here as well and chatting. For the best   co-pilot experience, especially with premium, your  file should be saved to one drive or shareepoint.    This allows copilot to work with your workbook in  the cloud and use the more advanced models. So,   in this case right here with a quick demo, if  I'm saving it to go to file, you can see we have   save as. I have my one drive right here. I can go  and make a new folder. Let's call this one Excel   copilot. I'm just going to hit okay. I'll open  this up and I'm just going to give this a name

and I'll just call this sales. And I hit save. So  now I'm on autosave up here. This is saved in the   cloud and this is where C-Pilot will work best. I  want to give you some information about supported

files. Copilot supports modern Excel file types  like XLSX or XLSM and clouds sync files. The older   formats such as XLS or incompatible file types  may not work fully with Copilot. So saving these   in the latest file format ensures full feature  compatibility. One important thing to know and   if you've worked with Copilot before, your data no  longer needs to be formatted as an Excel table for   C-Pilot to understand it. As long as you have the  clear headings and it's structured like a table,   Copilot's going to work fine with this data. That  being said, I still believe it's best practice

to take your range and turn it into a table when  working with Copilot in Excel. And you can do this   very quickly. If you click anywhere inside the  range here, and if you move up to insert, you're   going to see table right here. I would get used to  using the shortcut CtrlT. Just any shortcuts will   make everything a lot faster. But if I go ahead  and click on this, this is where I can have my   range of the table. My table has headers. I'm just  going to go ahead and click okay. So it quickly   turned this into a table now. And notice as I add  more uh rows to it, it automatically uh will be   part of that table. One of the good reasons why  you might want to switch over to it. So this is   just a normal range. If I go ahead and let's say  highlight this and I go and click on co-pilot,   notice I have all this uh available to me. Now,  if I wouldn't have saved this in the cloud,   these won't be showing. So, just let's go ahead  and click on show some interesting insights. And   I look over to the side and I'm just going to  stretch this out a little bit. You can see even   this just being a range. If I scroll down, I can  see now that co-pilot analyzed even though it's   simple information. If I go down, you can see it  even created charts automatically just by looking   at this to look at the insights. And that's  it for module one. You now know the difference   between chat and app skills, how to use co-pilot  suggestions, make sure you save to one drive or   shareepoint, and you don't need to format your  data as a table for co-pilot to work. In the next   module, we'll apply co-pilot to a real business  scenario and start generating insights, charts,   formulas, and pivot tables. In this section, we'll  explore how Copilot handles real world data sets,

although we'll keep them small and simple to start  with. We'll be generating insights and charts   to building formulas, cleaning data, creating  pivot tables, and even performing a quick whatif   analysis. This is where Copilot starts to feel  really powerful. Before we dive in, download the   practice workbook and save it to your one drive so  Copilot can work at its full capacity. If you take   a look at the workbook, notice at the bottom that  there's different tabs that we'll be using for   different demos. With the workbook open, let's go  ahead and be on our first tab here, retail sales,

the first worksheet. And we're just going to do  something we quickly did before, but we're going   to highlight the range. And everything has the  heading and is clean in this one, so no need for   a table. But I'm going to go ahead click on this,  and we're going to go to show me some interesting   insights. And you can see right away on the open  panel here, we're getting some insights. Let's   take a look what they're telling us. Now, let's  take a look at what Copilot created for us with   this one simple prompt. Show me interesting  insights. Now, I want to point out this right   here. AI generated content may be incorrect. It's  important for yourself to doublech checkck the   numbers. Now, Copilot can save you a lot of time  in creating everything, but you want to make sure,   especially if you're passing this off to someone  else, that you have the person in the loop that's   able to check this off and to confirm that the AI  is correct on this. But we have it at this point,   and this is a simple data set. So, we can easily  uh check this. But if we look at it, we can look   at the products that it's definitely pulled out,  the months, the regions, and we can say, "Yep,   that's all correct there. " They did an analysis an  analysis here. We have our products, our revenue.    They put it into one chart here. The total revenue  for each product here by month. And then if we   keep going down, we have it here by region and  the revenue. So from that one simple prompt,   we're able to dig into all this information.   They're letting us know jacket is the top selling   product. February saw a slight increase and east  region outperformed the west. So if you have a   large data set with a very simple prompt like  this, in seconds you're digging into the important   information that you can pull out of it. The other  thing I want to point out, let's go ahead and   start a brand new conversation here. And I'll just  go over here and I'll do the show me interesting   insights. And when we look at the response that  Copilot gave us this time, notice that this is   very similar than the first time, but we're not  getting the tables and everything. So my point   is here that each time you ask a question, you  might get a different answer back. Not to say it's   wrong, but it just might answer it differently. So  if I move down, you can always see the suggested.    So if I want to get deeper results using advanced  analysis mode or visualize the revenue breakdown   by product month and region. So this is a point  where I could add that in the first time it gave   me the answer or maybe I just go ahead and ask  it something specifically that I'm interested   in like show me only the revenue trends from the  west region and I'll send that off. And based on   this prompt this is what it created for me here.   So, if I move down, you can see that it has an   interactive chart as I hover over any of these  here and it has the legend at the bottom and I   have this insert into sheet. So, if I go ahead  and click on this, this gets added to the sheet.    We're going to come back to some more charts  uh in a moment, but we can see it answered the   question that I asked to it, but it even created  the chart as well. but another time it might not   do that exact same thing. Now I want to talk about  visualizations. show you how to

create visualizations using copilot in Microsoft  Excel. I'm on the marketing sheet here and I'm   just going to be making some quick charts with  this. Now the first thing I just want to point   out if I go ahead and highlight this and go up to  copilot this way. Notice we have summarize using   pivot table or chart. I'm going to come back to  that later when we get to pivot tables. So, that   will be further on. And right now, I just want to  stick to a basic chat with Copilot. So, I'm going   to go and open up chat here. And I'm going to ask  it to create a chart that also communicates this   data effectively. So, it's going to go through  and I'm going to send this off and create some   charts. It will create more than one. I like how  it explains it as well. Now using chat you can see   it went through for 21 seconds and reasoned all  the different steps that it went through. This   time we have a number of different charts that it  created. So retail sales, revenue by product and   month and you can see how it's clustered within  them. So we got January, February. But it didn't   stop there. It actually explains why it works.   So groups by month and product. So both time and   mix are visible. I really like this so that you  understand that they're just not giving you a   random chart. They're actually explaining why  this works. But it didn't stop at one chart. If   I move down, you can see now it's leads versus  spin. So, efficiency scatter. And we have this   type of graph here. And down below, it says  why it works. And then as I continue on here,   it even did a third one. We have donations, total  raised by campaign. And it did a bar graph here,   why it works. And now it shows you it tells you  about how to insert these into Excel quickly. Now   in this method they are telling you to download  each of these to your computer and then you're   going to go to the insert and then upload. But you  can also copy paste as well. So if I go and click   copy on any of these pictures I can move over here  and go Ctrl +V and paste them in. I can go ahead   and size them. uh you can move them all around the  sheet or place them in on whatever sheet you want.    But remember that this won't be uh interactive  anymore. It won't be dynamic. This is just a   picture over here. So that's why they actually go  through uh down here how you can recreate these.    So they go through step by step of how to create  this as a pivot table and putting it in. So it   does walk you through all of these steps. But  at the very bottom, you can see it says, "Yes,   please embed these charts into my workbook. " Let's  go ahead and click on this and send. But it's not   going to go and embed it directly into this.   What it does for you instead, if I just move   down a little bit, it has a download here. So,  I'm going to download the document. And then,   if I open this document up, but remember right  now, I'm just going to click enable editing.    All this one is saved to is to my PC. So if  I wanted to keep using this at this point,   I have a new sheet. If I want to use a co-pilot,  I would have to re-upload this one to one drive or   shareepoint. Now co-pilot isn't all about charts.   You can do more. You can create actual images. So   in this case, what happens if I want an image to  put on this sheet that would match this topic? So   what I did in co-pilot and this is just in the  chat. I asked it to create an image that would   match the topic from the marketing sheet and it  went and created me this. So I could go ahead and   take this now and I could go and paste it right  in the sheet. Now I could even go further with   this. You can see text labels for each campaign,  make the image more colorful, or I could give   it more the exact details that I'd like. But the  point I'm trying to make is you're not limited at   just charts. You can get it to create any type of  image you would like. One of my favorite features

of using Copilot in Microsoft Excel is formulas.   Whether it's creating instant formulas or giving   fabulous explanations to help you understand them.   So we're going to use the student score sheet   here. And I'm going to go and highlight this all.   And we're going to go to copilot. And you're going   to see that there's suggest a formula column. I'm  going to go ahead and choose this very quickly. It   comes back. This is a pretty simple uh example,  but you're going to understand how to use this   to help you with formulas. And let's see what it  did here. So, it said looking at A1D6 on student   scores. Here's one formula column to review in  insert in column E. So, it's going to add up each   student's exam, project, and quiz score to show  their overall performance. So, it's just creating   a very simple average. I can go show explanation.   It is telling me what it's doing here. So,   it has this new uh column that is suggesting,  do you want me to add this to? So, as soon as I   hover over or hover off, notice how it shows where  it's going to go. And if you like what it's doing,   I can go ahead and hit insert. Now, I want to  point out with each of these, this just didn't   copy these numbers. These are formulas. So, if  I I'm just going to go and double click in here,   and you can see that this is a formula. If I look  into the formula bar up top, I can see the exact   formula. If I click in a different one, I can see  uh the formula for that cell. So that's great.    Let's see if we can go a step further now. So if  I go ahead and ask it this, you know, create a   weighted final grade using 40% exam, 30% project,  and 30% quiz. So before you're kind of thinking,   how do you write all of this down in a formula?   But this time we're just asking it to do it for   us. And now let's see what it gave back to us.   Now it's determining based on the factors. I can   see given point four fraction of the total exam  for there. 3. We have our formula the explanation.    It's going through it step by step. So it even  tells me so it's using the round function to round   the total to one decimal place. So if you don't  know much about creating formulas or functions,   copilot is going to do this for you. So I'm going  to go hover over. You can see where it's going to   go and insert this in. And I have it. So I could  go ahead a little bit more if I wanted to say,   you know, explain this formula in simple terms. I  can go ahead and send this and it's going to come   back and even help me understand it more.   And look at the explanation. It gave it   kind of a bulleted form of each step of what's  happening. So you can really it really starts to   teach you about what's happening. So you can dive  in as well as getting co-pilot to do it for you,   but you can make any corrections that's needed.   But let's not stop there. Let's dig a little   deeper with some other expansion task. Let's  say create a performance rating of high, medium,   or low based on the final grade. And let's see  what it's going to create for us. Now, let's take   a look at what it created for us. And I can see  that they have some nested ifs in here. And they   have medium, low that's going to be put in here.   I'm going to hover over and see what we get. And   this would kind of make sense. Let's see what the  high. So 93. 1. And I can go through and tell it to   adjust. So I didn't even tell it to be 90 being  high. If I wanted to be 95 or 85, I can make all   those adjustments, whether I tell Copilot to make  those adjustments or I go and go ahead and insert   them. And then I can make the adjustments myself  inside the formula. And maybe you wanted to change   your waitings and we could go ahead. What if we  change the waiting to the uh 50 30 20 recalculate   and this time it didn't use the round one. So  like I said, it can give you the same answers but   in different ways each time you ask it and I can  go ahead and go to the bottom. It shows me what   it gets there. I can see that it is going to be  different marks just based on the weightings and   I can easily input that in. I could go and delete  this column if I wanted to and have the new final   grades. Now, I have something exciting to show you  with a new update with using Copilot and formulas.    And I'm going to go and delete this information  here because take a look at this. All I'm going   to do is press the equal sign in this cell. And  look what happens. I can see generating formula.    quickly. It took a look at the data and it kind of  knew that we have must have some exam project quiz   and he needed an average here because that's what  it created. It took these three cells B2, C2 and   D2 assigned waiting of 4. 4. 2 and I could go ahead  and adjust this if I wanted this five. I could   just type these in 5. 3. 2. But if I hit enter and  then I have the formula done and I can just double   click here to fill it down. So, I didn't even  type anything at all. I didn't even ask Copilot,   but it just looked at the data and kind of knew  what to do there. Now, a different way we could   do this as well, I'm going to delete all this  information here. If we start typing again,   and let's say if I start typing average this time,  and what you're going to notice is it knows I'm   looking for the average formula. So, it looked up,  you can see the symbol that it's using the AI. So   since I suggested average and I hit enter, it just  did it that way. So if I start equal and if I know   a certain formula that I would want, then I can  go ahead and just start typing it and then it kind   of guesses what I want to fill it in. So again,  anything I go down here, if I put equals here,   uh it will look, oh, do you want to sum it?   Or maybe if I want an average, I start typing   average. But just by that equal sign and putting  enter, I can quickly create formulas and copy them   to where I need to. It's fantastic how Copilot  and Excel can help people with understanding and

creating formulas. But another thing that it can  do is conditional formatting. So rather than going   up top and applying the rules and to highlight  things with color so they can easily stand out,   we can use co-pilot. So, in this case right  here, if I highlight everything and I go back   to Copilot, I'm going to suggest conditional  formatting. So, I want to see what it will   suggest to me. I could tell it what to do. But  if I go through this and you can see, okay,   here's some conditional formatting suggestions.   Highlight cells in exam when quiz cells are less   than 89. Highlight bottom one in exam. Highlight  cells greater than 90 in column exam. So,   let's say we want to highlight the high mark.   So greater than 90 in exam. So if I select this,   you'll see it will go through and set it up. All I  have to do is apply it. I'll go down a little bit   and you can see the color that they chose. So the  fill color is going to be yellow font, black. I   can go ahead and tell it to be a different color.   So if I said green and white font, it will go   ahead and do this. I'm just going to go ahead and  leave it like this. And I'm going to click apply.    And notice over here the conditional formatting  the rule was applied. If it's above 90 that color   was added to it. So let's try one more thing.   This time let's say apply a threecolor scale   based on percentiles instead of values. We have  the color scale here. Let's go ahead and apply   this. And right away this is being added to the  entire sheet here based on the color scale that I   told it to create. Data cleanup and categorization  is a very important thing to know how to do in in

Excel and there's lots of different ways you can  do this and but you have to go through the process   of learning all those but now co-pilot can do a  lot of this for you. So we have our HR roles here   this sheet and we're going to do some data cleanup  on this. You can see that we have some different   things like sales here is lowercase and we have  sales uppercase here we have HR. HR is a little   different. We have a repeat. So just some things  that I put into it to show you how Copilot could   uh do some cleanup. Let's go ahead and open  up Copilot. And let's start with this prompt.    I'm going to say standardize all job titles  and categorize them into job families. Okay,   so it comes back and I like how it always explains  what its thinking is. So we have our standardizing   job titles. These are the ones that it pulled out  of there and we have the standardized titles and   then it goes ahead and it has the job families  that are put into each of these from human   resources to sales to customer service. And then  as we look, we have this final table of the names.    And if I go to the bottom, we should have the  scroll over and it gives you an idea of how it   cleanly put things together. Now, it kind of told  me what it did here. Would you like this table to   be exported back to Excel or do you need further  breakdowns? The one thing I notice from up here is   it never removed the duplicates. And cleaning the  duplicates would be something I'd really wanted to   do. So, why don't I tell it to do that? I'm just  going to say check this sheet for any duplicates   or inconsistencies. And right away, you can see  that it found the duplicate name twice. It found   the sales rep, sales rep for duplicates, HR, HR or  duplicates with inconsistent formatting. All this   if, again, if you had a large sheet, the amount of  time this can save you just by pointing out all of   these things would be amazing to use. And I can  see right away it found the duplicate of the name   here. As I go through uh we have our sales rep,  the lowerase and the uppercase here. HR to HR   customer service. It found all these inconsistency  or the duplicates of here. If I had a large data   sheet here, you can see how much time this would  save me trying to go through and find all of these   things. I didn't need to create any formulas  to go and search for different things. It   has recommendations as I go through. We have our  examples or table here. And towards the bottom,   would you like help cleaning up the sheet or  exporting a correct version? And I'm going to say   yes, please clean up this sheet. So now I can see  the actions taken. It remove the duplicate name,   standardize the job titles, categorize each role  into job families like I mentioned before, and we   have this looking good here. You know, the other  thing and I have the sheet that I can download.    So, just like I showed you before, I can download  this. Then I would also have to upload it again,   but it's going to be a separate sheet. There's  one more thing that I'm just wondering and if   I'm can say make the titles more professional now.   It shows me the approach it's talking about from   sales representation to sales account executive,  HR assistant, human resource coordinator. Oh, I   like that. It is sounding much more professional.   So, not only is it helping me find the duplicates   or inconsistency, it's just helping me with  things sounding better. And as I go through   at the very end now, so now I can see, would  you like this updated table exported to Excel?    So I could say yes to that and it would create  me that new Excel file that I could download,   then upload again and continue on all with just  having that conversation with chat in Copilot.    I want to show you a different way to do a little  bit of cleaning. And I'll come back to this in the   capstone project, but if you go up top, if you're  under home, go over to data and notice that we   have clean data. And a lot of times this will pop  up when you freshly open a sheet and you'll see   this yellow bar that will have suggestions about  cleaning data. If I click on this, notice that it   highlights what do I want to change. So I could  change sales rep to this or maybe I want this to   be a capital. I could go ahead and change it like  this. So now I'm going to apply both to change   here. And if I hit apply, it quickly changes this.   And I'm not uh changing I don't have to create   a brand new sheet or download anything to do it  this way. But this is more for punctuation and and   different capitals like this. But it's definitely  an overlooked thing uh using Copilot to clean your   data this way. Earlier on in this module, I showed  you how to use Copilot for visualization. with a

simple prompt, we were able to make multiple  charts that we could use with our sheets. So,   what I want to show you now is how to create pivot  tables or pivot charts with co-pilot, even if   you've never used a pivot table before. So, we're  going to use this data right here in finance. I'm   going to go and highlight this. Now, if we go up  to co-pilot, we can summarize using pivot table   or charts here. Or if we go over to copilot here,  you need to be app skills. You'll notice that you   can summarize using pivot table or charts here.   So let's go ahead and click on this to see without   really much of a prompt giving the details what we  want specifically what it will create for us. So   very quickly it went and created this pivot table.   And I knew it wasn't going to be very large cuz we   don't have much information over here. But what  we can do with this now is add it to a new sheet.    Just like if you were starting a pivot table  from scratch, this would be one of the options.    The difference now is that Copilot set things up  for you. So, I'm going to go ahead add it to a new   sheet. And if you look at the very end, I have  this brand new sheet called 19. I'll just zoom   up so we can see this a little bit better. I'm  going to uh close out of this. And I'm just going   to click inside the pivot table and it's going  to open the fields. I can still go through and   customize this. So, if I click on date, you can  see how that quickly gets added or category. And   I can just click these on. And I can move uh these  different uh fields from different places, columns   and rows. And but I didn't have to do that to set  this up. If I go over to here, I can go through   and if I wanted to do some filtering. You can see  how quickly you can make all these changes. Now,   I'm going to close out of this and just go back  to Copilot and I'm going to keep this sheet here,   but I'm going to go back to our finance one. And  I want to change it a little bit this time. So,   I can be specific with this, but remember when  we're asking for the pivot table, we're going to   be under the app skills. And this time, I'll  ask create a pivot table showing the average   transaction size by category. So, you can see that  it created the pivot table based on exactly what   I would like. Let's go a step further this time.   Let's say create a chart for this. And it selected   a pie chart here. I'm going to go ahead and add  this to a new sheet. I want to point out what it   did here. So before it created images for me in  the different ones that I was putting in before.    Uh but this is interactive. So this is connected  to this pivot table over here. So if I was going   to go and change, let's say I didn't want supplies  and I hit okay. Look how this is dynamic. So I can   create these pivot tables and pivot charts right  inside Copilot. even if I've never ever created   one before and didn't know how to start one.   Another powerful way to use Copilot in Microsoft   Excel is for trend spotting and what if analysis.   I'm on the delivery performance sheet here and

we're going to ask Copilot some trend questions.   And so if I go ahead and we're just going to use   chat and this is the question that I want to ask  it. Find trends in orders and on-time performance.    And I also wanted to identify one anomaly. I'm  going to send that off. Okay. So here are the key   trends. So we see order trends. Steady growth from  January to May 320 410. Um on-time performance   trend generally improves over the period with  volatility kind of breaks that down. Relationships   between order and time percentage moderately  positive. Oh, they give me the correlation there.    Uh months and Okay, I will keep going. and they  got a little chart that they put together for me.    Uh, one anomaly to call out, February, despite a  healthy rise in orders plus 20 on time performance   dipped by to 87%. That would be a very important  thing to know to be able to find that. And again,   with just this one question, I was able to use  co-pilot to search through the data. It's even   suggesting what this applies. So scaling through  spring didn't uh degrade service quality may   achieve both the highest order volume and the best  on time. So not only does it stop at the trends,   it's suggesting what it implies. What if I want  to go a little bit further? You can see there's   suggestions down here provide more details on the  fee February anomaly. But I want to go if on-time   performance increased by 5%. So this is my what  if. How many fewer late orders would there be each   month? I'm going to send this off. So here we go.   If on-time performance increased by 5%, how many   fewer late orders? So this is the analysis that  it did here. So here's the summary. Each month   improving performance by 5% would reduce late  orders by about 16 to 20. So again, the detail   that it gives with just a simple prompt looking  at the data that you provided. So, I could even go   again further and say, you know, forecast the next  three months based on the current trends. And here   we go. Below is the simple three-month forecast  based on the linear trends in the delivery   performance. And we have this here that we could  copy paste over to our sheet if we wanted to,   but it's just more this conversation with Copilot  to understand what's happening with the data. I

want to show you now a way that a lot of people  don't think about using Copilot in Microsoft Excel   and that's for summaries for presentations. And  we're just going to use this little bit of data   and create a summary that we could use. I'm going  to go over to chat here and I'm going to ask it   this. So based on this donation sheet and the  reason I'm saying this donation sheets because   that's the name of this sheet is if I don't  sometimes it will go through the entire workbook   and it will apply the board report based on all  those. So sometimes you do have to be specific   what you're asking for. So I'm asking it to create  a border report summary with three insights,   one risk, and one recommended visual. So I'm  being very uh direct with what I want here. Okay,   let's see what it did. So it analyzed the  donations data and we have our key insights   that it wrote up a little bit of a summary about  uh consistent donor engagement a little bit there,   seasonal variable in fundraising. So it gave  the three points that I wanted. It's giving   a risk here and then a recommended visual. It's  just saying a cluster column chart would be what   it would recommend. Let's try this. So if all of a  sudden I want to do this for a leadership meeting,   rewrite meeting. I'll  send that. And now if I take a look at this,   you can see how this becomes a little bit  different than for a report but more about a   uh for a leadership meeting. We can even ask  it to provide two recommended next actions. And   here it is. So we have develop offseason engage  strategies, diversify fundraising channels and   campaigns. So my whole point of this part of this  module is to making sure that you realize how you   can chat in copilot with your data to get any  type of information and help you need without   even leaving Excel. In the first two modules, we  focused on understanding how Copilot works inside

Excel and how to use it for everyday data task.   Generating insights, creating visualizations,   building formulas, cleaning data, and summarizing  information. In this module, we're going to take   things a step further. These lessons are about  advanced specialization scenarios where Copilot   Premium can even save you more time and  open up new possibilities inside Excel.    I also have a new workbook for you to download and  save to one drive. Before we jump into the more

advanced scenarios in module 3, I just want to  show you a really powerful co-pilot feature that's   easy to miss. Copilot can extract structured  information directly from files like a PDF, even   if that PDF was never designed for spreadsheets.   So, this means you can analyze financial reports,   operation updates, or summaries from your  organization without retyping anything. Let   me show you how to do this. So I have already  uploaded a PDF to one drive. But if you need   to upload it, you can go ahead add content. You  can see where you can add work content or upload   images and files and you can go and grab it. So  I have it right here on my computer. So I could   go ahead and add it that way. I also want to point  a shortcut. If I just hit forward slash, it will   uh go right away into um the your files that are  stored in the cloud. So I can see it right here.    So, I'm going to go ahead and just grab this PDF.   And I do want to show you real quickly what this   PDF looks like. And you can see just an intro  paragraph and some financial table information.    So, if I go ahead and send this away, even without  asking it to do anything, you've seen how Copilot   works. It's going to provide me a summary, and  it's even going to pull out the table for me.    All right, let's take a look. So you can see that  it has the monthly financial overview. It's citing   it from that PDF that I've uploaded. Here is the  table uh that's in it. And we can go ahead take   a look key insights it's given me. And at the very  bottom I can go ahead and add this to a new sheet.    So I do have my new uh workbook open. And if I  just hit the add to new sheet, it will add it   to the very end. You can see it says sheet six.   I could go ahead and call this I'm just going to   retype this as PDF and it inputed this as a table.   You can see this is already as a table and it gave   me the key insights. So you could keep asking it  to do more things too. So if I go ahead create a   line chart showing revenue and net profit uh from  the extracted data format it so revenue net profit   uses different line styles and clear labels and I  can see it reason for 11 seconds. And here is the   chart. And just like before, we can take this, we  can paste it uh in to our spreadsheets. So all the   things that I've showed you before, whether it's  calculating trends, executive summaries, highlight   patterns, I can ask Copilot starting from a PDF.   So think about all that time that you don't have   to recopy things over to Excel. Just upload it to  Co Copilot and let it do the work for you. Now,   I hope you have your new workbook open as we  continue on with this module. So in this lesson,   we're going to combine two related data sets,  production runs and defect log. So you can see   I have it right here, the first two in this  workbook. Uh we'll let Copilot help us pull   them together and calculate the defect rates by  line and product. So the first thing I'm actually   going to do also is in this module I'm going to be  turning my information into tables. And remember   how I showed you how to do this. We can go CtrlT  or we can go to table and go ahead and it should   automatically pick up the range if you're selected  in the range. Hit okay. And just like that,   we have our table. And I'm also going to turn  this one. This time I'm just going to go CtrlT   for the shortcut and hit okay. So quickly I  have the two tables. I'm not going to bother   changing any of the styles on it. I'm just going  to leave them as is. Now I'm just going   to go up top and I'm going to go and choose  app skills. I could go through chat and have   kind of the same conversation, but I'm going to  wanted to create a new sheet. So, remember how I   explained the differences at the beginning with  from the conversation to the actual doing. So,   that's why I'm using that one. So, I'm going  to start with this. So, I'm going to ask this   question. Describe the production run sheet and  tell me what each column represents. Something   very simple. It's just going to look at this sheet  right here and just look at the headings and it   should come back with a quick update. All right.   So here is the description. It just looks at all   the column headers. The sheet provides a record  production activities including when, where, when,   produce. So pretty simple. Let's go with this now.   Describe the defect log sheet and how it relates   to the production run. So we have two different  sheets. So we have the defect log and production   run. These two different ones. So I'm going to  go and send this off and again in a few seconds.    So we have the explaining of the defect log sheet  here with the different heading kind of set up the   same relationship defect log entries can be linked  to production runs and production runs using the   shared column state line produ uh product. So yes  I would agree with all that. Okay. So now I wanted   to create a new sheet for me. So this is what I  want. Create a new sheet that combines product   runs and defect log by matching date, line, and  product include units produced and defect count.    So just by a prompt, I'm just telling it what I'd  want based on these two different sheets. Okay,   it tells me a new sheet can be created by merging  production uh production runs and defect log. We   have it right down here. I can expand it. We can  take a look at it. Make sure it's what I want. and   at the bottom, insert to new sheet. So, I'm going  to go ahead and insert this. And I'm just going to   go ahead and call this uh combine. Just like that.   And on this one. So, what do I want next? So, we   have our new sheet. I'm going to go and say this.   Add a defect rate column. So, I'm on the new sheet   that calculates defect count divided by units  produced and format formatted as a percentage. So,   I'm telling it to do a few more things.   Now, even if I don't know much about Excel,   I can kind of explain what I want. Okay. So, now  from that prompt, I can see the formula just like   what we showed before. I can see the explanation  and what it looks like. If I hover over,   I can see there's the defect right here. I'm going  to insert that column. Now, let's go ahead and   ask another question here. Which line and product  combines uh combinations have the highest defect   rates? summarize in three bullet points. And here  we go. We have the output right here. And here's   the bullet points that I asked for. So I could go  ahead and copy paste them somewhere else. And one   last question. Let's go ahead and ask it to create  better spell that right. Create a column chart   showing defect rate by line. And there we have our  chart. So through this whole conversation I was   able to get Excel using copilot and I was using  the app skills so I could uh do things insert   things in. I just had that conversation with it  and it was able to very fluidly create everything   that I wanted in one go. I'm going to go ahead  and open up my app skills again because I'm going

to have some new sheets created and some columns  again on healthcare visits. So make sure you're on   the right tab. I have this ready to go. And the  first thing I want is from the notes column in   healthcare visits, list the most common symptoms  and how many visits mention each one. So again,   if you're digging into information, you have these  long list of data. Take advantage of co-pilot. And   here we go with our themes. I can see that they  have counts. They have headache and migraines,   pain and swelling. And they seem to put this  together quite easily. everything is going   to be uh cited back to it. All right, let's go a  little deeper now. I'm going to ask it to create   a new table listing symptom and count based on  the notes in the healthcare visits. All right,   let's check out what it gave us. Here we have our  output. I can expand that, but I'm going to just   keep going down here and looking at what it says.   The new table has been created. So, I'm going to   go and just insert to a new sheet. And I'm going  to go call this one symptom and count here. So   that should work. And we have this as a table cuz  I requested a table and we have it on a new sheet.    Now I'm just going to go back to my healthc care  visits sheet right here. And I'm going to go ahead   and ask this add a new column to healthcare visits  called category and assign each visit to a highle   category like respiratory, musculature, cardio,  gastro, and others based on the notes. All right,   I'm just going to move over here so we can check  out this new column. As I hover over, we can see   the themes right there. So, I can insert that in.   And now I have I can stretch this out a little bit   to give it some more room. Everything themes based  on each of these notes. Now, we'll look at how to

get deeper insights by iterating on our questions,  not just accepting the first answer Copilot gives   us. And for this, I'm on the sports stat sheet,  and I'm going to go ahead and open up our app   skills again. And we're going to start with  this. So, I wanted to look at the sports stats   and tell me who the top performers are based on  overall contribution. Okay. So, I can see that to   uh identify top performers based on the overall  contribution, a composite metric that combines   key statistics such as point, assist, and rebounds  was calculated for each player. I can see how they   created that now. and here are the top performance  and it pulls out that information. But I want to   refine the metric here. So what I'm going to say  is define an efficiency metric that takes into   account points, assist, and rebounds per minutes  played. Add a column called efficiency that   calculates this. All right. So if I take a look  at this now, I can see that it's using the points,   assists, rebounds divided by the minutes. We  have an efficiency column. If I hover over,   there it is. I'm going to go ahead and insert  this in. And notice this is one of the reasons   why we like tables is because it's able to just  take these entire columns into it. I'm going   to go this time rank the players by efficiency  metric and highlight the top five. And this time   the players will be ranked based on efficiency.   We have Gavin Price, Quinn James, Peter Gray.    uh and that we have that list based on how we  changed it, which is a different top five than   the last ones. Of course, we always like to create  charts with our information. So, let's create a   chart showing efficiency of the top 10 players.   Now, here we have our chart that we can quickly   add, but I want to even go further with with  questioning. Compare average efficiency by team   and summarize which team appears strongest overall  based on this metric. So, we keep digging with   this information. Now we have the teams listed and  the efficiency ratings. Now focus on the guards.    Who are the top three guards across all teams and  here we go. teams   ranked on their efficiency are hey this could  help maybe with some fantasy football leagues   or different things like that to analyze stats.   Now this is the pattern I want you to notice.    You ask a broad question, refine the metric, rank,  visualize, and compare subgroups. Copilot lets you   iterate quickly. In this final lesson for module  3, we're going to look at something more advanced,

but extremely powerful. Using Copilot, premium,  and Python, and Excel, even if you've never   written Python code before, Copilot can create the  code for you, explain what it's doing, and build   custom forecast and charts directly inside Excel.   Make sure you've turned your data into a table   like I showed you before. I'm going to go up to  copilot and go to app skills. And the first thing   I want to point out is that one of your options  is give a unique insight using Python. So I'm   just going to go ahead and choose this. So it went  through and created this table and it was using   Python. I'll show you what I mean. Here's the uh  the code through here. Uh it created this. I can   go ahead. I'll expand it. I can insert this into a  new sheet. Now, if you go through and do the same   thing, it might create a chart for you each time.   It will create maybe something a little different   like what I've talked about with different copilot  prompts. It's not always the exact same one,   but I'm not going to insert this one. I want to do  something else. I want to go to this one here. Get   deeper results using advanced analyst mode. So,  if I would have asked this at first, I could have   jumped to this, but I'm going to go and do this  now. And what you're going to notice I'm going to   get the option to create a new sheet where it's  going to move everything over to uh with Python.    So right now you can see start. I'm just going  to click this. It's going to create a new sheet   automatically write and insert Python formulas and  answer your prompt and multiple messages. So I'm   just going to hit start. And remember my prompt is  just this the you know get results using advanced   analysis mode. So, it's going to go through  and look at a bunch of different things. Uh,   and then we can look what it creates for us.   Okay. So, after only a few moments, I have all   of this analysis sheet here. So, you can see from  the charts that it placed in uh the growth rate,   the regions, it did a deep dive into it. I  didn't really specify it directly what to do.    And you can see at this point as I go through  uh the deeper results, we can see the analysis,   the code for each of these. And I can continue  on with this too. So if I was going to go   uh to the bottom of this and I'm going to put this  in. Use Python in Excel to forecast the next six   months of revenue for each region. Let's see what  it creates for us. All right, I can see it added   it right to the bottom here. I can add results to  a new sheet as well. Well, I also get that option   where I can if I wanted it on new sheet, I just  asked it to put it directly in this one. I can see   I have my next 6 months going through here uh in  the forecast for it. So using Python becomes a lot   easier even if you have no idea or have never  used it before. So take a look at this using   C-Pilot. And that's it for this Copilot and Excel  tutorial. I hope you found it helpful and gives   you the confidence to start using Copilot in your  own spreadsheets. If you've enjoyed the video,   feel free to give it a like, subscribe, and  check out some of my other Excel videos and   AI tutorials. Thanks for watching, and I'll see  you next time with more tech tips and tutorials.