Relative cell reference

What is a relative cell reference?

When a formula is copied to another cell, the relative cell reference (a form of reference) automatically changes the originally identified cell in the formula according to how much it moved from the original spot to the new (copied) spot.

Now we will look at examples on how to use it.

Example of use) Copy・Paste

Below is a sales report for an internet shop.
We will use this report to explain what a relative cell reference exactly is, and how it is useful.

In cell G4, there is an IF function formula set up to calculate the transportation fee for the product ‘table (wood)’ under the rule that ①’if the sales amount (cell F4) is equal to or more than $45.00, transportation fee will be free, and if not, it will cost $5. (For more information on the IF function, click here)

=IF(F4>=45,0,5)

To get the transportation fees for cell G5 and G6, we would have to input the following formula.
=IF(F5>=45,0,5)
=IF(F6>=45,0,5)

We must shift the cells in the formula according to each total sales price (F5 and F6) but doing this manually will be extremely time consuming.

To prevent this time consuming process, Excel operates the “relative cell reference” which shifts cell numbers according to how much the copied cell moved from its original position.

We will try copying the formula in cell G4 and paste it on cell G5.
Below are the steps to copy and paste cells.

(1) Copy cell content
There are mainly 3 ways in copying cells:

Method I) Right click on cell to copy and select ‘Copy’.
Method II) On cell to copy, press the ‘ctrl key’ (control key) and ‘C’ at the same time.
(ctrl + C is a shortcut for copy.)
Method III) Copy from menu as follows.

→ Click on cell to copy (=G4).

→ Click ‘Home’ in the ribbon.

→ Click ‘Copy’ on top left for Excel.

 

(2) Paste cell content
Next is pasting the cell content we copied. There are also 3 main ways to paste cell content:

Method I) Right click on cell to paste and select ‘Paste’.
Method II) On cell to paste, press the ‘ctrl key’ (control key) and ‘V’ at the same time.
(ctrl + v is a shortcut for paste.)
Method III) Copy from menu as follows.

→ Click cell to paste (=G5).

→ Click the ‘Paste’ button on top left and the cell content is pasted.
※Attention: Do not click the word ‘Paste’ but the picture above.

(3) Cell copying and pasting is complete!

We managed to copy and paste. (The table below is the result of copy and paste.)

(4) Check to see if the copied cell is correctly pasted with relative cell reference

We will now check to see how the formula has changed (=relative cell reference). The table below is the aftermath of copy and paste.

If we compare the 2 formulas, we can see that in the original cell, the cell referenced is ‘F4’ whereas in the copied cell below the cell referenced is changed to ‘F5’.

cell G4 (original): =IF(F4>=45,0,5)
cell G5 (copy): =IF(F5>=45,0,5)

We can see that the relative cell reference is working and that the ‘transportation fee’ is correctly ‘free for purchase of $45.00 or more’.

How to copy a value/formula into multiple cells

To save time of copying a value/formula into many cells, there is a way to do this collectively.

This method is called the “AutoFill“. We will look into this in more detail below.

What is “AutoFill”?

“Autofill” can collectively copy and paste values or formulas selected by using the “fill handle”. We can copy and paste the same formula to many cells with 1 simple step, without having to do the routine of copy and paste for each cell.

What is a fill handle? A fill handle is a square that appears on the bottom right of cell when selected.

Now we will see how to reflect a value/formula to multiple cells collectively using AutoFill and the fill handle.

Example of use) AutoFill

Below are the steps to reflect a value/formula into multiple cells:

(1) Select cell with value/formula

→ Select cell to copy (=G4).

Bring the mouse over the fill handle. If correctly done, the mouse cursor will change to a black cross.

(2) Drag the fill handle to where we want to copy

When we drag the fill handle (black cross) to where we want to copy (until G6), the data will be reflected according to the mouse cursor.

(3) Check to see if value/formula is correctly reflected

We will check that the value/formula is correctly reflected to the new cells. The table below is the aftermath of AutoFill.

We can see in the formula that the original cell has ‘F4’ as reference, and the formula changes to ‘F5’ and ‘F6’ respectively, based on the direction of the fill handle (in this case downwards) and how much it moved from the original cell.

cell G4 (original): =IF(F4>=45,0,5)
Copied cells
cell G5 (cell below original): =IF(F5>=45,0,5)
cell G6 (2 cells below original): =IF(F6>=45,0,5)

We have successfully managed to reflect the formula in multiple cells.

 

