Relative absolute and mixed references - Excel Shortcut

Relative absolute and mixed references

There are 3 types of references in Microsoft Excel which we can use in our formula or function -

1. Relative Reference.
2. Absolute Reference.
3. Mixed Reference.

Today I'll show you how they are working and also shortcut to use References in Excel. And Finally we prepare multiplication table with just single formula. Before preparing multiplication table we should have knowledge of  all 3 references in excel. Relative absolute and mixed reference.

You have noticed cell address contain Column and Row number. A1 - here A is column and 1 is row. In all 3 references $ sign is playing important role.

Lets Start -  

1. Relative Reference -

Relative Reference is normally we use when source of formula changes as per formula. In Relative Reference no $ sign is use. In below screenshot I have enter formula in C11 but when I copied same cell to E11 then answer is 0. Because the source of cell reference has been also changed when copied to other cells.

 

2. Absolute Reference - 

As name suggested Absolute reference the sources of formula is stick despite of copy formula to other cell or other worksheet. 

In Absolute Reference $ is used to lock the cell address before column and before row. Example $A$1.

In above example I have put $ sign before Column and before row number. When this formula copied to cell E11 the answer become same i.e. 15. We have seen that in Relative reference answer was 0. Now you understand the difference between absolute and relative reference.

Shortcut to Absolute Reference -

It is difficult to put $ in each cell address when you use more cell in your formula. So to put $ sign simply select cell address in formula and  press F4 key from keyboard. It will automatically add $ sign to cell address.

3. Mixed Reference - 

Mixed Reference is combination of both Absolute reference and Relative reference. Where you want to lock only column or only row then use Mixed reference. To understand the Mixed reference we prepare Multiplication table with just one formula -

In column C and row 5 I have written numbers. Now enter formula in any cell within than table. For sake of simplicity I'll written formula in first cell i.e. Cell D6.

We want to lock column C as well as row 5 to getting answer. Here we can not use Relative reference and absolute reference. 

Formula we use is Row cell * Column Cell

Select row value first then lock the row by putting $ sign before row number in the formula only simply press F4 key 2 times after selecting cell. Now type * and select first column value 

and lock column by putting $ before column of press F4 keys 3 times after selecting cell.

Your formula look like below screen - 

Now use fill handle or copy that formula and paste in other cells.

Hope you understand the difference between Relative absolute and mixed references and how to use all 3 references.

Video Tutorial - 

If you have any issue then leave comments.

Thank you !! Enjoy Excelling !! Have a Nice Day !!