Excel Conditional Formatting: Complete Guide
Master Excel conditional formatting with color scales, data bars, icon sets, and custom formula rules. Visual guide with examples.
What Is Conditional Formatting?
Conditional formatting automatically changes the appearance of cells based on their values. It helps you spot trends, outliers, and patterns at a glance. Excel offers built-in rules for highlighting cells greater than a threshold, top/bottom values, duplicates, and dates. You can also create custom rules using formulas for complete flexibility. Conditional formatting applies to any cell range and updates dynamically as data changes.
Built-In Rules: Color Scales, Data Bars, and Icon Sets
Select your range and go to Home > Conditional Formatting. Color Scales apply a gradient (e.g., red-yellow-green) based on cell values — perfect for heat maps. Data Bars add in-cell bar charts proportional to each value. Icon Sets display arrows, flags, or traffic lights based on thresholds. These built-in options require zero formulas and are the fastest way to add visual context to your data.
Custom Formula Rules
For advanced scenarios, use 'New Rule > Use a formula to determine which cells to format.' The formula must return TRUE or FALSE. Examples: =A1>100 highlights cells above 100. =A1<>B1 highlights mismatches between columns. =$C1="Overdue" highlights the entire row when column C contains 'Overdue' (note the $ on C to lock the column). You can combine multiple rules and use Manage Rules to set the priority order.
Let AI Handle Complex Formatting
Simple highlights are easy, but building multi-condition rules with custom formulas across large worksheets gets tedious fast. With ohsheet.ai, just describe what you want: 'Highlight rows where revenue is below target in red, above target in green, and add data bars to the profit column.' Upload your file and get it back with all the formatting applied — no rule manager required.