# Relative reference, absolute reference, and mixed reference

Uploaded time: October 8, 2021 Difficulty Beginner

A free Office suite fully compatible with Microsoft Office

Relative reference, absolute reference, and mixed reference

# Relative reference, absolute reference, and mixed reference

When copying formulas to other cells, we might encounter three reference modes, namely relative reference, absolute reference, and mixed reference. We need to choose the proper reference method according to the specific calculation requirements and the formula.

· **1. Relative Reference**

When copying formulas to other cells, the formula will change according to the position of the referenced cell.

Take this table as an example. Enter **=B2*C2** in cell D2, and then pull down the fill handle to copy the formula. We can see that the formula has changed according to the position of the cell.

· **2. Absolute reference**

When copying formulas to other cells, the position of the referenced cell remains unchanged.

We select cell C5, enter **=B5*B20** in the cell, and then pull down the fill handle to copy the formula. Now we find that the results are incorrect. Why?

When we put the cursor on cell C6, we can find that the formula is **=B6*B21**, and the corresponding content of cell B21 is empty. This is the reason why the result will be 0. Because the referenced cell has moved when copying the formula.

We need to use the symbol **$** to make an absolute reference to the B20 cell, and always keep the referenced B20 cell as a fixed referenced cell. Select B20 in C5 and press the **F4** shortcut key to add an absolute reference. Then pull down the fill handle to copy the formula. Now we can get the correct calculation result.

· **3. Mixed reference**

After what we have just learned, we need to know that using absolute reference in cells will lock the row and column labels at the same time.

The mixed reference can lock the row or column label of the cell separately.

For better understanding, I will show you the two reference ways.

+In the absolute reference table, enter **=A1** in the F2 cell. Press the **F4 **shortcut key to add the absolute reference symbol, then drag the fill handle to copy the formula.

Since the row and column labels are locked with the symbol $, the content of all the filled cells is **Year**.

+In the mixed reference table, enter **=A1** in cell F9 and press the **F4** shortcut key several times to switch to mixed reference. Here, we press F4 three times and add a **$** sign in front of the column label, that is, **=$A1**.

At this time, we have locked the column label without locking the row label. When we use the fill handle to fill the formula to the right, the cell content will not change. When we use the fill handle to fill down the formula, the cell content will change according to the position of the cell.

In the same way, enter **=A1** in cell F15. Press the **F4** shortcut key twice to add the **$** symbol in front of the line number.

Now we only lock the line label. If we use the fill handle to fill down the formula, the cell content won't change. If we use the fill handle to fill to the right, the content of the cell will change.

In most cases, mixed reference is used in conjunction with functions. Therefore, we need to practice several times to strengthen our understanding.

To be an office excel advancers, you could learn how to use WPS Office Spreadsheet online in WPS Academy.

Also Read:

- 1. Look up data quickly with LOOKUP function
- 2. COUNTIFS function: count data of multiple criteria
- 3. Use the ROW function to mark the row number
- 4. Use IFS function to check whether multiple conditions are met
- 5. Use the cross-reference function to quickly jump
- 6. Get started with functions and formulas

Does this video help you?