Hello and welcome to working with Microsoft 365 Copilot in Excel. My name is Lee Mucciarone and I'm a Microsoft 365 content creator for Cloud Academy. I have four years of IT experience working for Microsoft with two years of specialized experience as a customer training specialist training users on Microsoft tools.

Today we're looking at Microsoft's new AI assistant, Microsoft 365 Copilot. Specifically, I'll be taking you through Microsoft Excel and how Copilot can improve your workflow with its powerful generative capabilities. So let's jump right into Excel and see what we're working with.

Here we are in Excel with a sample workbook full of data. This is all demo data which I got right off of Microsoft's web page, so if you want to follow along, I have linked the page and the document in the Additional Materials section.

Although, due to the nature of generative AI capabilities, it is important to note that Copilot responses can vary depending upon a variety of different factors, including the language you use, the data set you're using, and more. So even if you are following along, you might not necessarily get the exact same responses as I do. But with that out of the way, let's get into the demo.

As is consistent with all of the Office tools, you can find the Copilot option in the top-right of your ribbon within your Home tab. This will bring up the all too familiar prompt window where we can interact with Copilot.

You'll notice that upon opening Copilot, it's grayed out. That's because Copilot in Excel functions slightly differently than other apps. It only works on data held within a table, so before we do anything, we need to ensure that our data is within a table. Currently my data is already set up as a table, but if yours isn't or you're using a different set of data, just convert it to a table and Copilot will be good to go.

Now that we have Copilot enabled within our table, we can start interacting with it. Like other apps, we have our main suggestions on how Copilot can assist us within Excel: Add formula columns; highlight; sort and filter; and analyze. But let's start off with some basic functionality and then move into some more unique uses.

So we'll just begin with highlighting. Copilot has the ability to highlight certain data within a table based on a prompt that you provided. As we can see, I have a bunch of different columns with information about products, units sold, sales, profits, and more.

So if I wanted to get a better visual representation of the top profit numbers within this table, I can ask Copilot to “Highlight the top 10 of ‘Net Profit’”. Copilot will take a look at the data in that column, and if I scroll through the sheet after a moment, we'll see that it has indeed highlighted the top 10 numbers within the Net Profit column.

But let's say that I want to highlight those numbers in blue, and instead of the top 10, I want the top 10% of the net profit numbers. So I'll quickly undo that and ask Copilot to “Highlight the top 10% of ‘Net Profit’ in blue”.

Again, Copilot will take a moment to look over the data and then it will highlight the top 10% of the numbers in that Net Profit column with the blue color. The really cool thing about this is that it actually creates the conditional formatting for the column.

So I can actually go into the Conditional Formatting options and click on “Manage Rules” to see that there is a top 10% condition and clicking on that opens up the options for the formatting and how it applies to the cells within that column. But we'll closeout of this now. I just wanted to showcase that it does indeed create that formatting option.

Actually, this is a good point to talk about the Change topic button. Generally, I have found that whenever you're moving into a new task with Copilot, you should almost always click the “Change topic” button. This is effectively a reset on the AI to help it focus on a completely new prompt. It's not always necessary, however, I find that Copilot seems to respond better when consistently using that Change topic button.

So, we'll click that now and then move into our next prompt. Like always, Copilot provides us with suggestions. So let's actually ask Copilot for some suggestions about formulas. This is effectively Copilot analyzing the data to provide us with a formula that it believes would be of assistance to us in some way. While this isn't exactly what I was looking for, it does give us a good idea of Copilot's capabilities here.

But I do have a formula in mind, and I'm not super sure how to make it. So let's undo this and ask Copilot to make it for us. I want to compare my cost of goods and services to my sales. So I'm going to ask Copilot to “Add a column to compare ‘Sales’ to ‘COGS’” which is my cost of goods and services column.

After a moment, Copilot generates a new formula which it shows us and then asks if we want to add the column and if I didn't necessarily understand what the formula was for, I can click on the “Explain formula” button to get a better understanding of what it does.

As we can see, it compares the value of Sales and COGS columns using the IF function where if sales is greater than COGS, it will return sales greater than COGS, whereas if sales is less than COGS it will return sales less than COGS. While this is pretty cool, it's not exactly what I was going for. So let's be a little bit more specific with our prompt.

