How to use absolute and relative references
What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD and rows have numbers, 1 to 1 048 576. The total number of columns in Excel 2007 are 16384.
A cell can contain a value, formula or constants. A formula starts with a = (equal) sign. You can use values from other cells in your formula. To use another value you create a reference to the cell containing the value you want to use in your formula.
A formula starts with a "=" equal sign. A reference is a column letter and a row number, like B1. B is the column B and 1 is row number one.
Example:
 Double click cell A1 and type =B1 then press Enter
 Double click cell B1 and type 11 then press Enter
You have now created a reference to cell B1. B1 contains the value 11. A1 contains the reference to B1 so both cells now show the number 11. Excel uses relative references by default. =B1 is a relative reference.
What is a relative reference in excel?
When you copy a cell containing a relative reference and paste it into another cell, the relative reference changes. Let me show you an example.
Now copy cell A1 and paste it to cell A3.
 Right click on cell A1 and click Copy
 Right click on cell A3 and click Paste
 Press Escape to remove selection.
When you copy a formula containing relative references the reference changes when you paste it into a another cell. Select cell A3. The formula window now show =B3. See picture above.
Cell A3 has the value 0 (zero) and the formula field shows the relative reference =B3. Cell B3 is empty and that is why cell A3 displays 0 (zero).
When you copied the cell reference in cell A1 to A3, the relative reference changed to B3. The reference changed two rows down. The exact same number as of rows between the copied cell A1 and cell A3.
How much the reference changes is relative to the position of the copied cell and the position of the new cell the formula is pasted into. Excel uses relative references by default.
How to change a relative reference to an absolute reference.
 Double click A1.
 Press F4
 Press Enter
The relative reference =B1 changes to an absolute reference =$B$1.
What is an absolute reference in excel?
An absolute reference in a formula stays the same if copied to another cell.
Example:
 Right click cell A1 and click Copy
 Right click cell A3 and click Paste
 Press Escape to remove selection.
Select cell A3
The cell reference in cell A3 stays the same (is locked to cell B1). An absolute reference does not change when copied or filled into other cells.
Insert a row or column
Remember, if you insert a row or column into your excel sheet the absolute reference changes.
Example
Cell A1 contains an absolute reference to B1 (=$B$1).
The absolute reference changed to =$B$2.
To keep absolute references use INDIRECT function.

Type =INDIRECT("$B$1") + ENTER in cell A1
 Select row 1
 Right click on row 1 and select "Insert"
 Done!
The absolute reference is still referencing $B$1. $B$1 is empty and $B$1 returns a 0 (zero).
There is a downside using INDIRECT. INDIRECT is a volatile function. A volatile function is recalculated every time a recalculation is made. You might think that is alright. But if your excel sheet has many calculations and INDIRECT is often used, the calculation can become very slow.
How to quickly select a non contiguous range
A noncontiguous list is a list with occasional blank cells and that makes it harder to select the entire cell [โฆ]
How to find errors in a worksheet
Excel has great builtin features. The following one lets you search an entire worksheet for formulas that return an error. [โฆ]
Remove print preview lines (Page Breaks)
Have you ever wondered how these lines got there on a worksheet? They show where pages will break, in other [โฆ]
Convert column number to column letter
Use the following formula to convert a column number to a column letter: =LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703})) [โฆ]
How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas
This article demonstrates different ways to reference an Excel defined Table in a dropdown list and Conditional Formatting. There are [โฆ]
How to change cell formatting using a Drop Down list
This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column [โฆ]
Prevent duplicate records [Data Validation]
Debra Dalgleish demonstrated last week how to block duplicate entries in excel table. I made a comment and I thought I would share [โฆ]
Highlight lookups in relational tables
This article demonstrates a worksheet that highlights lookups across relational tables. I am using Excel defined Tables, if you add [โฆ]
9 Responses to โHow to use absolute and relative referencesโ
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form
[...] D25:$D$25 is a relative and absolute cell reference. [...]
[...] can enter a reference to a single cell or a cell range. The ROW function returns an array of numbers if you enter a [...]
HOW TO AUTO SEARCH NAME THE DISPLAY
LALIT,
CAN YOU EXPLAIN IN GREATER DETAIL?
[โฆ] There are relative cell references in the CONCATENATE function and they change in each cell. Donยดt know much about relative and absolute cell references? Read this: Absolute and relative references in excel [โฆ]
[โฆ] insert or delete a row or column in excel the cell references in formulas changes, even if you use absolute cell references. The Indirect function helps you solve that problem. The following picture demonstrates what [โฆ]
[โฆ] in cell E5: =$E$3+C5/24 Copy this cell and paste to E6:E8. There are both absolute and relative cell references in this [โฆ]
[โฆ] formula is valid for cell A3, when you copy it to cell A4 the cell refs change. Check out blog post absolute and relative cell references and learn how to build smarter [โฆ]
[โฆ] formula basics in conditional formatting. It is really good if you have some knowledge about absolute and relative cell references but it is not necessary. I think the examples shown here explains a lot when it comes to cell [โฆ]