In this lesson you can learn about types of cell references in Excel.
Types of references
In Excel, there are three types of references:
Relative reference
Relative cell references are the default type of cell reference, and they adjust automatically when a formula is copied or filled down or across a range of cells. For example, if you have a formula in cell A1 that references the value in cell B1, and you copy the formula to cell A2, the reference in the formula will automatically change from B1 to B2.
Absolute reference
Absolute cell references, on the other hand, remain constant when a formula is copied or filled down or across a range of cells. An absolute cell reference is created by including a dollar sign ($), in front of the row and column parts of the reference. For example, the absolute reference to cell B1 would be $B$1. If you have a formula in cell A1 that references the value in cell B1 using an absolute reference, and you copy the formula to cell A2, the reference in the formula will remain $B$1, and will not change.
Mixed reference
is an absolute reference to the column and relative to the row, or absolute to the row and relative to the column. Absolute column reference looks like that: $A1, $B1. Absolute reference to row looks like that: A$1, B$1. When you change the position of the cell containing the formula, the relative reference changes, and the absolute reference does not change. When you copy a formula in rows or columns, the relative reference is automatically matched, and the absolute reference does not match. For example, if you copy a mixed reference one cell right it will match the appeal of =A$1 to =B$1.
In one formula you can use both relative reference and absolute.
A quick way to convert addressing mode is to press the F4 keyboard shortcut. Pressing this key changes the address. Of course, you can type the $ (dollar) sign from the keyboard.
When using cell references in a formula, you can also reference cells in other worksheets or workbooks. For example, if you have a worksheet named "Data" in the same workbook, you can reference a cell in that worksheet using the syntax Data!A1. If you have a workbook named "Data.xlsx" saved on your computer, you can reference a cell in that workbook using the syntax [Data.xlsx]Sheet1!A1.
You can also use cell references to create links between cells. For example, if you have a worksheet that contains a list of sales figures, and you want to create a summary worksheet that displays the total sales for each region, you could use a formula in the summary worksheet that references the cells in the sales worksheet. If the sales figures change, the total sales in the summary worksheet will automatically update to reflect the changes.
Further reading: Status Bar Display Cells Containing Formulas