excel cell reference

Cell reference is a term in Excel, which refers to the identification of the coordinate position of a cell in the table. is an important learning content for computer basics.

#Cell references are divided into two reference styles: A1 and R1C1. In the A1 reference style, the column number and row number of the cell are used to indicate its location, such as C5, which indicates row 5 of column C. In the R1C1 reference style, R represents row, C represents column, and R5C4 represents the fifth row and fourth column, which is cell D5.

EXCEL cell references include absolute references, relative references and mixed references.

Absolute Reference

An absolute cell reference in a cell (for example, $F$6) always refers to cell F6 at the specified location. If the position of the cell containing the formula changes, the absolute referenced cell always remains the same. If you copy a formula across multiple rows or columns, absolute references will not be adjusted. By default, new formulas use relative references, they need to be converted to absolute references. For example, if you copy the absolute reference in cell B2 to cell B3, it will be the same in both cells, $F$6.

Relative References

Relative cell references in formulas (such as A1) are based on the relative position of the cell that contains the formula and the cell reference. If the location of the cell where the formula is located changes, the reference changes as well. If you copy a formula across multiple rows or columns, references automatically adjust. By default, new formulas use relative references. For example, if you copy a relative reference in cell B2 to cell B3, it will automatically adjust from =A1 to =A2.

Mixed references

Mixed references have absolute columns and relative rows, or absolute rows and relative columns. Absolute reference columns take the form $A1, $B1, etc. Absolute reference lines take the form A$1, B$1, etc. If the position of the cell where the formula is located changes, the relative reference changes, but the absolute reference does not change. If you copy a formula across multiple rows or columns, relative references automatically adjust, while absolute references do not. For example, if you copy a mixed reference from A2 to B3, it will be adjusted from =A$1 to =B$1.

When entering formulas in Excel, you can simply switch between relative and absolute cell references by using the F4 key correctly. Now give an example.

The formula entered in a certain cell is "=SUM(B4:B8)".

Select the entire formula and press the F4 key. The content of the formula changes to "=SUM($B$4:$B$8)", which means absolute references to both horizontal and vertical cells.

Press the F4 key for the second time, and the formula content changes to "=SUM(B$4:B$8)", which means an absolute reference to the horizontal rows and a relative reference to the vertical rows.

Press the F4 key for the third time, and the formula will change to "=SUM($B4:$B8)", which means a relative reference to the horizontal rows and an absolute reference to the vertical rows.

When the F4 key is pressed for the fourth time, the formula returns to the initial state "=SUM(B4:B8)", that is, relative references are made to cells in both horizontal and vertical rows.

For more Excel-related technical articles, please visit the Excel Tutorial column to learn!

The above is the detailed content of excel cell reference. For more information, please follow other related articles on the PHP Chinese website!