Relative cell reference and Absolute cell reference.(Microsoft Excel)

Japanese version.

There are two methods of cell referencing (specifying cells in formulas and functions): relative cell reference and absolute cell Reference.

Two Cell Reference

Relative cell reference

When Auto Fill or Copy and Paste formulas with cell references, the position of the cell reference shifts one by one.
This is a relative cell reference.

If you specify a cell reference without being aware of it, it becomes a relative cell reference.

Absolute Cell Reference

This is a method of specifying that Auto Fill or Copy and Paste formulas with cell references does not change the position of the cell references.

Absolute cell references are set by prefixing the reference you do not want to change with a $.

You can specify rows only, columns only, or both.

When $ is added to only rows or only columns, it is also called a compound cell reference.

Entering $ in the formula by keyboard entry or pressing the F4 key in the cell designation portion of the formula bar toggles between both matrix, row only, column only, and relative cell reference.

Example of using absolute cell references

We always want to use cell C2 as the commission rate, so we specify $ in the column and row.

Example of cell reference change

This is how the reference changes when you copy and paste from a yellow cell to a green cell.

(Actually, the reference does not change with just the letter A1; you need to add = to make it a cell reference.)

Spil

A feature called Spill has been added to Excel 2019 and later.

This allows formulas to be efficiently constructed without absolute cell references in many cases.

---

Links

Introduction