I will again click the “Change topic” button just to refresh Copilot and then write in a more specific ask. Let's assume I want the column to be a taxed profit column, subtracting the cost of goods and services from the total sales and accounting for a 5% tax on top of that end result. So I'll ask Copilot to “Create a column of ‘Sales’ minus ‘COGS’ and subtract 5% from the total for taxes named ‘Taxed Profit’”.

As you can obviously see this is a much more specific prompt, but it is a great example of how powerful Copilot can be when you understand how it interprets your prompts. Now Copilot has returned a formula for tax profit which looks pretty good to me, but just to verify we can check the “Explain formula” option again. According to Copilot, this formula subtracts the value of COGS from Sales and multiplies the result by 0.95, which would equate to the 5% tax, which is exactly the kind of formula I'm looking for.

So I can click on “Insert column” and Copilot will then insert a new Taxed Profit column right into our table. And similar to the conditional formatting I showed you earlier, I can click on any cell in this column and see the formula displayed in the formula bar up here, showing that Copilot is properly formatting all cells within that column and allowing us to change the formula if needed.

Now let's go even a step further than that. I want to see what percentage of gross sales is taxed profit. So let's see if Copilot can work with data that it has just generated. I'll write out the prompt: “Create a column that shows ‘Taxed Profit’ as a percentage of ‘gross sales’ named ‘Profit Percentage’”.

And after a moment, Copilot creates our profit percentage formula, which we can review if we want. But it does look good to me. Just quickly here, so I can click on “Insert column”, and after a moment we have our percentages just like we wanted.

But this isn't necessarily the easiest thing to look at, and I'm a pretty visual person. So let's ask Copilot to create some data bars for those percentages. I'll write the prompt, add data bars to the profit percentage column, and Copilot will be off to work. After a moment, I have some great data bars which makes this much easier to digest than just the raw numbers. And at this point I'm pretty happy with our data set.

So now it's time to start making the data look good and organizing it. Let's change topic on Copilot again and ask it to “Sort the data by the highest to lowest ‘Taxed profits’”. Now that our data is sorted, we can see our top profit numbers next to our profit percentages. But one of the cooler things Copilot can do is to summarize information.

I can ask it to give me a summary of this data and Copilot will give me a blurb about the information within the table and usually provides a really nice pivot chart that I can insert directly into a sheet. And even though what it provided for me here isn't exactly super helpful, it does lead me into the creation functionality.

Copilot really likes to create graphs and pivot tables, so let's ask it to “Create a graph of the ‘taxed profit by country’”. Again, it takes a moment, but just like that, Copilot created a clustered bar chart that we could insert into a new sheet.

But this isn't exactly what I wanted, so let's undo that and try again, but more specific to what I'm looking for: “Create a graph of the taxed Profit per month by country”. This way we breakdown the data by month and location to get a better idea of when and where that money comes from. This chart looks a bit much and we could see it did create another clustered bar chart, but let's add it to a new sheet and take a look.

It looks pretty nice and it might initially be overwhelming, but that's kind of the benefit of it being a pivot chart. We can actually click on the chart itself and adjust the fields to get more specific information about the data Copilot put into the chart.

As you can see, Copilot is currently using the Country, Taxed Profit, Years, and Months fields. But I think Months is a bit specific, so let's uncheck that and just leave it as Year instead. And that already looks a lot better. But let's add some more charts. Actually, let's see what Copilot thinks.

Let's go back to our original sheet and reset Copilot with our Change topic button. I sort of alluded to this earlier, but Copilot pulls insights from the data which it uses to create these charts. Clicking on “Show data insights” will give me the same chart it gave me earlier. And this is great, but I want to know all of the insights that Copilot has for me.

Copilot actually even has a suggestion prompt to “Add all insights to grid”. So let's click that now and see what Copilot comes up with. After a moment we'll be brought to a new sheet that has all of the different insights and charts that Copilot has created for us. And like before I can click on any of these to change the fields of any chart, but for now I think I'm happy with this.

But those are the basics of Copilot within Excel. As with every other app, there is so much functionality that is impossible to cover everything Copilot can do. So I highly suggest getting in there and messing around with it for yourself to get a better idea of its capabilities. Thank you very much and have a great day.

Working with Microsoft 365 Copilot in Excel

About

Learning Objectives

Intended Audience

Prerequisites