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
whiz 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 Shafi and I work
at Microsoft as a Copilot 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 gonna 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 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 ok and it just turns this
into a table 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 copilot 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 copilot 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 is 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 actually actually takes you to the
copilot 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
drop down 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 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've bookmarked 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 is 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 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
pull the first column this is an option it's
saying that you know 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 copilot
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 cells and salary when
the name starts with patricia highlight rows
when cells are greater than a hundred thousand
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 whiz 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 a hundred
thousand 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 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. All right. And then also
this one, the frequency of training completion
looks like most people have 88 % of their training
completed. All these different charts and insights
are provided for you. 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 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 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.
So 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 color -coded 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 or just
kind of falling in between so this gives you
a good visual representation of which people
can be potentially underpaid or which people
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 copilot
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 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 thanks for watching and i'll catch you
on the next one