Analyze data in Excel using Copilot - Microsoft Copilot Tutorial

- In this video, we will use Copilot inside of Excel to analyze data in a spreadsheet. It's important to remember if you have a Copilot Pro subscription, but do not have a Microsoft 365 subscription, you will be limited to the Excel web app. But if you do have Microsoft 365 subscription and you do use the desktop application, there is an important additional detail in Excel. So I'll open Excel on my computer. Of course, keeping in mind that you do need a Microsoft 365 subscription to install the full application. I'll open a spreadsheet that I have stored on my computer and I do see the Copilot button in the Home ribbon. But when I click that to see the Copilot panel, it says that I cannot work with Copilot because AutoSave is turned off. The AutoSave option is available in the top left corner, but if I try to turn that on, it prompts me to upload this file to my OneDrive. So here's the point. You can only use Copilot in Excel if the file you're working on is stored in one of Microsoft's cloud storage tools like OneDrive. So if you are using the Excel web app, you won't have this problem because the web app only works with files that are stored online. But if you are using the full desktop application, you will need to upload the spreadsheet to OneDrive first. Then in Excel, when you go to open a file, there's an option to go to your OneDrive there. I do have a copy of that inventory spreadsheet on my OneDrive, so I'll open that and I'll make sure that the AutoSave feature is turned on, and now when I open the Copilot panel, it will work for me. So let's see a few things I can do here. There are some suggested prompts at the top. These are great to get started and learn how Copilot works, but for now, I'll just write a few of my own prompts using plain language. Now, this spreadsheet contains a product inventory, and one of the columns in this sheet is Suggested Retail. So I will ask Copilot to sort by retail price. Now it does not immediately sort the sheet. Instead it analyzes the data, then shows the action that it thinks I want, and this gives me the chance to check what it's suggesting. It looks right so I'll click Apply, and now the table is sorted so the items with the lowest retail price are at the top. And I can always ask follow-up questions and Copilot will remember the context. Next, let's try a filter. In the Category column, I can see that there are lots of different types of products in this inventory sheet. I see inverters, batteries, solar panels, and more. So I'll ask Copilot to filter to only show batteries. Again, it shows me the suggested change, and I'll click Apply. And now it's only showing items that have batteries listed in that Category column. And it's still sorted by retail price, so you can combine sorting and filtering. I'll reset by telling Copilot to clear the filter and I'll apply that change. And let's finish by asking Copilot to analyze this data to show me some specific information. There are several items that have the category of inverters, several batteries, several different types of solar panels and so on. So I'll ask it to show me the total number of units from each category. And now it is showing me this chart, and this is a pivot chart. Now, I don't really know how to make a pivot chart, but Copilot does. I could have asked Copilot specifically to make a pivot chart and that also would've worked, but I didn't need to use that language. It understood the analysis I was asking for in plain language and Copilot suggested a pivot chart. If I wanted a pivot table instead, I could ask for that in a follow-up question. So I'll ask, "Give me that in a pivot table." And here is a pivot table. I want to keep this so I can click this button to add it to a new sheet in this document, and now I have a separate sheet in this document. In that new sheet, I have the table. And when I click somewhere in that table, Excel opens the pivot table controls on the right. So this is a pivot table. If you know how to work with and modify pivot tables, you can do that right here, but you don't have to because Copilot was able to analyze my data based on my plain language requests. I'll click the tab to go to the original sheet in this document, but my pivot table is still saved in that new tab. And from here, I encourage you to experiment with your own prompts or try the suggested prompts to get an idea of what Copilot can do in excel.