Cell references in Excel are a way of referring to the location of a cell or group of cells. Every cell in is identified by a unique cell reference which consists of a column letter and a row number.
For example, the cell in the first column and first row. Cell A1 is its reference. The cell in the third column and second row. Cell C2 is its reference.
Formulas and functions to perform calculations based on the values stored in cells uses their references. For example, you can create a formula that adds the values in two cells by using their references in the formula.
There are two types of cell references in Excel: relative and absolute.
- Relative Reference : Relative cell references adjust automatically when a formula copy and paste to a new location. For example, if you have a formula that adds the values in cells A1 and A2. You copy that formula to cell B3, the cell references will adjust to B2 and B3.
Relative Cell Referencing - Absolute Reference : Absolute cell references, on the other hand, remain fixed when a formula copy and paste to a new location. This can be useful when you want to refer to a specific cell or range of cells in a formula.
Absolute Cell Reference - Mixed Reference : A mixed cell reference in Excel is a combination. It is of relative and absolute references within a formula. It allows you to anchor either the column or row reference. While allowing the other part to adjust when the formula is copied or filled to other cells.
In Excel, a cell reference consists of the column letter and the row number, such as A1 or B3. When you use a mixed cell reference, you use the dollar sign ($) to indicate. By putting $ we show whether the column or row should be absolute or relative.
There are two types of mixed cell references:
- Absolute Column/Relative Row: In this type of mixed reference, you use $ before the column letter to make it absolute, while the row number remains relative. For example, $A1. If you copy or fill the formula horizontally, the column reference will remain the same ($A). While the row reference will adjust based on the relative position.
- Relative Column/Absolute Row: In this type of mixed reference, you use a dollar sign before the row number to make it absolute, while the column letter remains relative. For example, A$1. If you copy or fill the formula vertically, the row reference will remain the same ($1). While the column reference will adjust based on the relative position.
Mixed cell references are particularly useful when you want to lock either the column or row reference while allowing the other part to change. For example, if you have a formula that calculates the total sales for each product (column) across multiple months (row), you can use a mixed cell reference to keep the column reference constant while allowing the row reference to adjust as you copy the formula to different cells.
By utilising mixed cell references effectively, you can streamline your formulas and save time when working with complex calculations in Excel.
