How To Apply Conditional Formatting on More Cells

Ask a question

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:
, you should remove the $ sign before the row number. The result will look like this:

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.
Jean-François Pillou

Jean-François Pillou - Founder of CCM
Better known as Jeff, Jean-François Pillou is the founder of He is also CEO of CCM Benchmark and digital director at the Figaro Group.

Learn more about the CCM team