Relative vs. Absolute References in Formulas [spreadcheats]

This is the first installment of the spreadcheats series.

I have used excel an year and half before I learned about the relative and absolute references. It was such a joyous feeling to find out that you can actually write one (or just a few) formulas and use the power of auto-fill to do the dirty work for you.

What is a reference?

A reference in excel lingo “identifies a cell or a range of cells on a worksheet and tells Microsoft Excel where to look for the values or data you want to use in a formula.”

So what is the difference between relative and absolute references?

When you say a reference is relative, you are telling excel to adjust that reference in formulas based on where you move or copy the formula. For eg. if you have a formula in cell B1 as =a1*2 and now if you copy paste this in another cell, lets say, C1, the new formula would read like =b1*2

When you say a reference is absolute, you are telling excel not to adjust that reference in formulas when you move or copy them.

Switching between relative and absolute references:

while editing the formula you can use F4 function key to change the reference of a cell on which cursor is focused. By pressing F4, excel switches the references between relative (A2), absolute ($A$2), relative column & absolute row (A$2) and absolute column & relative row ($A2).

Understanding relative & absolute references plays a key role in writing effective spreadsheet formulas.