In this video, learn to generate formula columns with Microsoft 365 Copilot in Excel. I hope you enjoy this clip from my full Pluralsight course on Microsoft 365 Copilot!
FULL Course on Copilot in Microsoft 365 Apps (Microsoft Office)
Video Summary
Here are the key points from the video:
- Using Copilot for Formulas: The speaker is excited about using Copilot in Excel to create formulas, such as calculating total cost per product or total profit for marketing campaigns in 2024.
- Practical Examples: They demonstrate asking Copilot to add a column that calculates the number of days between the date of an accident and the date of a claim, which Copilot does efficiently.
- Combining Data: Another example shows Copilot combining vehicle year, make, and model into a single column, making the data more concise and readable.
- Sample Data: The speaker mentions that Copilot can provide sample data for practice, which is useful for tutorials and experimenting with different prompts.
- Profit Calculation: They push the boundaries by asking Copilot to calculate total profit for marketing campaigns, which involves understanding that profit is revenue minus budget. Copilot successfully adds the column, showing which campaigns are profitable.
For more information, read the transcript blog below, or watch the video above!
Video Transcript
Our next task that we can use Copilot on is working with formulas. This is one of my favorites as I always have trouble creating Excel formulas, so I’m glad Copilot can help. We can ask Copilot to, for example, calculate the total cost per product in a new column. Another example is to ask Copilot to add a column that calculates the total profit for each marketing campaign in 2024. We can also work with it to add a column that calculates the number of days after the product launch event. Now that we have talked about the theory, let’s head back to the lab and see how we can work with formulas with Copilot in Excel.
We’re back in the lab. Let me open up the Excel file; it will still be our auto claims Excel file. By the way, I just wanted to mention that I will put the Excel file in the exercise files together with the prompt, so if ever you’re looking to do the same thing that I’m doing, you’re able to download it from there and also try the prompts yourself. But you know what I want to do? Let’s go here and let’s ask Copilot to add a column that calculates the difference in days between the date of claim and the date of accident. So you see here, I have a date of the accident and then I have a date of the claim. I want to see how long it usually takes people before they do it, and maybe I even want to see the correlation between whether it’s approved or not. So let’s add that column here, one thing at a time. So I’m simply asking Copilot to add the column and do the calculation for me. Let’s give it a few seconds and see what it comes up with. There we go, so Copilot finished. You can see here’s a suggestion for you: it calculates the number of days between the date of accident and the date of claim for each entry in the table. It shows me the formula, which is awesome. It will explain the formula for me as well, and I can add the column at the end. There we go, it added it for me, so now I have this data inside my table. Great!
I can also ask it to do another one here. Let’s say, add a column that combines the vehicle year, the vehicle make, and vehicle model. This way, I don’t have, like, say, Hyundai Elantra 2016 inside three different columns. I want to have a nice column that says 2016 Hyundai Elantra. So let’s go and ask Copilot to do that. It should be pretty fast again. It will show me what the formula it did is and then allow me to add it to the table. There we go, it finished. So again, if we go here, here’s a suggestion for you: it concatenates the vehicle year, make, and model into a single string to provide a more detailed description. There, and then I have what the formula is. See, only if I hover over it would I see what it looks like. This way, I can preview it, which is awesome. Let’s insert the column, and there we go, we have it.
Okay, last thing before we finish up, something else I wanted to show you. If I go and start a new sheet over here, you see if I start from blank, Copilot is still open here. In addition to the file that I gave you, you can also have Copilot in Excel give you sample data. This is how a lot of the videos you might see or tutorials that do not have data might just use the sample data that Excel gives us. If we do try an example here, see, we have information about marketing campaigns such as campaign owner, campaign name, launch date, campaign type, budget, revenue, and so on. Again, you can use many of the prompts we talked about, or you know what, just take this data and try and see what kind of questions you can ask it. So here, I just asked it to add a column that calculates the total profit for each marketing campaign. Now you see, I’m a bit pushing the boundaries here because Copilot would need to understand that profit is the revenue minus the budget. And there we go, you see it figured it out, and now I can add the column and I can see which ones are profitable and some of them were not profitable at all. But if ever you want to try it out, you can always just, in a new tab, get Copilot in Excel to give you data. If not, you will always have the auto claims sample data in the exercise files. But this is it for this demo on how we can use Copilot in Excel to generate formula columns. It works really great. This is also the last demo for this module on Excel, so let’s head back to the slides and finish off the module.
