Microsoft Excel is getting some AI love called Copilot. It can figure out formulas,
it can add new columns, it can generate charts, it can highlight cells, and it can even analyze
data. Basically, it promises to revolutionize the way you work with spreadsheets. But is it
all hype or is it truly the future of Excel? Well, let's find out. To get Microsoft Copilot in Excel,
head to the following website. You can click on the card in the top right-hand corner,
and I've also included a link down below in the description.
First, you'll need a Microsoft 365 subscription,
either a family or a personal plan. Either one works. Once you have that,
you'll also need another subscription or a second subscription to something called Copilot Pro. You
can click on this link up on top of the page. So, you basically need two different subscriptions.
With Copilot Pro, you get access to Copilot within all of the different apps, like Excel,
Word, PowerPoint, and Outlook. Plus, you also get some other benefits, like using GPT-4 during peak
hours, and you can also create images faster using Dolle 3. So, you do get some additional perks.
Once you finish getting all of the required subscriptions, you should now have access to
Copilot. And here in Excel, I have some sample data for the Kevin Cookie Company. If you'd like
to follow along, I've included a link to this data down below in the description of this video. Now
that I have Copilot, up on top, let's click on the Home tab, and all the way over on the right-hand
side, you should now see a new option for Copilot. But right now, it's grayed out, and I can't
actually click on it. Here, if we hover over the Copilot icon, here we'll see this tool tip appear,
and it says that Copilot only works with files stored in OneDrive or SharePoint. Currently,
this file is stored locally on my computer, so I need to put it into the cloud first. So over
in the top left-hand corner, I'll click on the File menu, and then go down to Save As, and here
I can save in either OneDrive or in SharePoint. I'll select OneDrive, and over here, I'll click
on Save. Now that we've saved this workbook in the cloud, up in the top right-hand corner, you'll see
that I'm now able to click on the Copilot icon. So, let's click on this. This now opens up a pane
over on the right-hand side with all of Copilot's capabilities. But here too, you'll see that these
are also now grayed out. If we look down just a little bit, here first it wants to convert this
data range, or all of this data over here, into a table. To be able to use Copilot, it has to act
on a table. At least here, it offers to convert it for me, so let's click on this button to convert.
And just like that, it's now converted all of this data range into a table. Aside from being able
to use Copilot, you get all sorts of advantages from using tables in Excel. Here, for instance,
you get this nice formatting with all these banded rows. Right up above, you can also very
quickly change the formatting. Maybe something else tickles your fancy. Right over here,
you also get all these different filter headers, so you can very quickly filter or sort your data.
And right up above, you have various options, like adding a total row. Then here, you get a few more
options as well. Over on the right-hand side, let's now go to the very top of Copilot. Here,
you'll see all sorts of different ways of how you can engage with the Copilot. For example,
you can use it to add formula columns. And in a moment, we'll see what that means. You could
also highlight cells, you can sort and filter. And here, you could also analyze your data and
identify if there are any outliers. Here, we see a few more suggestions. And down at the very bottom,
you have your prompt box. This is where you can ask questions or make requests of the Copilot AI.
And here too, there's no shortage of learning material. If you're struggling with forming
a prompt, down here, we could click on this View Prompts icon. And here too, you'll see
all sorts of sample prompts that you can enter in. For example, here, if I click on Create,
you'll see the different types of activities that you can do with the Copilot. For now,
let me click out of that. And here, I'll simply click into the prompt. I want to start with a very
simple question. I want to know, can the Copilot figure out how many customers we have here at the
Kevin Cookie Company? Over here, if I look at all of my data, you'll see that in column B,
I have all of the different customer names. And one quick way to figure out how many
unique or distinct customers we have, I could simply click on this filter icon, and here,
I can see that we have five. But let's see if the AI can figure this out. Down below, I'll
type in my question. How many customers does the Kevin Cookie Company have? Now, along with typing
in a question, I can also speak the question using my microphone by clicking on this icon,
but I prefer typing. Over here, let's now send this prompt to the AI. And right now, it looks
like it's working on it, and it's attempting to understand the data. Now, one thing you'll notice
is it does take a little bit of time. One thing I needed to do to make sure that the AI could work
properly is I reduced the data down just to 2022. Previously, when I had additional years in here,
the AI was struggling with that. So just something to keep in mind as you use it. And look at that.
Over on the right-hand side, here it says, "According to the data, Kevin Cookie Company
has five distinct customers." That's exactly what we saw when we clicked on that filter menu. So,
it looks like it's working properly. If I scroll up a little bit more, here I see a table that also
shows five, and I can now add this to a new sheet. Let's click on this button, and this now drops us
into a new worksheet. And here, if I scroll in a little bit, now click on this value of five,
and here I can see the formula and the function that the Copilot used to derive that answer,
so that way I can check the work. And it looks like this worked exactly as I would expect it
to. To be fair though, it is a fairly complicated formula. Let's now go back to the order sheet.
Let's now try something a little bit more complicated. Let's see if we can add a new
column to this table with the profit. That's the revenue minus the cost. Right down below,
here I'll type in my prompt, add a new column with profit. That should be easy enough,
but let's see what it comes back with. I’ll click on send. Right up above, it looks like
it came back with a response. It proposes that it's the revenue minus the cost. Well,
that's exactly right. Over here, I can click on this carrot icon, and I can also have it
explain the formula to me. That can be helpful if it's a little bit more complicated than this
example. Here, I'll minimize that. Now over here, I can hover over this insert column,
and there I can preview what it'll look like when it adds that profit column to my table. And if I'm
certain that I want to add it, here, I'll simply click on that button, and right down below it says
done. I added profit to column G. And of course, if I actually didn't want that column, I could
also undo it. So that's pretty cool. Here, it understood my data, it understands what profit is,
and here it automatically calculated that. That helps save some time. At the Kevin Cookie Company,
I like celebrating our most profitable orders, and I like to highlight them in a color just so
they stand out to me. Now, traditionally, I would go up to conditional formatting,
and I would try to figure out this menu to see how I could highlight all of our orders over 2000.
But let's see if maybe the Copilot can give me a shortcut. Right over here, I'll type in the text,
can you highlight profit in green when above 2000? Let's see what it comes back with. And
that's all done. Look at that, that was easy. It's now highlighted all the cells over 2000.
I'd also like to show it as a currency, so let's try that. And there it's now formatted the cells
in currency notation. Nice. Next, I'd like to understand which customer here at the Kevin
Cookie Company ordered the most cookies from us. That way we can wine and dine them with,
of course, cookies and milk. There's no better way of getting a big contract.
Now, traditionally, I would go up to the insert menu and I would insert a pivot table,
and then try to make sense of the data. But again, let's see if Copilot can help us. Down below,
I'll type in which customer ordered the most cookies, and then let's send that. And right
up above, it looks like the Copilot answered my question. It looks like it's Cascade Grovers with
255,000 cookies. Now that's a lot of cookies, but probably not even close to what you get on
the web every single day. If I scroll up just a little bit more, it looks like it made a pivot
table for me. Let's try adding this as a new sheet. This now drops me onto a new worksheet
where I can see that the AI has inserted a pivot table. If I click into the pivot table,
this opens up all the different pivot table fields over on the right-hand side. Right here,
I can see that it's filtered it to the customer who ordered the most cookies. If I click on the
filter, I can clear the filter, and here I can confirm that Cascade Grovers did indeed order
the most cookies from us. And by long shot, they ordered a lot more than all the other
customers. Now with pivot tables, you could do additional analysis on your data. And if you'd
like to learn all about pivot tables, be sure to check out the video down below in the description.
Down below, let's click back into the orders worksheet. Back now in orders, let's now see
if Copilot can maybe help us pull together a visual or a chart. Sometimes a chart is just
a much better way of conveying information and data. Right down here, let's type in,
can you make a line chart showing profit by month? And let's see what it comes back with. If we
scroll up just a little bit, here I can see that it generated a chart showing me profit by month
of the year. And look at that, there's this big spike towards the end of the year. Now over here,
I could add it to a new sheet, just like we did with the pivot table. And then I can make
additional changes or customizations to this chart. But let's actually see if maybe the AI
can help us modify this chart. Down below, let's ask the question, can you change the line color
to green in the line chart? That should be a fairly simple change. Let's see if it can help.
Right up above, here we see a message saying, I'm sorry, I'm unable to change the line color of
the chart directly. But then it walks through and tells me how I can do that. Maybe the AI wants me
to do some work too, since I've just been asking all these questions. More seriously though, maybe
in a future iteration, it will be able to do that. That'd be nice if you could continue prompting to
make changes and customizations to the results that you get back. Let's now try one more prompt
down below. And I want to see if it can analyze this data and maybe find some outliers. So, I'll
ask the question, are there any seasonal trends in cookie sales? If we scroll up just above,
here it's identified outliers in all of our cookies shipped. And it looks like it's right
around the holidays. That's when we sell the most cookies here at the Kevin Cookie Company.
So that's helpful to see. Now, of course, with all this functionality that we've looked at today, you
can get to all of it by clicking on the right tab or clicking on the right button. But it takes some
knowledge of knowing where things are and what features are available. With Copilot now, it makes
a lot of this functionality a lot more accessible. As long as you can enter in a prompt or a question
or a request, chances are you'll find what you're looking for and you'll be well on your way to
analyzing your data. All in all, it's great to see AI finally coming into Excel, especially
for newer users who may not be familiar with all the available functionality. Keep in mind,
Excel has been around for almost 40 years. Like, how do you separate a first name from a last name?
Or how do you apply conditional formatting? That's not always obvious and AI can really help there.
For power users though, using natural language by prompting and then waiting
for the AI to respond is probably slower than just doing it yourself. Of course,
the AI will continue to improve over time. That's why it's just in preview,
which is why you see that text next to Copilot in Excel. Now, I wouldn't be surprised if soon
enough, Copilot is an essential tool for both beginners and advanced users alike.
To watch more videos like this one,
please consider subscribing and I'll see you in the next video.