Conditional Formatting in Excel: Complete Visual Guide
SheetHub4 min
You have a spreadsheet with 5,000 rows of sales data. Somewhere in that sea of numbers are the high performers, the outliers, the errors. Finding them manually is like looking for a needle in a haystack.
Conditional formatting applies colors, icons, and data bars automatically based on your rules. High values turn green. Low values turn red. Errors highlight themselves. Your data tells its own story.
Formulas calculate. Conditional formatting reveals. A properly formatted sheet lets you spot trends, outliers, and problems at a glance — without reading a single number.
Greater than, Less than, Between:
Find your best and worst performers:
Data bars add a horizontal bar inside each cell — like a bar chart inside your table:
Color scales use a gradient across your data:
Add icons (traffic lights, flags, stars) to each cell based on its value:
For conditions that the built-in rules cannot handle:
Highlight rows where order value > $1,000 AND region is West:
The $ before the column letter is critical. It locks the column so the rule applies to the whole row.
Use conditional formatting with Tables. Rules automatically expand as you add rows to an Excel Table.
Copy formatting with Format Painter. Select a formatted cell → click Format Painter → apply to other cells.
Combine multiple rules. Apply a data bar AND an icon set to the same range for richer visualization.
Too many rules. More than 5-6 rules on the same range makes the sheet slow to calculate. Consolidate where possible.
No $ in formula rules. For highlighting entire rows, the column reference must be absolute ($B2) while the row is relative.
Formatting overrides values. Conditional formatting changes appearance, not the underlying data. Sorting and filtering still work on actual values.
Does conditional formatting work in Google Sheets? Yes. Format → Conditional formatting. The interface differs but the concepts are the same.
Can I copy conditional formatting to another sheet? Yes. Copy the formatted cells → Paste Special → Formats.
Why is my conditional formatting not working? Check that your formula returns TRUE/FALSE, the range is correct, and there are no conflicting rules with higher priority.
Why This Matters
Highlight Cell Rules
- Select your data range
- Home → Conditional Formatting → Highlight Cells Rules
- Choose: Greater Than, Less Than, Between, Equal To, or Text that Contains
- Set your value and formatting color
Top / Bottom Rules
- Top 10 Items — Highlight the top values
- Bottom 10% — Highlight the bottom percentile
- Above Average — Values above the average
- Below Average — Values below the average
Data Bars
- Select range → Conditional Formatting → Data Bars
- Choose gradient or solid fill
Color Scales
- Select range → Conditional Formatting → Color Scales
- Choose a 2-color or 3-color scale
Icon Sets
- Select range → Conditional Formatting → Icon Sets
- Choose directional, shapes, indicators, or ratings
Custom Rules with Formulas
- Select range → Conditional Formatting → New Rule → Use a formula
- Enter your formula (must return TRUE/FALSE)
=$B2="East"=AND($C2>1000, $B2="West")Managing Rules
- View rules: Conditional Formatting → Manage Rules
- Edit rules: Double-click the rule in the manager
- Delete rules: Select and click Delete
- Priority: Rules at the top take precedence
Pro Tips
Common Mistakes
Related Functions
- IF, Nested IF, IFS — Create formula-based conditions for your formatting rules
- Data Validation — Control what data enters your formatted ranges
FAQ
Topics
Topics in this article
Explore related topics and continue reading similar content.
Share this article
Discussion
Preparing the comments area...