How To Apply Conditional Formatting on More Cells

December 2016


Conditional formatting in Microsoft Excel allows you to highlight cells with a certain color, depending on a cell's value. In a mass document, copying and pasting these formatting options is not always effective. Here's a simple way to apply conditional formatting en masse in your document.

Apply Conditional Formatting to More Cells

Let's take an example which supposes that you are working with 2 columns (A and B). You would like to apply conditional formatting whereby a cell in column B is colored red if it is greater than the number in cell A, and green if the number is less than the one in cell A. In order to apply conditional formatting to all of your data, you need to make the formula with what is called relative referencing.

Therefore, if your formula looks like this:
=$B$2>$A$2 
, you should remove the $ sign before the row number. The result will look like this:
=$B2>$A2 
.

Using this, the need to copy the conditional formatting is null. This will allow you to select an entire range of data before applying the formatting and, therefore, insert the formula as if you'd only selected the first cell. Excel will program itself to apply the correct formulas to all of the other cells in the range (assuming this is in a column).

An alternative to this solution is to simply apply this formula to the first row of data and copy it with the format painter.

If you have any further questions, Microsoft offers a video on this subject on its website.

Related :

This document entitled « How To Apply Conditional Formatting on More Cells » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.