Conditionally Format Rows Based On Column Entry In Excel

Here is how we will use Conditional Formatting to highlight the entire row in a data range based on the values in one column:

(Note: Video version above, text version below)

Text version (note: click on an image for a better view):

Here I have a data range of 214 rows. I want to highlight the entire row for anyone that has USA in column A.


I need to highlight the entire range starting in cell A2 through E214. Then I will click on the Conditional Formatting button in the Styles group on the Home tab and select New Rule:


I will then select “Use a formula to determine which cells to format”. The formula I will enter is =$A2=”USA”. Notice how I have a mixed cell reference $A2. As the data range is being evaluated for formatting, I want to lock Column A since that is where I want it to check for “USA”, but go down each row. I also put “USA” in quotes since it is text. I then click on the “Format” button and format the cells how I want them to appear:


I then hit OK and OK, and now my data range is formatted perfectly. If I change any of the entries in column A, the formatting will adjust accordingly.


Happy Excelling!

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

No comments yet.

Leave a Reply