**Absolute cell reference** is necessary when we want to lock the position of a certain cell in order to copy the cell for further use. In a previous article, how to do absolute cell reference in Excel is discussed elaborately. Interestingly, in today’s talks, I am showing the shortcut for absolute cell reference in detail with the necessary explanation.

Firstly, we’ll see the overview of the uses of the shortcut for absolute cell reference in the following table. Then the main discussion will be shown.

Shortcut | Cell Reference | Description |
---|---|---|

Press F4 key |
Single Cell or Cell Range | Allows changing neither the column nor the row. |

Press the F4 key twice |
Row Reference | Allows changing the column reference but the row reference is fixed. |

Press the F4 key thrice |
Column Reference | Allows changing the row reference but the column reference is fixed. |

**Table of Contents**hide

## Absolute Cell Reference Shortcut Overview

Let me give chance to display today’s dataset. In the following dataset, some items with their order ID, states of the U.S., and sales are provided.

## 1. Absolute Cell Reference Shortcut for a Single Cell

In the beginning, we’ll see the shortcut of absolute cell reference for a single cell.

**F4**key once

Assuming that the tax rate in percentage is given (cell: **I5**). Now we want to calculate the sales tax for each item based on the tax rate and the number of sales.

Just follow the steps below.

**Steps:**

**⏭** Select the cell where you want to calculate the sales tax

**⏭** Press the **Equal** (**=**) sign and enter the following formula.

`=E5*I5`

Here, **E5 **is the starting cell of sales, and **$I$5** is the tax rate

**⏭** Move the cursor after the cell of **I5** and press the **F4 **key once. Then you’ll see the absolute reference **$I$5 **as and the formula will be-

`=E5*$I5$5`

**⏭** Now press **Enter **and the output will be as follows.

**⏭** Use the **Fill Handle Tool** to copy the formula for the below cells. If you look closely at the lower-right corner of the above output cell, you’ll see a **Plus **sign. Then move the cursor on the **Plus **sign and drag down the cursor.

Then the sales tax for all items will be as the following output.

**Note: **In the previous version of Excel on the Mac, the shortcut of absolute cell reference is-

**Command**+

**T**

But in the case of Mac Excel 365, the following shortcut also works-

**Fn**+

**F4**keys

**Related Content:** **Absolute Reference in Excel (With Examples)**

## 2. Absolute Cell Reference Shortcut for Cell Range

We’ll use the following shortcut for placing absolute cell reference in the case of cell range.

**F4**key once

If you want to find a certain thing e.g. sales of ‘Monitor’ (lookup value) from the cell range **B5:E15**, you may utilize the **VLOOKUP** function.

**Steps:**

**⏭** Select the cell where you want to get the expected amount of sales.

**⏭** Press the **Equal** (**=**) sign and enter the following formula.

`=VLOOKUP(G5,B5:E15,4,FALSE)`

Here, **G5 **is the lookup value, **B5:E15** is the table array (cell range), **4** is the column index as the sales is located column no. 4 from the ‘Items’ column, and lastly **FALSE** is for exact matching.

**⏭** Move the cursor at the right side of the cell range **B5:E15** and press the **F4 **key once. Then you’ll see the absolute reference as **$B$5:$E$15** and the whole formula will be-

`=VLOOKUP(G5,$B$5:$E$15,4,FALSE)`

**⏭** Now, press **Enter **and the output will be as follows.

The above picture shows the number of sales of ‘Monitor’ as $1500.

**Similar Readings**

**Difference Between Absolute and Relative Reference in Excel****Different Types of Cell References in Excel (With Examples)****Reference Another Sheet in Excel (3 Methods)****Excel Sheet Name in Formula Dynamic (3 Approaches)****How to Lock a Cell in Excel Formula (2 Ways)**

## 3. Absolute Cell Reference Shortcut for Column

The shortcut for ensuring absolute cell reference in the case of column reference is-

**F4**thrice

In the previous example, we saw how to find one lookup value. Imagine, you want to get a series of lookup values in a column e.g. the sales of ‘Monitor’, ‘AC’, ‘Fan’, and ‘TV’.

In such a situation, you may follow the steps below.

**Steps:**

**⏭** Select the cell where you want to calculate the sales tax

**⏭** Press the **Equal** (**=**) sign and enter the following formula.

`=VLOOKUP(G5,$B$5:$E$15,4,FALSE)`

Here, **G5 **is the lookup value, **B5:E15** is the table array (cell range), **4** is the column index as the sales are located column no. 4 from the ‘Items’ column, and lastly **FALSE** is for exact matching.

**⏭** Move the cursor at the right side of the **G5 **cell and press the **F4 **key thrice. Then, you’ll see $G5 as the absolute reference and the whole formula will be-

`=VLOOKUP($G5,$B$5:$E$15,4,FALSE)`

**⏭** Now, press **Enter **and the output will be as follows.

**⏭** Use the **Fill Handle Tool** to copy the formula for the below cells.

Finally, the output will look as follows.

## 4. Absolute Cell Reference Shortcut for Row

We can use the following shortcut for fixing the absolute cell reference in the case of row reference.

**F4**twice

We have seen how to get a single lookup value and a series of lookup values in a column. Right now, we’ll see how to find a series of lookup values in a row.

**Steps:**

**⏭** Select the cell where you want to calculate the sales tax

**⏭** Press the **Equal** (**=**) sign and enter the following formula.

`=VLOOKUP(H5,$B$5:$E$15,4,FALSE)`

Here, **H5 **is the lookup value, **B5:E15** is the table array (cell range), **4** is the column index as the sales are located column no. 4 from the ‘Items’ column, and lastly **FALSE** is for exact matching.

**⏭** Move the cursor to the right side of the **H5** cell and press the **F4 **key twice. Then you’ll see **H$5** as the absolute reference and the formula will be as follows.

`=VLOOKUP(H$5,$B$5:$E$15,4,FALSE)`

**⏭** Now, press **Enter **and the output will be as follows.

**⏭** Use the **Fill Handle Tool** to copy the formula for the right-side cells.

At last, you’ll see the following output.

## If Shortcut Key for Absolute Reference is Not Working

You may get troubled with the **F4 **shortcut for absolute cell reference in some cases especially while using a laptop keyboard. Because the **F4 **key on some keyboards controls computer brightness or volume or as a connector to project to another screen.

In such cases, the shortcut will be as follows.

Shortcut | Cell Reference |
---|---|

Press Fn + F4 keys |
Single Cell or Cell Range |

Press Fn + F4 keys twice |
Row Reference |

Press Fn + F4 keys thrice |
Column Reference |

**Download Practice Workbook**

## Conclusion

This is how you may use the shortcut for fixing the absolute cell reference in the case of a single cell, cell range, column reference, and row reference. I strongly believe that today’s article will increase your caliber. However, if you have any queries or suggestions, don’t forget to share those in the following comments section.