BUY EXCEL BOOKS ONLINE: 1. VBA & Macros      2. VBA for Modelers      3. Excel 2013 VBA and Macros     
4. Excel VBA for Dummies      5. Excel with VBA & .NET      6. Mastering VBA      7. Excel 2013 Programming

Relative and Absolute References in Excel Formulas

When the reference is made to the Absolut Vodka, it's called absolute reference and when the reference is made to some family member, it's called Relative reference.. Hahhahaa...Just kidding..
On serious note, absolute and relative reference is related to Excel Formulas and that's why it's here. Whenever we drag formulas or use auto-fill option to quickly fill the formulas, the reference of formulas may change depending on their type.

What are the types of references?
1. Absolute Column/Absolute Row
e.g. $A$1
means Whether you use auto-fill across column or across row, the reference won't be changed.

2. Absolute Column/Relative Row
e.g. $A1
means When you use auto-fill across column, it will remain column A but when you auto-fill across row, the reference will change to $A2, $A3, $A4 and so on.

3. Relative Column/Absolute Row
e.g. A$1
means When you use auto-fill across row, it will remain row 1 but when you auto-fill across column, the reference will change to B$1, C$1, D$1 and so on.

4. Relative Column/Relative Row
e.g. A1
means When you use auto-fill across column, the reference will change to B1, C1, D1 and so on and when you auto-fill across row, the reference will change to A2, A3, A4 and so on.
How to change the reference?
1. Prefix with $ manually.
2. Right after entering the formula use F4 to toggle between the four kinds of references. (recommended)

Where to use?
You can use it in formula or function in Excel spreadsheet, rest depends on your logic.

Video Example:


Bonus Tip: When you move formula (Cut+Paste), reference is preserved irrespective of absolute and relative reference but when formula is copied (Copy+Paste), reference is adjusted of 'Relative Referenced Cells'.