How to Use Absolute and Relative Cell References in Microsoft Excel 2010?

There are three types of cell references that can be used in Microsoft Excel 2010 formulas; these are relative cell reference, active cell reference and mixed cell references. Hence, we need to choose the right type of cell reference while copying Excel formulas to other cells. Microsoft Excel contains formulas that contain references of other cells – by copying the formula from one cell to another, Excel automatically update the cell references in three different ways. This is the result you get when you copy any formula on Microsoft Excel 2010 –

Relative cell references

When we copy any formula to the two rows to the right, then Excel will shift the references within the copied formula and move two cells to the right. Similarly, when you copy any formula or a few rows down, the referred cells in the formula will also be moved by the same number of rows left and down – this is how relative cell reference works.     

Active cell references

While copying formula in active cell reference, you will see a dollar sign ($) glued next to the row number and the column letter. Active cell reference is needed when you do not want the cell references to change while copying the formula. By adding a ‘$’ sign before the column letter if you want the column alphabet number reference to be the same and before the row number to retain the same row number in the formula.

While using these two cell reference methods, press F4 key after you enter the cell reference value to be able to alternate between relative cell reference and active cell reference.  

Mixed cell references

On the other hand, a mixed cell reference you can blend the both reference and active cell reference techniques. You can set the active cell reference for the column and relative cell reference for the row number or vice versa. Remember that whether you choose the column or row reference to be absolute, you just need to input the dollar sign before that.

To use these two cell references, first you need to select and copy the cell containing the formula. Next, navigate to the formula bar and change the reference while you write formula in Microsoft Excel 2010. Follow these examples shown below:

• Both active cell reference: $A$1
• When the column is relative and the row is absolute [mixed]: A$1
• When the column is absolute and the row is relative [mixed]: $A1
• Both relative cell reference: A1

Do you find these tips to use relative cell reference, active cell reference and mixed cell reference useful? Share your comments and queries on Microsoft Excel 2010 on our Facebook and Twitter channels.