POINT!

The Autofill does not only copy vertically but also horizontally, so now we will look at how to copy formulas horizontally.

Below, we used the Autofill horizontally to calculate the sum of ‘Number’, ‘Total (of sales amount)’ and ‘Transportation fee’ by copying the formula of the ‘Sales price’ total (cell D7).

We can see in these SUM formulas that the reference in the formula is being changed as the cell moves to the right.

cell D7 (original)

=SUM(D4:D6)

cell G7 (copy)

=SUM(G4:G6)

 

When we used Autofill for 3 columns (from column D to G),  the reference in the formula also moved 3 columns. We can see that the Autofill automatically changes the reference according to the direction of the Autofill.

 

Absolute cell reference

What is an absolute cell reference?

An absolute cell reference keeps references in a formula fixed. Unlike the relative cell reference, it will not change the content of the formula according to how much it moved from the original cell.
In order to keep the reference fixed, we use the symbol ‘$’ in front of the cell/column/row we want to fix.

Example) To keep cell ‘A1’ fixed → $A$1

Before looking into detail of the absolute cell reference, we will look at an example that the relative cell reference does not work out.

Example of the relative cell reference not working)

The table below is a sales report that automatically extracts the product name based on the product code on the left.
In cell ‘C7’, there is ①”a VLOOKUP formula of automatically pulling out the correct product name based on the product code on the left, referring to the list of product codes and names on the right (I4:K9)”.
(For more information on the VLOOKUP function, click here)

When we tried to do the same for ‘C8’~ ‘C9’ with Autofill, we got the following results:

Results after Autofill 

Cell ‘C8’ looks like it is correct, but the error symbol “#N/A” in cell C9 must be “Table (plastic)”.
“#N/A” appears when there is an error in the formula, so it shows that the formula in cell C9 is not correct.

We will look at what happened to the formula in cell ‘C9’.

Why is there a “#N/A”?

Below is the original formula and the copied formula:

Cell C7 (original)

=VLOOKUP(B7,I4:K9,2,0)

Cell C9 (copy)

=VLOOKUP(B9,I6:K11,2,0)

(Check 1) The product code cell ‘B7’ has been copied 2 cells downwards with relative cell reference using Autofill. The cell referred to is cell ‘B9’, and it is correct. The problem does not seem to be lying here.

(Check 2) The reference range of product code (the table on the right) ‘I4:K9’ has also been copied 2 cell downwards and resulted in the range ‘I6:K11’.

To get the correct results, the reference range of the product code (i.e. cell range ‘I4:K9’) should have been fixed for cell ‘C9’, but due to relative cell reference, the reference range also moved, displaying an error as it could not find the product code “A02” (cell ‘I5’).
To keep parts of the formula fixed, we use the ‘absolute cell reference’.

Example of use) Input the ‘$’ symbol/fix a reference

We will look at how to fix a reference in the formula.
The table below is an example that has a formula set up with absolute cell reference.

How to set up a formula with absolute cell reference
To fix a reference in a formula, we input the ‘$’ symbol in front of the row/column we want to fix.

(1) Input formula =VLOOKUP(B7, I4:K9 ,2,0)” in cell C7.
(2) Input ‘$’ in front of the row and column of reference range ‘I4:K9’.
→ ‘$I$4:$K$9’
(※Input ‘$’ before every alphabet and number of ‘I4:K9’)

=VLOOKUP(B7,$I$4:$K$9,2,0)

 

Check the Autofill results

After creating a formula with absolute cell reference, the new formula is ready to be copied.

The formula in cell ‘C9’:

=VLOOKUP(B9,$I$4:$K$9,2,0)

The reference ‘I4:K9’ we fixed is properly fixed in cell ‘C9’ as well.
By using the ‘absolute cell reference’, we can fix parts of the function and copy them to other cells without having to worry about the formula changing.

 

About the ‘$’ symbol

What does ‘$’ symbolize here?

By adding it in front of the column (A, B, C…) or row (1, 2, 3…), it fixes the specific column/row and prevents it from changing when the formula is copied to other cells.

 

Shortcut to input ‘$’

There is a shortcut to add the ‘$’ symbol in the formula. The [F4] key is helpful.

Now we will try using the [F4] key to set up an absolute cell reference formula.

Example of use) Using the F4 key

Method① Set it up while typing the formula

We can set it up with the function key (upper hand on keyboard) while typing the formula.
In this case…

