Summary of Excel formula reference address method

Rule: The column and line numbers with the absolute reference "$" are the absolute addresses that will not change when the formula is copied to the side, and the column and line numbers without the absolute address symbol are relative addresses, and will change when the formula is copied to the side. Partial addresses change when mixing references.

Relative references, absolute references, and mixed references refer to how the address changes when you use the address of a cell or range of cells in a formula when you copy the formula to the next. Examples of specific circumstances:

1, relative reference, copy the formula when the address followed by changes, such as:

C1 cells have formulas: =a1+b1

When you copy a formula to cell C2: =A2+B2

When you copy a formula to cell D1: =B1+C1

2, absolute Reference, copy the formula when the address will not follow the changes, such as:

C1 Cell has a formula: = $A $1+ $B $

When the formula is copied to cell C2, it is still: = $A $1+ $B $

When the formula is copied to cell D1, it is still: = $A $1+ $B $

3, mixed reference, copy the formula when the address part of the content followed by changes, such as:

C1 Cell has a formula: = $A 1+b$1

When you copy the formula to cell C2: = $A 2+b$1

When you copy the formula to cell D1: = $A 1+c$1

I don't know, look at the picture.

As the position of the formula changes, the position of the referenced cell is also a relative reference, and the position of the cell referenced as the position of the formula changes is the absolute reference.

Let's talk about the difference between "C4", "$C 4", "c$4" and "$C $".

In a worksheet, the data in C4 and C5 are 60, 50, respectively. If you enter "=C4" in cell D4, then drag D4 down to D5, the content in D5 becomes 50, the formula inside is "=C5", the contents of the D4 to the right are 60, and the formula becomes "E4,e4".

Figure 1 Excel Worksheet