How to Use Conditional Formatting in Excel: Stepwise Guide for Beginners
- Sharon Rajendra Manmothe
- Aug 27
- 6 min read
Table of Content
How do you make Excel cells change color automatically based on due date?
What is top 10 in conditional formatting?
What is a Type 2 conditional sentence?
Conditional formatting in Excel lets you automatically color or style cells based on their values or contents. Here’s a friendly, stepwise blog for beginners, written in simple human language
Ever stared at a boring spreadsheet and wondered how to make important numbers or dates jump out? That’s exactly what conditional formatting does! It’s a way to make your Excel sheets colorful, organized, and super easy to understand at a glance.
Step 1: What Is Conditional Formatting?
Conditional formatting means telling Excel, “If a cell fits a rule (like greater than 100), then make it stand out!” You can highlight sales above a target, show late deadlines in red, or spot duplicate values quickly.
Step 2: Open Your Data in Excel
Start by opening your Excel file and looking at the data you want to highlight. This could be sales numbers, dates, people’s scores, or anything else.
Step 3: Select the Range You Want to Format
Click and drag to highlight the cells you want—maybe a whole column or just a small table. For example, highlight all your sales amounts.
Step 4: Find the Conditional Formatting ButtonGo to the Home tab in the top menu.
In the “Styles” group, look for Conditional Formatting (it’s usually near the middle of the Excel ribbon).
Click it!
Step 5: Choose the Rule Type
A drop-down menu appears. You have many options—but here are the most common and useful ones:
Highlight Cell Rules: Use for “Greater Than…”, “Less Than…”, “Between…”, or “Contains text…”
Top/Bottom Rules: Make high or low performers pop out.
Duplicate Values: Instantly spot copies.
Date Occurring: Show recent or overdue dates in color.
Pick the one that fits your need. For example, to show sales above 100, choose Greater Than…
Step 6: Set Your Condition
Excel will ask you to type in the value or text for the rule.Example: Type “100” for “Greater Than…”
Step 7: Choose a Formatting Style
Excel will show you color choices—like red fill, green text, yellow highlight. Pick what makes sense and helps you see what’s important.
If you want to customize further, click “Custom Format...” to choose any color, font, or even borders.
Step 8: Click OK and See the Magic!
Once you’re happy, click OK (or Done on some versions). Instantly, Excel highlights cells that meet your condition.
Step 9: Change, Remove, or Add More Rules
To edit or remove a rule, go back to Conditional Formatting and choose “Manage Rules.”
Add more rules for different conditions or colors if you want lots of details at a glance.
Step 10: Use Advanced Tricks When Ready
You can use formulas for more complex rules—for example, color only if two conditions are true, or compare to another cell’s value.
The 5 Types of Conditional Formatting in Excel
The five core types you’ll use most are:
Highlight Cells Rules
Top/Bottom Rules
Data Bars
Color Scales
Icon Set
The 5 Types of Conditional Formatting in Excel
1) Highlight Cells Rules
Great for flagging values that meet simple conditions (greater than, less than, between, equal to, text contains, dates, duplicates).
Steps (example: highlight Sales below Target):
Select B2:B7 (Sales).
Home ▸ Conditional Formatting ▸ Highlight Cells Rules ▸ Less Than…
In the box, type =C2 (a cell reference, not a number) and click OK.
Choose a preset format (e.g., Light Red Fill with Dark Red Text).
Conditional Formatting ▸ Highlight Cells Rules ▸ Less Than
Tips
Use Duplicate Values… in the same menu to catch repeats.
Cell references make rules dynamic (each row compares B to its own C).
2) Top/Bottom Rules
Quickly find outliers and trends—top/bottom items or percentages, above/below average.
Steps (example: top 10% Sales):
Select B2:B7.
Conditional Formatting ▸ Top/Bottom Rules ▸ Top 10%…
Change 10 to any percent or choose Top 3 items etc. ▸ OK.
Other handy picks
Above Average / Below Average to spot performance vs. mean.
Bottom 10% to find underperformers fast.
3) Data Bars
Adds in-cell bars to show magnitude—perfect for dashboards.
Steps:
Select B2:B7.
Conditional Formatting ▸ Data Bars ▸ pick a style (Gradient or Solid).
Refine (optional):
Conditional Formatting ▸ Manage Rules… ▸ Edit Rule…
Set Minimum/Maximum to Number (e.g., 0 and 70000) for consistent scales.
Check Show Bar Only to hide the numbers and keep clean bars.
Data Bars
4) Color Scales
Applies 2-color or 3-color heat maps—great for comparing many values at once.
Steps (example: heat map on Sales):
Select B2:B7.
Conditional Formatting ▸ Color Scales ▸ choose a 3-color scale.
Low values get the first color, highs the last, middle values the midpoint.
Pro tip
Use a 2-Color Scale when you only care about low vs high, not the middle.
Color Scales
5) Icon Set for Conditional formatting
Select B2:B7.
Conditional Formatting ▸ Icon Set
Icon Set for Conditional formatting
Understanding the Purpose of Conditional Formatting in Excel
Conditional Formatting is one of Excel's most powerful features. It helps you automatically format cells based on their values or conditions. This makes it easier to spot trends, find errors, and make your data more visually meaningful. In this blog, we will explore the purpose of conditional formatting and provide simple steps to use it effectively.
Why Use Conditional Formatting?
1. Visualize Data Quickly
Numbers can be overwhelming. Conditional formatting adds color, icons, or bars to cells so you can see patterns or outliers at a glance.
2. Highlight Important Information
You can emphasize key values—like sales below target, overdue dates, or top performers—without manually checking each cell.
3. Reduce Errors
It helps identify duplicates, missing data, or values outside a desired range, ensuring your data stays accurate.
4. Make Better Decisions
By visually organizing your data, you can make faster and more informed business or academic decisions.
Make Excel cells change color automatically based on due date — Step-by-step
Sample sheet (paste into Excel)
A B C
Task Due Date Status
Task 1 2025-08-20 Open
Task 2 2025-08-27 Done
Task 3 2025-09-01 Open
Task 4 2025-08-25 Open
Assume Due Date is column B (B2:B100) and Status is column C (optional).
Quick preparation (important)
Make sure the Due Date column is formatted as Date (Home → Number → Date).
Decide whether you want to color only the date cells (B2:B100) or the entire row (A2:C100).
If entire row → lock the column in formulas with $ (e.g., $B2).
Rule set: common useful rules (order matters)
Order the rules so the most urgent (overdue) is on top.
1) Red = Overdue (Due date before today)
Select range: B2:B100 (or A2:C100 to color whole row).
Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
Formula (color DATE cell only):=AND($B2<>"",$B2<TODAY())If formatting only the date column and selection started at B2 you may use =B2<TODAY().
Click Format → Fill → pick red → OK → OK.
2) Orange = Due Today
New rule, same selection.
Formula:=AND($B2<>"",$B2=TODAY())
Format → Fill → orange.
3) Yellow = Due within next 7 days (including today)
New rule.
Formula:=AND($B2<>"",$B2>=TODAY(),$B2<=TODAY()+7)
Format → yellow.
4) Green = Due in more than 7 days (future)
New rule.
Formula:=AND($B2<>"",$B2>TODAY()+7)
Format → green.
Variation: exclude tasks already marked “Done”
If you have a Status column (C) with values like Done, Closed, or Completed, wrap each rule with a check:
Example (overdue but not done):=AND($B2<>"",$C2<>"Done",$B2<TODAY())
Use the same pattern for other rules (add ,$C2<>"Done" inside the AND).
Apply formatting to entire rows
If you want the whole row (A through C) to change color based on the due date in column B:
Select the rows range (e.g., A2:C100).
In the rule formulas use a fixed column and relative row: e.g., =$B2<TODAY() or =AND($B2<>"",$B2<=TODAY()+7).
Create rules and choose fill colors. The $ in $B2 locks the column so Excel checks column B for each row.
Google Sheets (quick guide — very similar)
Select range (e.g., A2:C100) → Format → Conditional formatting.
Under Format rules, choose Custom formula is.
Use the same formulas without the leading = in some versions, but best to include = (e.g., =AND($B2<>"",$B2<TODAY())).
Set formatting style → Done.
$50
Product Title
Product Details goes here with the simple product description and more information can be seen by clicking the see more button. Product Details goes here with the simple product description and more information can be seen by clicking the see more button
$50
Product Title
Product Details goes here with the simple product description and more information can be seen by clicking the see more button. Product Details goes here with the simple product description and more information can be seen by clicking the see more button.
$50
Product Title
Product Details goes here with the simple product description and more information can be seen by clicking the see more button. Product Details goes here with the simple product description and more information can be seen by clicking the see more button.
Comments