You Won’t Believe What Excel’s Copilot Can Do! (new updates)

So, Copilot in Excel recently got some updates  that blew my mind, and I want to share these with you. So, if you have Microsoft 365 and you have a Copilot license, you are going to see Copilot up here in the Home tab. The moment you select it,  the sidebar is going to open up, but we need to have AutoSave turned on to be able to use  Copilot, so we are going to turn it on here.

Now, we are able to use it on our data. The first  thing that amazed me was Copilot's ability to summarize text. If your data involves a lot of  freely written text, like feedback or reviews, you know how time-intensive it is to read each  one and decide what to do with it.

For example, here we have A1A Car Wash Reviews. Thumbs up if  you know where that comes from. We have reviews like "Great place, super clean and fast service"  – that's a positive review.

"Terrible experience, the owner is never reachable" – negative. Problem  is that the owner is not reachable. Then we have mixed reviews like "Good service, but I felt  rushed.

Not sure if I should come back. " Neutral reviews like "Not sure what the fuss is about;  it's just a car wash. Kind of sketchy place, owner was barely around.

" Notice there  is a pattern here: the owner was barely around; the owner is never reachable. But  you can see how time-intensive this is, right? If you had to read all of these reviews  – and here we just have 96 rows – it is going to take a lot of time to decide what to do with this  and to find improvement suggestions and potential.

Copilot makes this process super easy. All we have  to do is ask it a question. You can also turn on your microphone and just speak the question.

I'm  just going to type it in. So, what I wanted to do is summarize the reviews. Whenever you're  working with text, it's always good to use the word "summarize" in your prompt, and we're going  to send this off.

Notice also, this data set isn't formatted as an official Excel table, but Copilot  has picked up the range here and is analyzing this data, finishing up, and here we have our summary.  We have Positive Feedback: Many customers praise the cleanliness, fast service, and friendly staff.  Some highlighted specific positive experiences, such as their car looking brand new and being  satisfied.

Negative Feedback: Several customers express dissatisfaction with the service,  citing issues such as long wait times, high prices, and difficulty finding the owner. Mixed  Reviews: some reviews were mixed, with customers acknowledging good service or clean cars  but mentioning drawbacks like feeling rushed. Suggestions for Improvement: customers  suggested improvements such as better management presence and more employees.

General Impressions:  this is great feedback. If you're happy with this, you can copy this to the clipboard, paste it in  an email, and send it to your boss. If you also want to dig deeper, you could ask it to, for  example, "summarize the improvement suggestions.

" This is going to give us more detail and more  things to act on to improve our car wash service. So, here we have things to improve: Owner Presence:  Many reviews mentioned the owner's absence; Staffing Issues – understaffing is a common  concern. There are also pricing concerns, service speed, and facility and equipment.

Lots of great  feedback, and all we had to do was ask. Another thing Copilot can do really well is show insights  on your data. For example, here we have some HR data for a fast food chain, and we quickly want to  get some insights.

I'm going to bring up Copilot, go to the box here, and just type in "show me  insights on the data. " Copilot is analyzing, and I get "Salary by Performance Rating — Average  of Salary in Thousands. " So, we have performance rating here and the average salary.

Well, that's  pretty cool, but I want to get another insight, so I'm going to select "Can I see another  insight? " and I get "Salary has outliers at hire date" – that's the date the fast food  chain was opened and on this date. Now, if I like this insight, I can add it to the sheet,  or maybe I'm interested in all the insights, and I want to add all insights to the grid. 

So, I'm going to go with that. And then this way, I can just select the ones that I like the  most. So these are the ones we saw before, we have "Owner has noticeably higher salary" – okay,  yeah, that makes sense.

This is pretty cool: "Frequency of salary," and down here we have "For  Role: Maintenance Technician, Salary increases over time. " This is pretty cool, and all we  had to type in was "show me insights on data. " Adding formula columns is another thing Copilot  can do really well.

If I scroll to the top here, we can see we have some predefined actions.  One of these is to create formulas. We can get Copilot to suggest a formula column. 

Now, if we take a look at my data set, it's pretty simple. We just have HR  data: name, role, hire date, salary, and performance rating. Now, what type of column  could Copilot suggest?

Well, let's find out. It's suggesting adding a column for "Years of Service. "  This calculates the number of years each employee has been with the company.

This is actually great.  I would love to have this information. Now, I can see the formula that it's using here.

I can get a  preview of the results, and if I'm happy with it, I can insert the column. But actually, I would  like to have the results with one decimal place, so let's see if we can do that. I need  one decimal place – see if it actually understands what I mean.

It seems like it does.  It updated the formula, and it's now using the YEARFRAC function. That's exactly what I need. 

Now, I can hover over it, notice where it's going to insert it. I'm going to select this,  and my formula column is added to my data set. Copilot can also look up values from  another sheet.

When you start to use Excel, sooner or later, you are going to need to learn  your lookup functions because you might need to do something like this. On a separate sheet  called "Department," we have a unique list of roles and the department each role belongs in.  Now, what we want to do is grab this department, which, notice, is on the left-hand side  of the row, and bring it over here.

So, depending on the role the person has, we want  to see the respective department. We need to look it up from the other table. Let's get  Copilot to do this for us.

I'm going to ask it to create a column that shows the department  for each employee. I still need to give it more information because it doesn't know at this point  where to look it up, so let's tell it to look up the department from the Department sheet. Let's  see if this is enough information for it to get the task done.

Okay, so we have something. It is  using the XLOOKUP function. This actually looks great.

It's looking up C4 – that's my role here –  and it's looking it up from the Department sheet, and that's the range. That's great. I'm  going to go ahead and insert the column.

Now, let's move on to formatting. What if  you wanted to highlight duplicate values? Let's say we have the suspicion that some  of these names might be repeated.

So, I'm going to ask Copilot to highlight duplicate  values in the Name column. Let's see if we can do that. Looks like it can.

Sure, here's one  conditional formatting rule to review and apply. The rule is for duplicate values, and  it's going to apply this yellow color. I just want to change that – let's ask it to change  the color to light orange.

Is it going to understand what I want? Yeah, it's updated its  previous conditional formatting rule. So now, I'm going to go ahead and apply this,  and we can see the duplicate names are automatically highlighted.

Copilot has gone ahead  and added the conditional formatting rule for us. Now, how about highlighting an entire  row based on a single cell? Conditional formatting can get tricky when you want  to highlight the entire row of data.

So, let's see if Copilot can do it for us. In  this example, let's say whenever performance is "excellent," I want to highlight the whole row  in light green. So, let's ask it to highlight the entire row where performance is "excellent" in  light green.

Okay, so it's come up with a custom formula inside conditional formatting, and this  looks good. So, I'm going to go ahead and apply it, and now, wherever the performance rating  is "excellent," the entire row is highlighted. Now, I think these are really exciting  developments, and I'm curious to hear what you think.

Let me know in the comments  which Copilot feature you're excited about the most. Now, of course, knowing your  way around Excel is still important if you want to get the best results with  Copilot. So, if you're ready to improve your skills and invest in your skills, you  can check out our step-by-step courses, which guide you through mastering Excel's  most powerful features.

You're going to find the link below this video. Thank you for  watching, and I'll catch you in the next one.