Color the Excel row based on a value/status

Very often, we would like to differenciate the rows that are having certain status (or specific values in a specific column) in a different color.

We can filter for the status and apply the color manually, but we can also do that dynamically or automatically with a specific tweak to the Conditional Formatting feature in Excel

I am referring to Excel 2016 but very well applicable to earlier or later versions of excel.

I hope you are familiar with Conditional formatting. Its in Home Tab 

Steps involved:

1. Select the Table area to be formatted.  (in this case B3:E11)
2. Home Tab > Conditional Formatting > Manage Rules
3. Select "New Rule"
4. Select "Use a formula to..." (refer snapshot below)
5. Enter the formula as < =$D3="in progress" > as my status column to be checked was D. please adapt as needed.
6. Select "Format" to specify the format of matching celles. I selected Blue color in "Fill" Tab. (refer Snapshot). 
7. click OK on all three windows and apply the formatting.

Key Snapshots:

The before

The after

The Formula

The Fomat Selection

Some Additional Explanation:

Let's have a look at what happened.

two things are importatnt.  

1, the first row of selection and 2, the column specified in the formula.

• In this case the first row of selection is Row 3 and the column where the status to be checked is column D.
• Hence the first cell for comparison is D3
• The $ sign before D (in $D3) is improtant as that makes the column an absolute reference (locked).  Click here if you would like to know more how $ works in a formula.

Excel applies the formula for each cell of the selection.  lets look some random cases..

• for cell B3, the checked cell is D3
• for cell C4, the checked cell is D4
• for cell E4, the checked cell is D5

In short, since we used $D in the formula, the for any row that is checked, its corresponding D columns will be the one that will be compared.

Footnote:

You can repeat the steps if you want another color for another status.

You may also use other formula like >, <, >=, <= with numbers to set format based on numeric values.

I hope you are clear how it works and having the row formatted based on a value is specific column will be easy for you..  

Feel free to leave a comment if you have issues or have any question..  Would be happy to help.