Conditional Formatting

1. Conditional Formatting Create special color coded and icon “views” of data based on various criteria and “Rules”

2. Conditional Formatting • Why use conditional formatting? • Emphasize data • Call attention to errors • Easy data correlation • Can use built-in conditional formatting rules, or create your own • Can “Filter” data based on Cell Format color • Built in rules can usually be modified: • (ie: Top 10 can be any Number: Top 2, Lowest 3, Top 15%) • Can reference a cell, use a “Constant” value, or even a formula: $A$17 70 =$A5>$B5 .

3. Conditional Formatting

4. Conditional Formatting: Data Bars and Icons You can “hide” or “display” the Data Value with the graphic.. Be careful with the use of colors for color blindness and printing While there may be 3 to 5 Icons, you can define the rules Icon Sets Data Bars & Icons

5. Custom Conditional Formats • You can have multiple conditions that apply to the same range of data. • Each time you create a custom conditional format, you are defining a conditional formatting rule • A rule specifies the type of condition (such as formatting cells greater than a specified value) and the type of formatting to apply when that condition occurs. • When you have multiple rules, you should test your conditions to make sure the logic is applied in the correct /desired sequence.

6. Exercise • Highlight “Duplicate” records • Create/Edit Conditional Formatting Rules • Highlight an entire ROW of data based on a Formula • Filter Records by color

7. Average Analysis using Conditional Formatting #1

8. Using Conditional Formatting – Example #2 This example is a more complex use of the Conditional Formatting Option to highlight an entire ROW of data based on a condition. The workbook used is: Student_Data_Table . Highlight the Data Range (A5:E24) [Home] {Conditional Formatting} Manage Rules New Rule Use a Formula to determine which cells to format Enter the Formula: =$ColumnRow =value String data needs to be in “Quotes” Use conditions: =, >, <, >=, <=, <> Example: = $C5=“W” =$D5>25 (You need to use the ABSOLUTE Cell Reference Select the FORMAT for the Rule. Verify the APPLIES TO: range $A$5:$E$24 http://www.free-training-tutorial.com/animations/conditional-formatting-row.html

9. Conditional Formatting Use the {Find and Select } option on the [HOME} tab to identify cells that may contain conditional formatting