Excel - Absolute References

The cell reference remains fixed throughout the column/row and is termed an absolute reference. It acts as a constant value, so it always refers to the exact cell location no matter where the formula is applied. An absolute reference is always denoted by a dollar sign($). For example ($D$8), row 8 and column D remain fixed while rendering the formula either downwards or sidewards.

Keyword Shortcut for Absolute Reference

Yes, the absolute reference keyword shortcut is fn+ f4. For example, if you write and select =D4-D6 in the formula box and press the fn +F4 keys, then its relative references D4 and D6 are switched into the absolute cell references like ($D$4) and ($D$6) in the formula. Therefore, the formula becomes =$D$4-$D$6.

How to use Absolute Reference?

Step 1 − Consider the sample dataset, which comprises three columns named "Electronic Device," "Individual product," and "Total price." The main task is to evaluate the Total price of the electronic device in the C column.

First, you can use the formula "=$C$10*B2" in the C2 cell. In this Excel formula, the B2 is a relative reference, and the $C$10 is an absolute reference pointing to the value(12) remaining constant when filling the same formula in the range C3:C5.

You can press the Enter tab to get the result.

Step 2 − After that, drag the fill handle(+) from the C2 cell to the C5 cell to calculate the total price of the remaining electronic device. The order quantity is the same for all the products.

When to Avoid Using Absolute References in Microsoft Excel?

• If the dynamic change of the cell reference is required in the formula, then use the relative reference.
• When a complex formula evaluates with different ranges of cells, then, in this case, absolute reference encounters a problem.
• If you wish to fix cell values only in rows or columns, use the Mixed reference, for example, H$2.