QuadExcel.com

How to Highlight a Row in Excel Using Conditional Formatting

R.Admin

You can use conditional formatting to format or color the cells based on their contents. For example, you have a cell to turn red when it contains text ‘No’ or a value less than 90%. But how do you highlight an entire row? When a cell contain specified content.

If you’re new to Conditional Formatting, you might want to look at Using Conditional Cell Formatting in Excel 2013.

This tutorial will help you learn how to highlight other cells based on a cell’s value? To see how this was done, read on.

Example Data

Creating The Conditional Formatting Rules

  1. Select the first cell in the first row you’d like to format
  2. Click Conditional Formatting (in the Home tab) and select Manage Rules.  
  3. Click on ‘New Rule’.
  5. Click on ‘Use a formula to determine which cells to format’.
  6. Enter the formula =$D3=”No”
  7. Click on ‘Format’.
  8. Make necessary formatting: color, font, size, borders, etc.
  9. Click on ‘OK’ button.
  10. Click on ‘OK’ button.
  11. Select desired range in ‘Applies to’ e.g; range =$B$3:$D7 in the example
  12. Click on ‘OK’ Button 
  13. This is how the data gets formatted

Tadaahh… Magic…!!

Download the sample file: Highlight a Row in Excel Using Conditional Formatting

Here is the trick:

Your formula must evaluate to “True” for the rule to apply, and must be flexible enough so you could use it across your entire table later on. Let’s analyze my sample formula:

=$D3 – this part is a cell’s address. D is the column which I want to format by (“Really?”). 3 is my current row. Note the dollar sign before the D – if I don’t have this symbol, when I apply my conditional formatting to the next cell, it would expect E3 to say “No”. So in this case, I need to have a “fixed” column ($D) but a “flexible” row (3), because I will be applying this formula across multiple rows.

=”No” – this part is the condition that has to be met. In this case we’re going for the simplest condition possible – it just has to say “No”. You can get very fancy with this part.

So in English, our formula is true whenever cell D in the current row has the word No in it.

Every row in your table should now be formatted according to the new rule:

That’s it! Now all you have to do is create another rule to format rows that say “Yes”. If your data is more complex, you may need to set up even more rules. Follow this method and in no time you’ll be creating intricate spreadsheets with data that pops right off the screen. Feel free to of your share your tips, ideas & questions in the comments!

Also Read:

Exit mobile version