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.
