1. 5. Cell Referencing:

  2. The concept of relative and absolute cell references.

In Microsoft Excel, cell references are used to identify and locate specific cells within a worksheet. There are two types of cell references: relative and absolute.

  1. Relative Cell References: A relative cell reference is the default type of reference in Excel. When a formula containing a relative reference is copied or filled to other cells, the reference adjusts based on its new location relative to the original cell. This means that if you copy a formula from one cell to another, the reference will change based on its position relative to the new cell.

For example, let’s say we have a formula in cell B2 that multiplies the value in A2 by 2: =A22. If this formula is copied to cell B3, the reference will automatically adjust to =A32, as it reflects the change in row number.

  1. Absolute Cell References: An absolute cell reference, on the other hand, remains fixed regardless of where it is copied or filled. It is denoted by adding a dollar sign ($) before both the column letter and row number of the cell address. By using absolute references, you can ensure that certain cells are always referred to in a formula, regardless of its position.

For example, let’s consider a formula in cell B2 that adds up the values in column A from rows 1 to 10: =SUM($A$1:$A$10). If this formula is copied to another cell or filled across multiple cells, the absolute reference ensures that the range remains constant as $A$1:$A$10.

  1. Mixed Cell References: In addition to relative and absolute references, there is also a mixed reference which combines aspects of both types. A mixed reference contains either an absolute column and relative row or a relative column and absolute row. You can achieve this by using either $A1 or A$1 notation.

For example, if we have a formula in cell B2 that multiplies the value in column A by 2 and always refers to row 1, we can use the mixed reference =A$1*2. When this formula is copied to other cells, the column reference will adjust while the row reference remains constant.

In summary, relative cell references adjust when copied or filled, absolute cell references remain fixed, and mixed cell references combine aspects of both types.)