How to Use Copilot in Microsoft Excel

So let's say we're going to do something in Excel, like that example there, where it wasn't very great to do that in the chatbot. So what can we do in Excel? First, I want to go through just some examples, generally, and then we'll go through and actually see these. So you can have it write formulas for you.

You can have it insert columns. You can have it apply color and formatting. You can have it create visualizations.

So it can create things like pivot tables and pivot charts. Also, if you ask it questions that require more advanced analysis. So Python was added to Excel.

So you can actually run Python code in Excel. And now you don't have to understand how to do Python code, but if necessary, Copilot writes Python code behind the scenes and analyzes your data in Excel using Python code if necessary. If it can do it with just normal Excel functions, it will.

But if it needs to resort to Python code behind the scenes, it'll write that and run it for you. So no matter how it needs to do it, it can do a lot of stuff actually. So let's say I want to create a new column.

I want to create new columns for the first and last names. And I want to not have any middle initials in there. So I've got a company sales file.

And so let's say I put that into my OneDrive. So I'm going to do this in Excel. Close out my other stuff here.

Close this stuff out. And I'm going to go to Excel. Now, first of all, how do we know if we have Copilot in our app? If I'm in a file, I should have a Copilot button.

If we don't have a Copilot button, that tells us we don't have Copilot. Or it hasn't been enabled properly to work in the apps. Of course, you want to make sure you're signed in with an account that has Copilot enabled.

So you want to make sure you're not signed in with the wrong account, like a personal account or something. So I'm signed in with my accounts. I've Copilot there.

And when I click on Copilot, now I've got this Copilot chat in my file. And it can see my file that it's in. Now, one thing it's going to say is that autosave is turned on.

Copilot only works with files that have autosave turned on. If I click turn on autosave, basically this autosave feature is files that are in your OneDrive. So you've got to store it in OneDrive with autosave turned on for Copilot to work.

For Copilot to work, that's just, it's just a requirement for Copilot to work. Yes. So I'm going to save it into, so I could save this one in, but I want to actually open up my other file.

So I'm not going to save this one in. So I'm going to open up from my computer. I'm going to hit browse.

So for me, I've got this on my desktop in class files, AI for workplace productivity, and I've got this company sales file. Yeah, we're going to do company sales. Now right now, this file is just on my computer.

It's not stored in OneDrive. So for Copilot to work, I need to save this into OneDrive. And yes, I'll enable editing.

And once again, if I click Copilot, we'll see, it's going to say autosave needs to be turned on, which means it's in OneDrive with autosave turned on. So I could either click turn on autosave, or I could simply save it into OneDrive. So I could say file, save as, and I can save it in to my OneDrive.

And let's say I create a new folder here. I'm going to call this my Copilot class November 2024. I'll create a folder there just so I can save it there.

So I've saved this into my OneDrive. Autosave is now turned on. And now Copilot is loading, and Copilot can work.

While that's loading, let's look at the customer name. See how customer names sometimes have middle initials? I just want their first names and their last names. I want a column for first name and a column for last name.

So I'm going to tell it, so it says, ask a question or tell me what you'd like to do. And notice it says from A1 to D11. So it's already sensed the table that's there.

So it's already working with that range of data. So I could say, 'Create new columns for first and last names, making sure to exclude middle initials.' I'm being specifically very vague to see can it figure this stuff out? I can always be more specific, but I just want you to see, like, hey, how much intelligence is there in this artificial intelligence? I didn't specify where to look for the name.

I didn't specify exactly what to name the columns. So let's see what it does. I like to test the AI and see how smart it is.

But always, if it's not getting what you want, you can be more specific to say, look in this column, right, to do the same. Oh, ran into an error. All right.

Well, let's reopen that. It's a wonderful way to do the first thing. Oops, there was an error.

Still an error. Okay. So that's strange.

Let me close out my other file here. Let's try once more. It still doesn't work.

I'll quit it and restart it again. Okay. Now I just need to save it again.

Okay. So create new columns for first and last names, making sure to exclude middle initials. Oh.

All right. Let me try quitting this and restarting it. It's back up again.

Cancel your feedback. All right. Let's try this once again.

All right. So create new columns for first and last names, making sure to exclude middle initials. All right.

So I might have to switch to a different computer instead of this one. Or let me try it on my Mac.

So I have both a Mac and a PC. It might have to do with the fact that everything usually works fine on my virtual PC.

Let's make sure my OneDrive is working. Yep. All right.

That's good. Why is that not working? Open from. Why is OneDrive not working here? Online locations.

