Dollar ($) symbols are used to freeze a Row or Column or Cell or Range in side the formulas. Based on freezing cell references can be broadly classified into two types VIZ 1.Absolute Reference 2. Relative reference.

What these references mean it?

Absolute = Fixed

Relative = Changing

When you copy and paste absolute reference formula to other cells, exact replica of the formula will be copied. In case of relative references, cell references will be changed.

If you put $ symbol both in front of  a Column and Row number that is called Absolute Reference e.g. $A$10 . Inside a formula when you don’t use $ symbol for cell references it is called Relative reference.

What is the necessity of these references?

1.Without entering the right kind of references inside a formula you can’t get the right results

2.When you use right kind of references in your formulas your speed increases drastically. See the picture to understand its importance. You can drag the formula in this picture both horizontally and vertically in one stroke. This formula keeps both row header and column header as constant to get the desired results.

Different Types of References with illustrations

$A$10 - Absolute Cell Reference. Also Known as Constant Value. When you drag this $A$10  in any direction this will remain same as $A$10.

$A$10:$A$20 - Absolute Range Reference. Also known as Constant Range. For example, in Vlookup for “table_array” argument we use this kind of reference to keep the range or table constant. If you don’t use $ symbols for table_array when you drag the formula, in this case A10:A20 will become A11:A21. To avoid changing references like this we use $ symbols to freeze.

$A10 - Absolute column and Relative Row. When you want to freeze a column while dragging a formula horizontally, we use this. When you drag this $A10 vertically it will become $A11 and if you drag horizontally it will remain same as $A10

A$10 - Relative column and Absolute Row. When you want to freeze a row while dragging a formula vertically we use this. When you drag this A$10 vertically it remains the same A$10. Suppose if you drag horizontally it will become B$10.

A10 - Relative Cell Reference. When you drag this kind of references it will become A11 or B10 depending on which direction you are dragging.

When you record Macros in Excel again this concept comes in. There are two types of recording macros Viz.Absolute recording and Relative recording. Usually recording will be done in combination of these two methds. How to record macros in a professional way i will be explaining in my upcoming articles. Just be tuned.

Tweet This