Formula in Conditional Formatting in Excel

Mostly it become necessary to analyze the data in different ways like what is the current situation to previous quarter and from this point of view, data analyst has to present the report by highlighting the figures accordingly. May it be higher or lower to performance as compared to previous results.

We will learn two ways to highlight the data by apply formulas and build our own rule in conditional formatting.

In my previous article  How to Create own rule in Conditional Formatting in Excel    I shown an example of function IF & COUNTA to determine that HR activities are all Closed or Open

Formula: =IF(COUNTA($B$3:$B$10)=COUNTA($D$3:$D$10), TRUE, FALSE))

 Today we will deep dive in applying and creating our own rule to analyze the data.

The Formula is so simple in this example, simply I want to highlight the values in cells those are Greater than 100, if YES then Color the cell in YELLOW and Font in MAROON.

Most important practice is you can try the formula in sheet first and if check the result. I strongly recommend that to work with formula in sheet and analyze the result. If you are getting expected result then only you can start with apply rules in Conditional Formatting wizard.

Steps:

        1. Select the range on which you want to apply conditional formatting.

        2. Click on HOME -> Conditional Formatting-> New Rule on Ribbon.

        3. Apply formula as shown in above figure.

That's it.  You should be getting expected results.

Now Let us try another formula as to determine that how did we progress in this quarter as compared to previous quarter.

This is also another method to determine the performance of this quarter to previous quarter, for that I have used OFFSET Function with IF logical Function.

Syntax: OFFSET(reference,rows,cols,[height],[width])

Offset function checks the value from reference cell with given row number and column. In this example we want to check the value in same row but 1 column to left. I want to check if Qtr-2 Value is Greater than Qtr-1 Value and so on.

The suitable formula developed was.

          =IF(OFFSET(C3,0,-1)>100, TRUE, FALSE)

If condition is true than apply conditional formatting color coding in cell.

Once done with apply formula, copy the cell and apply PasteSpecial Format all over the data, table.

Hope you found this is helpful, keep excelling.

Download Worksheet with Example

 You can