Did you want Working with Numbers: 2007,2010,2013,2016  or español

A special hidden use of the IF function is to create formatting that depends on certain conditions - conditional formatting. Combining different values and different formats based on a logical comparison can make your sheet super cool! And informative!

Excel 97 is the first version to include conditional formatting.

For example, in the Grades Calculator, when the Final Average is under 70, you could change the formatting of the Final Average. You could have different formatting if the grade dropped below 60, a failing grade.

Click on the illustration to see conditional formatting in action. An IF function adds appropriate text and conditional formatting changes the look of the Final Average cell. These combine to point out dangerously low grades.

IF function and conditional formatting

Where you are:
JegsWorks > Lessons > Numbers

Project 2: Excel Basics

Project 4: Groups & Formulas

Search
Glossary

Appendix

## Step-by-Step: Conditional Formatting

 What you will learn: to create conditional formatting to find cells that have conditional formatting

### Format Cells: Conditional Formatting

With conditional formatting you can have different formatting for the TotalPoints cell when its value is large enough to qualify for a bonus. You can change the formatting for the Bonus cell, too. You will make the TotalPoints and Bonus cells Green with White text when there is a bonus, and leave the formatting you already applied when there is no bonus due.

1. Select cell D11 and from the menu select | The dialog opens with the Condition 1 form ready for you to fill in.

2. Set Condition: Fill in the text boxes by choosing: Cell Value Is and greater than or equal to from the drop lists. Click in the third text box and type in  =\$C\$14  or just click on cell C14.

If you do not type the  =  yourself, Excel will think you are typing text and will "correct" your entry to read ="\$C\$14" , putting quotes around your typing. This is not what you want!

3. Set Formatting: Click on the Format… button in the dialog and choose Pattern | Cell Shading = Green and Font | Color = White and Font style = Bold. You do not get quite all of the Font choices that you are used to seeing in a Font dialog.

4. Click OK to close the Format Cells dialog. The Conditional Formatting dialog shows a sample using your choices. You can add up to two more sets of conditions - each with different formatting. This time you only need the one Condition.

5. Click OK again to close the dialog. When this condition is not met, you will see the original formatting.

6. While cell D11 TotalPoints is selected, click the Format Painter button. Then click on cell G9 Bonus to copy the same formatting to it. The Conditional Formatting is applied also!

Unfortunately, teal border was also applied to the bottom and right of the cell and the font size was reduced and you lost the currency formatting. <sigh> Still, it is often easier to copy a conditional format this way and just fix any problems that it creates.

7. Repair formatting: With G9 selected, open | | and change the border to just a heavy green border on the right and no border on the bottom.

Use the Formatting bar to change the Font Size to 12 and apply Currency format.

8. Save as  trips37.xls

9. Test: Change some of the trip numbers to change the TotalPoints to less than 50. Use numbers that will give exactly 50 points. Does the formatting change as planned in both cases? (This is super cool!)

11. Gardner: With Gardner's data back in place, show the formulas. (Tools | Options | View ) Print the sheet on one page in Landscape orientation. Do not print the comments. After printing, hide the formulas.

12. Heinz Fill in the trip numbers and name for Heinz and print page 1 in Portrait orientation,  without formulas. Do not print the comments. (Use the values on the sheet Specials to figure out what he sold.)

### Find Cells with Conditional Formatting

How can you tell later which cells have conditional formatting? Many cells might have your conditional formatting applied, but not show it because the conditions are not met at this time.

There is a way to highlight these cells, but it will take a few clicks.

To find ALL cells with conditional formatting:

• Select any cell on the sheet.
• From the menu select | | | and then select Conditional Formatting.
• Click on OK. Cells with conditional formatting will be highlighted.

To find cells with the SAME conditional formatting:

• Select a cell with the formatting you want to match.
• From the menu select | | | and then select Conditional Formatting and select Data Validations - Same.
• Click on OK. Cells with matching conditional formatting will be  highlighted.

Try this with your Bonus sheet. Remember that the selected cell has the selection border but not the highlight.

Close trips37.xls when you are done.