- 0.50x
- 0.75x
- 1.00x
- 1.25x
- 1.50x
- 1.75x
- 2.00x
We hope you enjoyed this lesson.
Cool lesson, huh? Share it with your friends
About this lesson
Learn how to perform calculations using formula columns with Copilot in Excel.
Exercise files
Download this lesson’s related exercise files.
Add Formula Columns53 KB Add Formula Columns - Solution
5.8 MB 02-03-Add Formula Columns-Start.xlsx
550.3 KB 02-03-Add Formula Columns-Finish.xlsx
772.9 KB 02-03-Sales Data Exercise-Start.xlsx
18.1 KB 02-03-Sales Data Exercise-Finish.xlsx
18.8 KB
Quick reference
Add Formula Columns
Use Copilot to add formula columns to our table to perform calculations.
When to use
We add formula columns whenever we want to perform a calculation using data in our Excel table.
Instructions
Copilot can be used to help us with formulas when working with Excel. However, Copilot can currently only add formula columns to a table, a bit like calculated columns in Pivot Tables. Copilot cannot be used to generate formulas which we can then copy and paste into any cell in the workbook.
Let's explore some of the ways we can work with formulas using Copilot.
Add a Total Sales Column
Our dataset has a 'Price per Unit' and a 'Units Sold' column but we don't have a 'Total Sales' column. This would be a simple calculation of Price per Unit * Units Sold.
We can use Copilot to add a 'Total Sales' column.
- Type the prompt: 'Calculate the Total Sales by multiplying the Price per unit column by the Units Sold column'.
- Press Enter.
Copilot will start to generate formula column suggestions.
- Scroll up to see the details, some instructions, and the syntax of the formula column.
Even though the formula syntax is shown, we cannot copy and paste the formula from the Copilot pane directly into the cell in the worksheet. We can only add the column.
It's worth noting the Explain formula drop-down. This is very useful, particularly if you have received a workbook that contains formulas we don't understand. We also have the option to give the result a thumbs up or a thumbs down. This feedback is sent directly to Microsoft and helps them improve the product going forward.
- Click Insert column.
A new column named 'Total Sales' will be added to the table. New columns will always be added to the end of the table. We can use Copilot to move columns or we can do this manually by holding down Shift and dragging the column to its new location.
Calculate the Operating Margin
We can use Copilot to calculate the operating margin. This means what percentage is the operating profit of the total sales.
- Type the prompt: 'Add a column that calculates the Operating Margin based on the Operating Profit and Total Sales. Use percentage format with 0 decimal places'.
- Press Enter.
- Click Insert column.
Add a Total Row
We can add a total row to our table using Copilot. A total row allows us to aggregate data in the column above using several different aggregation methods.
- Press CTRL+down arrow to jump to the last row of the table.
- Type the prompt: 'Turn on the total row for this table'.
- Press Enter.
We can then choose which columns to aggregate and which aggregation method to use for each column.
Get Information about Formulas
Aside from calculations, Copilot can be used as a way to learn more about Excel formulas including the syntax of the formula.
- Type the prompt: 'What is the syntax of the SUMIFS formula if I want to calculate the Units Sold by Region'.
- Press Enter.
If we scroll up and read the instruction text, we can learn more about the formula, how it works and see the syntax in the context of our data.
Hints & tips
- Formula results can be previewed by hovering our mouse over Insert column. This will show a preview of the column. Click to add the column permanently.
- Use CTRL+down arrow to jump to the bottom of a dataset and CTRL+up arrow to jump to the top of a dataset.
- Multiplication in Excel is represented by an asterisk (*).
- 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.