Cell reference concept in Excel

Excel’s address reference concept

The cell arrangement of excel is equivalent to the fourth quadrant of the rectangular coordinate system

The rows are arranged from top to bottom from 1 to 66536 rows

The columns are arranged from left to right as A-IV

The upper leftmost cell is the origin, which is cell A1

The second row and the third column are C2

Cell references are divided into relative references and absolute references

The so-called relative reference means that the cell referenced in the formula changes with the change of the position of the cell where the formula is located

For example, set the formula =A2

When B2 is copied and pasted to B3, the formula of B3 changes according to the position from B2 to B3 and becomes =A3

The so-called absolute reference means that the cells referenced in the formula do not change with the change of the position of the cell where the formula is located

Still the same as the above example, set =$A$2 in B2 (the $ sign is the symbol of absolute reference)

Copy the B2 formula to B3. The formula is still =$A$2

Suppose the formula of B2 is =$A2, then this is the so-called mixed reference

In the formula, there is a $ sign before the column label, which means that when the formula is copied to other locations, the column will not change and the row number will change accordingly

And =A$2 means that when the formula is copied to other locations, the columns will change but the rows must not change

Brief introduction to the specific meaning of relative references in Excel

I think that explaining the specific meaning of relative references in Excel alone cannot solve the problem of references in Excel in depth, so I will explain it to you clearly together, which will be better:

Most formulas will reference one or more cells using a cell or range address (or name if there is only one cell). There are 4 types of cell references, which are distinguished by dollar signs:

◆Relative reference: Based on the relative position of the cell containing the formula and cell reference, it is a completely relative reference. If the location of the cell where the formula is located changes, the reference changes as well. If you copy or fill a formula across multiple rows or columns, references automatically adjust. For example A1.

◆Absolute reference: Refers to the cell at the specified location without changing the cell address. It is a completely absolute reference. An absolute cell reference always refers to a cell at a specific location. If the position of the cell containing the formula changes, the absolute reference remains unchanged. If you copy or fill a formula across multiple rows or columns, absolute references will not be adjusted. When the formula is copied, the cell reference does not change, such as $A$1.

◆Row absolute: This kind of reference is a partially absolute reference. When the formula is copied, the relative column reference adjusts, but the absolute row reference does not change, such as A$1.

◆Column absolute: This kind of reference is a partially absolute reference. When the formula is copied, the relative row reference adjusts, but the absolute column reference does not change, such as A$1.

Hope it helps.

The above is the detailed content of Cell reference concept in Excel. For more information, please follow other related articles on the PHP Chinese website!