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.

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.

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.

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-

⏭ 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-

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

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.

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.

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-

⏭ 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-

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.

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-

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

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.

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.

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

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.

Related Articles

• Example of Mixed Cell Reference in Excel (3 Types)
• Cell Reference in Excel VBA (8 Examples)
• Relative and Absolute Cell Address in the Spreadsheet
• Example of Relative Cell Reference in Excel (3 Criteria)
• How to Keep a Cell Fixed in Excel Formula (4 Easy Ways)