In this tutorial, we’ll learn how to highlight a complete row if a cell in that row contains a specific text or value through conditional formatting. We have a data set consisting of the details of students’ performance during exams. We want to highlight all those subjects in which the student has obtained marks higher than a specific value available in a separate cell. This cell will contain the value (55 in our example) which will decide if the student passed the subject exam or not. So let’s see how to do it.

Microsoft Excel offers incredible features to ease mathematical calculations by using built-in formulas. Along with data analysis features, it also provides us with the tools to sort, highlight and format data values to make them presentable. One of those features is the conditional formatting. It enables us to highlight the specific range of values from the entire data set.

Step 1 – Select a data range

– With the help of handle select and drag to define the data range.

Step 2 – Go to Home tab

– Now go to the Home tab.

Step 3 – Go to Styles group

– Now go to the Styles group -> Conditional Formatting -> New Rule.

Step 4 – Write the formula to do conditional formatting

– Write the following formula in the conditional formatting formula bar;

Step 5 – Set the format for conditional formatting

– Click on the format tab so that we can choose formatting styles to highlight our data range.

Step 6 – Set the font to bold for conditional formatting

– In the next dialog box, click on the Font tab and select Bold.

Step 7 – Set the suitable colour for conditional formatting

– In the same dialog box, click on the Fill tab and select an appropriate colour for highlighting the data.

Step 8 – Finalise and apply the conditional formatting settings

– When you click on the OK button in the last step, you will see the following window.
– Click on OK again. This will implement the conditional formatting rules to the selected data range and we can see that complete rows are highlighted which follow the conditional formatting criteria.

The trick behind highlighting the whole row

The above formula will highlight the complete row 2, only if the value of cell B2 is greater than the numeric value 55. The same process will be repeated for the next row 3 and this time the formula will check the value of C3 against the numeric value. The trick here is to use dollar sign ($) before Column Name B to lock the column to be checked every time the cells of any row will be analysed. However, the criteria will remain fixed as we have hard coded it to be a numeric value 55. The same thing can be achieved with a text based criteria as well.