OneDrive. There we go. There's my file.

It's strange. I don't know why that's not working. All right.

So let me save a copy of that. I don't know why that's not working. I'll just save a copy of that.

Make a new one. Save a copy. Call this two.

To my OneDrive. There we go. Okay.

Autosave is on. Got it. Also, if you don't like autosave, just keep in mind that you have version history.

If you ever make mistakes, OneDrive saves history that you can go back and undo those mistakes. Okay. Now let's just create new columns for first and last names.

Making sure to exclude middle initials. So generating the formula. There we go.

Now it's giving you the formula. And there's a button to insert columns. And notice when I hover over that, see how it shows me what it would do? It called it first name, last name.

Even though I said insert columns for first and last names, plural, notice how it did first name, last name, which is what I would choose to name those columns. I like that. So it gives me a little preview.

And then I can just click insert. And it inserts those. And it's got the formula for doing that.

So even if I go in there and say Lorna, for example, it's doing a formula that will always go in and get their first or last name. And I didn't have to write that formula. And I didn't have to copy and paste the formula.

I just let it do it for me. That's the benefit of having it integrated. You just say to do it.

And it just does it. I didn't even have to touch formulas. It took care of them for me.

That's the benefit. I love the little preview thing when you hover over that you can see. Like does it look correct? So let's say you have the address here, right? So let's say address.

And then if you have like 185 Madison Avenue, this is our location here. New York, New York 10016. I don't know that ChatGPT knows the longitude and latitude.

But let's see. So I'll say insert a new column with the longitude and latitude for the address. God bless you.

Let's see. You can always ask it if it can't do it. Yeah.

So interesting. So this is saying if address. So that's what it would put in.

But I'm just looking at the formula here and say if it's this. So there wasn't like a built-in formula in Excel that can look up the longitude and latitude. So it looks like ChatGPT figured that out.

So they basically said like if it's this address, then put in this longitude and latitude because they knew what that longitude and latitude was. Probably because there's not a built-in Excel formula that can turn an address into longitude and latitude. This is one of the things where it's nice that ChatGPT can know this.

Right. So yes, it can do that. Now, this is probably using the underlying ChatGPT knowledge of where things are.

It's possible they might not know where every single address is. Right. But if you do something in Excel, it might not have the formula to do that.

That would require Excel to have a list of addresses and to know where that is. But this is where ChatGPT can come in. It's like, oh, I know that knowledge.

So I'll put that in. It's a bit of a weird formula, though, because if you have multiple addresses, you don't know the list of stuff. Like, for example, 594 Broadway, New York, New York.

I think that's what it was. If I go back and say to do this again, it's not very effective to have an if it's this address, if it's this address, if it's this address, if it's this address. Because imagine you have that for like 10,000 rows.

That's not going to be a great formula to be. Yeah. So they're just doing ifs for all that stuff.

But it does look like it's correct. I mean, I have to double check, but they're not that far away from each other. So they're basically very similar latitude and longitude.

So that would seem to be correct, though. Will that scale up to be a big database type stuff? Probably not. But it does look like it actually found a way to do it.

There's just nothing built into Excel to do that. So what else could we do? Let's say we want to do a Pivot Table. We could say make a Pivot Table of the best to worst selling products.

Make a Pivot Table of the best to worst selling products. So there's these different products. I know this is a small dataset, but just so we can kind of see it and say to make that so we can look at the products, the quantity sold that shows you.

And you might just want to look at it. But if you want to add that as a new sheet, you can add that as a new sheet. And now you've got a new sheet there and made that Pivot Table for you.

It's a working Pivot Table. If you want to go and change things, if you're not good at making pivot tables, you're like, oh, man, I hate creating pivot tables. It made one for you.

We got the best to worst selling products based on the, notice how they added up the quantities. So they figured out, oh, there's quantity. And added those things up so they can see headphones sold the most.

Headphones sold the most. So again, if you were doing this in like, let's say, ChatGPT or in the chatbot, they could tell you how to do it. But here in Excel, they just do it for you.

Let's see. Calculate the profits for each row. So in the global superstore file, I open that up.

And actually, let me go grab the files from my PC side here. Copy them over to my Mac side. Copy onto my Mac here.

Desktop. Class files. Or did I already put them there? I already put them there.

Okay. Good. All right.

So I've got open from my Mac. On my desktop. Got class files.

It's not there. Oh, no, that's my PC side. Sorry.

This is my, where's my Mac? Oh, here we go. This is the Mac. Desktop.

