By default MS Excel uses relative cell address in each formula in excel but lot of time we need to know how to Excel- Switching between Relative, Absolute and Mixed Cell References in any formula in Excel.
By default we have to use relative cell reference during typing the formula. But after reading this guide you can easily switching between relative, absolute and mixed cell reference 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.
Must Read: How to Apply formula on multiple worksheets with 3D reference in Excel
Switching between Relative, Absolute and Mixed Cell References in Excel
Cell reference is an important part of any formula in Excel. By default we have to use relative cell reference in the excel. There are three types of relative, absolute and mixed cell references. Cell 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. The function key F4 allows you to easily start switching between Relative, Absolute and Mixed Cell References 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
Must Read: Lock or Unlock particular areas of a protect worksheet in Excel
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:
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.
I hope after reading switching between relative, absolute and mixed cell references guide, you can easily change your cell reference. Either you have to use manual method or you can also use excel F4 shortcut key to switching the formula. If you have any suggestion regarding this guide then share us in the comment box. Thanks to all.