↑ Return to Manage Data

Absolute Relative References

The cell reference is the code that identify a cell: A1, B3, C15, … AA375. But in Excel, it’s not just reference, we have absolute, relative and mixed references (but what-is-this-stuff 😕 )

Relative reference

A relative reference is a reference that will change when you copy it.

For example in the example, when you copy the formula in D5 to the other cell in column D, the row’s reference change.

We start with the formula B5*C5 and after the copy/paste, you see that the references were changed: B6*C6, C7*B7, B8*C8 …

 

 Absolute reference

An absolute reference is a reference that will not be changed during a phase of copying. We are talking here of a reference set.

A cell is fixed when we put the $ on both sides of the reference example

 

=$F$2

 

 

 

 

 

You must used the absolute reference of a cell when you use a cell as parameter or referral. Consider the VAT rate in cell F2 we want to calculate in E5 the total of the column D with the tax rate. The formula is:

=D5*(1+$F$2)


When you copy this formula, references of the rows change but those of the cell F2 (the tax rate) will not change.

In the following example, convert the total in Euro in Dollar taking as referral the cell C2 (Enter your formula directly into the Excel spreadsheet below).

 

 

 

Mixed reference

A mixed reference is a reference that is fixed either on the row or on the column.

Before you show by example a calculation using mixed references, we will detail the position of the $ in a reference. On an absolute reference, we have 2 dollar sign. Indeed 1 is used to fixe the row and the other one the column.

But which one does what? It’s very simple. You just have to look if you have a $ on the left of your cell reference.

 

For instance, if you have the reference B$1, the $ is on the left of 1 (the row). So when we copy this formula, the row will never vary contrarily to the reference of the column (the letter B). Conversely, if we wrote the reference =$A2. Here is the column’s reference will not change and the row’s reference yes.

Now, if we want to build a multiplication table, we just need to build the formula for the first cell and then copy it so that we filled all our table. The formula in B2 is:

=$A2*B$1

 

  1. Copy the cell B2 (Ctrl + C)
  2. Select the cells B3:B10
  3. Paste the formula (Ctrl + V)
  4. Double-Click in one of the cell to see the formula

Explanation: In this formula, we always use the values of the column A and the row 1. So we just need to write a single formula and when you copy it to the rest of the range of cell, you will fill all the cells without error.

Reference range of cells

Very often, you will be asked to fix references to a range of cells to formulas as NB.SI, SOMME.SI, VLOOKUP … In general, this will happen every time you have to refer to a range of cells that contains the reference data.

But it is also possible to block a single part of a range of cells for a cumulative result.


For example, if we write a SUM function by blocking the first reference and leaving free the second, we create is a formula that performs a cumulative calculation.
In this example against, we have a list of odd numbers. We want to calculate the cumulative column B values in column A. We then write the formula in B2

= SUM($A$2:A2)

 

  1. Copy the previous formula in B2 (Orange cell)
  2. The result is = 1
  3. Copy the cell B2 (Ctrl + C)
  4. Select the cells B3:B10
  5. Paste the formula (Ctrl + V)
  6. Double-Click in one of the cell to see the formula

 

 

 

2 comments

  1. Jelena Petrović

    Please; can anyone explain in what cases in practice do we use these references?

  2. investeringar

    What your declaring is utterly genuine. I do know that will everyone need to say the identical issue, although I recently imagine you add the item in a fashion that everyone may have an understanding of. I also indulge typically the photos you add in the adhering to. Many people suit consequently nicely with what they are endeavoring to state. Internet marketing positive youll obtain so many individuals with exactly what youve acquired to say.

Leave a Reply

%d bloggers like this: