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.