Excel Conditional Formatting for Whole Rows

In the basic Excel “Conditional Formatting” the formatting of a single cell is done based on its value. That way, for example, one can highlight a cell if it contains the value “Highlight”. To do that simply select the needed cell and click on the “Conditional Formatting” option “Highlight Cells Rules” and select the type of rule “Equal To…”, such as the following:

Image

Then type the cell’s rule, in this example the text “Highlight”:

Image

And you are done. The result should look like this:

Image

That is simple enough. But how do you conditionally change the formatting of complete row based on a single cell value? The use case is as follows: One column is representing the status of the row. If that status has the value “Highlight”, the entire row should be formatted to be highlighted.

Well, Excel has an only slightly hidden option to support that scenario. To do that you have to write down a formula to format the rows.

Start by selecting the relevant rows (or a table). Then go to “Conditional Formatting” and select the “New Rule” option:

Image

Then select the last option called “Use a formula to determine which cells to format”:

Image

And then write a rule. In this example, the row should be highlighted if the value of the cell on column A equals to “Highlight”. The rule is:

=$a1=”Highlight”

Like in the picture below:

Image

The weird part of the rule is that it looks like it is only applicable for the first row, but as you can see the rule include $a1 and not $a$1 and there is a big difference.

$a$1 means that the formula will always look at cell A1. Fixed row and column.

However, $a1 (without the dollar sign before the digit 1) means that the column is fixed and will always be column A but the row is variable and will be implemented for all the rows in the selected range.

The end result looks like this:

Image

More examples and guides can be found here:

http://office.microsoft.com/en-us/excel-help/conditionally-formatted-data-examples-and-guidelines-HA010230841.aspx

Advertisements

Author: dave

Consider myself kid in soul and naive by choice. I am interested in people, technology and business and thrive when they all work together. My favorite quote and motto is that “You can fool some people some times but you cant fool all the people all the time” ― Bob Marley

2 thoughts on “Excel Conditional Formatting for Whole Rows”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s