Excel - Copying formulas Tutorial

In this video, you will learn about copying formulas in Microsoft Excel.
The video covers how to copy a formula down a column to add pairs of cells.
It demonstrates how to use the black plus sign in the bottom right-hand corner of a cell to copy the formula into other cells.
The video also explains the concept of relative cell references, which is the default in Excel.
However, it shows that sometimes you may want to use absolute cell references to prevent the references from changing when you copy a formula.
By using the dollar sign before the column and row references, you can make a cell reference absolute.
This will help you understand how to copy formulas accurately and efficiently in Excel.

3:00
4404 views

Available in Microsoft365

Objectifs :

Understand how to copy formulas in Excel while managing relative and absolute cell references effectively.

Chapitres :

Introduction to Copying Formulas in Excel
In this section, we will explore how to copy formulas in Excel, specifically focusing on the difference between relative and absolute cell references. This knowledge is essential for efficiently managing data in spreadsheets.
Creating and Copying a Basic Formula
We begin by creating a simple formula that adds two cells. For instance, the formula in cell C2 is A2 + B2. To copy this formula down the column, follow these steps: - Position your mouse pointer over the bottom right corner of cell C2 until it changes to a black plus sign. - Click and hold the left mouse button, then drag the plus sign over the cells you wish to fill. As you do this, the formula is copied into the selected cells. However, it is important to note that the formula does not remain static; it adjusts based on its new position.
Understanding Relative Cell References
When we copy the formula from C2 to C3, the relative position of the formula increases by one row. Thus, the formula in C3 becomes A3 + B3. This behavior continues for the formulas in C4 and C5, demonstrating the concept of relative cell references, which is the default setting in Excel.
Using Absolute Cell References
In some cases, you may want to prevent certain cell references from changing when copying a formula. For example, if we have a formula in which F2 is divided by E2, we want all subsequent calculations in column F to reference E2. To achieve this, we need to make E2 an absolute cell reference. To do this, type a dollar sign before the column letter and the row number, like this: $E$2. After pressing Enter, when we copy the formula, the first cell reference will update as expected, but the absolute reference to E2 will remain unchanged.
Common Errors with Cell References
If we attempt to copy the formula without making E2 absolute, we may encounter errors. For instance, in cell G3, the copied formula would read F3 divided by E3, where E3 is blank. Excel interprets a blank cell as zero, resulting in a math error. By using absolute references, we can avoid such issues and ensure accurate calculations.
Conclusion
In summary, understanding the difference between relative and absolute cell references is crucial for effective spreadsheet management in Excel. By mastering these concepts, you can enhance your data manipulation skills and avoid common errors when copying formulas.

FAQ :

What is the difference between relative and absolute cell references in Excel?

Relative cell references change when a formula is copied to another cell, while absolute cell references remain fixed. For example, A2 is a relative reference, but $A$2 is an absolute reference.

How do I copy a formula in Excel?

To copy a formula, click on the cell with the formula, hover over the bottom right corner until you see a black plus sign, click and hold the left mouse button, and drag it over the cells you want to fill.

What happens if I copy a formula with relative references?

When you copy a formula with relative references, Excel automatically adjusts the cell references based on the new location of the formula. For example, copying A2 + B2 from C2 to C3 changes it to A3 + B3.

Why do I get a math error when copying a formula?

A math error can occur if the formula tries to divide by a blank cell, which Excel interprets as zero. To avoid this, use absolute references for cells that should not change.

How do I create an absolute cell reference in Excel?

To create an absolute cell reference, add a dollar sign before the column letter and row number (e.g., $E$2). This prevents the reference from changing when the formula is copied.

Quelques cas d'usages :

Budget Tracking

In a financial spreadsheet, you can use relative cell references to calculate monthly expenses by copying a formula down a column. For example, if you have a formula that sums expenses in one month, you can easily copy it to calculate expenses for subsequent months.

Sales Data Analysis

When analyzing sales data, you might want to calculate the profit margin for each product. By using relative references, you can copy the profit margin formula across multiple rows to apply it to each product's sales data.

Consistent Division Calculations

In a scenario where you need to divide multiple values by a constant (e.g., total sales by a fixed tax rate), you can use an absolute reference for the tax rate. This ensures that as you copy the division formula down the column, the tax rate reference remains unchanged.

Inventory Management

In inventory management, you can use relative references to calculate the total stock for each item by summing quantities from different suppliers. Copying the formula allows for quick updates across the inventory list.

Performance Metrics

When tracking employee performance metrics, you can use relative references to calculate averages or totals for different time periods. This allows for efficient data analysis by simply copying formulas across the relevant cells.

Glossaire :

Formula

A mathematical expression used in Excel to perform calculations on data in cells.

Cell Reference

The way to refer to a specific cell in Excel, typically using the column letter and row number (e.g., A2).

Relative Cell Reference

A cell reference that adjusts automatically when a formula is copied to another cell. For example, copying a formula from C2 (A2 + B2) to C3 changes the reference to A3 + B3.

Absolute Cell Reference

A cell reference that remains constant, regardless of where the formula is copied. It is denoted by adding a dollar sign before the column letter and row number (e.g., $E$2).

Math Error

An error that occurs in Excel when a formula attempts to perform an invalid mathematical operation, such as dividing by zero.

00:00:06

now let's copy the formula down the column,

00:00:09

so it adds the other pairs of cells.

00:00:12

The formula in C2, is A2+B2.

00:00:17

To copy it, put the mouse pointer over the bottom right-hand corner

00:00:22

of the cell until it's a black plus sign.

00:00:26

Click and hold the left mouse button and drag the plus

00:00:29

sign over the cells you want to fill;

00:00:32

and the formula is copied into the other cells.

00:00:36

But the formula wasn't just copied.

00:00:39

If it was just copied,

00:00:40

all of the cells with the formula would have remained A2 + B2.

00:00:46

And by looking at the results in each cell, that obviously hasn't happened.

00:00:52

Copying the formula from C2 to C3 increased

00:00:57

the relative position of the formula by 1 row

00:01:01

but didn't change the column.

00:01:04

The formula in C2 was A2+B2.

00:01:10

The formula in C3 is A3+B3.

00:01:16

The cell references in the formula increased by one row.

00:01:20

The formulas in C4 and C5 updated similarly,

00:01:26

this is called a relative cell reference and it's the default for Excel.

00:01:33

But you don't always want cell references to change when you copy a formula.

00:01:38

In this example F2 is divided by E2

00:01:44

and we want the other numbers in column F to be divided by E2 as well.

00:01:50

In other words, we want E2 to not change when the formula is copied.

00:01:56

We can do that by making the reference absolute instead of relative.

00:02:02

If we copy the formula like we did before we get an error.

00:02:08

For example, in G3 the copied formula is F3,

00:02:13

which is 12 divided by E3 which is blank.

00:02:18

Excel interprets a blank cell as a zero and that's a math error.

00:02:25

To make E2 an absolute cell reference,

00:02:28

I type a dollar sign before the E, and a dollar sign before the 2 and press Enter.

00:02:35

Now when we copy the formula we get the results we expect.

00:02:41

When we look at the formula in G3, the first cell

00:02:44

reference did update, as expected,

00:02:48

but the E2 absolute cell reference didn't update. Neat.

00:02:54

Now you've got a pretty good idea about

00:02:56

what cell references are and how to use them.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2795 Views

Video Training Microsoft Excel - Discovery level

Discover and familiarize yourself with Excel: from creating and using your tables to enhancing your visuals and collaboration. Our introduct...

ALL COURSES