Calculating the budget variance - Microsoft Copilot Tutorial

- [Instructor] In the previous video of this course, Copilot helped us create a project budget tracker structure, and working in that same file, let's now figure out the variance between the estimated and actual cost for each of the tasks in this tracker. We're going to start first by deleting the fictitious data that we have here in column E, so let's select all of these values and press Delete on the keyboard. Next, we're going to go to cell E2, and we're going to step through the syntax of what was included in the instructions in that last video when it was showing us things that we could do with this tracker. So the manual way for you to do this is to start by typing an equal sign just like you would for any formula or function. Come on over here to cell C2 and select it. You'll notice that now it is updated with the name of that column, which is Estimated Cost. When a table is created, that has a name assigned, and each of the columns in that table have names assigned for you to be able to use in calculations like this. Okay, so we've got this part. Let's go ahead and add a minus sign, and then select, you probably guessed it, cell D2. And then we're going to press the Enter key. And watch what happens to all of these empty cells when we do that. So we'll press the Enter key. Notice how it updates each of these with the appropriate variance. When you select one of these cells, if you take a peek up here at the formula bar, it is showing the formula hidden behind that value, and that's a great way for you to spot-check if this in fact has been set up properly. Okay, so that's one way to manually update a column calculation for variance within a table. Now, what if this column did not exist? Let's go ahead and select column E, right-click and delete it. We're playing out a different scenario. You have created this project tracker structure so far, but you don't have a column for the variance. Copilot can help you with this, and Copilot can help you with this in a couple of different ways, and let me show you how. With a cell selected in the table, we'll go to the Home ribbon and select Copilot. One of the things you could do is you could type in here, "suggest a column for this table" or "suggest a column," and variance likely would be one of the ones that would come back as a suggestion, and then you can insert that into the table. You could also enter something like this, "Calculate the variance for each task." Just a simple statement like this, we'll send it through to Copilot. We have some notifications as it's working its way through, and here is what Copilot is returning. Let's go up and take a closer look and see if this in fact is what we want. Looks like it is, so we can see the range that it looked through A1 to E11. There's one formula column to review and insert into column F, and that is variance. It gives us a summary of what this calculation does. There is the syntax for it, just like you saw earlier as we tried this out, and it's showing us a preview of what it would look like. Now, this is inserting it into column F. I could have been more specific by asking it to place it between column D and E, and it's pretty good about doing that as well. But we'll go with putting it in column F for now. Before I click on Insert Column, take a look at what's happening on my screen. I'm going to move my mouse away, notice how column F is empty. When I hover over Insert Column, it is showing me a live preview of what this will look like. If I like that, we'll select it, and there it is. And that is how you can manually calculate the variance for each task or get Copilot assistance with it.