(1) Input ” =VLOOKUP(B7,I4:K9,2,0)” in cell C7.
(2) Select cell range ‘I4:K9’ with mouse.
(3) Press the [F4] key.
(4) Input the rest of the formula.

The formula is now complete.
Make sure to check the formula bar that the ‘$’ symbol is added in (2) properly.

=VLOOKUP(B7,$I$4:$K$9,2,0)

Method② Fix an already written formula from the formula bar

If the formula is already written, we can fix it from the formula bar (on the upper hand in Excel).

What is a formula bar?

A long section on the upper hand of Excel, and displays values or formulas that are written in the selected cell.
Furthermore, we can correct the data in the formula bar.

In this case…

(1) Select cell C7 where the formula is written.
(2) Select range ‘I4:K9’ with mouse on formula bar.
(3) Press the [F4] key once.
(4) Press ENTER to finish task.

The set up is complete.
In the same way as method ①, ‘$’ has been added successfully in the formula, and the absolute cell reference will work.

Like this, use the absolute cell reference when there are sections in the formula that need to be fixed (not changed).

 

Mixed cell reference

What is a mixed cell reference?

A mixed cell reference is a mixture of ‘relative cell reference’ and ‘absolute cell reference’, and there are 2 types:

・ Absolute cell reference (=fix) for the column direction (horizontal), and relative cell reference (=no fix) for the row direction (vertical).
・ Absolute cell reference (=fix) for the row direction (vertical), and relative cell reference (=no fix) for the column direction (horizontal).

It is a complicated form of reference but a useful one to remember when using Excel.

In what kind of occasion is the mixed cell reference useful? We will look at an example.

Example for mixed cell reference)

Below is an income and travel expense list for employees in a company.
In cell D11, a SUMIF formula to calculate ① the income (D4:D7) of the Sales team (cell C11) searching from the department (B4:B7). (For more information on the SUMIF function, click here)
To extract the income total and travel expense total for each of the departments using Autofill, what should we do?

First of all, we fixed (=absolute cell reference) cell ranges ‘B4:B7’ with the departments and the ‘D4:D7’ with the incomes.

=SUMIF($B$4:$B$7,C11,$D$4:$D$7)


Check the Autofill results
We will now check what will happen if we Autofilled cell D11 downwards.

① When Autofilled downwards

The formula in cell 12 is,

=SUMIF($B$4:$B$7,C12,$D$4:$D$7)

The total income of C12 (Marketing) was calculated. This one is a success.

② When Autofilled to the right

The formula in cell E11 is,

=SUMIF($B$4:$B$7,D11,$D$4:$D$7)

The results are incorrect.

We will look at the problem in formula ②.

(1) The cell that indicates the department has moved right from ‘C11’ to ‘D11’.
(2) For this cell, the total for travel expenses =‘E4:E7’ must be calculated, but the income=‘D4:D7’ is fixed through absolute cell reference.

What needs to be done is:

(1) The reference for the department name must not be fixed for only the row direction (vertical).
(2) The cell range to calculate the total for must not be fixed for only the column direction (horizontal).

The ‘mixed cell reference’ will help us to correct this formula.

Example of use) Set up the reference using the F4 key②

 

Setting up the mixed cell reference

① Fix only the column direction (horizontal) of the reference for department → ‘$C11’

(1) Highlight ‘C11’ from the formula bar.
(2) Press the [F4] key 3 times.

=SUMIF($B$4:$B$7,$C11,$D$4:$D$7)

② Fix only the row direction (vertical) of cell range to calculate the total for ‘$D$4:$D$7’ → ‘D$4:D$7’

(1) Highlight ‘$D$4:$D$7’ from the formula bar.
(2) Press the [F4] key once.

=SUMIF($B$4:$B$7,$C11,D$4:D$7)

※See that the position of the ‘$’ symbol changed every time the [F4] key was pressed.

 

Finally, we will see if the result for Autofill came out correctly.

Autofill results)

When Autofilled to the right, the ‘travel expense total for Sales team’, and
when Autofilled downwards, the ‘income total for Marketing team’ has been correctly extracted.

 

POINT!

The relative cell reference・absolute cell reference・mixed cell reference can be changed by the number of times the [F4] key is pressed.

The diagram below shows how to change the type of cell reference using the [F4] key:


Cell references are important functions in using Excel, so a few practices might be good in moving on to the next stages of studying more complicated functions and formulas.