Introduction:

Everyone wants to work smart! Everyone wants to work fast! How much time could you save if you can write formula once and copy the same? But sometimes just copying the formula changes the cell reference in excel. What if I tell you that you can copy 90% of the excel formula you use in your daily life through following 3 types of cell references only? Yes, you can!

How do I copy a formula in excel without changing cell reference?

It is one of the main problems of excel users. No worries, we have got solution. The use of absolute, relative and mixed reference of excel can save your lots of time.

You just need to learn how to use 3 types of cell reference and when to use absolute reference, relative reference or mixed reference. Now let’s understand each type of cell references one by one

1. Relative Reference:

Excel by default uses relative cell reference. Relative cell reference keeps on changing with change in row or column. For example, when you write =A3 + B3 in cell A1. (Pic1)

When you copy or drag the same formula to B1, it becomes =B3+C3. (Pic2)

When you copy or drag the same formula to A2, it becomes = A4+B4. (Pic3)

2. Absolute Reference:

When assigning dollar ($) symbol before the row and column of cell, it becomes an Absolute cell reference.

For example: In the same example, if we press F4 (Shortcut to relative reference when given before, in mid or after cell reference) two $ (Dollar) sign would come. [Original formula was =A3+B3] One before A and second before 3. i.e. it will become $A$3. Repeat the same procedure to B3. Now we have formula like =$A$3 + $B$3. (Pic4)

Now when you copy the same to the next column or row. i.e. in B1 or A2 respectively, the cell reference will not change because we have given an absolute reference to cell. Irrespective of the paste place, cell reference will not change. (Pic5 and Pic6)

3. Mixed Reference:

Now, let’s understand the most important type of cell reference. i.e. Mixed Reference. Basically, there are two types of mixed cell reference.

A. Absolute Column and Relative Row:
When we only need to give an absolute reference to the column only and not to the rows, use =$A3+$B3. You can give an absolute reference to column and relative reference to row by pressing F4 (Shortcut key – Function Key 4) thrice before, in mid or after cell reference. (Pic7)

Now when you copy the same to B1 cell, it won’t change as we have given an absolute reference to column. So, irrespective of the change in column, our formula won’t change. (Pic8)

But, when you copy the same formula to A2, it will change as we have given relative reference to row. Which means change in row would result in change in formula. (Pic9)

B. Relative Column and Absolute Row:
When we need to give absolute reference to row and relative reference to column, press F4 (Shortcut key – Function Key 4) twice before, in mid or after the cell reference. Our cell reference would look like =A$3+B$3. (Pic10)

If we copy the same formula to B1, it will become B$3+C$3. It shows column is having relative reference. (Pic11)

But, when we copy the same formula to A2, it won’t change as the row is having an absolute reference. Thus, changes in row won’t affect the formula but changes in column will. (Pic12)

Summary: