How to Use of Conditional Formatting in Microsoft Excel

To color rows based on text criteria we use the “Conditional Formatting” option. This option is available in the “Home Tab” in the “Styles” group in Microsoft Excel.

Conditional Formatting: - To highlight the specific cells or range on the basis of text criteria’s, number criteria’s etc.

 
Let’s take an example to understand the use of Conditional Formatting in Excel:-

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

img1

 

To highlight the cell on the basis of  text criteria:

    • Select the range C2:C10 by pressing the keys “CTRL+SHIFT+Down arrow key”.
    • Go to the “Home Tab” in the “Styles” group and select “New Rule” from the “Conditional Formatting” drop down menu.
    • The “New Formatting Rule” dialog box will appear.
    • Select therule type “Format only cells that contain”. In the “Edit the Rule Description” dropdown select “Specific Text.
    • In the 2nd drop menu select “Containing” and type “A” in the box.
    • The “Format” option is showing in the belowdialog box. Click on the “Format” icon, then the “Format Cells” dialog box will appear. In the “Fill” tab select the green color and click on ok.
    • The selected color will display 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..

img4

 

  • Click on “New Rule” then again the “New Formatting Rule” dialog box will be 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” and type “B” in the box.
  • The “Format” option is showing in the below dialog box. Click on “Format” icon, the “Format Cells” dialog box will appear. In the “Fill” tab select the yellow color and click on ok.
  • In the “Conditional Formatting Rules Manager”dialog box click on apply then click on ok.

img5

 

After using the Conditional Formatting conditions for all grades you will get the data as the below snapshot. Also if once you update the score the grade will be automatically updated.

 

img6

 

image 48

 

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

 
 

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube