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:


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


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


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:


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


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:


Like in the picture below:


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:


More examples and guides can be found here:



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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s