In formulas and functions of Excel, we refer to other cell or group of cells as arguments. This is called referring to a range and the range being referred is known as 'reference'. Today we are going to discuss about different types of references that Excel uses.
Excel accepts cell references in what are called absolute and relative ranges.
Relative Reference
Relative cell references are basic references that adjust and change when copied or when using AutoFill. This is the most widely used type of cell reference in formulas. Excel by default keeps the reference as relative.
For example: You want to get value of cell A1 in cell B1 through a formula. Our formula in cell B1 will be “=A1” (without quotes). Excel interprets the formula as the relative position of cell A1 compared to B1. Since A1 is one cell left to B1, Excel remembers this formula as ‘where ever this formula is copied to, get the value from one cell to the left’. So if you paste this formula in cell B2, Excel places a formula “=A2” (A2 is exactly one cell left to B2, right?). Accordingly, if you paste the formula in cell F25, Excel places a formula “=E25” (also one cell to the left)
Point here is to remember the relative position of the referenced cell compared to the cell where formula is placed. Let’s see if we got this correct by another quick exercise.
I have a formula in cell F1 as “=A6”. What will be the formula if I copy and paste this in cell K8?
You are correct if you said “=F13”. Since A6 is 5 columns to the left and 5 rows down from cell F1, where ever you paste this formula, Excel take the reference as 5 columns to the left and 5 rows down. Calculating the same from cell K8, 5 columns left and 5 rows down will be F13.
This brings up a question, what if I don’t want to change the reference when I copy the formula to other cells. Read on.
Absolute Cell Reference
In those situations where a cell reference must remain the same when copied or when using AutoFill, we use what is called an ‘absolute reference’. Dollar signs are used to hold a column and/or row reference constant. For example, "$C$3" refers to cell C3, and "$C$3" will work exactly the same as "C3", except when you copy the formula, Excel will not change the reference. The trick is, deciding before you copy a formula what cell references you want, relative or absolute.
Absolute cell reference can be used when a particular cell reference needs to be constant. Cell references are often used in formulas, charts, functions and other commands. In some scenarios, it becomes important to keep a cell reference constant when it is copied to a different cell.
So in our first example where you want to get the value of cell A1 in B1, if you use “=$A$1” in cell B1, no matter where you copy this formula to, Excel will always keep the formula same and always retrieve value from row 1 of column A.
How will you place absolute cell reference in formulas? After entering formula in a cell, you can either type $ symbol in front of row and column reference or there is a shortcut for placing absolute cell references in your formulas! When you are typing your formula, after you type a cell reference - press the F4 key. Excel automatically makes the cell reference absolute.
Example
See the example below. I have rebate % in cell B1 and want to apply this on sales value to find out rebate. Look at the below picture when I use ‘relative reference’ in cell C4 and copied over till C9.
Notice the B1 changed to B2 in cell C5. It kept on changing till C9 and my formula result is not what I expected. Let’s use absolute reference and see what happens. Look at the picture below.
Just the way we wanted, right? We also have another example under VLOOKUP post. So depending on the situation, you have to choose relative or absolute references. Otherwise results may not be what you expect.
Alright, I got another question. What if I want only row to be relative and column to be absolute or the other way around? Excel facilitates this hybrid functionality too. By continuing to press F4, Excel will cycle through all of the absolute reference possibilities. See below for a summary of the same.
A common use of mixing absolute and relative range specifications is to create a running total of a column of numbers. See below example of using mixed references.
For example, if you have data in cells B4:B9, the formula “=SUM(B$4:B4)” in cell C1 and filled down to cell C9 will return the running total for the numbers in column B.
Point to remember
Even with an absolute referencing style, Excel will still change row and column references when you insert a row or column. To have a truly absolute cell reference that will not change under any circumstances, use the INDIRECT function. For example =INDIRECT("A1") will always refer to cell A1, regardless of any changes made to the worksheet. This works because Excel does not interpret the string "A1" as an address. Instead, it treats it as plain text and therefore does not change it.
Are we 'absolutely' clear now?
