Créer un dashboard Excel interactif avec Copilot (Full Transcript)

[00:00:00] Speaker 1: In this video, I'll show you how to turn your Excel data into a fully interactive dashboard using Microsoft Copilot. We'll have charts that automatically update, key metrics that track performance, and interactive filters that let you explore your data with just a few clicks. And here's the best part. Instead of building all of this manually, we'll simply tell Copilot what we want and it'll build the dashboard for us. I'm Kevin, and let's dive in. To get started, I have an Excel spreadsheet with sales data from my small business, the Kevin Cookie Company. It includes 500 orders from the past year with details like the cookie type, quantity, price, order date, and sales region. We'll use this data to build our interactive dashboard. If you'd like to follow along, I've included a link to this same spreadsheet in the description below. You'll need access to Copilot in Excel. I'm using Microsoft 365 Family, but the features available to you may vary depending on your subscription. Let's open Copilot to see what it can build for us. In the bottom right-hand corner, I'll click on the Copilot icon. Instead of manually creating charts, calculations, and interactive filters, we can ask Copilot to build the entire dashboard for us. Now, before entering my prompt, notice that I have allow editing selected. This lets Copilot make changes directly to our workbook. Now right here, I can also choose plan if you'd like to review what Copilot intends to do first, or right down here at the bottom, we have chat only if you just wanna ask questions without changing anything. Since we want Copilot to build our dashboard, I'll leave allow editing selected. Down below in the prompt field, I'll describe the type of dashboard that I want. I've included the prompt in the description if you'd like to follow along with me. Now, right over here, let's submit the prompt. Copilot will now analyze our data and figure out which metrics and charts to include. And look at that. Copilot has created an entire dashboard from just one prompt. We have a professional looking layout with key sales metrics right here across the top. We have the total revenue, fulfilled orders, average order value, and here we have the units sold. Now below that, we have charts showing monthly sales trends. If I scroll down a little bit more, we have revenue by cookie type. Here we have revenue by region. Down a little more, we have the order status mix, and here we have all of our top customers. And Copilot has taken care of all of this formatting and the layout for us. But what I think makes this really impressive, here at the top, this dashboard is fully interactive. Let's say that I only want to see sales from the West region. Over here, I'll filter it just to West, and look at this, all the different charts update to reflect just that region. Over here, let's filter again. Let's look at just chocolate chip, and maybe we look at the month of December. And here, again, it updates all of the charts and all the different metrics to reflect all the different filters that we selected. And right up on top, if I want to get the full picture again, over here, I can reset my filters, and there we are back to where we started from. Now, you're probably already well aware of this, but anytime you use AI to analyze data, it's always a good idea to double check the results. Right over here in the key metrics, Copilot tells us that we generated $41,586 in revenue from 424 orders. Like mama always said, there's money in the cookie business. Let's go back to our original data to verify those numbers. Down below, I'll click on the orders worksheet. Then right up on top, I'll select the header of this table, and up on the top toolbar, let's click on data, and over here, let's turn on the filter. Now, let's filter this just to the orders that are fulfilled. Now, click on this, deselect all these options, and let's select fulfilled. Then over here, I can see the order total for all of the fulfilled orders. I'll press control shift down arrow to select all of these orders, and right down below on the toolbar, I can see that the total is 41,586. That number probably sounds familiar. If I go back to the dashboard, here we can see that the numbers match up one for one. So in this case, Copilot got our key metrics right, but I think it's always important to check, especially when you're using the dashboard to make business decisions. Next, let's customize the dashboard to match my company's branding. One thing you'll notice is that Copilot created a different type of sheet for our dashboard. You can recognize it by the special icon next to the sheet name right down here. Now, unlike a traditional Excel worksheet, I can't directly edit individual cells or move the charts around on this sheet. Instead, I can ask Copilot to make the changes for me. So right over here, let's click on the Copilot icon again, and this time, I want to update this dashboard and give it a modern dark theme with blue and gold accents. So over here, I'll type in my prompt. Again, I've included this in the description below. And then over here, let's send that. And there we go. We've completely changed the look of our dashboard without having to rebuild anything. Our dashboard looks great, but what happens when we get new sales? After all, the Kevin Cookey company is not going to stop taking orders just because we built this beautiful dashboard. Let's see how easy it is to keep everything up to date. Let's head back to the original sales data and add a new order. Down below, I'll click on the order sheet. Microsoft just placed a massive order for 5,000 chocolate chip cookies. What can I say? They really like our cookies. At $2 each, that's a $10,000 sale. Not bad for the Kevin Cookey company. Now, let's head back to our dashboard and see what happens. Down below, I'll click on the dashboard. And look at that. The dashboard automatically updated to include the new order. Here, you can see that the revenue has increased by 10,000, and we can also see the impact on our monthly sales chart. Look at that massive spike in December. And as new orders come in, this dashboard will continue to reflect the latest data. And that's how you can turn your Excel data into a fully interactive dashboard using Microsoft Copilot. Again, if you'd like to try this yourself, I've included a link to the sample Excel workbook right down below in the description. And if you'd like to learn how to build an interactive dashboard the traditional way, I've included a step-by-step Excel dashboard tutorial right up there. Thanks for watching, and I'll see you in the next one.