Conditional Formatting Formulas That Highlight Data Rows Automatically

How To Use Conditional Formatting To Highlight Rows Of Interest (Automatically)

How To Highlight Particular Excel Rows (Automatically) Using Conditional Formatting Formulas

Conditional Formatting is a very useful, powerful and versatile tool. As its name suggests, it has two parts - a condition and a format. When the condition is true, the format is applied.

Feature Download: Grab 2 fantastic takeaways (both free). Watch a free video demonstrating everything discussed below and download a handy cheat sheet (printable reference) summarising all the key steps.

You can check for all kinds of things. You can also apply a multitude of different formatting options ranging from basic cell colours and font colours to coloured data bars, colour scales (heat maps) and even icon sets.

Moving past the basics you can set a condition based on a formula and that’s what we’ll be doing in this cheat sheet example.

The Conditional Formatting tool is available on the Home tab.

Example: Highlighting every ‘Category A’ row

Consider this worksheet. Every ‘Category A’ row has been highlighted automatically.

Fig 1 - In action: Highlighting cells using a conditional formatting formula

Figure 01 - In action: Highlighting cells using a conditional formatting formula

Here’s how it's done.

  1. Select cells A2:D10 (the cells that you want to highlight when appropriate).
  2. Select the Home tab, if necessary.
  3. Click the Conditional Formatting icon.
  4. Choose New Rule.
  5. Selecting New Rule

    Figure 02 - Selecting New Rule

  6. Choose ‘Use a formula to determine which cells to format’.
  7. Write a formula. When creating a formula to be applied across a cell range, write the formula as if you were only checking the first cell. In this case, that is cell A2. The formula is

    A2=”A”

    However, you need to adapt the formula so it works correctly for every row and every column.

    Leave the row number untouched. Relative referencing is what you need. For each subsequent row, the formula should check the cell in column A for that row.

    But you must fix the column letter, because every cell in a row need to check the cell in column A. If that cell is category A, then the current cell should be formatted.

    So, you need to change the formula to:

    $A2=”A”

  8. Set the formatting, e.g. set the cell colour to yellow.
  9. Write a Conditional Formatting formula

    Figure 03 - Write a Conditional Formatting formula

That’s it.

To test the formula, whenever the category in column A is "A" every data cell in that row is highlighted automatically.

Try out any combo

Figure 04 - Try out any combo

Have you used conditional formatting in interesting ways? 

Please share your thoughts and feedback in the comments below, and if you found this post useful, please share a link on your social networks.

Cheers. Here's to your learning and success. Enjoy the rest of your day.

Jason Morrell blog signature

Jason Morrell photo and bio

Cheat Sheet - Highlighting a row using conditional formatting

FREE CHEAT SHEET & VIDEO

Watch the video to see it done and download a printable PDF cheat sheet of all the key steps

PLUS, GET A FREE COPY OF MY 30 EXCEL POWER TIPS

>