Absolute Cell References In Excel

When you reference a cell in Excel, there are 2 kinds of reference types, they are relative reference and absolute reference. This article will tell you how to reference an Excel cell relatively and absolutely with examples.

1. What Are Absolute Cell References In Excel?

1.1 What Are Relative Cell References?

1. By default, we reference a cell in a formula using the relative cells reference.
2. For example, in the formula =A2+B2, we reference the cells A2, and B2 relatively.
3. So when you apply the autofill or flash fill operation to the above formula cell, it will change the formula content to such as =A3+B3, =A4+B4, =A5+B5, =A6+B6 for different rows and the same column.
4. You can read the article What Does Flash Fill And Autofill Do In Excel to learn more.
5. So if you need to reference a cell relatively in an Excel formula, just use strings like A2 and B2.

1.2 What Are Absolute Cell References?

1. But in some cases, we need to reference a fixed cell in the formula.
2. We do not want to change the cell address when applying autofill or flash fill operation to a formula cell.
3. how can we do this? The absolute cell references can do that.
4. To use absolute cell references, we need to add the $ character before the column name and row number.
5. For example, $A$2 will refer to cell A2 absolutely, when you apply autofill and flash fill operation to the cell formula, it will not be changed.
6. We can also use $A2 to fix the column or A$2 to fix the cell row number, but this is rarely used.

2. Absolute Cell References In Excel Examples.

1. Below is the example data table, column A saves the English test score number, column B saves the Math test score number, and column C is a multiplier number.

      A         B      C           D           E
   
   1 English  Math   Multiplier  Add Score  AddScore * Multiplier
   
   2  92       98       3         =A2+B2      =D2 * $C$2
   
   3  89       95     =A3+B3
   
   4  90       88     =A4+B4
   
   5  80       89     =A5+B5

2. We need to calculate the English + Math score number and save the number in column D.
3. We also need to calculate the AddScore*Multiplier number and save the number in column E.
4. In this example, we will use both relative cell reference and absolute cell reference in Excel.
5. So we can input the formula =A2+B2 in cell D2, then press Enter key to select cell D2.
6. Select cell D2, then there is a mini square on the bottom right corner of cell D2.
7. Drag the mini square down to row 5, and release the mouse key.
8. You can find the cell content of cells D3, D4, and D5 are =A3+B3, =A4+B4, and =A5+B5.
9. This is because we use relative cell references in the cell D2 formula.
10. Input the formula =D2 * $C$2 in cell E2 and press the Enter key.
11. Select cell E2, then you can find the small square in the bottom right corner of cell E2.
12. Drag the small square down to row 5 and release the mouse key.
13. You can find the cell content of cells E3, E4, and E5 are =D3 * $C$2, =D4 * $C$2, =D5 * $C$2.
14. This is because we reference cell C2 with the absolute cell references in the formula ($C$2).
15. Then it will not change the cell address in the formula when you autofill the formula to other cells.