Use the Cell Value in your Calculation

In Excel, it's very easy to use the value of a cell in your calculation. You just have to call the cell reference.

What is the cell reference?

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

 

Reusing the value of a cell

Let's say that you have today's date in cell A1 in your workbook. If you want to reuse the value of the cell A1 in the same sheet, you just have to follow these easy steps:

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

=A1

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

  1. Select any cell in another sheet
  2. Press the =sign (this activate the edit mode)
  3. Return to the worksheet with the cell that you want to link
  4. Select the cell with the contain to link
  5. The formula is now:

=Sheet1!A1

Absolute or relative reference?

When you reuse the contain of a cell, there is 3 ways to write the reference:

  • When you copy your formulas and you want to change the references of your cell, you use a relative reference (this is the purpose of this article).
  • In contrast, if you want to always use on the same cell (same reference), you use an absolute reference.
  • There is a third situation where you want to change only the reference of the row or the column. This is a mixed reference. You will find an example in this post.

Copy a formula containing a reference downwards

When you copy a formula that contains references to other cells, the copy function 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😊

Related posts


Permanent link to this article: https://www.excel-exercise.com/use-the-value-of-a-cell-in-excel/


Leave a Reply

Your email address will not be published.