- 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 (*).

Lesson notes are only available for subscribers.