Use Conditional Formatting to Highlight a Row or Column

Conditional Formatting is a feature in Excel that allows us to change the format of cells based on a set of rules or conditions.  There are instances when we need to highlight a row or a column, depending on the data we have and the desired results. This step by step tutorial will assist all levels of Excel users in highlighting rows or columns based on a condition.

Figure 1.  Using conditional formatting to highlight a row

Setting up the Data

Here we have a table showing Products A to G, with corresponding Color, Size and Pieces Sold.  We want to highlight the rows for products with the color “Black”.

Figure 2.  Sample Data for conditional formatting to highlight a row

Using Conditional Formatting to Highlight a Row  

To highlight an entire row, we use Conditional Formatting and enter a formula based on the required or given criteria.  

  • Step 1. Select the cells to be formatted.  In this case, select cells B4:E10.

Figure 3.  Selection of the data range for conditional formatting

  • Step 2. Click the Home tab, then the Conditional Formatting Menu and select “New Rule”. The New Formatting Rule dialog box will pop up.  

Figure 4.  Creation of a new rule in conditional formatting

  • Step 3. Select the Rule Type “Use a formula to determine which cells to format” and enter this formula in the dialog box :

=$C4=“Black”

Important note:

To highlight a row, we fix the column that serves as the reference for the conditional formatting

Note that the column is fixed by using the symbol “$”.  This formula triggers the conditional formatting, and the “$” before “C” ensures that the reference column is only the column for “Color”.  For every row of data, the format will be changed if the “Color” is “Black”.

Figure 5.  Entering the formula as a condition or formatting rule

  • Step 4. To change the format, click “Format” and then decide on the new format to apply to the entire row.  We can change the font, borders or fill the cells with different colors.

Example :

Select “Fill” and choose Orange, Accent 6, Lighter 60% and click OK.  

Figure 6.  Selection of the format to use

Figure 7.  Completion of the new formatting rule with formula and selected format

This rule highlights the rows of data that satisfy the condition of Color=Black”.

Figure 8.  Output: New conditional formatting rule reflected in the rows of data with the color “Black”

As shown, we are able to change the format of the entire row for Products A, D and G with color “Black”.  

There are also cases where we need to highlight a column because the data we have requires it that way.  

Setting up the Data for Highlighting a Column

Here we have a similar table as the above example, only that the headers are in the leftmost side and the information per product is shown per column.  In the same manner as the previous example, we want to highlight the columns for products with color “Black”.

Figure 9.  Sample Data for Conditional Formatting to Highlight a Column

Using Conditional Formatting to Highlight a Column

The steps for highlighting a column are similar to that of highlighting a row.  The only difference is in the formula we use to satisfy the condition.

  • Step 1. Select the cells to be formatted.  In this case, select cells C3:I6.

Figure 10.  Selection of the data range for conditional formatting

  • Step 2. Click the Home tab, then the Conditional Formatting Menu and select “New Rule”. The New Formatting Rule dialog box will pop up.  
  • Step 3. Select the Rule Type “Use a formula to determine which cells to format” and enter this formula in the dialog box :

=C$4=“Black”

Important note:

To highlight a column, we fix the row that serves as the reference for the conditional formatting

Note that the row is fixed by using the symbol “$” before row “4”. This ensures that the reference row is only the row for “Color”.  For every column of data, the format will be changed if the “Color” is “Black”.

Figure 11.  Entering the formula as a condition or formatting rule

  • Step 4. Change the format as per your preference.  

Example :

Select “Fill” and choose Orange, Accent 6, Lighter 60% and click OK.  

Figure 12.  Completion of the new formatting rule with formula and selected format

This rule highlights the columns of data that satisfy the condition of Color=Black”.

Figure 13.  Output: New conditional formatting rule reflected in the columns of data with the color “Black”

We have successfully highlighted the columns for Products A, D and G with color “Black”.  

Other examples

Criteria: Highlight rows or columns for products with greater than 200 pieces sold.

Follow Steps 1 to 4 as discussed above, but use the formula: Pieces sold>200

Figure 14.  Entering the formula =$E4>200 and setting the format

Figure 15.  Output: New conditional formatting reflected for products with >200 pieces sold

Figure 16.  Entering the formula =C$6>200 and setting the format

Figure 17.  Output: New conditional formatting reflected for products with >200 pieces sold

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar