How to Use Conditonal Formatting Using the LOOKUP and if Function -

If you are working on a large spreadsheet it is difficult to make sense of all the data manually. You can use the IF and LOOKUP function in Excel to make sense of the data for you.

Edit Steps

1. Open the spreadsheet you are working on to determine what to format. For example, you may have a roster that contains a list of 200 students and their exam grades. How would you make sense of all this data? You can enter all the corresponding letter grades manually. For example 95 percent means "A" and 90 percent means "A-," or you can use the "IF" or "LOOKUP" function in Excel and have the grades entered automatically as you type the percentage.
   
   
   
   
   
   

   
   

   

   
   Example 1. An example grade roster using Excel.

   
   
   
   


2. Click on the cell you wish to write the function.
   
   
   
   
   
   

   
   

   

   
   

   
   
   
   


3. The first example below uses the IF function. Type: =IF(B3>89,"A",IF(B3>79,"B", IF(B3>69,"C",IF(B3>59,"D","F")))). Notice that the letter in the roster will change live as you enter the grades. This IF function states that if B3 is > than 89 is true than assign an letter "A" grade. In the second example below, we will use the LOOKUP function. Type in =LOOKUP(B4,{40,45,60,65,80,85,90,95},{"D-","D","C-","C","B-","B","A-","A"})in B4 that corresponds to Dave. This will tell Excel to look-up the data in cell B4. This equation programs 45 percent to return a "D" grade. Do the same for B5 and B6 or you can pull down on the fill handle so that the equation applies to all the students.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   

   
   

   

   
   

   
   
   
   
   
   
   
   
   
   

   
   

   

   
   

   
   
   
   


4. Practice. If you are still having trouble understanding Excel and writing equations it is because you have not yet mastered the basics. The table below will summarize the basic rules and conditional formatting guidelines. Please click on the chart below for a larger view so that you can use it as a reference.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   

   
   

   

   
   

   
   
   
   
   
   
   
   
   
   

   
   

   

   
   

   
   
   
   

Edit Tips

• Always review your algebra skills when typing a logical statement or an equation if math is not your best subject.


• Always remember the order of operations when entering an equation to prevent errors.


• There are many free self-help resources online and at your local library if you do not have the budget to take college computer courses.

Edit Things You'll Need

• MS Office Excel 2007 or later.

Article Tools

• Read on wikiHow


• Email this Article


• Edit


• Discuss

Fuente