Class files. There we go. Paste.

So global superstore. Got to save it into my OneDrive. I'm going to put it into my same CoPilot class folder.

It's global superstore. If you're planning on using CoPilot, start using OneDrive. Start creating folders and organizing things, putting it into OneDrive.

So in here, I've got sales, quantity, cost, and I want to, what do I want to do? I want to calculate profits. That's what I was saying. I want to calculate profit.

And I'm just going to tell it how to, I'm just going to say calculate profit and see if it can figure out what I want to do. Of course, I could say like add up this, you know, times this. I could say that, but I just want to see, can it figure this out? So I'm going to open up CoPilot.

The whole idea here is maybe you're not so great at doing analytics. And so you want it to figure this stuff out and see, can it do it without you having to figure out the math. Calculate the profits for each row.

I didn't say to create a column. I just said, I want to know the profit for each row. Right? So let's see if it figures this out.

Like, yeah, it should really add a column for profit. So here's a formula. Calculates the profit for each order by subtracting the cost from the sales amount.

Does anyone have an issue with this? Sounds right, right? But is the cost on the sale, are they on one or are they? So here's, here's sales, right? Here's cost. Well, yeah. So this is where you have to know your data and you'd have to ask yourself, wait, is this a cost per item or is this the total cost? And these are the questions I want you to ask yourself, because this spreadsheet doesn't really tell me that.

Like, if, is this the cost per item or total costs? So this might be correct or it might not be correct, depending. I don't know by just looking at this spreadsheet, you know, you might have to say like to somebody, see if they said cost per item or total costs, that would have been a better, clearer spreadsheet. Right.

But this is also like, if somebody gave this to me and didn't give me more context, I wouldn't even know. Right. Let alone the AI knowing.

So this might be correct. If that's the total costs, this spreadsheet could be better in terms of its names so that anybody could understand this. But that's my point is I want you to always look at something.

I like that they show you the preview before applying it, because then you can decide, is that correct in my situation or is it not? And in this case, unless you know the person who created it and whether this is the total costs. Now, if you look at that total costs, let's try to figure this out. If your cost is $973 and your sale was 1200, you can't have a per item cost of $900 because then that would be like almost $5,000 of costs on a $1200 sale.

So thinking this has to be total costs. This cannot be per item cost or else we're losing a boatload of money on this. Right.

So it's got to be total costs in this case. So then that's correct. It's not a per item cost.

So then I can insert the column and it did say, okay, sales minus cost is profit. So sometimes you can figure this out yourself without having to ask somebody. You're like, oh, yeah, we'd be just losing a boatload of money if that's the per item cost.

Right. But you should always double check yourself and the AI for this. Also, once it's inserted, if you don't like it, there's even an undo button right there.

If you're like, oh, wait, wait, I didn't like that. You can just hit undo right there. So you're in control.

You see what it does when you're doing this. So let's say you want to do some formatting of some cells. Maybe you want to highlight cells that have negative values because you lost money.

We could say, you know, so if you look here, some of these are negative. So I could say highlight all cells with a negative value so it can do some conditional formatting here. So I typed in, I wanted to highlight all cells with a negative value because, you know, there's some negative profits.

So I said highlight all cells with a negative value and anything that has a cell value less than zero is going to be formatted as pink. When I hit apply, it'll go add that conditional formatting. And there we go.

Now anything that is negative gets highlighted in pink. If you don't like it, you can always undo it. Now you might say for some of these things, oh, I already know how to do this.

I could do that myself. So you have to ask yourself the question is, is it faster to do it this way or is it faster for you to do it yourself? You might know how to do these things, but also I'm showing these on simpler examples, but obviously you could do more complex examples as well. So the more complex it gets, do you know how to do those more complicated things? And if you know how to do it, but this is faster, maybe you do it this way because it's faster to type something in rather than doing it yourself.

Obviously, if you can do it faster manually, you always have that option. But like you might not know how to do some of these things and now you can do them because you can ask Excel to do it for you. Now, beyond this a little bit, you could say, analyze last year's business results.

Analyze last year's business results. Let's see what it comes up with. So, you know, the other things were very specific, like do this thing, fix this thing, but we can do data analysis, ask it to give us some insights into things.

This is a little bit of a bigger dataset. There's 40,42,000 rows. So it takes a little bit longer.

And so they say the total sales for last year were basically 4.3 million with a total profit of half a million, a total quantity of over 60,000, and a total cost of 4.8 million. So they did the analysis. If you want to have that as a sheet, you could insert it as a sheet if you want to actually have it there.

