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.
