Conditional Formatting and Data Validation
This course covers Conditional Formatting and Data Validation and consists of 13 topics.
Top and Bottom Rules – This video shows how to highlight the 20 warmest temperatures since 1881 and all above average and below average temperatures since 1881.
Highlight Cells Rules – This video shows how to highlight duplicates, any cells containing a given text string, and highlighting cells based on criteria involving dates.
Data Bars, Colors Scales, and Icon Sets – This video shows how Data Bars, Color Scales, and Icon sets are used to summarize numerical data.
Formula Option Part One – This video shows how cells can be formatted based on a complex Excel formulas. For example, highlight in green, each quarter a company’s sales increase, and in red each quarter a company’s sales decrease.
Formula Option Part Two – This video continues to show how to conditional format based on a formula.
Stop if True – This video shows how to use the Stop If True Criteria to highlight 10 states that have the highest income with an up arrow and not highlight any other states.
Data Validation – This video illustrates how Data Validation can return an error message when incorrect data is entered. For example, if we enter that an NBA team scored 1000 points, we receive an error message. We also show how to create a drop-down list from which a user selects an input.
Custom Option – This video shows how Data Validation criteria can be set up with Excel formulas. For example, we show how to ensure that entering any non-numerical entry in column A creates an error message.
Using Data Validation To Prevent Entry Of Duplicates – In this video we show how to use data validation to prevent users from entering duplicate rows in a workbook
Circle Invalid Data – This video shows how to ensure that data not meeting a desired criteria is circled.
Highlighting New Hires – This video shows how to highlight employees who have just been hired.
Conditional Formatting and Sports Part One – In this video we show how to highlight all baseball players who are good on HR’s, Stolen Bases, and Batting Average.
Conditional Formatting and Sports Part Two – In this video we show how to use data bars, color scales, and icon sets to format sports data.