MS Excel Tutorial About Reference

Relative and Absolute Cell Reference in MS Excel

Relative Reference

The Cell reference which some time also called Relative Cell reference. Normally, if you copy a formula which involves a cell reference to another location, then the cell reference is adjusted relative to its starting point. For example, copying a formula which calculates the sum of a column of numbers to an adjacent cell will sum the adjacent column of cells.  The formula has updated automatically to reference adjacent cells.

Lets take an example and describe this in batter and easy way:

If you have a table have some values in column A and B:

So when we copy the formula =Sum(A4:A7) from Cell A8 to B8 then its automatically update its reference such that =Sum(B4:B7) as in above image. These cell reference are called Relative Cell Reference.

Absolute Reference

Sometimes when we copy a formula and we want that cell reference remain unchanged, regardless of where the formula is placed then we use Absolute cell reference in which cell address not change when its copy from one location to an other location. in Simple word we say that in Absolute cell reference the cell address or fixed so that it does not update when we copy the formula to another location.

How we Make a Reference Absolute

1.  Type a $ sign before both the column letter and the row number of the cell reference. For example the relative reference A1 becomes the absolute reference $A$1.

2.  In the Formula bar highlight the cell reference for the cell, which is to be made absolute then press the F4 key from keyboard. $ signs are automatically placed in front of the column and row references.

For Example :

In above image table you can see that we want to Fix the A4 address. its means every number of column B is multiply by 25 which is A4. So A4 never change when we copy this formula in next cell C4 to C5 and so on.

Related topics

PMT() Financial Functions

Calculate the Sum of a Range Using AutoSum function

User Define Function (UDF) In Excel

Macros In Excel

Relative and Absolute Cell Reference in MS Excel

Page Setting and printing In MS Excel

Fill In Excel

How we use The Functions in MS Excel

How we Write Excel Formulas

Components of Excel 2007 Environment

Rounding The Numbers MS Excel

SUMIF FUNCTION In MS EXCEL

COUNTIF Function In MS Excel

COUNTBLANK Function In MS Excel

VLOOKUP Function In MS Excel

Pivot Table In MS Excel

Consolidation In MS Excel

Remove Duplicate Value In MS Excel

Convert Excel Data in to a Table