WEBVTT 00:00:00.000 --> 00:00:02.560 Today we're going to learn how to master Copilot 00:00:02.560 --> 00:00:06.000 inside Microsoft Excel. We'll start off by preparing 00:00:06.000 --> 00:00:09.339 our Excel document before using Copilot and then 00:00:09.339 --> 00:00:11.820 move on to where to find Excel prompts and how 00:00:11.820 --> 00:00:15.019 to interact with Copilot in Excel. Both simple 00:00:15.019 --> 00:00:18.079 interactions and more complex ones using Python. 00:00:18.620 --> 00:00:20.940 Don't worry, you don't need to be an Excel formula 00:00:20.940 --> 00:00:24.379 whiz or a Python coder. We'll even have Copilot 00:00:24.379 --> 00:00:27.480 create charts for us and color code our data 00:00:27.480 --> 00:00:30.579 using conditional formatting. Welcome to Collaboration 00:00:30.579 --> 00:00:33.619 Simplified. My name is Shervin Shafi and I work 00:00:33.619 --> 00:00:36.200 at Microsoft as a Copilot Principal Technical 00:00:36.200 --> 00:00:42.979 Specialist. Before we continue, please give this 00:00:42.979 --> 00:00:45.600 video a like as it really does help the channel. 00:00:46.079 --> 00:00:48.100 thank you very much for your support all right 00:00:48.100 --> 00:00:51.700 we're gonna fire off Microsoft Excel and I want 00:00:51.700 --> 00:00:54.100 to first explain this Excel spreadsheet that 00:00:54.100 --> 00:00:56.859 we're looking at together it's for human resources 00:00:56.859 --> 00:00:59.420 and it has a variety of different data that I've 00:00:59.420 --> 00:01:02.719 put together and it's all fictitious data so 00:01:02.719 --> 00:01:05.640 don't look into it too much but basically it's 00:01:05.640 --> 00:01:08.359 for human resources it has people's names and 00:01:08.359 --> 00:01:11.409 their job titles when they started when we expect 00:01:11.409 --> 00:01:13.250 them to retire how many years they've been with 00:01:13.250 --> 00:01:15.950 the company their salary their performance level 00:01:15.950 --> 00:01:19.569 out of 10 their pay range if they're at risk 00:01:19.569 --> 00:01:21.590 of leaving the company if they're a manager what 00:01:21.590 --> 00:01:23.609 their team attrition rate is like how many people 00:01:23.609 --> 00:01:26.650 leave the company what percentage of their training 00:01:26.650 --> 00:01:29.450 they've completed their pto balance and their 00:01:29.450 --> 00:01:32.670 work life balance score out of 10. so it has 00:01:32.670 --> 00:01:36.140 some interesting data and not an hr person but 00:01:36.140 --> 00:01:38.659 i guess there's information like this out there 00:01:38.659 --> 00:01:42.359 for people if you're an hr let us know if there's 00:01:42.359 --> 00:01:45.739 information like this you have on us now before 00:01:45.739 --> 00:01:48.000 i get going i wanted to show you one thing real 00:01:48.000 --> 00:01:51.120 quick this is a pro tip which is notice that 00:01:51.120 --> 00:01:54.659 all of this is actually in table form in excel 00:01:54.659 --> 00:01:57.620 now if it wasn't in table form it would look 00:01:57.620 --> 00:02:00.500 like something like this and what i have noticed 00:02:00.500 --> 00:02:05.120 is that if i don't have data in table form and 00:02:05.120 --> 00:02:08.400 i interact with copilot sometimes the responses 00:02:08.400 --> 00:02:11.759 are not that good or i might even get errors 00:02:11.759 --> 00:02:15.060 all right i don't know why that is but my suggestion 00:02:15.060 --> 00:02:18.800 is when you have an excel document simply go 00:02:18.800 --> 00:02:21.479 from the beginning all the way to the end and 00:02:21.479 --> 00:02:25.000 just highlight it and then move over to insert 00:02:25.000 --> 00:02:30.400 table and just select ok and it just turns this 00:02:30.400 --> 00:02:34.310 into a table now you can start interacting with 00:02:34.310 --> 00:02:37.409 it all right so just do that before you start 00:02:37.409 --> 00:02:41.650 doing anything with copilot so looking at our 00:02:41.650 --> 00:02:44.949 Excel document that's in table form you'll see 00:02:44.949 --> 00:02:47.229 that you can interact with copilot in a variety 00:02:47.229 --> 00:02:49.770 of different ways first of all when I select 00:02:49.770 --> 00:02:53.050 any cell you'll see this copilot icon that when 00:02:53.050 --> 00:02:55.990 I click on it it gives me options that I can 00:02:55.990 --> 00:02:58.990 select that's one way of interacting with copilot 00:02:59.740 --> 00:03:02.379 the other way is that if we were in our home 00:03:02.379 --> 00:03:06.039 icon up top you'll see copilot on the top right 00:03:06.039 --> 00:03:08.759 hand side show up inside excel so you can select 00:03:08.759 --> 00:03:12.259 copilot and then you'll start being able to interact 00:03:12.259 --> 00:03:17.000 with copilot right from here now right away you'll 00:03:17.000 --> 00:03:20.500 see something like create a table create a total 00:03:20.500 --> 00:03:24.460 rows all kinds of auto prompts that give you 00:03:24.460 --> 00:03:27.639 ideas on how to interact with copilot and you 00:03:27.639 --> 00:03:29.860 can select more examples more examples and just 00:03:29.860 --> 00:03:32.300 keep getting different examples this is what 00:03:32.300 --> 00:03:35.159 i love about copilot in excel because it guides 00:03:35.159 --> 00:03:37.639 you through all the things that you can do on 00:03:37.639 --> 00:03:40.360 the bottom right hand side we have these icons 00:03:40.360 --> 00:03:43.199 like adding an image viewing prompts managing 00:03:43.199 --> 00:03:47.300 content and also using the microphone if i highlight 00:03:47.300 --> 00:03:49.699 this over here this is these are what the icons 00:03:49.699 --> 00:03:52.120 look like and over here this is where you start 00:03:52.120 --> 00:03:54.599 typing in your custom prompts if you wanted to 00:03:55.229 --> 00:03:57.909 And then Copilot always gives you some suggestions 00:03:57.909 --> 00:04:00.430 of things you can run, like showing data insights 00:04:00.430 --> 00:04:04.129 and so on. So one of the things that I like to 00:04:04.129 --> 00:04:08.030 do is I like to select view prompts. And Microsoft 00:04:08.030 --> 00:04:11.610 does a great job of showing you different prompts 00:04:11.610 --> 00:04:15.129 that you can execute within Excel. So there's 00:04:15.129 --> 00:04:17.490 so many things like highlighting data, getting 00:04:17.490 --> 00:04:21.029 column information, visualizing relative values, 00:04:21.129 --> 00:04:25.100 all this really good stuff. however what i will 00:04:25.100 --> 00:04:27.959 say is that on the bottom right hand side there's 00:04:27.959 --> 00:04:30.379 a section called see all prompts when you select 00:04:30.379 --> 00:04:33.439 that this actually actually takes you to the 00:04:33.439 --> 00:04:38.060 copilot prompt gallery and over here not only 00:04:38.060 --> 00:04:42.959 can you find prompts that are for excel but also 00:04:42.959 --> 00:04:47.819 for word for powerpoint onenote outlook teams 00:04:47.819 --> 00:04:51.579 and so on and so when you select this application 00:04:51.579 --> 00:04:55.060 drop down over here You can also filter based 00:04:55.060 --> 00:04:58.579 on task or job title. You select the application 00:04:58.579 --> 00:05:01.120 and then you select Excel and you see all these 00:05:01.120 --> 00:05:04.279 different Excel prompts that you can use. And 00:05:04.279 --> 00:05:05.939 when you get to the bottom, you can click show 00:05:05.939 --> 00:05:08.879 more, get to the bottom, click show more. There's 00:05:08.879 --> 00:05:11.699 so many different kinds. Now, what you'll notice 00:05:11.699 --> 00:05:15.060 is that there's this ribbon icon that you can 00:05:15.060 --> 00:05:17.300 select to save your prompt and some of them I've 00:05:17.300 --> 00:05:21.370 already saved. And so what this does is it takes 00:05:21.370 --> 00:05:23.990 these prompts that you save kind of like bookmarking 00:05:23.990 --> 00:05:26.850 inside a web browser and it puts it into your 00:05:26.850 --> 00:05:29.810 prompts so these are some of the ones that i've 00:05:29.810 --> 00:05:34.490 saved inside my excel and we'll be using some 00:05:34.490 --> 00:05:37.490 of these for the copilot demo let's go back to 00:05:37.490 --> 00:05:40.529 the excel spreadsheet and i'm going to select 00:05:40.529 --> 00:05:43.769 your prompts which are basically my prompts these 00:05:43.769 --> 00:05:45.449 are the ones that i've bookmarked for myself 00:05:45.449 --> 00:05:47.949 and one of the things that i'll do very simply 00:05:47.949 --> 00:05:52.589 is highlight the data so what i did was i selected 00:05:52.589 --> 00:05:55.949 highlight d and then the rest of it i have to 00:05:55.949 --> 00:05:59.370 fill out on the bottom here myself so i'm going 00:05:59.370 --> 00:06:05.110 to say highlight the top five salaries just something 00:06:05.110 --> 00:06:07.709 simple like that copilot is going to understand 00:06:07.709 --> 00:06:10.129 the fact that i'm referring to the salary column 00:06:10.129 --> 00:06:13.670 it's going to look at the top five analyze it 00:06:13.670 --> 00:06:16.350 and then highlight it for us it's going to say 00:06:16.350 --> 00:06:19.569 that i'm going to be using yellow and black for 00:06:19.569 --> 00:06:22.589 the background and the font if this looks good 00:06:22.589 --> 00:06:25.990 then i simply select apply and it's going to 00:06:25.990 --> 00:06:28.790 go ahead and execute that for me so here it is 00:06:28.790 --> 00:06:36.209 somebody makes 132 138 132 136 and 226 wow that's 00:06:36.209 --> 00:06:38.389 that's a good salary and on the bottom right 00:06:38.389 --> 00:06:40.410 it gives us some additional information like 00:06:40.970 --> 00:06:43.470 pull the first column this is an option it's 00:06:43.470 --> 00:06:45.410 saying that you know you want to do this next 00:06:45.410 --> 00:06:47.629 or do you want to show items with the pay range 00:06:47.629 --> 00:06:50.750 of 70 to 80 so it's giving you ideas of things 00:06:50.750 --> 00:06:53.170 to do next now the next thing i want to show 00:06:53.170 --> 00:06:55.290 you is i'm just going to close out this copilot 00:06:55.290 --> 00:06:58.870 window on the right and let's go ahead and hover 00:06:58.870 --> 00:07:01.730 over salaries over here i'm going to click on 00:07:01.730 --> 00:07:03.990 it and click on copilot here's an interesting 00:07:03.990 --> 00:07:06.329 one suggest conditional formatting i'm going 00:07:06.329 --> 00:07:09.610 to go ahead and click on that it opens up copilot 00:07:09.610 --> 00:07:12.649 and it's going to give us some options here so 00:07:12.649 --> 00:07:15.810 here they are highlight cells and salary when 00:07:15.810 --> 00:07:18.449 the name starts with patricia highlight rows 00:07:18.449 --> 00:07:21.209 when cells are greater than a hundred thousand 00:07:21.209 --> 00:07:24.769 let's go ahead and select any salaries that are 00:07:24.769 --> 00:07:27.629 higher than 100 i don't know if you're noticing 00:07:27.629 --> 00:07:30.350 here but it's automatically creating these formulas 00:07:30.350 --> 00:07:33.009 so you don't have to be a formula whiz anymore 00:07:33.009 --> 00:07:37.009 and if it looks good just select apply and basically 00:07:37.009 --> 00:07:40.290 anything that's over 100k it's highlighted for 00:07:40.290 --> 00:07:44.730 us now you can get a little fancy right so on 00:07:44.730 --> 00:07:47.089 the bottom let's create something custom and 00:07:47.089 --> 00:07:53.350 say show me all the people that make over a hundred 00:07:53.350 --> 00:07:59.930 thousand and have been at the company less than 00:07:59.930 --> 00:08:04.370 10 years so generally especially if you have 00:08:04.370 --> 00:08:08.350 like a huge document with thousands of rows this 00:08:08.350 --> 00:08:10.110 would take you a long time right because you'd 00:08:10.110 --> 00:08:12.050 have to sort you'd have to come up with a formula 00:08:12.050 --> 00:08:15.350 but we can just ask it something like this in 00:08:15.350 --> 00:08:19.230 natural language it goes ahead and analyzes it 00:08:19.230 --> 00:08:22.829 and gives us the response right here and it gives 00:08:22.829 --> 00:08:26.370 us the option to do deeper analysis using python 00:08:26.370 --> 00:08:30.689 or additional prompts if this looks good What 00:08:30.689 --> 00:08:34.210 I like to do is simply select add to new sheet 00:08:34.210 --> 00:08:37.710 because I want to have this information in a 00:08:37.710 --> 00:08:39.889 different place where I can do additional analysis. 00:08:40.049 --> 00:08:42.629 So here it is. These are the people that make 00:08:42.629 --> 00:08:46.990 100 or more and have been at the company less 00:08:46.990 --> 00:08:50.509 than 10 years. That was easy enough. Now let's 00:08:50.509 --> 00:08:54.750 go back to the HR data and let's ask it something 00:08:54.750 --> 00:08:58.470 like show some data insights. This is a good 00:08:58.470 --> 00:09:01.690 one. And I always find a ton of value. So I'm 00:09:01.690 --> 00:09:04.210 just going to select that and let it execute. 00:09:04.649 --> 00:09:06.970 One of the things that I like to do when I ask 00:09:06.970 --> 00:09:10.529 it this prompt is on the bottom, it says, add 00:09:10.529 --> 00:09:14.889 all insights to grid. And because that's one 00:09:14.889 --> 00:09:17.350 of the insights, but there's actually multiple. 00:09:17.629 --> 00:09:19.769 So when I go ahead and select add all insights 00:09:19.769 --> 00:09:23.090 to grid, it creates a new sheet for me. And it 00:09:23.090 --> 00:09:26.269 puts all the various insights on this sheet for 00:09:26.269 --> 00:09:29.009 me so that I can do some further analysis. A 00:09:29.009 --> 00:09:31.990 lot of them are visual. So it's a nice way for 00:09:31.990 --> 00:09:35.409 me to look at what all this analysis is like. 00:09:35.610 --> 00:09:39.429 This one, for example, is title manager has noticeably 00:09:39.429 --> 00:09:43.190 higher PTO balance. All right. And then also 00:09:43.190 --> 00:09:46.330 this one, the frequency of training completion 00:09:46.330 --> 00:09:51.769 looks like most people have 88 % of their training 00:09:51.769 --> 00:09:54.840 completed. All these different charts and insights 00:09:54.840 --> 00:09:58.000 are provided for you. Let's go back to HR and 00:09:58.000 --> 00:10:01.379 see what else we can do here. Now, if I select 00:10:01.379 --> 00:10:05.240 this copilot icon, I can select get deeper analysis 00:10:05.240 --> 00:10:08.139 results using Python. I'm going to select that. 00:10:08.740 --> 00:10:13.059 And it says over here, do you want to start the 00:10:13.059 --> 00:10:15.759 advanced analysis, which we need to. I'm going 00:10:15.759 --> 00:10:18.059 to select that. It's going to open up another 00:10:18.059 --> 00:10:21.580 sheet and start doing all the preliminary work. 00:10:22.200 --> 00:10:25.179 to get us information using python so basically 00:10:25.179 --> 00:10:28.539 it's set up now to give us some deeper analysis 00:10:28.539 --> 00:10:32.820 but what is it exactly that we're looking for 00:10:32.820 --> 00:10:34.700 so it's giving us some suggestion it's saying 00:10:34.700 --> 00:10:39.120 look do you want to analyze the correlation between 00:10:39.120 --> 00:10:42.860 salary and performance level interesting identify 00:10:42.860 --> 00:10:46.919 employees at risk of leaving and their characteristics 00:10:46.919 --> 00:10:53.580 interesting one going to come back with so basically 00:10:53.580 --> 00:10:56.200 it's saying look the analysis identified employees 00:10:56.200 --> 00:10:58.399 at risk of leaving along with their characteristics 00:10:58.399 --> 00:11:02.080 such as job title years of service salary performance 00:11:02.080 --> 00:11:04.919 level work life balance score and pto balance 00:11:04.919 --> 00:11:08.919 you can view these results in the sheet for further 00:11:08.919 --> 00:11:13.000 analysis we can add these to a new sheet or we 00:11:13.000 --> 00:11:15.620 can just simply look at them here so it's basically 00:11:15.620 --> 00:11:18.799 telling us look These are the people's names, 00:11:19.019 --> 00:11:21.320 and these are the people's job titles, the years 00:11:21.320 --> 00:11:24.100 of service, salary, performance level. And I 00:11:24.100 --> 00:11:27.740 guess these people are at the highest risk of 00:11:27.740 --> 00:11:31.279 leaving, and they've been shortlisted for us 00:11:31.279 --> 00:11:34.840 to do some further analysis. Now, we have some 00:11:34.840 --> 00:11:37.059 other options, like visualize the distribution. 00:11:37.659 --> 00:11:40.940 So visualize the distribution of salaries for 00:11:40.940 --> 00:11:44.220 employees at risk of leaving. Maybe people that 00:11:44.220 --> 00:11:48.049 make less want to leave. maybe people that make 00:11:48.049 --> 00:11:51.190 more want to stay who knows right so let's look 00:11:51.190 --> 00:11:54.129 at this chart that it created for us and basically 00:11:54.129 --> 00:11:56.350 it's saying look the frequency the number of 00:11:56.350 --> 00:12:00.990 people that make less on the left hand side there's 00:12:00.990 --> 00:12:04.009 more of them that want to leave and the people 00:12:04.009 --> 00:12:07.570 that make more there's not as many of them right 00:12:07.570 --> 00:12:10.929 but they tend to not necessarily want to leave 00:12:10.929 --> 00:12:13.450 now we can always cross reference this if we 00:12:13.450 --> 00:12:17.149 go back to our hr data and if we find that person 00:12:17.149 --> 00:12:22.450 that makes about 226 000 if we look at this risk 00:12:22.450 --> 00:12:25.429 of leaving it looks like this person does actually 00:12:25.429 --> 00:12:28.389 want to leave so you know if you want to double 00:12:28.389 --> 00:12:31.789 check the work that's fine it's right there and 00:12:31.789 --> 00:12:34.090 but generally speaking there's not that many 00:12:34.090 --> 00:12:36.840 people that make as much anyways but there's 00:12:36.840 --> 00:12:40.000 a few of them that seem to be wanting to leave 00:12:40.000 --> 00:12:42.600 because there's a gap here between the 150 and 00:12:42.600 --> 00:12:46.860 200. so providing some really good analysis now 00:12:46.860 --> 00:12:49.399 with that we'll stop and we'll just go back to 00:12:49.399 --> 00:12:52.840 the hr data and i wanted to show you some really 00:12:52.840 --> 00:12:55.960 cool things that are not so heavy on the python 00:12:55.960 --> 00:12:59.200 side but before we do that even though i went 00:12:59.200 --> 00:13:03.279 back to the hr tab i you have to actually stop 00:13:03.279 --> 00:13:06.720 the advanced analysis before you can give it 00:13:06.720 --> 00:13:10.000 a different prompt so i just stopped it and now 00:13:10.000 --> 00:13:13.279 i can go ahead and click on view prompts and 00:13:13.279 --> 00:13:18.059 look at my prompts that i've saved and one of 00:13:18.059 --> 00:13:21.179 the prompts that i like it's a nice visual representation 00:13:21.179 --> 00:13:24.279 is color coding the data so i'm going to select 00:13:24.279 --> 00:13:31.320 apply color coding to column for salary and let's 00:13:31.320 --> 00:13:33.320 see what it comes back with So it's giving me 00:13:33.320 --> 00:13:37.059 this scale of red, white, and green. I'm going 00:13:37.059 --> 00:13:40.980 to select Apply. And basically what you're seeing 00:13:40.980 --> 00:13:43.240 now, I'm going to close out this Copilot window, 00:13:43.360 --> 00:13:48.379 is that it's color -coded the salaries from dark 00:13:48.379 --> 00:13:53.240 red being the lowest salary to dark green being 00:13:53.240 --> 00:13:55.940 the highest salary. And the different shades 00:13:55.940 --> 00:13:59.830 of white and light. green or light red or just 00:13:59.830 --> 00:14:02.110 kind of falling in between so this gives you 00:14:02.110 --> 00:14:04.850 a good visual representation of which people 00:14:04.850 --> 00:14:07.429 can be potentially underpaid or which people 00:14:07.429 --> 00:14:10.090 looks like this person is definitely overpaid 00:14:10.090 --> 00:14:13.490 because the salary band is 105 now on the bottom 00:14:13.490 --> 00:14:15.669 right hand side we also have this microphone 00:14:15.669 --> 00:14:19.149 icon that you can leverage to just speak to copilot 00:14:19.149 --> 00:14:21.590 so let's ask it a question how many people do 00:14:21.590 --> 00:14:24.230 i have in the company that have the title manager 00:14:24.230 --> 00:14:26.750 and i'm going to go ahead and send that all right 00:14:26.750 --> 00:14:29.950 it came back with this list here which i can 00:14:29.950 --> 00:14:32.549 again add it to a sheet and it tells me i have 00:14:32.549 --> 00:14:36.580 10 people that are manager three that are project 00:14:36.580 --> 00:14:39.740 managers two that are it managers i hope you 00:14:39.740 --> 00:14:42.159 enjoyed today's video please give it a like and 00:14:42.159 --> 00:14:44.980 consider subscribing share your thoughts and 00:14:44.980 --> 00:14:47.580 your favorite co -pilot in excel prompts in the 00:14:47.580 --> 00:14:50.179 comments thanks for watching and i'll catch you 00:14:50.179 --> 00:14:51.080 on the next one