How To Apply Conditional Formatting on More Cells

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.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « How To Apply Conditional Formatting on More Cells », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (