Today we're going to learn how to master Copilot inside Microsoft Excel. We'll start off by preparing our Excel document before using Copilot and then move on to where to find Excel prompts and how to interact with Copilot in Excel. Both simple interactions and more complex ones using Python.
Don't worry, you don't need to be an Excel formula wiz or a Python coder. We'll even have Copilot create charts for us and color code our data using conditional formatting. Welcome to collaboration simplified.
My name is Shervin Chaffy and I work at Microsoft as a co-pilot principal technical specialist. Before we continue, please give this video a like as it really does help the channel. Thank you very much for your support.
All right, we're going to fire off Microsoft Excel. And I want to first explain this Excel spreadsheet that we're looking at together. It's for human resources and it has a variety of different data that I've put together and it's all fictitious data so don't look into it too much but basically it's for human resources it has people's names and their job titles when they started when we expect them to retire how many years they've been with the company their salary their performance level out of 10 their pay range if they're at risk of leaving the company if they're a manager what their team attrition rate is like how many people leave the company, what percentage of their training they've completed, their PTO balance, and their work life balance score out of 10.
So, it has some interesting data. And I'm not an HR person, but I guess there's information like this out there for people. If you're an HR, let us know if there's information like this you have on us.
Now, before I get going, I wanted to show you one thing real quick. This is a pro tip which is notice that all of this is actually in table form in Excel. Now if it wasn't in table form it would look like something like this.
And what I have noticed is that if I don't have data in table form and I interact with Copilot sometimes the responses are not that good or I might even get errors. All right. I don't know why that is, but my suggestion is when you have an Excel document, simply go from the beginning all the way to the end and just highlight it and then move over to insert table and just select okay and it just turns this into a table and now you can start interacting with it.
All right, so just do that before you start doing anything with Copilot. So looking at our Excel document that's in table form, you'll see that you can interact with Copilot in a variety of different ways. First of all, when I select any cell, you'll see this C-pilot icon that when I click on it, it gives me options that I can select.
That's one way of interacting with Copilot. The other way is that if we were in our home icon up top, you'll see co-pilot on the top right hand side show up inside Excel. So you can select copilot and then you'll start being able to interact with copilot right from here.
Now right away you'll see something like create a table, create a total rows, all kinds of auto prompts that give you ideas on how to interact with copilot and you can select more examples, more examples and just keep getting different examples. This is what I love about Copilot in Excel because it guides you through all the things that you can do. On the bottom right hand side, we have these icons like adding an image, viewing prompts, managing content, and also using the microphone.
If I highlight this over here, this these are what the icons look like. And over here, this is where you start typing in your custom prompts if you wanted to. And then copilot always gives you some suggestions of things you can run like showing data insights and so on.
So one of the things that I like to do is I like to select view prompts and Microsoft does a great job of showing you different prompts that you can execute within Excel. So there's so many things like highlighting data, getting column information, visualizing relative values, all this really good stuff. However, what I will say is that on the bottom right hand side, there's a section called see all prompts.
When you select that, this act actually takes you to the co-pilot prompt gallery. And over here, not only can you find prompts that are for Excel, but also for Word, for PowerPoint, OneNote, Outlook, Teams, and so on. And so when you select this application dropdown over here, you can also filter based on task or job title, you select the application and then you select Excel and you see all these different Excel prompts that you can use.
And when you get to the bottom, you can click show more. Get to the bottom, click show more. There's so many different kinds.
Now, what you'll notice is that there's this um ribbon icon that you can select to save your prompt. And some of them I've already saved. And so what this does is it takes these prompts that you save, kind of like bookmarking inside a web browser, and it puts it into your prompts.
So these are some of the ones that I've saved inside my Excel. And we'll be using some of these for the Copilot demo. Let's go back to the Excel spreadsheet.
And I'm going to select your prompts, which are basically my prompts. These are the ones that I bookmark for myself. And one of the things that I'll do very simply is highlight the data.
So what I did was I selected highlight D and then the rest of it I have to fill out on the bottom here myself. So I'm going to say highlight the top five salaries. Just something simple like that.
Copilot's going to understand the fact that I'm referring to the salary column. It's going to look at the top five, analyze it, and then highlight it for us. It's going to say that I'm going to be using yellow and black for the background and the font.
If this looks good, then I simply select apply and it's going to go ahead and execute that for me. So, here it is. Somebody makes 132,000, 138, 132, 136, and 226.
Wow, that's that's a good salary. And on the bottom right, it gives us some additional information like bold the first column. This is an option.
It's saying that, you know, do you want to do this next or do you want to show items with the pay range of 70 to 80. So, it's giving you ideas of things to do next. Now, the next thing I want to show you is I'm just going to close out this co-pilot window on the right.
And let's go ahead and hover over salaries over here. I'm going to click on it and click on copilot. Here's an interesting one.
Suggest conditional formatting. I'm going to go ahead and click on that. It opens up copilot and it's going to give us some options here.
So, here they are. Highlight sales and salary when the name starts with Patricia. Highlight rows when cells are greater than 100,000.
Let's go ahead and select any salaries that are higher than 100. I don't know if you're noticing here, but it's automatically creating these formulas, so you don't have to be a formula wiz anymore. And if it looks good, just select apply.
And basically, anything that's over 100K, it's highlighted for us. Now, you can get a little fancy, right? So, on the bottom, let's create something custom and say, "Show me all the people that make over 100,000 and have been at the company less than 10 years.
" So generally, especially if you have like a huge document with thousands of rows, this would take you a long time, right? Because you'd have to sort, you'd have to come up with a formula. But we can just ask it something like this in natural language.
It goes ahead and analyzes it and gives us the response right here. And it gives us the option to do deeper analysis using Python or additional prompts. If this looks good, what I like to do is simply select add to new sheet because I want to have this information in a different place where I can do additional analysis.
So here it is. These are the people that make 100 or more and have been at the company less than 10 years. That was easy enough.
Now let's go back to the HR data and let's ask it something like show some data insights. This is a good one and I always find a ton of value. So I'm just going to select that and let it execute.
One of the things that I like to do when I ask it this prompt is on the bottom it says add all insights to grid and because that's one of the insights but it there's actually multiple. So when I go ahead and select add all insights to grid, it creates a new sheet for me and it puts all the various insights on this sheet for me so that I can do some further analysis. A lot of them are visual.
So it's a nice way for me to look at what all this analysis is. Like this one for example is title manager has noticeably higher PTO balance. Let's go back to HR and see what else we can do here.
Now, if I select this copilot icon, I can select get deeper analysis results using Python. I'm going to select that. And it says over here, do you want to start the advanced analysis?
Which we need to. I'm going to select that. It's going to open up another sheet and start doing all the preliminary work to get us information using Python.
So, basically, it's set up now to give us some deeper analysis. But what is it exactly that we're looking for? So it's giving us some suggestion.
It's saying look, do you want to analyze the correlation between salary and performance level? Interesting. Identify employees at risk of leaving and their characteristics.
Interesting one. I'm going to select the second one and let's see what it it's going to come back with. So basically it's saying look the analysis identified employees at risk of leaving along with their characteristics such as job title years of service salary performance level work life balance score and PTO balance.
You can view these results in the sheet for further analysis. We can add these to a new sheet or we can just simply look at them here. So it's basically telling us look the this is these are the people's names and these are the people's job titles the years of service salary performance level and I guess these people are at the highest risk of leaving and they've been shortlisted for us to do some further analysis.
Now we have some other options like visualize the distribution right so let's select visualize the distribution of salaries for employees at risk of leaving maybe people that make less want to leave maybe people that make more want to stay who knows right so let's look at this chart that it created for us and basically it's saying look the frequency the number of people that make less on the left hand side there's more of them that want to leave And the people that make more, there's not as many of them, right? But they tend to not necessarily want to leave. Now, we can always cross reference this.
If we go back to our HR data and if we find that person that makes about 226,000, if we look at this risk of leaving, it looks like this person does actually want to leave. So, you know, if you want to double check the work, that's fine. It's right there.
And but generally speaking, there's not that many people that make as much anyways, but there's a few of them that seem to be wanting to leave because there's a gap here between the 150 and 200. So providing some really good analysis. Now, with that, we'll stop and we'll just go back to the HR data.
And I wanted to show you some really cool things that are not so heavy on the Python side. But before we do that, even though I went back to the HR tab, I you have to actually stop the advanced analysis before you can give it a different prompt. So I just stopped it.
And now I can go ahead and click on view prompts and look at my prompts that I've saved. And one of the prompts that I like, it's a nice visual representation is color coding the data. So I'm going to select apply color coding to column for salary and let's see what it comes back with.
So it's giving me this scale of red, white, and green. I'm going to select apply. And basically what you're seeing now, I'm going to close out this Copilot window, is that it's colorcoded the salaries from dark red being the lowest salary to dark green being the highest salary.
And the different shades of white and light green or light red are just kind of falling in between. So this gives you a good visual representation of which people can be potentially underpaid or which people are looks like this person is definitely overpaid because the salary band is 105. Now on the bottom right hand side we also have this microphone icon that you can leverage to just speak to co-pilot.
So let's ask it a question. How many people do I have in the company that have the title manager? And I'm going to go ahead and send that.
All right, it came back with this list here which uh I can again add it to a sheet and it tells me I have 10 people that are manager, three that are project managers, two that are IT managers. I hope you enjoyed today's video. Please give it a like and consider subscribing.
Share your thoughts and your favorite co-pilot in Excel prompts in the comments.
