Add Formula Columns

Quick reference

• 00:04 In the previous lesson, we were looking at how we can use Copilot to help us out
• 00:08 when it comes to formatting our table data.
• 00:12 And if you're wondering, well, I want to know how I can use it to construct
• 00:16 formulas, well, this is the lesson for you.
• 00:19 And we're going to start out by talking about adding columns.
• 00:22 Now, remember that Copilot currently can only work with data within a table.
• 00:27 So if you're expecting it to be able to output formulas to different cells outside
• 00:31 of your table, it's not going to be able to do that at this stage.
• 00:35 As I keep reiterating, we're still in the very early stages of Copilot.
• 00:39 So, Copilot currently can only work or can only construct formulas and
• 00:44 add them as columns to the current table.
• 00:47 So, let's take a look at that in action first of all.
• 00:50 Now if we take a look at this data set, I can see that I have a Price per Unit
• 00:54 column, I have a Units Sold column, and then I have an Operating Profit column.
• 01:00 But what I don't have is a Total Sales column.
• 01:04 And the Total Sales would be a very simple calculation of Price per
• 01:09 Unit multiplied by Units Sold.
• 01:12 So I'm going to use Copilot and get it to add this column into my table.
• 01:17 So let's go down to our prompt area, type in our request.
• 01:22 So there is my prompt to calculate the Total Sales by multiplying the Price
• 01:27 per Unit column by the Units Sold column.
• 01:30 Let's see what copilot does with this.
• 01:34 Now you can see there it says, generating formula column suggestions.
• 01:38 So I'm quite interested to see what it comes up with.
• 01:40 And check out what it's given us.
• 01:42 If we scroll up a little bit, you can see it says here's a suggestion for you,
• 01:47 calculates the total sales for each row in the table by multiplying the price per
• 01:51 unit by the number of units sold.
• 01:54 And then underneath it's given us the formula it's going to use.
• 01:59 And I could see by looking at this, that this looks to be correct.
• 02:03 Now notice underneath we have a little explain formula dropdown, and
• 02:07 this is going to go through exactly what the formula above is doing to help
• 02:11 you understand.
• 02:13 So this is actually a brilliant little feature, particularly if you have
• 02:17 received a spreadsheet from a colleague or maybe somebody else.
• 02:20 And maybe they've got a formula that's quite complicated in a cell, and
• 02:24 you don't really understand what it is doing.
• 02:27 What you could do is click on that cell and
• 02:30 get Copilot to explain what the formula is doing to help you understand.
• 02:35 So from a learning perspective, this is brilliant.
• 02:38 Now what we want to do here, is we want to add this formula as a column in our table.
• 02:43 And notice at the bottom we have Insert column.
• 02:46 Now just before we do this, as a side note,
• 02:49 it's also worth noting that when we get any of our results in copilot,
• 02:52 we get the opportunity to give it a like or give it a thumbs down.
• 02:56 So this is going to feed back into the system and really help Microsoft
• 02:59 understand which features are working and which features aren't.
• 03:04 So I'm going to give this a little thumbs up.
• 03:06 And you can see immediately it takes me to a little pane where I
• 03:08 can give some feedback.
• 03:10 Now, you might not want to be doing this in the middle of a formula
• 03:12 when you're busy, but this is really going to help the product going forward.
• 03:17 So I'm not going to include a screenshot.
• 03:19 It's asking me if I want to share my prompt and the generated response.
• 03:23 Now, I don't mind in this case, I'm going to say yes.
• 03:25 Of course, you could say no, maybe contact you about your feedback.
• 03:29 Well, yes, sure, why not?
• 03:31 Let's click on Submit.
• 03:32 So now that we've given our feedback, let's click on Insert column.
• 03:37 And you can see it's inserted that Total Sales column on the end there and
• 03:41 it's performed the calculation.
• 03:43 And if we click in any of these cells, much like any other formula,
• 03:46 you can see it up here in the formula bar.
• 03:50 Now it's always going to add the new column onto the end of the table.
• 03:54 So if you want this column in a different position, you could try and
• 03:57 get Copilot to move it for you.
• 03:59 A lot of the time it's just going to be quicker to move it yourself.
• 04:02 So I'm going to select the column and I'm going to hold down Shift and
• 04:06 drag it into the correct position.
• 04:09 We should find is that Total Sales is just Price per Unit multiplied by Units Sold.
• 04:15 What are the calculations might we want to perform on this dataset?
• 04:19 Well, I can see here the Operating Profit for each record in this table.
• 04:25 And maybe I want to find out what percentage
• 04:28 the Operating Profit is of the Total Sales.
• 04:32 So let's ask Copilot.
• 04:36 So I've written a longer prompt here and I've been pretty specific.
• 04:40 I've said add a column that calculates the operating margin based on
• 04:45 the Operating Profit and Total Sales.
• 04:48 Use percentage format with zero decimal places.
• 04:52 Let's send it through and see what Copilot can do.
• 04:55 So let's take a look at what it's come up with.
• 04:58 So it says, this formula calculates the operating margin for
• 05:02 each sale by dividing the operating profit by the total sales, sounds good so far.
• 05:07 This provides insight into the profitability of each sale in the table.
• 05:12 And then underneath, we have the formula Operating Profit divided by Total Sales.
• 05:17 And once again, if we expand explain for formula,
• 05:20 it's going to give us a bigger explanation.
• 05:22 So I think this looks pretty good.
• 05:24 Now, if you want to preview this before you actually insert it,
• 05:28 just hover your mouse over Insert column.
• 05:31 And it's going to ghost down, it's going to show you a preview in the table
• 05:35 as to what the column is going to look like.
• 05:38 So it's not until you actually click on Insert column,
• 05:41 then it's going to put it into the table.
• 05:43 So that is exactly what I want, let's click on Insert column.
• 05:47 And once again, I'm just going to move this column.
• 05:51 So we are looking good so far.
• 05:53 What else can I do?
• 05:55 Well, maybe I want to add a total row to the bottom of this table.
• 06:01 So I've just pressed Ctrl+Down to jump to the bottom.
• 06:04 I want a nice total row just here.
• 06:06 So I'm going to ask Copilot to turn on the total row for this table.
• 06:14 Let's hit Enter.
• 06:15 And there we go, I can see we have a row added.
• 06:17 Let's scroll across, make sure it is the total row, which it is.
• 06:21 And now, what I can do if you're not familiar with total rows is,
• 06:26 I can specify which rows I want to total.
• 06:30 They don't necessarily just have to total the values, you can click
• 06:33 the drop down and choose Average, Count min, Max, whatever you like.
• 06:37 Now I can see over here it's put a number in it.
• 06:39 Column that doesn't contain any values.
• 06:41 So we're going to change that.
• 06:42 We're going to say none and maybe I'm interested in totaling the total sales.
• 06:48 Let's do a sum and I can double-click to widen that column out.
• 06:51 And I'm also interested in the sum of units sold and the operating profit.
• 06:57 Let's do that one as well.
• 06:59 So once again,
• 07:00 we going to Copilot to do a calculation indirectly using that total row.
• 07:05 Ctrl +Up arrow to jump to the top of our dataset.
• 07:08 Now the final thing I just want to show you is how you can ask questions
• 07:12 about formulas to Copilot and get an answer.
• 07:15 For example, I could click in my prompt area, what is the syntax of
• 07:19 that the SUMIFS formula if I want to calculate the units sold by region?
• 07:23 Let's hit Enter.
• 07:24 Now, this time it's given me an explanation.
• 07:27 It's showing me the syntax of SUMIFS and it's telling me what I need to do
• 07:31 to change this formula so that I'm calculating Units Sold by region.
• 07:36 And you can see then underneath it has exactly what that's going to look like.
• 07:41 So the point here is that you can also use this prompt area to ask questions,
• 07:46 get feedback, get help as to what type of formula you need to use on your data.
• 07:51 So those are some of the ways that you can use Copilot to help you out when it
• 07:56 comes to formulas.

Lesson notes are only available for subscribers.