How To Use Conditional Formatting To Highlight Whole Data Rows (Automatically)


Jason Morrell

Reading Time:
  minutes remaining

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

1.  Conditional Formatting using a Custom Formula

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.

Those are the standard conditional formatting options.

But moving past those you can set a condition based on a custom formula. That's where things start to get interesting. And that’s the focus of this post.

2. Example: Highlighting every ‘Category A’ row

Consider this worksheet. Every ‘Category A’ row is 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,.

3.  Click the Conditional Formatting icon.

4.  Choose New Rule.

Selecting New Rule

Figure 02 - Selecting New Rule

5.  Choose ‘Use a formula to determine which cells to format’.

6.  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


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

The row number must be relative (not prefixed with $). This means that for each subsequent row, the formula will check the cell in column A for that row.

But the column letter must be absolute (prefixed with $). This means that every cell on a row check the cell in column A. If that cell is category A, then the current cell will be formatted.

Therefore, you need to change the formula to:


7.  Set the formatting, e.g. set the cell colour to yellow.

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

3. Watch the video (over the shoulder demo)

Click to watch video

4. What next?

Have you used conditional formatting in interesting ways? Pleae share below.

Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! 

ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.

Enjoy the rest of your day!

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell

Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!


Office Legends Academy | Making Office Legends

Master Microsoft Office
in 30 days or less

more from

Office Mastery logo
Read More
SOS-Click: The Easy Automated Backup Tool For Home Users
Read More
6 Excel Templates to Help Organize Your Daily Activities
Read More
10 Ways Excel Consultants Can Help You Fully Leverage Your Business Spreadsheets
Read More
How to fix formulas: 21 Fatal Formula Fails (and how to avoid them)


{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}