What does F4 do when editing a reference?

Cycles through absolute and mixed references

Cell References

By the Syncfusion DevTutorials team · Last updated October 20, 2026

Relative, absolute, and mixed references, copying formulas, and how references update when the sheet changes.

One of Excel's biggest advantages is that you can write a formula once and copy it to many cells. Excel automatically adjusts cell references when a formula is copied, helping you save time and avoid repetitive work. In this lesson, you'll learn about relative, absolute, and mixed references, and how to control whether references change or stay fixed when formulas are copied.

Open the Excel sandbox and follow along.

Relative References

A standard cell reference such as B2 is called a relative reference.

A relative reference changes based on the formula's new location when copied.

For example:

If D2 contains: =B2*C2

and you copy the formula to D3, Excel automatically changes it to: =B3*C3

If you copy it to D4, it becomes: =B4*C4

Each copied formula works with the values from its own row.

Relative references are the most commonly used type of reference because they automatically adjust when formulas are copied.

Absolute References

Sometimes a formula must always refer to a specific cell, such as a tax rate, discount rate, or constant value. In this situation, use an absolute reference.

An absolute reference uses dollar signs ($) to lock both the column and row.

Example: $F$1

If E2 contains: =D2*$F$1

and the formula is copied down the column, Excel changes only the relative reference:

=D3*$F$1

=D4*$F$1

The reference $F$1 always remains fixed.

While entering a formula, place the cursor on a cell reference and press F4 to cycle through the available reference types automatically.

Mixed References

A mixed reference locks either the row or the column, but not both.

Reference	Column	Row	Behavior When Copied
`B2`	Changes	Changes	Both row and column adjust
`$B$2`	Fixed	Fixed	Never changes
`$B2`	Fixed	Changes	Column stays B, row changes
`B$2`	Changes	Fixed	Row stays 2, column changes

Mixed references are particularly useful when formulas are copied both across columns and down rows, such as in multiplication tables or calculation grids.

Copying Formulas

You can copy formulas using several methods:

Copy and Paste

Select the formula cell.
Press Ctrl + C.
Select the destination cells.
Press Ctrl + V.

Fill Handle

The fill handle is the small square in the bottom-right corner of a selected cell.

To use it:

Select the formula cell.
Point to the fill handle.
Drag it downward, upward, left, or right.

You can also double-click the fill handle to automatically fill formulas down adjacent rows containing data.

Before copying a formula, decide whether each reference should move or stay fixed. Use relative references for values that should change and absolute references for values that should remain constant.

Formula Updates

Excel automatically updates references when worksheet changes occur.

Inserting Rows or Columns

If you insert rows or columns referenced by a formula, Excel updates the references automatically.

Example:

If a formula references: =SUM(B2:B10)

and you insert a row within that range, Excel expands the formula to include the new row.

Moving Referenced Cells

When referenced cells are moved using cut and paste, Excel updates the formula to follow the moved data.

Deleting Referenced Cells

If a referenced cell, row, or column is deleted, Excel cannot find the required data and displays: #REF!

This indicates an invalid reference.

The #REF! error appears when a formula refers to a cell that no longer exists. If this happens accidentally, press Ctrl + Z immediately to undo the deletion.

Common mistakes

Mistake	Result	Solution
Copying `=D2*F1` down a column	References change to F2, F3, and F4	Use `=D2*$F$1`
Locking every reference with `$`	Every row returns the same result	Lock only the references that should stay fixed
Deleting a referenced cell	`#REF!` error appears	Undo the deletion or correct the formula
Locking the wrong row or column	The formula behaves incorrectly when copied	Use F4 to choose the correct reference type
Entering a constant directly into formulas	Updates require editing every formula	Store the constant in a separate cell

Exercise

Click Reset in the sandbox header and enter the following data:

A1: Item
B1: Price
C1: With tax
E1: Tax rate
F1: 0.1
A2: Lamp, B2: 40
A3: Desk, B3: 150
A4: Chair, B4: 60

Then complete each task and click Check.

What to Remember

Relative references (B2) change when a formula is copied.
Absolute references ($F$1) remain fixed when copied.
Mixed references ($B2 and B$2) lock either the column or the row.
Press F4 to switch between relative, absolute, and mixed references.
Use the Fill Handle to copy formulas quickly.
Excel automatically updates references when rows and columns are inserted or moved.
Deleting a referenced cell causes a #REF! error.
Store constants such as tax rates in separate cells and use absolute references to refer to them.

In the next lesson, you'll learn how to use AutoFill to quickly create sequences of numbers, dates, and repeating patterns.