12/14/15

How to Use Conditional Formatting Based on Another Cell?


In this tutorial we are learn about how to conditional formatting based on another cell? In this tutorial we will learn how to create different formatting rules, how to edit and copy our formatting rules in ms excel, and also how to do conditional formatting based on another cell. It is a really powerful feature when it comes to applying different formats to data that meets certain condition. It can help you highlight the most important information in your spreadsheets and identify variances of cells' values with a quick glance.
How to create conditional formatting rules?
1.      First select the range of cell which we want to create conditional formatting.
2.      Then we go to conditional formatting command and press the option we will see the drop
         down list.
3.       And select the Highlight cell rules, and select Greater than option.


















4. when we select the Greater Than option we see a Greater than option as given in below image.












In this image we see, First when we want to format cell greater than 1000,so we will enter the value of 1000. He highlights the number which is greater than 1000. Same process do in all option.

Preset conditional formatting
Microsoft excel has several presets we can use to quickly apply conditional formatting to your cells. They are grouped into three categories:

1.      Data Bars
2.      Color Scales
3.      Icon set

Data Bars:- First we discuss about Data bars, Data bars are horizontal bars added to each cell, much like a bar graph.



Note:-  Its not work when we select the alphabetical cell. Its work when we select Numerical cell.

Color Scales:- Now we discuss about Color Scales, Color scales change the color of each cell based on its value. Each color scale uses a two- or three-color gradient. For example, in the  -Yellow-Red- Green  color scale, the highest values are yellow, the average values are red and the lowest values are green.




Icon set:-  Now we discuss about Icon Sets, Icon sets the change the color of each based
on its value. Each directional sign shows icons in data cells by applying desired Condition.

For Example, Red sign is low number yellow sign shows medium number and green sign shows highest number.

In above image shows that Red color show Lowest Number, yellow color shows Medium Number and Green color shows Highest Number.We also choose shapes, indicators and Ratings to show the Numbers.

For More icon set ruler, we will go to conditional formatting select Icon sets and go to more rules and press it. Now we see New formatting Rule box as image given as below.




















In this image we will go to Select a Rule Type and select Format all cells based on their values the go to Edit the Rule Description and We set format all cells based on their Values as we want.
If you have found any mistake or have any doubt related to above how to conditional formatting based on another cell? tutorial then comment below. If you like the tutorial the follow on twitter and also like us on facebook.

"+y+""}else{if(A==5){c+='
  • '+w+""+y+"
  • "}else{if(A==6){c+='
  • "+w+'
    '+u+""+y+"
  • "}else{c+='
  • "+w+"
  • "}}}}}s.innerHTML=c+=""+y;d.callBack()};randomRelatedIndex=h;showRelatedPost=g;j(d.homePage.replace(/\/$/,"")+"/feeds/posts/summary"+e+"?alt=json-in-script&orderby=updated&max-results=0&callback=randomRelatedIndex")})(window,document,document.getElementsByTagName("head")[0]); //]]>

    15 comments:

    1. HOW TO MAKE A SALARY SHEET IN EXCEL

      ReplyDelete
    2. click the link below as you given
      https://www.youtube.com/watch?v=DGD0UMe2A4k

      ReplyDelete
    3. hey mahesh please help me in pivot table and various tyoes of table format

      ReplyDelete
    4. hey mahesh please help me in pivot table and various tyoes of table format

      ReplyDelete
    5. formaat as style in ms excel is so simple

      ReplyDelete

    6. I need to apply the format to the whole sheet of March and not just column A of march. Also, is there a way to search the whole sheet of "roster" and not just "D:D"? Would indirect("Roster"&"!A:F") work?

      ReplyDelete
    7. The peron names and roles are contained in a single cell: is this correct? If so, is it possible to seperate them into two columns?

      ReplyDelete
    8. The Post seems to be good i really gather lot of information from the post thanks for sharing this awesome post.
      http://www.staffcontrol.com/

      ReplyDelete
    9. I saw two other comparable posts although yours was the most beneficial so a lot try here

      ReplyDelete
    10. Even when using the data templates, it is not guaranteed that different vendors of TEDS-compliant systems will interpret what data goes into the electronic templates in the same way. yük hücresi

      ReplyDelete
    11. These days, clients may acquire a broken phone to any of the wireless repair places.Handy reparatur

      ReplyDelete
    12. I was in a café a week ago, and a cell phone began to ring. Out of nowhere like coordinated robots everybody's hands went for their tote, pocket or belt and quickly raised their cell phone to answer the call. mobile tracker app

      ReplyDelete
    13. You can find the information you need in a safe and clandestine manner. A tracker is the perfect solution for employers, law enforcement, parents and even a suspicious lover. telefon takip programı

      ReplyDelete

    Follow on Twitter

    Linkedin

    Categories

    Contact Form

    Name

    Email *

    Message *

    Mad About Computer. Powered by Blogger.
    This site is in no way affiliated with or endorsed by specified business. It exists as a compendium of supporting information intended for informational purposes only. If you want to buy this website, please don't hesitate to contact us via e-mail: "d e n a c c 9 7 7 (at) g m a i l (dot) c o m" (delete spaces) or you can find and buy it on Afternic domain auctions.