Conditional Formatting is a feature of excel that allows you to apply formatting to data cells based on specific conditions.
In specific conditions, we might need to apply formatting to entire rows, based on a value in one cell. In this article, I will discuss how to do that in simple steps.
An example of this scenario is shown below. I want to highlight all the rows in red color which have the status “UNBILLED” in column D.
Essentially I want to highlight rows 4, 5, 9 and 12. Now in a small dataset, it is very easy and all you have to do is manually select the row and highlight the cell. But what if the data rows increase, or it is a very large dataset. It will become a very tedious task, and there is a big potential for making a mistake. This is where the conditional formatting comes in handy.
You can download the link below to follow along the tutorial.
Highlighting Rows Steps
Say you have a dataset where you want to highlight the rows which show an “UNBILLED” Status in column D.
Here is how you can do it:
Select the entire data set. For this example select the cells A1 to D13.
On the ‘Home’ Tab, under the ‘Styles’ group, you will find an icon for ‘Conditional Formatting’. Click on that.
On clicking the icon, you will find an option for ‘New Rule’. Click on that.
In the ‘New Formatting Rule’ dialog box, click on ‘Use a formula to determine which cells to format’.
In the ‘Formula’ field, enter the following formula: =$D2=”UNBILLED”
After that click on the ‘Format’ button.
Under the ‘Fill’ section in the dialog box, choose the color that you want to use to highlight the rows.
Finally, click on OK.
Doing this will highlight all the rows where the Status of the supermarket is “UNBILLED”’.
Working of the Formula
The feature of Conditional Formatting checks the condition (=$D2=”UNBILLED”) that we have stated for each cell in the dataset.
If the cells in the column D checkers for the word UNBILLED, the row will get highlighted, else it will not. The dollar sign ($) ensures that the column in the formula remains constant. It will check for the condition only in column D.
© 2020 PGupta0919