Learn to use appropriate “Cell Reference” in Excel Formulas

Learn to use appropriate Cell Reference in Excel Formulas

Learn to use appropriate “Cell Reference” in Excel Formulas

>> Relative Cell Reference
=================
Cell Reference changes with formula being copied and pasted in other cells
>> Default cell references in a spreadsheet are relative, which change when copied and pasted.
eg; E8, E9, E10, etc

>> Obsolete Cell Reference
=================
Cell Reference does NOT change with formula being copied and pasted in other cells
>> An absolute cell reference is used when you don’t want the cell reference to change with the formula or function being copied and pasted to other cells.
>> To keep the cell reference unchanged, dollar signs ($) have to be prefixed to column and row.
>> A shortcut to prefix dollar signs is to click on a cell reference and then press F4 key on the keyboard.
eg; $E$5

>> Mixed Cell Reference
=================
Part of the Cell Reference changes with formula being copied and pasted in other cells

eg1) $E10: In this case, only “Column E ($E)” is fixed and will not change with formula being copied and pasted in other cells
eg; $E10, $E11, $E12, etc

eg2) E$10: In this case, only “Row 10 ($10)” is fixed and will not change with formula being copied and pasted in other cells
eg; E$10, F$10, G$10, etc

Please feel free to ask any question in this regard

Show Comments

2 Comments

  1. Jean

Leave a Reply