Guides

$ Signs in Excel: Relative, Absolute and Mixed References (F4)

By Ravindra Bagale, Excel & Data Analytics trainer · LinkedIn · Published: 6 October 2026 · Last updated: 6 October 2026

Zoya writes =B2*F1 to work out a rep's commission with the rate in F1. She copies it down and every other row shows 0. In row 3 the formula has become =B3*F2, and F2 is empty. Adding two dollar signs, =B2*$F$1, fixes all six rows.

A relative reference (B2) changes when you copy the formula. An absolute reference ($F$1) never changes. A mixed reference locks only the column ($A3) or only the row (B$2). Press F4 while the cursor is on a reference to cycle through the four types.

Download the practice file (absolute-relative-references.xlsx)

Excel file, 7 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.

=B2*$F$1
=$A3*B$2

The four types

Example 1: one commission rate

F1 holds the rate 5%. C2 holds =B2*$F$1 and is copied to C7.

Change F1 to 6% and all six commissions change. The total in C8 is ₹43,300 at 5%.

Example 2: a price × quantity grid with one formula

Prices are down column A (A3:A6) and quantities across row 2 (B2:E2). In B3 type =$A3*B$2 and copy it to the whole grid B3:E6.

• $A3: the column A is locked, so every cell reads a price from column A; the row changes as you go down.
• B$2: row 2 is locked, so every cell reads a quantity from row 2; the column changes as you go right.
• E6 becomes =$A6*E$2 = 35 × 25 = 875.

Shortcuts and traps

• F4 works while editing a formula. On some laptops press Fn + F4.
• Select a whole reference such as B2:B7 before F4 to lock both ends at once.
• Inside an Excel Table you rarely need $: =[@Sales]*Rate with a named cell Rate stays correct.
• Cutting and pasting (Ctrl + X) moves a formula without changing its references; copying (Ctrl + C) changes the relative parts.