Compare two columns in Microsoft Excel and Use conditional formatting

Compare two columns in Microsoft Excel and Use conditional formatting:

Let’s say we have below data in excel:

Here, we are going to use conditional formatting in column C comparing column B and understand which is falling down, going up and neutral.

Let us use a simple formula here called “offset”.

To understand the functionality of offset, I have given the offset formula with a same reference to cell B2, and navigate from row 0, column 0,  then row 1, column 0,  then row 2, column 0, then row 3, column 0 as shown below for easy understanding using a concatenate function in cell E2
Formula I have used is:

=CONCATENATE(OFFSET(B2,0,0), " ", OFFSET(B2,1,0)," ",OFFSET(B2,2,0)," ",OFFSET(B2,3,0))

It gave the values from cell B2, B3, B4, B5 as shown below

By understanding the usage of Offset function, lets us use this formula in our excel.

Highlight the Cell C2 alone. And under HOME tab -> select Conditional formatting -> Icon Sets -> More Rules as below

It will open up the New formatting Rule window as below.  

Here select your Icon Style as shown below.
Change the Type to Number. 

For Green Icon, change Symbol to > as shown below and type the formula 

=offset($B$2,row()-2,0)

For Neutral Icon, change the symbol to >= as shown below and type the same formula

=offset($B$2,row()-2,0)

Note:  You can’t use B2.  You should use $B$2 here.

For red, any thing < will auto apply anyway. So you will have data as shown below:

Click OK.

You will see that, selected cell C2 reflects the expected result by comparing Cell B2. 

Now, to use same formatting, you need to highlight cell C2, and Doubleclick the format painter icon as shown below:

(NOTE:  You should not do single click..  But double click the format painter.  Very important).

Now just click on cell C3, you will see that the formatting applies to C3 cell as well.

Instead of doing each cell one at a time, by selecting C2 cell and double format painter and then to click Cell C4 and again repeating steps for C5 cell, we can now, 

Select both cell C2 and C3 and Double Click Format Painter icon as below:

Since we have copied 2 cells under format painter, 

Now, just drag over 2 cells (C4, C5) to use the format.  

Now you will see, all cells have required conditional formatting as expected: 

Thanks for spending your time in my blog:  Happy learning.