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 press with left mouse button on cell A1 and type =B1 then press Enter
- Double press with left mouse button on 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.
- Press with right mouse button on on cell A1 and press with left mouse button on Copy
- Press with right mouse button on on cell A3 and press with left mouse button on 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 press with left mouse button on 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:
- Press with right mouse button on cell A1 and press with left mouse button on Copy
- Press with right mouse button on cell A3 and press with left mouse button on 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).
- Select row 1.
- Press with right mouse button on on selection and select "Insert".
- Done!
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
- Press with right mouse button on 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.
Excel basics category
A non-contiguous list is a list with occasional blank cells and that makes it harder to select the entire cell […]
Have you ever wondered how these lines got there on a worksheet? They show where pages will break, in other […]
Excel has great built-in features. The following one lets you search an entire worksheet for formulas that return an error. […]
Selecting cell ranges in Excel can sometimes be a real pain scrolling forever it seems. There is a quick and easy […]
This article demonstrates how to select all blank cells in a given cell range and how to delete them. It […]
In this article, I am going to show you two ways on how to find blank cells. Both techniques are […]
In this smaller example, column D (Category) has empty cells, shown in the picture above. If your column contains thousands of […]
The Quick Access Toolbar is located at the very top of your Excel window, I highly recommend that you place your […]
The picture above shows data in column B, some cells contain nothing, they are blank. I will now go through […]
This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than […]
Use the following formula to convert a column number to a column letter: =LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703})) […]
Making your sheets easy to read is a fundamental approach of creating useful worksheets. Your message must be crystal clear, […]
If your cell text is taking to much space Excel allows you to rotate text in any angle. Here are […]
The formula in column B returns a running count based on values in column C. Formula in cell B3: =IF(C3<>"",COUNTA($C$3:C3),"") […]
A number that is formatted as text will be left-aligned instead of right-aligned, this makes it easier for you to […]
Excel formula categories
Excel categories
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.
[...] 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 […]