Conditional Formatting in Excel is more than just adding colour—it’s about turning raw numbers into visual insights. This detailed, step-by-step guide explores every feature inside the Conditional Formatting menu. From simple highlights to advanced icon sets and formulas, we’ll walk through it all using a relatable dataset and real screenshots. It’s ideal for beginners and useful for pros—clear, visual, and practical from start to finish.
Table of Contents:
Conditional Formatting is one of the most powerful tools in Excel to help you make sense of your data visually. Instead of manually scanning rows to spot trends, outliers, or issues, you can set rules that automatically highlight cells based on their values or content. For example, you can colour-code high sales figures, flag cells where inventory is running low, apply red icons to overdue dates, or even use colour gradients to visualise performance across regions.
What makes Conditional Formatting so useful is that it doesn’t just decorate your spreadsheet—it helps you read it faster and make smarter decisions without extra formulas or filters. You define the logic, and Excel takes care of the rest.
In this tutorial, we’ll explore every feature available in the Conditional Formatting menu—from basic rules like “Greater Than” and “Duplicate Values” to advanced options like icon sets, formulas, and managing rules. We aim to make this guide useful for all skill levels, whether you’re just getting started or already comfortable working with spreadsheets.
• • •
About the Dataset We’ll Use
To make things practical and hands-on, we’ve prepared a sample dataset that you’ll use throughout this tutorial. You can download it using the link below and follow along in real time.
The dataset is a quarterly Sales & Inventory Tracker, designed with enough variety to let us try out every type of conditional formatting rule. It includes:
- Product names and their assigned regions
- Sales data for four quarters (Q1–Q4)
- Target vs. actual total sales
- Inventory levels and stock status
- Launch dates for each product
This structure allows us to explore both number-based and text-based formatting options, test date rules, compare actuals to targets, and use visual tools like color scales and data bars.
We’ll be using Microsoft Excel on desktop and Excel for the web, both supporting all the features shown in this guide. Most steps will also work on Google Sheets, but our walkthrough will follow the Excel interface to ensure consistency.
Download the dataset to follow along
Once you’ve opened the file, you’ll be ready to dive into the first section: Highlighting Cells Based on Their Values.
• • •
Before we dive into individual rules, let’s first understand how Conditional Formatting works behind the scenes and where to find it in Excel.
How It Works (In Simple Terms)
Think of Conditional Formatting as Excel’s way of saying:
“If this cell meets a certain condition, then apply a specific format to it.”
For example:
- If the value is greater than 50,000, make the cell green.
- If the text says “Out of Stock”, turn it red.
- If the date is in the past, gray it out.
Excel checks each cell against the rule you set. If the condition is true, the formatting kicks in automatically. If not, it stays unchanged.
Now, let’s find out where all this magic begins.
Where to Find the Conditional Formatting Button
Step 1: Open your downloaded dataset in Microsoft Excel or Excel for the Web.
Step 2: Select any range of cells—try selecting the Q1 to Q4 Sales columns for now
Step 3: Go to the Home tab on the top ribbon.
Step 4: Look toward the middle-right section. You’ll find a button called:
Conditional Formatting with “” icon.
Clicking this will reveal a dropdown with several categories of formatting tools, such as:
- Highlight Cell Rules
- Top/Bottom Rules
- Data Bars
- Color Scales
- Icon Sets
- New Rule, Clear Rules, and Manage Rules
Each of these contains multiple options we’ll explore in detail, using our dataset so you can see how they work in real time.
Quick Disclaimer
The exact cell references (like A2, J101, etc.) in this tutorial are based on our sample dataset.
If you’re working with your data or a different layout, the
formulas might need small tweaks to match your column structure and row numbers.
When in doubt, double-check which column holds the value you’re referencing (like
“Units Left” or “Date Added”), and adjust the formula accordingly.
Don’t worry—it’s a common part of working with Excel. You’ll get the hang of it as you go!
Excel offers several built-in formatting styles that visually transform your data in different ways. Here’s a quick snapshot of what each one does:
- Highlight Cell Rules – Color specific cells based on values, text, or dates.
- Top/Bottom Rules – Emphasise the highest, lowest, or average-performing values.
- Data Bars – Add mini bar charts inside cells to show relative size.
- Color Scales – Use gradient colors to compare values across a range.
- Icon Sets – Apply arrows, flags, or traffic lights as visual indicators.
- New Rule – Create custom rules with your conditions and formatting.
- Manage Rules – View, edit, or delete all the rules applied to your sheet.
We’ll explore each of these styles in action—using our sample dataset—so you can see exactly how and when to use them.
Highlight Cell Rules: A Simple Start to Smarter Spreadsheets
Highlight Cell Rules are among the easiest ways to get started with Conditional Formatting. These rules let you visually flag numbers, dates, or text based on clear conditions—like values being too high, too low, matching a certain label, or falling within a specific range.
Using the sales dataset you’ve opened, let’s explore how each rule behaves in real context, so you’re not just learning where to click, but also how to make meaning out of what you see.
1. Greater Than
Let’s say, in the above data sets, you’re managing regional sales performance and want to highlight quarters where sales exceeded ₹50,000—indicating a strong-performing product or region.
Steps:
- Select the Q1 Sales to Q4 Sales columns.
- Go to Home > Conditional Formatting > Highlight Cell Rules > Greater Than.
- Enter 50000 and choose a green fill.
- Click OK.
By highlighting only the outperformers, you can quickly identify what’s working, without scanning each number manually. It’s also a great visual to show in reports.
2. Less Than
Use case: Maybe you’re tracking sales dips or identifying markets that need intervention. Here, we’ll flag any value below ₹30,000.
Steps:
- With the same columns selected (Q1 to Q4 Sales), choose Highlight Cell Rules > Less Than.
- Enter 30000 and apply a red fill.
Tip: This is ideal when reviewing performance targets across time. Try combining it with ‘Greater Than’ formatting to create a heatmap of highs and lows.
Use case: Not every number is a hit or a flop. Sales between ₹40,000 and ₹50,000 might represent stable performance. Highlighting this range can help you understand your mid-tier market.
3. Between
Steps:
-Select the Q1 to Q4 Sales columns.
-Go to Highlight Cell Rules > Between.
- Enter 40000 and 50000, then apply a yellow fill.
Why this matters: It brings focus to the ‘middle zone’, where consistent but improvable performance lives—perfect for coaching or nudging strategies.
4. Equal To
You might want to quickly check if a specific sales target was met exactly, for example, ₹49,000 in Q4 Sales.
Steps:
- Select the Q4 Sales column.
- Choose Highlight Cell Rules > Equal To.
- Enter 49000 and apply a distinct color like teal.
Equal To rules are great for scanning exact price points, inventory levels, or dates that have compliance relevance.
5. Text That Contains
Let’s identify all products marked as “Out of Stock.” This helps warehouse or procurement teams instantly see where restocking is needed.
Steps:
- Select the Status column.
- Choose Highlight Cell Rules > Text That Contains.
- Type in Out of Stock and choose a red fill.
Best practice: Use this rule with filtering or sorting. Once the red flags appear, you can act faster with filtered views or automated notifications.
6. A Date Occurring
Say you want to highlight all products launched this year—this is helpful for marketing teams that want to push newer items.
Steps:
- Select the Launch Date column.
- Choose Highlight Cell Rules > A Date Occurring.
- From the dropdown, pick “This Year.”
- Choose a light blue fill.
Excel recognizes actual date values (not just text that looks like dates). If your format isn’t working, double-check that the column is formatted as a date.
Quick Note for Users: Excel’s web version doesn’t include rich date rules under A Date Occurring, but you can still flag older or specific-year launches using a custom formula.
Let’s suppose we want to highlight all products launched in 2023.
Steps:
- Select the Launch Date column.
- Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
Enter this formula:
=YEAR(Launch_Date)=2023
- (Replace Launch_Date with the actual cell reference for the first launch date, like in our case =YEAR(O6)=2023 if your first date is in O6.)
- Choose a light purple or gray fill, and click Done.
Why this works: The YEAR() function extracts the year from the launch date and compares it to 2023. Any row that matches gets formatted—even in the web version.
7. Duplicate Values
Use case: Duplicate entries in the Total Sales column might indicate data entry errors or overlapping reports.
Steps:
- Select the Total Sales column.
- Go to Highlight Cell Rules > Duplicate Values.
- Choose a fill color that’s visible but not overwhelming.
Why this matters: Detecting duplicates is crucial in financial reports, budgeting, or when merging data from multiple sources. You can also switch the rule to find unique values instead.
• • •
Top/Bottom Emphasis Rules in Action
Beyond simple cell highlighting, Excel’s Top/Bottom Rules are designed to surface trends and outliers, especially useful when you’re working with metrics like sales, revenue, or performance scores.
We’ll now walk through each available option using our dataset. All of these are located under: Home > Conditional Formatting > Top/Bottom Rules
8. Top 10 Items
Use case: Quickly identify the highest revenue-generating products in your sales list.
Steps:
- Select the Total Sales column.
- Go to Top/Bottom Rules > Top 10 Items.
- In the dialog, change "10" to "5" for a more focused list.
- Choose a green fill to signify top performers.
- Click Done.
Why this matters: It’s perfect for reporting or business reviews where highlighting success stories or bestsellers is key.
9. Bottom 10 Items
Use case: Spot products that are underperforming or consistently failing to meet sales expectations.
Steps:
- Select the Total Sales column.
- Go to Top/Bottom Rules > Bottom 10 Items.
- Change "10" to "5", use a red fill to flag concern areas.
- Click Done.
Why this matters: This can help teams prioritize problem-solving or decide on product retirement strategies.
10. Top 10%
Use case: Identify the top percentile performers—useful when your dataset is large and absolute values aren’t enough.
Steps:
- Select the Total Sales column.
- Choose Top/Bottom Rules > Top 10%.
- Leave it at 10% or adjust as needed.
- Use bold green formatting.
- Click Done.
Why this matters: Percentile-based rules offer a more dynamic view when data isn’t evenly distributed.
• • •
11. Bottom 10%
Use case: Spot the lowest 10% of your product line to investigate consistent underperformance.
Steps:
- Select the Total Sales column.
- Go to Top/Bottom Rules > Bottom 10%.
- Choose a soft red fill.
- Click Done.
Why this matters: Useful for spotting subtle but consistent laggers in a large dataset.
• • •
12. Above Average
Use case: Highlight products doing better than the overall average sales.
Steps:
- Select the Total Sales column.
- Go to Top/Bottom Rules > Above Average.
- Pick a light green fill.
- Click Done.
Why this matters: A great visual for reports where you want to show which items exceed expectations without manually calculating the mean.
• • •
13. Below Average
Use case: Highlight products falling short of average performance.
Steps:
- Select the Total Sales column.
- Go to Top/Bottom Rules > Below Average.
- Use a yellow-orange or light red fill.
- Click Done.
Why this matters: It allows decision-makers to zero in on weak performers without complex filtering or formulas.
• • •
Data Bars, Color Scales & Icon Sets
These styles are less about binary conditions and more about visualising magnitude and trends within your data. When applied thoughtfully, they make scanning your spreadsheet feel intuitive, even at a glance.
All three are found under:
Home > Conditional Formatting
We’ll now walk through each style using our Total Sales column from the dataset.
14. Data Bars
Use case: You want to show relative sales performance in Q4 Sales across products, like a horizontal bar chart inside each cell.
Steps:
- Select the Total Sales column.
- Go to Conditional Formatting > Data Bars.
- Choose a Gradient Fill or Solid Fill style.
- A color bar will fill each cell based on its value relative to the column.
Why this matters: It allows for instant pattern recognition without sorting or filtering. You can see who’s leading or lagging—even if all values look close numerically.
Pro tip: Hover over “More Rules” to access options like reversing the scale or hiding the actual numbers.
• • •
15. Color Scales
Use case: You want to apply a heatmap-like format that reflects low-to-high values using color transitions.
Steps:
- Select the Total Sales column.
- Go to Conditional Formatting > Color Scales.
- Pick a 2-color or 3-color scale (e.g., red-yellow-green).
- Excel will color low numbers red and high numbers green, with gradient tones in between.
Why this matters: Great for comparing large sets of data, especially when you’re scanning for relative performance or anomalies.
Pro tip: Choose colors that work well with your spreadsheet’s background for accessibility and contrast.
• • •
16. Icon Sets
Use case: You want to visually categorise stock levels using arrows that indicate whether inventory is high, moderate, or running low.
Steps:
- Select the Units Left column.
- Go to Conditional Formatting > Icon Sets > Arrows.
- Choose the 3-arrow icon set (green up, yellow sideways, red down).
- By default, Excel will:
- Assign a green up arrow to the top third (high stock),
- Yellow sideways arrow to the middle third (moderate),
- Red down arrow to the bottom third (low stock).
Why this matters: Arrows are more immediately understood than numbers, especially in stock or operations reports. A glance tells you what needs attention.
Pro tip: Click More Rules to:
- Customise thresholds (e.g., red arrow for <10 units),
- Change the comparison type (per cent, number, or formula),
- Hide the number value and show only the icon for cleaner dashboards.
Choosing the Right Visual Format:
| Feature |
Best For |
Data Type |
Visual Style |
Ideal When… |
| Icon Sets |
Categorizing performance/status |
Numbers, text |
Symbols (arrows, flags) |
You want quick visual categories like good/average/bad |
| Color Scales |
Spotting trends or ranges |
Numeric only |
Gradient fill colors |
You’re comparing low to high values in a heatmap style |
| Data Bars |
Showing relative volume/weight |
Numeric only |
In-cell horizontal bars |
You want to rank or compare magnitudes within the same column |
Quick Recommendations:
- Use Icon Sets for:
Status indicators (In Stock, Performance, Risk Level), Dashboards
- Use Color Scales for:
Ratings, Customer Sentiment, Forecast accuracy, Revenue distribution
- Use Data Bars for:
Sales volume, Attendance, Completion %, Inventory usage
• • •
You’ve already brushed past New Rule earlier—remember when we used it under “Date Occurring” to highlight products added This Year? That little detour used a behind-the-scenes formula to filter dates dynamically.
“New Rule” lets you write your own logic—no more relying only on Excel’s presets.”
This is where conditional formatting becomes context-aware. You can create formatting that checks multiple conditions at once, compares columns, or even highlights patterns that only you care about.
Use Case: Highlight all products added in the year 2024
You’re flagging all new products introduced this year—a common reporting need.
Steps:
- Select your entire dataset.
- Go to:
Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
- Paste this formula:
=YEAR($O6)=2024
- Click Format, choose a calming blue or green fill, and hit OK.
• • •
By now, you’ve used several of Excel’s built-in rules—from highlighting duplicate values to creating color scales. But what if you want to apply formatting based on logic that’s not available in the default menu?
That’s where formula-based conditional formatting comes in.
This feature lets you build rules using Excel formulas, giving you full control over what gets formatted, when, and how.
Use Case: Highlight products from the “South” region with sales underperforming their quarterly target (i.e., Total Sales < Target)
Steps (Excel Web):
- Select your dataset range → D7:O14 (excluding headers)
- Go to Home > Conditional Formatting > New Rule
- Choose “Use a formula to determine which cells to format.”
- Enter this formula:
=AND(E7=”South”, I7<K7)
- Pick a fill color or border style to indicate underperformance
- Click Done.
Manage Rules & Clear Rules
As you begin layering multiple rules across your dataset—maybe a color scale for sales, an icon set for units left, and a custom formula for date—it’s easy to lose track of what’s active and where. That’s where Manage Rules comes in.
It’s your command center for conditional formatting.
• • •
What You Can Do with “Manage Rules”:
- Edit existing rules if something isn’t working as expected.
- Reorder rules when multiple formats overlap. Excel applies rules in sequence.
- Check which rule applies to which range—especially useful when copying formatting.
- Disable or delete outdated rules instead of starting over.
Access it via:
Home > Conditional Formatting > Manage Rules.
By default, it shows rules for the selected area only. You can switch the dropdown at the top to “This Worksheet” to see all active rules.
• • •
When to Use “Clear Rules”:
If your spreadsheet starts looking like a circus—icons here, bright fills there—you might want to start fresh.
Use Clear Rules to:
- Remove formatting from the entire sheet
- Or just from the selected rows/columns
Find it under:
Home > Conditional Formatting > Clear Rules
• • •
Why this matters in real-world use:
Managing rules becomes especially important when collaborating. You might not know what others have set up, or why a cell is glowing red. The Manage Rules dialog gives you control and clarity, keeping your data clean and consistent.
Conditional Formatting is more than just a way to make your spreadsheet colorful—it’s about making your data speak. Throughout this guide, we’ve walked through every major feature, from simple highlight rules to custom formulas, color scales, icon sets, and rule management.
We’ve worked with a practical dataset, explored real-world use cases, and learned how Excel can visually surface patterns, outliers, and trends—all with just a few clicks.
And yes, Excel can feel a bit overwhelming when you’re starting. But like most tools, once you start using it with purpose, it becomes second nature. The trick is to experiment, observe, and refine. Start small, focus on clarity, and let your formatting serve the story your data wants to tell.
So take what you’ve learned here, play with your datasets, and build spreadsheets that are not only accurate, but intuitive, insightful, and even a little bit delightful.
FAQs
1. What is conditional formatting in Excel?
Conditional formatting is a powerful Excel tool that applies visual formatting—colors, icons, or bars—automatically when cell values meet rules you define (e.g., above a threshold, matching text, date-based). It helps highlight trends and issues without manual review.
2. How do I use conditional formatting based on another cell?
Select your target range, go to Conditional Formatting > New Rule > Use a formula, and enter a formula like =$C2=”Yes”. Excel will apply formatting to each row depending on the referenced cell’s value.
3. Can I use conditional formatting for dates and times?
Yes—Excel lets you highlight cells based on date rules like Yesterday, Last 7 Days, This Month, etc. You can also use formulas (e.g., =YEAR(A2)=2024) for more precise time-based formatting.
4. How can I remove or clear conditional formatting from cells?
Select the cells or sheet, then choose Conditional Formatting > Clear Rules and pick “From Selected Cells” or “From Entire Sheet.” This removes formatting but keeps your data intact.
5. Can I apply multiple conditional formatting rules to the same cells?
Absolutely. Excel allows stacking multiple rules on the same range. Use Conditional Formatting > Manage Rules to prioritize, edit, or remove rules, and control which formats take precedence.