 Tweet

# Absolute, Relative and Mixed Cell Reference Excel Tutorial

Cell reference is the address of a cell, that defines the location of the cell being referred.
A cell reference always starts in a alphabet i.e., Column reference.eg. A1, B1, C6 etc. A cell reference can be the name of the cell that we give. To give a name to a cell, select the cell and keep the cursor in name box and type the name that you want to give. In the above case the cell B2 can be named as discount by typing discount into Name Box with B2 selected.

Now type a number, say 1000 into B2. Then the cell name is discount and value in cell B2 is 1000. Then cell B2 can be referred as discount. Write a formula in D2 =discount gives the result as shown ## absolute and relative reference in Excel

By default excel uses relative reference. From the above table I want to multiply the value in B1 with each of the values in A2 to A6 and the result to be in B2 to B6.By looking at the formula bar it is obvious that B2=A2*B1 Now if the formula in B2 is copied to B3 (by copy paste / using Fill Handle on right lower corner) Relative reference ( Not suitable for this case) Formula in B3 became =A3*B2 which is not expected.(It should have been A3*B1)

This has happened because excel uses relative reference. (A2 is replaced by A3 and B1 is replaced by B2)

But to get the expected result, that is to multiply each of the value in A2 to A6 with B1, lets give the absolute reference of B1 by using \$ symbol as shown below (these \$ symbols can be toggled by using function key F4) By doing so we are using absolute reference of B1. Now even copying the formula or using the fill handle gives expected result. Absolute reference (suitable for this case) But we have one more scenario where neither absolute nor relative reference work. There we need to use mixed reference.

Let us have a look at the table below.

We want to CONCATENATE the strings in 1st row and 1st column for each cell.

That is we want to set JAN-2010 in B2, FEB-2010 in B3 and so on If the formula is copied and pasted for the cells below B2, result is not the one we expected Erroneous result with relative reference

To correct this error we may give absolute reference of B1 in B2 as shown below (This will work for one column only)  Absolute reference with \$B\$1 work only for one column in the above case
But for the purpose of calculations in C, D and E columns it will give error. But the one below (with absolute reference) will work for one row.  Absolute reference with \$A\$2 will work for one row only in this case Erroneous result

## Mixed reference in Excel Tutorial

So to fix this we need to use mixed reference as shown below In the above figure, if the formula [=CONCATENATE(\$A2,"-",B\$1)] is copied to the cell right to B2: \$A will not change because of absolute reference,2 will not change as it is the same row. B will change because of relative reference (No \$), \$1 will not change because of absolute reference. That is the actual requirement to get our expected result In the above figure, when the formula [=CONCATENATE(\$A2,"-",B\$1)] is copied to the cell below B2: \$A will not change because of absolute reference,2 will change as the row is changed. B will NOT change because it is the same column, \$1 will not change because of absolute reference. That is the actual requirement to get our expected result.    