Excel: Switching between Relative | Absolute | Mixed Cell References

By default MS Excel uses relative cell address in each formula in excel but lot of time we need to change or switch Relative | Absolute | Mixed Cell References in MS Excel formula from relative to absolute or mixed as per requirement. In this post we know about cell, types of cell address and how could you change cell reference type in MS Excel.

Meaning of Cell:

It is distinguish between the row number and column label, where you can feed some data of any type.

Must Read: How to secure your computer from Hacking Attack

Meaning of Cell Address / Cell Reference:

It is prepared by the combination of column label followed by the row number. Why we require to switch between relative, absolute and mixed cell reference in excel. There are three types of cell address or cell reference in excel.

1. Regular Cell Reference: In this type of cell address you have to give simple type of cell address or reference in formula. Eg. A1, B10 etc.

2. Absolute Cell Reference: In this type of cell address you have to set dollar symbol in front of column label and row number both. Eg. $A$1, $B$10

3. Mixed Cell Reference: In this type of cell address you have to set dollar symbol either in front of column label or row number. Eg. $A1, $B10  (or) A$1, B$10

When you have to copy a formula that have any cell references, then the cell reference will be changed. In that case you first you have to active the formula by double click on the cell or select the cell then press F2 Function key, take the cursor on particular area or select the area which cell reference you want to modify then press F4 Function key switch between the different relative/absolute or mixed reference.

Must Read: Count Working Days between Two Dates – MS Excel

Note: You have to change cell reference type in formula using F4 function key.

How to change Cell Reference / Cell Address type in Excel Formula:

Switching-between-Relative-Absolute-Mixed-Cell-References

1. First you have to active your formula in cell either by double clicking on cell or, after select the cell you have to press F2 function key. Now your formula will be highlighted.

2. Now you have to press F4 function key excel will automatically change cell reference type in regular, absolute or mixed.

Leave a Reply

Your email address will not be published. Required fields are marked *