So right now I just have an order sheet, but I could click add new sheet if I want to, or I just might want to see that, I don't have to add it as a sheet if I don't want to. And I can zoom in here and I can see it's got the sum of sales, sum of profit, sum of quantity, sum of costs, and it wrote in a formula to do that. If the data ever gets changed, then this could update, or I just might want numbers here.

I could always copy and paste that out of the chat if I wanted to. I don't have to change my file if I don't want to. Yes.

So yeah. Okay. So what did you want to say? So let's say I want to highlight any sales over, I'll say, $100 profits, because there are some for copiers, right? Because this is for copiers.

So there's over $100 in copiers. So I'm giving it more specific stuff in green. Okay.

So I couldn't create the conditional format rule for highlighting sales over a hundred dollars profit. Okay. So in this case, you're just saying it couldn't do that.

If it can't do something, it'll tell you that it can't do it. So the idea is you can always, if you're not sure if it can do it, just ask it to do it. If it can't do it, it's just going to tell you that it can't do it.

Okay. So it's not going to try and do it. Nothing is going to be done automatically.

Like you're going to, you're going to say, can you do this? It's going to show you what it would do. And you can look at it to say, is that a good change before you apply it? Either it's going to say, I can't do that. So nothing happens.

Or it'll say, do you want me to do this? You you're in an approval. It's not going to just go mess up your file. You just ask it, see what the response is and see if you want to apply that or not.

You will always be asked to do things and it's up to you to approve it or not. But it's never just going to do it. It's going to ask you, do you want to do this? And it's up to you.

And it's nice because it previews what it'll do. Assuming you can do it, you know, I'm sure over time, it'll get better at doing things. You know, now is the kind of the worst we'll probably ever see this more and more features as time goes on.

In the latest year's business results, are there any trends or patterns in the latest year's business results? Are there any trends or patterns? Oh, I said of, let's see if it figures out that I made a typo instead of saying, or

Luckily at a lot of times it figures things out and can be like, Oh, I think you meant or so like I said, this file is about 40,42,000 rows, which isn't the biggest file by any means, but decent size enough that you can see, you know, that, you know, you don't want to work just, you don't want to just see an example of like 10 rows. You got to see that it can work with bigger amounts of data. The more data, the longer things would take, of course, to crunch.

Okay. So I analyzed and here I can make this bigger. If you want to see things bigger, you can resize the panel and get a bigger example here.

I analyzed this and here's what I found. Quantity shows a repeating pattern. So you notice how it kind of goes up and then down and then up and then down and up.

So there's, they're seeing that there's kind of a peak where it goes up and then down and up and down and up. We're seeing that repeating pattern. If you want to add this to a new sheet, you can.

So you can get that visual and then to make this bigger. So they created a pivot chart to see this and that way you could dive in here and see. So here is October.

Here's October. Here's October. So you can kind of see when during the year.

So it kind of towards the end of the year, we're seeing a spike and then it goes down. You can see some years you have a little bit of an earlier spike, but they're kind of pointing out like, hey, there's kind of a trend in that pattern that's going on. Now, is there always some insight for them to find? Is there always some pattern for them to find? Not necessarily.

There isn't. But it's interesting that they created that chart and notice that that's not an exact pattern, but it's kind of similar in the way that goes through that. There's more sales towards the end of the year.

Compare the year over year sales. Oops, made a typo there. Stop.

Compare the year-over-year sales. So here it generated another chart. I could add that to a sheet if I want to, or I could just look at the text results.

They just tell me what the results are. And I can see, if I zoom in, I can see what the results are. And then I can see it over time as a nice pivot chart there.

So they created a Pivot Table and they created a pivot chart. Cool. If you're not sure whether you can do it or not, just ask.

If you can't do it, it'll just say, I can't do that. Now, as far as making charts, maybe you're not good at making charts. I can say, create a chart of the sales growth by month.

If I'm in orders here, make a chart of sales growth per month. Now if you did this in ChatGPT or the chatbot, it would tell you how to do it. But here, they actually just do it.

They show you the chart. And then it's up to you if you want to insert it into a new sheet. You can insert it into a new sheet.

And they have the table for you. And then they also have the chart itself. So they created the Pivot Table and they created the pivot chart.

And you can zoom into that. If you're not good at creating those kind of things, or just think about, could you have done it that fast? Or is this faster? Right? So it's not always about it's enabling you to do things you don't know how to do, which it might. But it might just be enabling you to do things faster.

Okay. We're going to go through each app.