# Absolute Cell Reference Shortcut in Excel – 4 Examples

This is an the overview.

Shortcut Cell Reference Description
Press F4 Single Cell or Cell Range Changes neither the column nor the row.
Press F4 twice Row Reference Changes the column reference but not the row reference.
Press F4 thrice Column Reference Changes the row reference but not the column reference.

The following dataset showcases items, their order ID, states, and sales.

## Example 1 – Absolute Cell Reference Shortcut for a Single Cell

Press F4 once

The tax rate in percentage is given in I5. To calculate the sales tax for each item based on the tax rate and the number of sales:

Steps:

• Select the cell in which you want to calculate the sales tax
• Press the Equal (=) sign and enter the following formula.
`=E5*I5`

E5 is the first cell in sales and \$I\$5 is the tax rate

• Move the cursor to I5 and press F4 once. You’ll see the absolute reference \$I\$5. The formula will be:

`=E5*\$I5\$5`

• Press Enter

This is the output.

• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

Note: In previous versions of Excel for Mac, the shortcut of absolute cell reference is-

Command + T

Related Content: What Is and How to Do Absolute Cell Reference in Excel?

## Example 2 – Absolute Cell Reference Shortcut for a Cell Range

Press F4 once

To find the. sales of ‘Monitor’ in B5:E15:

Steps:

• Select the cell in which you want to see the amount of sales.
• Enter the Equal (=) sign and enter the following formula.
`=VLOOKUP(G5,B5:E15,4,FALSE)`

G5 is the lookup value, B5:E15 is the table array, 4 is the column index, and FALSE is used for exact matching.

• Move the cursor to the right side of B5:E15 and press F4 once. You’ll see the absolute reference \$B\$5:\$E\$15 and the formula will be:
`=VLOOKUP(G5,\$B\$5:\$E\$15,4,FALSE)`

• Press Enter.

This is the output.

## Example 3 – Absolute Cell Reference Shortcut for Columns

Press F4 thrice

You want to find a series of values in a column (the sales of ‘Monitor’, ‘AC’, ‘Fan’, and ‘TV’).

Steps:

• Select the cell in which you want to calculate the sales tax.
• Enter the Equal (=) sign and enter the following formula.
`=VLOOKUP(G5,\$B\$5:\$E\$15,4,FALSE)`

G5 is the lookup value, B5:E15 is the table array, 4 is the column index, and FALSE is used for exact matching.

• Move the cursor to the right side G5 and press F4 thrice. You’ll see \$G5 as the absolute reference. The formula will be:
`=VLOOKUP(\$G5,\$B\$5:\$E\$15,4,FALSE)`

• Press Enter.

This is the output.

• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

## Example 4 – Absolute Cell Reference Shortcut for Rows

Press F4 twice

To find a series of values in a row:

Steps:

• Select the cell in which you want to calculate the sales tax.
• Enter the Equal (=) sign and enter the following formula.
`=VLOOKUP(H5,\$B\$5:\$E\$15,4,FALSE)`

H5 is the lookup value, B5:E15 is the table array, 4 is the column index, and FALSE is used for exact matching.

• Move the cursor to the right side of H5 and press F4 twice. You’ll see H\$5 as the absolute reference and the formula will be:
`=VLOOKUP(H\$5,\$B\$5:\$E\$15,4,FALSE)`

• Press Enter.

This is the output.

• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

## The Shortcut Key for Absolute Reference is Not Working

Shortcut Cell Reference
Press Fn + F4 Single Cell or Cell Range
Press Fn + F4 twice Row Reference
Press Fn + F4 thrice Column Reference

## Related Article

<< Go Back to Absolute Cell ReferenceCell Reference in ExcelExcel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF