Absolute Cell Reference Shortcut in Excel (4 Useful Examples)

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.

Download Practice Workbook


Shortcut for Using Absolute Cell Reference in Excel

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

Dataset


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.

Press the 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 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

Shortcut for A Single Cell

Now press Enter and the output will be as follows.

Absolute Cell Reference Shortcut for A Single Cell

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.

Shortcut for A Single Cell

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-

Press 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.

Press the 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 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)

Absolute Cell Reference Shortcut for Cell Range

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

for Cell Range

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


Similar Readings:


3. Absolute Cell Reference Shortcut for Column 

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

Press 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 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)

Absolute Cell Reference Shortcut for Column

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

Shortcut for Column Reference

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

Finally, the output will look as follows.

for Column


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.

Press 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 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 at 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)

Shortcut for Row Reference

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

for Row

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

At last, you’ll see the following output.

Output of lookup value in row


If Excel Shortcut F4 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

Conclusion

This is how you may use the shortcut for fixing the absolute cell reference in the case of a single cell, cell range, a column reference, and a 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.


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo