Functions of $ in the Excel Formula

Difference between absolute reference ($) and relative reference

When creating a formula, the reference of the cell or cell area is usually relative to the cell containing the formula.
For example, the element B6 contains formula = A5. Microsoft Excel searches for values in a cell above cell B6 and in a cell at the left. This is relative reference.

When you copy a formula that contains a relative reference, Excel automatically adjusts the reference in the copy formula to reference other cells relative to the current formula position.
For example, cell B6 contains the formula = A5, and A5 is the upper-left cell of B6. When the formula is copied to cell B7, the formula in the formula has been changed to = A6, that is, the cell at the top left of cell B7.

Absolute reference if you do not want EXCEL to adjust the reference when copying a formula, use absolute reference.
For example, if the formula is used to multiply cell A5 by cell C1 (= A5 * C1) and copy the formula to another cell, Excel will adjust the two references in the formula. You can add the dollar sign ($) before the reference that you do not want to change, so that the cell C1 can be absolutely referenced. To absolutely reference cell C1, add the dollar sign = A5 * $ C $1 to the formula.

Switching between relative reference and absolute reference
If you have created a formula and want to change the relative reference to absolute reference (and vice versa), select the cell containing the formula first. In the edit box, select the reference to be changed and press F4.Each time you press the F4 key, Excel switches between the following combinations: absolute column and absolute row (for example, $ C $1); relative column and absolute row (C $1 ); absolute column and relative row ($ C1) and relative column and relative row (C1). For example, if you select the address $ A $1 in the formula and press the F4 key, the reference will change to a $1. Press the F4 key again, and the reference will change to $ A1, and so on.

 

In the Excel condition format, only $ is not added for the table to be copied.(= Not (iserror (MATCH (E2, $ M: $ M, 0 ))))