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

by Jason Morrell
December 20, 2016

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.

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.

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

###### A2="A"

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:

###### \$A2="A"

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

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.

Figure 04 - Try out any combo

## 4. What next?

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

I hope you found plenty of value in this post. I'd love to hear your biggest takeaway in the comments below together with any questions you may have.

Have a fantastic day.

Jason Morrell

Jason Morrell is a professional trainer, consultant and course creator who lives on the glorious Gold Coast in Queensland, Australia.

He helps people of all levels unleash and leverage the power contained within Microsoft Office by delivering training, troubleshooting services and taking on client projects. He loves to simplify tricky concepts and provide helpful, proven, actionable advice that can be implemented for quick results.

Purely for amusement he sometimes talks about himself in the third person.

SHARE

more from

## How to Consolidate Data in Excel: 2 Easy Ways to use Excelâ€™s Consolidation Tool

How to Consolidate Data in Excel: 2 Easy Ways to use Excel’s Consolidation Tool

## Excel Screen: A Beginners Tour

Excel Screen: A Beginners Tour

JOIN THE DISCUSSION