How to Reuse the Cell Value (reference) in a formula

Reading time: 3 minutes
Last Updated on 22/02/2023 by Frédéric LE GUEN

In Excel, it's easy to reuse the content of a cell in your formula.

Let's see how to do and also the impact on your worksheets.

What is the cell reference?

In Excel, a cell is often called by its reference. In other words, the intersection of a row and a column, like A1, B5, D8,...

If you are unfamiliar with the Excel keywords, you can do this QUIZ

Reference of a cell
Reference of a cell

 Reusing the value of a cell

In Excel, it's very easy to reuse the content of another cell.

  1. Select any empty cell in your worksheet
  2. Press the =sign
  3. Select the cell to link (here A1)
  4. The complete formula is displayed below (easy to understand 😉)

=A1

Select a cell

Remarks. When you refer to another cell

  • The content is duplicated, whatever the type of data, Text or Number
  • The format of the cell selected is not repro

Why it's important?

Creating such a reference is not complicated, and it's the best way to work with Excel. Because now, each time you update the value in the source cell, the linked cell is automatically updated

Change cell content and impact on the linked cell

Reusing the value of a cell in another worksheet

If you want to link a cell from another worksheet, the steps are the same

  1. Select any cell in another sheet
  2. Press the =sign (this activates the edit mode)
  3. Return to the worksheet with the cell that you want to link
  4. Select the cell with the content to link

The formula is now:

=SheetName!Cell Reference

Create reference between worksheets

The delimiter between the Sheet name and the cell reference is ALWAYS an exclamation mark (!)

If you change the Sheet name, automatically Excel will changes the reference

Sheet reference change when rename sheet

Copy a formula with references

When you copy a formula that contains references to other cells, the action to copy will change the references according to the direction of the copy.

For example, let's take the formula in cell C2 for calculating the total.

=A2*B2

When you copy down this formula, all the references will be changed as follows:

  • The reference of the row number changes
  • The reference of the column letter remains the same

Copy a formula containing a reference to the right

Now, if we copy a formula to the right:

  • The reference of the column letter changes
  • The reference of the row number remains the same

This example shows a cumulative sum of sales from month to month. C4 contains the formula:

=B3+C2

Now, by copying this formula to the right, all column references change but not those of the rows.

As you can see, depending on the direction in which you copy your formula, the cell references will be modified according to the direction of the copy. It's very clever😊

Absolute or relative reference

As you can see, each time we copy a formula with references, the references change. It's because we have written relative reference.

But we can avoid that by fixing the references. We call them absolute reference.

In this article, we will see how to use them.

Leave a Reply

Your email address will not be published. Required fields are marked *

How to Reuse the Cell Value (reference) in a formula

Reading time: 3 minutes
Last Updated on 22/02/2023 by Frédéric LE GUEN

In Excel, it's easy to reuse the content of a cell in your formula.

Let's see how to do and also the impact on your worksheets.

What is the cell reference?

In Excel, a cell is often called by its reference. In other words, the intersection of a row and a column, like A1, B5, D8,...

If you are unfamiliar with the Excel keywords, you can do this QUIZ

Reference of a cell
Reference of a cell

 Reusing the value of a cell

In Excel, it's very easy to reuse the content of another cell.

  1. Select any empty cell in your worksheet
  2. Press the =sign
  3. Select the cell to link (here A1)
  4. The complete formula is displayed below (easy to understand 😉)

=A1

Select a cell

Remarks. When you refer to another cell

  • The content is duplicated, whatever the type of data, Text or Number
  • The format of the cell selected is not repro

Why it's important?

Creating such a reference is not complicated, and it's the best way to work with Excel. Because now, each time you update the value in the source cell, the linked cell is automatically updated

Change cell content and impact on the linked cell

Reusing the value of a cell in another worksheet

If you want to link a cell from another worksheet, the steps are the same

  1. Select any cell in another sheet
  2. Press the =sign (this activates the edit mode)
  3. Return to the worksheet with the cell that you want to link
  4. Select the cell with the content to link

The formula is now:

=SheetName!Cell Reference

Create reference between worksheets

The delimiter between the Sheet name and the cell reference is ALWAYS an exclamation mark (!)

If you change the Sheet name, automatically Excel will changes the reference

Sheet reference change when rename sheet

Copy a formula with references

When you copy a formula that contains references to other cells, the action to copy will change the references according to the direction of the copy.

For example, let's take the formula in cell C2 for calculating the total.

=A2*B2

When you copy down this formula, all the references will be changed as follows:

  • The reference of the row number changes
  • The reference of the column letter remains the same

Copy a formula containing a reference to the right

Now, if we copy a formula to the right:

  • The reference of the column letter changes
  • The reference of the row number remains the same

This example shows a cumulative sum of sales from month to month. C4 contains the formula:

=B3+C2

Now, by copying this formula to the right, all column references change but not those of the rows.

As you can see, depending on the direction in which you copy your formula, the cell references will be modified according to the direction of the copy. It's very clever😊

Absolute or relative reference

As you can see, each time we copy a formula with references, the references change. It's because we have written relative reference.

But we can avoid that by fixing the references. We call them absolute reference.

In this article, we will see how to use them.

Leave a Reply

Your email address will not be published. Required fields are marked *