$B$1 - you use absolute column & absolute row because cell B1 contains the value you want to compare all other values against and you want this cell reference to be constant.Įxample 4.$A1 - you use an absolute column and relative row references because we want to check values in all cells of column A against the value in cell B1.Please pay attention to the use of the following references: You use absolute row and absolute column references if you want to compare all values in the selected range with some other value.įor example, let's create a rule that highlights all values in column A that are greater than a value in cell B1. And, because you want to compare values in each column individually, you create the rule for the left-most column (A) and use relative column references, without the $ sign. You use such references when you want to check values in a given row against a certain value or against values in another row.įor example, the below formula compares values in row 1 and 2 and the rule highlights a value in row 1 if it is greater than a value in row 2 in the same column:īecause you want the row numbers to be fixed, you use the absolute row references, with the $ sign.
In this case, the row number is always constant while the column changes. This cell reference type is the opposite of the previous one. And, since you are comparing the values in each row individually, you use relative row references, without $.Įxample 2. In this case, the formula is obvious:īecause you always compare values in columns A and B, you "fix" these column by using absolute column references, notice the $ sign before the column letters in the above formula.
#Relative cell reference excel 2016 add how to
If you need the detailed instructions on how to create conditional formatting rules with formulas, here you go - Creating an Excel conditional formatting rule using a formula. Let's make a very simple rule that compares values in columns A and B and highlights a value in column A if it is greater than a value in column B in the same row. This pattern is most typical for conditional formatting rules and in 90% of cases cell references in your Excel conditional formatting rules will be of this type. Now, let me show you a few examples that demonstrate how seemingly identical formulas produce different results depending on what cell references types are used. So, when making a new rule, you can simply pretend as if you are writing a formula for the top-left cell only, and Excel will "copy" your formula to all other cells in the selected range. In conditional formatting rules, cell references are relative to the top-left most cell in the applied range. In Excel conditional formatting rules, mixed cell references are used most often, indicating that a column letter or row number is to remain fixed when the rule is applied to all other cells in the selected range. $A1), or relative column and absolute row (e.g.
Unexpectedly, it's turned out that it's not creating a rule and even not making a proper formula that represents the greatest challenge. Recently we have published a few articles covering different aspects of Excel conditional formatting.