Microsoft Excel Tutorial: Preview of AI Copilot for Excel.
Welcome to episode 2636 of Mr. Excel's netcast! In this video, we will be taking a first look at Copilot AI for Excel, a new feature from Microsoft that has been generating a lot of buzz. I was lucky enough to get a sneak peek at this feature, even though it was initially only available for enterprises with over 100 customers. But now, I have access to it and I'm excited to share it with you.
In this video, we will be exploring the Copilot feature and its capabilities. We will be working in Excel online, as it is currently easier to use for this feature. The data we will be using needs to be in a table for Copilot to work. Once we have the data in a table, we can access the Copilot pane by clicking on the Copilot icon on the right-hand side of the screen. This feature allows us to add formula columns, highlight data, sort and filter, and analyze our data. The analyze function is similar to the analyze data feature we have had before.
I will be demonstrating various tasks using Copilot, such as adding a gross profit percent column, using the IF function, creating a running total, and creating a pivot table. We will also be testing Copilot's capabilities by asking it to perform more complex tasks, such as calculating a bonus for records with prime numbers and creating conditional formatting. While Copilot is not perfect yet, it is still very impressive and has a lot of potential. It is currently limited to data stored in OneDrive and can only work on one column at a time, but I believe it will continue to improve with time. So, let's dive in and take a closer look at Copilot AI for Excel. Don't forget to like, subscribe, and ring the bell for more videos like this. And feel free to leave any questions or comments down below. Thank you for watching!
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
You can help my channel by clicking Like or commenting below: Why clicking Like on a YouTube video helps my channel
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
Table of Contents
(00:00) First Look: Copilot for Excel
(00:23) Using Copilot in Microsoft Edge
(00:39) Four abilities for Copilot
(00:57) Copilot adding a GP% calculation
(01:34) Copilot shows suggested formula & explanation
(02:11) Hover to see preview of new column with wrong alignment
(02:21) Insert new column
(02:38) Can it write IF with AND formula?
(05:00) Formula for running total does not really work
(06:53) $100 bonus when Quantity is Prime Number
(08:28) Create a pivot table by month and year
(10:01) Wrong result - adding sector
(10:28) Highlight revenue over 20K
(11:20) Highlight dates in February fails
(12:25) Sorting by two columns
(12:46) Filter to one customer
(13:05) It is amazing for a preview
(13:41) Copilot outside of Excel
(14:17) Clicking Like really helps the algorithm
Welcome to episode 2636 of Mr. Excel's netcast! In this video, we will be taking a first look at Copilot AI for Excel, a new feature from Microsoft that has been generating a lot of buzz. I was lucky enough to get a sneak peek at this feature, even though it was initially only available for enterprises with over 100 customers. But now, I have access to it and I'm excited to share it with you.
In this video, we will be exploring the Copilot feature and its capabilities. We will be working in Excel online, as it is currently easier to use for this feature. The data we will be using needs to be in a table for Copilot to work. Once we have the data in a table, we can access the Copilot pane by clicking on the Copilot icon on the right-hand side of the screen. This feature allows us to add formula columns, highlight data, sort and filter, and analyze our data. The analyze function is similar to the analyze data feature we have had before.
I will be demonstrating various tasks using Copilot, such as adding a gross profit percent column, using the IF function, creating a running total, and creating a pivot table. We will also be testing Copilot's capabilities by asking it to perform more complex tasks, such as calculating a bonus for records with prime numbers and creating conditional formatting. While Copilot is not perfect yet, it is still very impressive and has a lot of potential. It is currently limited to data stored in OneDrive and can only work on one column at a time, but I believe it will continue to improve with time. So, let's dive in and take a closer look at Copilot AI for Excel. Don't forget to like, subscribe, and ring the bell for more videos like this. And feel free to leave any questions or comments down below. Thank you for watching!
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
You can help my channel by clicking Like or commenting below: Why clicking Like on a YouTube video helps my channel
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
Table of Contents
(00:00) First Look: Copilot for Excel
(00:23) Using Copilot in Microsoft Edge
(00:39) Four abilities for Copilot
(00:57) Copilot adding a GP% calculation
(01:34) Copilot shows suggested formula & explanation
(02:11) Hover to see preview of new column with wrong alignment
(02:21) Insert new column
(02:38) Can it write IF with AND formula?
(05:00) Formula for running total does not really work
(06:53) $100 bonus when Quantity is Prime Number
(08:28) Create a pivot table by month and year
(10:01) Wrong result - adding sector
(10:28) Highlight revenue over 20K
(11:20) Highlight dates in February fails
(12:25) Sorting by two columns
(12:46) Filter to one customer
(13:05) It is amazing for a preview
(13:41) Copilot outside of Excel
(14:17) Clicking Like really helps the algorithm
Transcript of the video:
Hey, just down below the video, if you click like that'll make sure that YouTube shows this video to more people.
Thanks - hey, this is great.
Microsoft gave us a sneak peek at the new copilot feature.
I didn't think I'd ever be able to see this.
They talked about it only being for enterprises with more than a hundred customers or something like that, but I have it.
It's pretty cool.
It's not perfect, but it's pretty cool.
Thought you'd like to see it.
Let's take a look.
Okay, so I'm here in Microsoft Edge.
I'm working in Excel online just because it's easier at this point.
The data has to be in a table for copilot to work.
Over here on the right hand side, the copilot icon opens the copilot pane.
It says that it can add formula columns, it can highlight data, it can sort and filter and it can analyze, and frankly, this analyze is very similar to the analyze data that we've had.
All right, so this is a data I use all the time.
Region, product, date sector, customer quantity, revenue cost of good, sold and profit.
So the first thing I said, let's just see if we can add a gross profit percent column.
So calculate GP percent.
I'll tell you the first time I tried this, I figured I actually had to spell out gross profit percent and it put in a column that had a three word header and then all kinds of bad things happened after that.
Couldn't understand anything.
So I tried it again with calculate GP percent and check it out.
Over here it's working on it.
Generating formula column suggestions.
It actually takes some time.
Sometimes it says it's trying to understand the data.
Okay, so here's a suggestion for you.
Calculate the gross profit margin for each transaction by dividing the difference between the revenue and cost of goods sold by the revenue.
Shows me what the formula is.
Now that firm is correct.
It would be just as easy to have done profit divided by revenue.
It'd be a shorter formula, but that's okay.
I'll take this.
Explain the formula.
It tells me that it's going to subtract cost of good sold from revenue, divide the gross profit by the revenue column, and then format as a percentage value with two decimal places.
When I hover over insert column, it shows me what it's going to look like.
Although the preview is left aligned, that would be horrible.
They're not really going to do that, are they?
No.
No, they don't really do that.
Done.
I had a GP present to column J.
I can click undo.
I can give them a thumbs up, I can give 'em a thumbs down and hopefully someone at Microsoft is looking at these to see whether they got it right or not.
Alright, another one I do all the time in my seminars is using if with.
And so let's calculate a bonus Of 2% of revenue when we'll say product is A, B, C, and GP percent.
The calculation we just created is in the top 20%, right?
That's a pretty hard formula we're going to have to use if with and checked column B, and then do something clever over there in column J, a press send.
It's working on it.
Understanding your data almost there.
Ah, sorry.
I can't do multiple things at once.
For the best experience, add one request at a time.
Lemme pause the video here.
Okay, let me try and ask it a different way.
Calculate a bonus of 2% of the revenue.
If the product is A, B, C and the GP percent is in the top 20%, which feels pretty much exactly like what I asked for there I All right, there it is.
So it knows to use the and function inside of if it's not nesting, the F statements.
I love that it was able to generate the percentile function.
I had to look for everything above 80%.
This formula is the right formula and it's so strange.
Why did it work here?
The only thing that I really see that's different is I capitalized revenue there and didn't capitalize it here.
GP percent is in the top 20%.
I don't know.
All right, strange.
Hey, remember it's a preview, right?
So we'll insert the column.
That one worked great.
That one worked great.
I am not a big fan of tables and they're forcing me to use a table here, so I thought I'd just kind of do something that's hard for tables to do.
Let's calculate a running total for revenue generating formula column suggestions.
Normally I would snip this out, but I'm leaving it in here at real times just so you can see how long it takes to actually think about this and come back with an answer.
Actually, I don't like this one at all.
The data is sorted by date, but what they're going to do Is not going to be correct.
See, everything for January 1st is going to be 52, 7 7 1.
I didn't ask for a year to date number or anything like that.
And it's funny, in earlier tests it gave me a different formula.
Lemme do something like sort by customer so it's not even sorted by date and see if the same request comes back with a better answer.
I know that I've gotten a better answer in earlier testing.
No.
Wow.
Yeah, that one's disappointing because I know that it's gotten it and it's gotten it.
Before, it used a really weird index syntax, which was not efficient at all, but it worked.
It worked.
Okay.
Now back in March we had a few videos that talked about chat GPT and how bad chat GPT was with hallucinations.
And so just to be fair, let's throw the same type of question at it.
I'm going to ask them to calculate a $100 bonus for each record where the quantity is a prime number.
Now, if you remember when we asked Jet GPT to do this, they said, oh, it's super easy.
Just use the is prime function, which of course does not exist in Google Sheets or in Excel or open office, but yet it hallucinated that it actually existed.
So Microsoft is trying to deal with this, okay?
They're refusing to do it.
Just 15 minutes ago when I tested this before the video, they came back with this equal is number, the match of the quantity, and then gave me an array constant with all the prime numbers from two to 97, which of course works great for all of the quantities that are less than 100, but it doesn't work.
And at that point, I kind of snarly asked it what is the minimum and maximum quantity?
And it knew that the quantity goes from 11 to 1 99.
And then I'm thinking, well, why would you even offer me that formula?
And now 15 minutes later, they're no longer offering me the formula.
Good for them.
Alright, let's do this one.
So we have daily dates here, right?
Let's create a pivot table with month and year.
This is tricky.
That means that they have to be able to group those daily dates up to months and years and put revenue in the pivot table.
All right, let's just go ahead and add this to a new sheet.
Yeah, that's right.
That's good.
They rolled it up correctly.
I really would've liked to have years going across, but I can easily just do that, right?
Moved years to the column section and I get my answer.
I tried create a pivot table with years across the top and months in the columns in the rose area, and total revenue as values format.
The revenue as currency can't do multiple things at once, so there's no way to get that to format as currency.
Let's try the rest of it though.
Yeah, this one's really strange.
I didn't mention anything about sector and this one just doesn't make sense at all.
So it'd certainly be one where I'd want to send a dislike and explain why.
Let's come back and some of the things that work really well is highlight all revenue over 20,000.
Actually set up conditional formatting on that column.
That's pretty cool.
So it applied to kind of ivory yellow color to G two to G 5 64 and knew where revenue was for a sale value greater than 20,000.
And if we come here to home conditional formatting and manage the rules, We can see that it did it sell value greater than 20,000.
That's great, that's great.
But then I said, alright, let's try this.
We'll come back to copilot and highlights all dates that fall in February.
All right?
And they somehow interpreted that as I wanted all of the dates in this month, and when I come here and look at that conditional formatting, they've set it up to be equal to this month, which is not.
It's December, I'm not even close to February, and it didn't give me December of last year.
I was looking for all Februarys of this year and last year, and that's not something that it appears to want to do.
Hey, this is really amazing.
It's getting a lot of very hard formulas, which is awesome.
Sort and filter, let's just say sorts by product within region.
And it did it correctly, tells me that it did it correctly.
Show only records for IBM there.
I didn't even tell it that it's in the customer column, but look, yeah, hey, I mean it's super impressive just being able to type natural language things and have it work.
Now again, as I said at the top, thanks to Microsoft for giving me access.
My company is way too small to be included in the preview, but yet they let a lot of MVPs give this a shot, and I really appreciate that.
I understand we're in preview.
Literally the first rollout of this was yesterday, right?
So they haven't had a chance to deal with any feedback yet.
I know that this will get better.
Copilot also has other features in Outlook and PowerPoint and Word, and then there's that amazing business chat that's going to be able to use all of the data from your company, and that's going to be astounding.
But for right now, today in this video, I just want to take a look at the Excel features.
And I got to say, it's promising.
It is not perfect yet, but it can do a lot of things that are fairly advanced, fairly advanced with some limitations.
It has to be stored in OneDrive.
It has to be a table.
It can't highlight the whole row yet.
It can only work on one particular column, but still it is fairly impressive.
Well, hey, I want to thank you for stopping by.
We'll see you next time for another net cast from Mr.
Xcel.
If you like these videos, please down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
Thanks - hey, this is great.
Microsoft gave us a sneak peek at the new copilot feature.
I didn't think I'd ever be able to see this.
They talked about it only being for enterprises with more than a hundred customers or something like that, but I have it.
It's pretty cool.
It's not perfect, but it's pretty cool.
Thought you'd like to see it.
Let's take a look.
Okay, so I'm here in Microsoft Edge.
I'm working in Excel online just because it's easier at this point.
The data has to be in a table for copilot to work.
Over here on the right hand side, the copilot icon opens the copilot pane.
It says that it can add formula columns, it can highlight data, it can sort and filter and it can analyze, and frankly, this analyze is very similar to the analyze data that we've had.
All right, so this is a data I use all the time.
Region, product, date sector, customer quantity, revenue cost of good, sold and profit.
So the first thing I said, let's just see if we can add a gross profit percent column.
So calculate GP percent.
I'll tell you the first time I tried this, I figured I actually had to spell out gross profit percent and it put in a column that had a three word header and then all kinds of bad things happened after that.
Couldn't understand anything.
So I tried it again with calculate GP percent and check it out.
Over here it's working on it.
Generating formula column suggestions.
It actually takes some time.
Sometimes it says it's trying to understand the data.
Okay, so here's a suggestion for you.
Calculate the gross profit margin for each transaction by dividing the difference between the revenue and cost of goods sold by the revenue.
Shows me what the formula is.
Now that firm is correct.
It would be just as easy to have done profit divided by revenue.
It'd be a shorter formula, but that's okay.
I'll take this.
Explain the formula.
It tells me that it's going to subtract cost of good sold from revenue, divide the gross profit by the revenue column, and then format as a percentage value with two decimal places.
When I hover over insert column, it shows me what it's going to look like.
Although the preview is left aligned, that would be horrible.
They're not really going to do that, are they?
No.
No, they don't really do that.
Done.
I had a GP present to column J.
I can click undo.
I can give them a thumbs up, I can give 'em a thumbs down and hopefully someone at Microsoft is looking at these to see whether they got it right or not.
Alright, another one I do all the time in my seminars is using if with.
And so let's calculate a bonus Of 2% of revenue when we'll say product is A, B, C, and GP percent.
The calculation we just created is in the top 20%, right?
That's a pretty hard formula we're going to have to use if with and checked column B, and then do something clever over there in column J, a press send.
It's working on it.
Understanding your data almost there.
Ah, sorry.
I can't do multiple things at once.
For the best experience, add one request at a time.
Lemme pause the video here.
Okay, let me try and ask it a different way.
Calculate a bonus of 2% of the revenue.
If the product is A, B, C and the GP percent is in the top 20%, which feels pretty much exactly like what I asked for there I All right, there it is.
So it knows to use the and function inside of if it's not nesting, the F statements.
I love that it was able to generate the percentile function.
I had to look for everything above 80%.
This formula is the right formula and it's so strange.
Why did it work here?
The only thing that I really see that's different is I capitalized revenue there and didn't capitalize it here.
GP percent is in the top 20%.
I don't know.
All right, strange.
Hey, remember it's a preview, right?
So we'll insert the column.
That one worked great.
That one worked great.
I am not a big fan of tables and they're forcing me to use a table here, so I thought I'd just kind of do something that's hard for tables to do.
Let's calculate a running total for revenue generating formula column suggestions.
Normally I would snip this out, but I'm leaving it in here at real times just so you can see how long it takes to actually think about this and come back with an answer.
Actually, I don't like this one at all.
The data is sorted by date, but what they're going to do Is not going to be correct.
See, everything for January 1st is going to be 52, 7 7 1.
I didn't ask for a year to date number or anything like that.
And it's funny, in earlier tests it gave me a different formula.
Lemme do something like sort by customer so it's not even sorted by date and see if the same request comes back with a better answer.
I know that I've gotten a better answer in earlier testing.
No.
Wow.
Yeah, that one's disappointing because I know that it's gotten it and it's gotten it.
Before, it used a really weird index syntax, which was not efficient at all, but it worked.
It worked.
Okay.
Now back in March we had a few videos that talked about chat GPT and how bad chat GPT was with hallucinations.
And so just to be fair, let's throw the same type of question at it.
I'm going to ask them to calculate a $100 bonus for each record where the quantity is a prime number.
Now, if you remember when we asked Jet GPT to do this, they said, oh, it's super easy.
Just use the is prime function, which of course does not exist in Google Sheets or in Excel or open office, but yet it hallucinated that it actually existed.
So Microsoft is trying to deal with this, okay?
They're refusing to do it.
Just 15 minutes ago when I tested this before the video, they came back with this equal is number, the match of the quantity, and then gave me an array constant with all the prime numbers from two to 97, which of course works great for all of the quantities that are less than 100, but it doesn't work.
And at that point, I kind of snarly asked it what is the minimum and maximum quantity?
And it knew that the quantity goes from 11 to 1 99.
And then I'm thinking, well, why would you even offer me that formula?
And now 15 minutes later, they're no longer offering me the formula.
Good for them.
Alright, let's do this one.
So we have daily dates here, right?
Let's create a pivot table with month and year.
This is tricky.
That means that they have to be able to group those daily dates up to months and years and put revenue in the pivot table.
All right, let's just go ahead and add this to a new sheet.
Yeah, that's right.
That's good.
They rolled it up correctly.
I really would've liked to have years going across, but I can easily just do that, right?
Moved years to the column section and I get my answer.
I tried create a pivot table with years across the top and months in the columns in the rose area, and total revenue as values format.
The revenue as currency can't do multiple things at once, so there's no way to get that to format as currency.
Let's try the rest of it though.
Yeah, this one's really strange.
I didn't mention anything about sector and this one just doesn't make sense at all.
So it'd certainly be one where I'd want to send a dislike and explain why.
Let's come back and some of the things that work really well is highlight all revenue over 20,000.
Actually set up conditional formatting on that column.
That's pretty cool.
So it applied to kind of ivory yellow color to G two to G 5 64 and knew where revenue was for a sale value greater than 20,000.
And if we come here to home conditional formatting and manage the rules, We can see that it did it sell value greater than 20,000.
That's great, that's great.
But then I said, alright, let's try this.
We'll come back to copilot and highlights all dates that fall in February.
All right?
And they somehow interpreted that as I wanted all of the dates in this month, and when I come here and look at that conditional formatting, they've set it up to be equal to this month, which is not.
It's December, I'm not even close to February, and it didn't give me December of last year.
I was looking for all Februarys of this year and last year, and that's not something that it appears to want to do.
Hey, this is really amazing.
It's getting a lot of very hard formulas, which is awesome.
Sort and filter, let's just say sorts by product within region.
And it did it correctly, tells me that it did it correctly.
Show only records for IBM there.
I didn't even tell it that it's in the customer column, but look, yeah, hey, I mean it's super impressive just being able to type natural language things and have it work.
Now again, as I said at the top, thanks to Microsoft for giving me access.
My company is way too small to be included in the preview, but yet they let a lot of MVPs give this a shot, and I really appreciate that.
I understand we're in preview.
Literally the first rollout of this was yesterday, right?
So they haven't had a chance to deal with any feedback yet.
I know that this will get better.
Copilot also has other features in Outlook and PowerPoint and Word, and then there's that amazing business chat that's going to be able to use all of the data from your company, and that's going to be astounding.
But for right now, today in this video, I just want to take a look at the Excel features.
And I got to say, it's promising.
It is not perfect yet, but it can do a lot of things that are fairly advanced, fairly advanced with some limitations.
It has to be stored in OneDrive.
It has to be a table.
It can't highlight the whole row yet.
It can only work on one particular column, but still it is fairly impressive.
Well, hey, I want to thank you for stopping by.
We'll see you next time for another net cast from Mr.
Xcel.
If you like these videos, please down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
