««««Excel: relative and absolute addressing of cells – that’s the difference

12:02 am May 12, 20179378

Excel knows two basically different methods to make reference in a formula to other cells. You should know the distinction, because it plays an important role when copying and modifying the table.

relative references are the norm

a simple example: in cell C1 should be the sum of the cells A1 and B1. In C1, so use the formula

 = A1 + B1 

Although it does not look through the clear mention of the cells that form a relative reference . This means that Excel automatically adjusts the references when you copy or move.

you would include even a heading in the line 1, so wander previous cells A1 to C1 in the line 2, then Excel changes your formula automatically to

 = A2 + B2 
Excel relativer Bezug
Excel relative reference

absolute references change not

in the cases , where you do not want this, you need the ID for the row or column to prepend a dollar sign. For example, instead of A1 use the absolute reference $A$ 1 .

this reference remains intact even when copying. This is useful, for example, if A1 contains a parameter that takes in the worksheet as a constant use — about an interest rate that should apply in the entire journal (actually you would but even better in this case n work with named cells).

so you use a mixture of absolute and relative references

Excel knows so called mixed references . Use for example a reference like C$ 12 , then is the reference to the line 12 not adjusted when you move the column according to, but.

a use case for this mixed references: A company produces plates in various sizes. The square footage for all combinations of length and width to be calculated both as a basis for the price calculation.

  1. to create first a framework, which lists all the lengths and widths:

    Excel Beispiel für gemischte Bezüge 1
    first you specify which lengths and sizes should be available.

  2. now you write in the first free cell, thus C3 the formula = $B3*C you copy the contents of the C3 to the other free cells $2
  3. – best with the fast copy method with the mouse .
  4. you get the desired result in the Excel calculates the surface for all combinations.

    Excel Beispiel für gemischte Bezüge 2
    Through the mixed cell references, Excel uses always the right cells to calculate.

it all works, because the calculation formula for the height with an absolute addressing of the column and the width with an absolute address for the line. When copying the forms only the line adapts so “Height” at and “Width” returns only the column.

by the way: If you want to know how in the example, the units were used as part of the cell formatting, read the tip Excel: millimeters, square meters, and other units of measure use .

last updated on the 19.12.2016 / affiliate links / images from the Amazon product advertising API