How to reference cells in Excel

1. Relative reference: the reference format is like "A1 ". This type of cell reference is completely relative. When the formula for referencing a cell is copied, the position of the cell referenced by the new formula changes. For example, we enter the value "1, 2, 3..." in the cell A1-A5 ..., 5 ", enter the formula" = A1 * 2 "in cell B1, and copy the formulas in cell B1 to B2 to B5 respectively, then we can see that the results in cells B2 to B5 are equal to the value of the left cell multiplied by 2.

2. Absolute reference: the reference format is like "$ A $1 ". This method of cell reference is completely absolute, that is, once it becomes an absolute reference, no matter how the formula is copied, the reference position of the cells that use the absolute reference will not change. For example, we enter the value "1, 2, 3..." in the cell A1-A5 ..., 5 ", then enter the formula" = $ A $1*2 "in cell B1, and copy the formulas in cell B1 to B2 and B5 respectively, then, we will find that the result in cells B2 to B5 is equal to the value of cell A1 multiplied by 2.

3. Absolute row reference: the reference format is like "A $1 ". The reference position of the cell is not completely absolute. When the formula for referencing the cell is copied, the reference of the new formula to the column will change, the row reference remains unchanged. For example, we enter the value "1, 2, 3..." in the range from cell A1 to cell B5 ..., 9, 10, then enter the formula "= A $1*2" in cell C1, and then copy the formula in cell C1 to area C2 to D5 and D1, respectively, the results in cells C2 to D5 and D1 are equal to the number of cells in the first row multiplied by 2, that is, the row reference is fixed.

4. Absolute column reference: the reference format is "$ A1 ". The reference position of the cell is not completely absolute. When the formula for referencing the cell is copied, the reference of the new formula to the row will change, the column reference remains unchanged. For example, we enter the value "1, 2, 3..." in the range from cell A1 to cell B5 ..., 9, 10, then enter the formula "= $ A1 * 2" in cell C1, and then copy the formula in cell C1 to area C2 to D5 and D1, respectively, the results in cells C2 to D5 and D1 are equal to the number of cells in the first column multiplied by 2, that is, the reference to the column is fixed.

5. Reference by name: this reference method uses a name to reference a specific cell. In essence, it is still an absolute reference. A single cell can be referenced by name reference, you can also reference the cell area. For example, we enter the value "5" in cell A1, select cell A1, enter "area" in the upper left corner of the document, and press enter, enter the formula "= area * 2" in cell B1 and press Enter. The result is 10, indicating that the reference to cell A1 is realized. Finally, the formula in cell B1 is copied to cell B2, then, the value in cell B2 is also 10, which enables absolute reference to the name cell. Therefore, the use of names to reference cells is absolute reference.

6. Finally, let's look at a comprehensive example: the implementation of the formula of 9-9 multiplication table 2. Enter the formula "= IF (ROW () <COLUMN ()," ", ROW () * COLUMN () * $ B $11)" in cell A1 and press enter, $ B $11 is an absolute reference to 2, and then the formula is copied to the A1 to I9 area.