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

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

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

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

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

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

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

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

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

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,

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

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

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

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,

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

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

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. Now I want to 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

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

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 it actually explains why it works. So groups by month and product. So both time and

stop there. It 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

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

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

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

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

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

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.

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

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 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

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

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.

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 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

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

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,

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

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

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

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

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

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

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

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 to go and search for different things. It

things. I didn't need to create any formulas to 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

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

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

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 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.

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

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

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,

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

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 this for a leadership 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

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.

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

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 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

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

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.

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

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

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

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

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,

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.

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. The top three guards across all 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,

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

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,

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

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.

AI tutorials. Thanks for watching, and I'll see you next time with more tech tips and tutorials.