Excel cell reference

I believe that excel in the study of friends know Excel is powerful, then Excel cell reference is how it? Today's small series to give you a detailed description of the relevant content of Excel cell reference, I believe that we read the content of small writing will certainly be helpful, well, The following small series will give you a specific say:

Excel cell references include a relative reference, an absolute reference, and a mixed reference of three kinds.

One, absolute reference

Absolute cell references in a cell, such as $F $, always refer to cell F6 at the specified location. If the position of the cell that contains the formula changes, the cell that is absolutely referenced remains unchanged. If you copy a formula in multiple rows or columns, the absolute reference is not adjusted. By default, new formulas use relative references and need to convert them to absolute references. For example, if you copy an absolute reference from cell B2 to cell B3, the same is $F $ in two cells.

Second, relative reference

A relative cell reference in a formula, such as A1, is based on the relative position of the cell that contains the formula and cell references. If the position of the cell that contains the formula changes, the reference changes. If you copy the formula in multiple rows or columns, the reference is automatically adjusted. By default, the new formula uses a relative reference. For example, if you copy a relative reference from cell B2 to cell B3, the =A1 is automatically resized to =a2.

Third, mixed reference

Mixed references have absolute and relative rows, or absolute rows and relative columns. The absolute Reference column takes the form of $A 1, $B 1. The absolute reference line adopts a$1, b$1 and other forms. If the position of the cell that contains the formula changes, the relative reference changes, and the absolute reference does not change. If you copy the formula in multiple rows or columns, the relative reference is automatically adjusted, and the absolute reference is not adjusted. For example, if you copy a mixed reference from A2 to B3, it will adjust from =a$1 to =b$1.

When you enter a formula in Excel, you can simply switch the relative and absolute references of the cell as long as the F4 key is used correctly. Examples are provided.

The formula you enter for a cell is "=sum (B4:B8)."

Select the entire formula and press the F4 key, which changes to =sum ($B $ $B $) to make absolute references to horizontal and vertical cells.

The second time you press the F4 key, the formula content becomes "=sum (b$4:b$8)", which indicates absolute reference to the row, and a relative reference to the vertical line.

The third time the F4 key is pressed, the formula becomes "=sum ($B 4: $B 8)", which indicates a relative reference to the row and an absolute reference to the vertical line.

The fourth time the F4 key is pressed, the formula changes back to the initial state "=sum (B4:B8)", which is a relative reference to the cells of the vertical row.

The point to note is that the toggle function of the F4 key only works for the selected formula segment.