Conditional Formatting In Excel
Excel’s Conditional Formatting feature enables you to format a range of values so that unusual or unwanted values, or values outside certain limits, or a required set of selected values, are automatically formatted in such a way as to call attention to them.
It is most often used as color-based formatting to highlight, emphasize, or differentiate among data and information stored in a spreadsheet.
In this video I shall be explaining how to effectively use conditional formatting to our advantage, supported with different types of examples (using Excel) to provide complete clarity on the same.
Before I walk you through creating and applying conditional formatting, you should understand the basics of how it works. The following structural aspects of Excel conditional formatting will guide how you create and apply rules:
If-Then Logic
Preset Conditions
Custom Conditions
Applying Multiple Conditions
If-Then Logic: All conditional formatting rules are based on simple if-then logic: if X criteria is true, then Y formatting will be applied (this is often written as p → q, or if p is true, then apply q). You won’t have to hard-code any logic, since Excel and other spreadsheet apps have built-in parameters so you can simply select the conditions you want the rules to meet. Advanced users can also apply the program’s built-in formulas to logic rules.
Preset Conditions: Excel has a huge library of preset rules encompassing nearly all functions that beginner users will want to apply. We’ll be looking at several of the most popular ones further in this video.
Custom Conditions: For situations where you want to manipulate a preset condition, you can create your own rules. If appropriate, you can use Excel formulas in the rules you write.
Applying Multiple Conditions: You can apply multiple rules to a single cell or range of cells. However, be aware of rule hierarchy and precedence – I’ll show you how to manage stacked rules in the walkthrough.
00:00 - Intro
00:50 - Basics Of Conditional Formatting
03:34 - Highlight Cell Rules
12:56 - Top/Bottom Rules
15:56 - Data Bars
17:43 - Color Scales
20:42 - Icon Sets
23:49 - Creating Your Own Conditional Formatting Rules
28:33 - Quick Analysis Tool
30:09 - Conclusion