How to Use Conditonal Formatting Using the LOOKUP and if Function
Open the spreadsheet you are working on to determine what to format., Click on the cell you wish to write the function. , The first example below uses the IF function., Practice.
Step-by-Step Guide
-
Step 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. -
Step 2: Click on the cell you wish to write the 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 a 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. , 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. -
Step 3: The first example below uses the IF function.
-
Step 4: Practice.
Detailed Guide
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.
, 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 a 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. , 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.
About the Author
Helen Burns
Brings years of experience writing about crafts and related subjects.
Rate This Guide
How helpful was this guide? Click to rate: