Relative references and absolute references in Excel

relative references and absolute references in Excel

Because relative references and absolute references are used in many operations, such as conditional formatting, data validation, function formulas, advanced charts, and even macros and VBA code.

If you don't know how to quote, so many tall applications are all free of talk.

When you refer to a cell or range in a formula, the type of reference can be grouped into the following three categories:

Relative reference

When you copy a formula to another cell, the row or column reference changes. The so-called row or column reference changes, meaning that the number representing the row and the letter representing the column will change according to the actual offset.

Give an example: D5 cell input Formula =A1

(Here we do not map the AH, you can open the Excel operation yourself) when the formula is filled down to D6, D7 cell, the formula will turn into =A2, =a3.

If you fill the formula to the right E5, F5 cell, you will become =b1, =c1 ...

This relative reference is actually loose with the wind down, gan to do a wall of grass ah.

Absolute reference

When you copy a formula to another cell, the row and column references do not change.

For example: D5 cell Input formula = $A $

When the formula is filled down, the formula remains = no matter which cell the copy is populated to $A

When the formula is filled to the right, no matter which cell the copy is populated to, the formula or = $A

This absolute reference is the eternal heart unchanged, lifelong to forever AH.

Mixed references

One of the rows or columns is a relative reference and the other is an absolute reference.

Give an example: D5 cell input Formula =a$1

Note that there is a $ symbol missing from the example above and the formula remains =a$1 when the formula is filled down to D6, D7 cells.

If you fill the formula to the right E5, F5 cell, you will become =b$1, =c$1 ...

You see here, because the row number is preceded by a $ symbol, the line number is fixed, and the column labels are not preceded by the $ symbol, the column labels will change. Does this mean that money is good everywhere?

There is also a use of mixed references: = $A 1

This is similar to the example above, but the $ symbol runs to the front of the column, and when the formula is filled down to the D6, D7 cell, the formula becomes = $A 2, = $A 3 ...

If you fill the formula to the right E5, F5, the formula remains = $A 1.

This mixed reference is half the water half of the fire, since ancient times beauty love rich, no money can roll how far is how far ah.

Using different reference methods, in addition to manually enter the dollar sign, you can put the cursor in the formula bar, press the F4 key, each time, the dollar sign will run to different places, that is, different ways of referencing. You can also click the F2 key, and then press the F4 key, the effect is the same.

Relative references and absolute references are better understood, while mixed references are relatively complex. When to use what kind of reference, but also in the actual operation of a lot of hard to understand.

The formula used in the multiplication table below is the use of different mixed references. If you are interested, you can try it, this will be more helpful to understand.