Excel Tip > Excel Text, Editing and Format > Excel Format > How to apply conditional formatting in row

How to apply conditional formatting in row

Fb-Button

In excel conditional formula can be used to highlight the specific condition. In this article we will learn how to color rows based on text criteria, we use the option of “Conditional Formatting”.

This option is available in the “Home Tab” in the Styles group in Microsoft Excel 2010.

Conditional Formatting: -Conditional Formatting is used to highlight the important points with color in a report. We are formatting the data using colors, symbols, icons, etc based on certain conditions.

Let’s take an example to understand how you can conditional formatting row based on text criteria.

We have data in range A1:A10. Column C contains the grades. Now in column C we want to highlight the text with color. “A” grade should be highlighted with green color and “B” grade with blue color.

 

img1

 

To color rows based on text criteria:

    • Select the range C2:C10 by pressing the key “CTRL+SHIFT+Down arrow key”.
    • Go to the “Home Tab” and then in the “Styles” group select “New Rule” from the Conditional Formatting drop down menu..
    • The “New Formatting Rule” dialog box will appear.
    • Select the rule type “Format only cells that contain”. In the “Edit the Rule Description” select “Specific Text” from the drop down menu.
    • In 2nd drop menu select “Containing”, type “A” in the box.
    • In the dialog box below you will see the “Format” option.Click on the “Format” icon, the “Format Cells” dialog box will be active. . Inthe “Fill” tab select the green color and click on OK.
    • The selected color will be displayed in the “Preview” box and then click on OK.

 

img2

 

    • Now select “Manage Rule” from the “Conditional Formatting” drop down menu.

 

img3

 

    • The “Conditional Formatting Rules Manager” dialog box will appear.
    • Click on “New Rule”, the “New Formatting Rule” dialog box will get activated.
    • Select a rule type “Format only cells that contain”. In the “Edit the Rule Description” box select “Specific Text” from the drop down menu.
    • In the 2nd drop down menu select “Containing”, type “B” in the box.
    • In the dialog box below the “Format” option is shown. Click on the “Format” icon, the “Format Cells” dialog box will get activated.In the “Fill” tab select the yellow color and click on OK.

 

IMG4

 

  • In the “Conditional Formatting Rules Manager” dialog box click on apply, then click on OK.

The output is shown below -

 

IMG5

 

This is the way you can put conditional format based on color for specific text.

 

image 29

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

Previous
Next

Comments

  2. Pete Vargas-Mas

    I'm trying to set up a conditional formatting case as follows:

    Look at the text in the cells of Sheet1 Column C
    If any part of that text matches one of the words in Sheet2 Column B Rows 1-10,
    then highlight the entire row in Sheet1 Column C where the match was found.

    How the heck do I create a Conditional Formatting rule for that?

    Reply ↓
  4. Reynold

    "I am trying to create a golf scorecard. I would like to enter the number for each of the 18 holes. Then after playing the course I would enter my score for each hole. I would like to format the cell when I enter my score to change the backgroud color. If I am 1 over par it would change to asy red. If I was 1 under par it would change to green if 2 under par it would change to blue. Can you tell me how to do this in Excel.
    Thanks for your help in advance."

    Reply ↓
  5. Raphael

    "I need to know if it is possible to use Conditional formatting to compare a long list of competitor pricing in relation to our retail and then highlight the lowest retail of all of our competitors.

    Any help would be appreicated.

    Email jmcclain@pearison.com"

    Reply ↓
  6. Richmond

    "The built in conditional formatting only allows for 3 sets of criteria.

    If you want more you'll need to use VBA.
    See here for an example:
    http://rhdatasolutions.com/ConditionalFormatVBA/

    This may have changed in newer versions of excel though (newer than excel 2000 anyway)."

    Reply ↓
  7. Robert

    I am trying to highlight data that is outside of the area 0 to 6. I also want it to highlight 0. How do I get it so it doesn't highlight blank cells???

    Reply ↓
  9. Mike

    "I am trying to create a golf scorecard. I would like to enter the number for each of the 18 holes. Then after playing the course I would enter my score for each hole. I would like to format the cell when I enter my score to change the backgroud color. If I am 1 over par it would change to asy red. If I was 1 under par it would change to green if 2 under par it would change to blue. Can you tell me how to do this in Excel.
    Thanks for your help in advance."

    Reply ↓
  10. Mike

    "I need to know if it is possible to use Conditional formatting to compare a long list of competitor pricing in relation to our retail and then highlight the lowest retail of all of our competitors.

    Any help would be appreicated.

    Email jmcclain@pearison.com"

    Reply ↓
  11. Mike

    "The built in conditional formatting only allows for 3 sets of criteria.

    If you want more you'll need to use VBA.
    See here for an example:
    http://rhdatasolutions.com/ConditionalFormatVBA/

    This may have changed in newer versions of excel though (newer than excel 2000 anyway)."

    Reply ↓
  12. Mike

    "I am trying to highlight data that is outside of the area 0 to 6. I also want it to highlight 0. How do I get it so it doesn't highlight blank cells???

    email me: john.morabito@ae.ge.com"

    Reply ↓

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Excel Tips

Categories

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.