How to Use Excel Conditional Formatting: Three Practical Examples

Key Takeaways

• Conditional formatting in Excel lets you automatically apply visual cues like colors, icons and data bars to cells based on their values, saving time and reducing errors when analyzing data.
• You can use icon sets to replace plain cell formatting with visual indicators like stars, arrows or traffic lights for quick data categorization.
• Formula-based conditional formatting rules let you highlight entire rows (not just individual cells) based on criteria in any column.
• Date-based conditional formatting formulas using YEARFRAC and TODAY can automatically flag records that meet age or deadline thresholds.

What Is Conditional Formatting in Excel?

Conditional formatting in Excel is a feature that automatically changes the appearance of cells based on rules you define. Instead of manually scanning rows and columns to find the data that matters, you set conditions and let Excel do the visual work for you. The feature lives on the Home tab under the Conditional Formatting dropdown button.

Excel offers several built-in rule types you can apply with just a few clicks:

Beyond these built-in options, you can also create custom formula-based rules that give you precise control over which cells get formatted and how. The three examples below focus on some of the most powerful techniques: icon sets, formula-based row highlighting and date calculations.

To follow along using our example, download FPS_Apply Conditional Formatting.xlsx

When to Use Conditional Formatting

Conditional formatting rules are useful any time you need to make patterns, exceptions or categories visible without manually formatting individual cells. Here are some of the most common use cases:

• Spotting duplicate values in a list of names, email addresses or ID numbers
• Flagging overdue dates or upcoming deadlines in a project tracker
• Identifying top and bottom performers in a sales or grading report
• Highlighting negative numbers or values that fall outside an acceptable range
• Tracking project status by color-coding stages like "complete," "in progress" or "not started"
• Visualizing data distributions across large datasets using color scales or data bars
• Categorizing donation levels, survey responses or risk ratings with icon sets

The three examples that follow walk you through icon sets for visual categorization, a formula-based rule for highlighting an entire row and a date calculation that flags records meeting an age threshold.

Use Icon Sets Instead of Cell Formatting

When you want a little more visual interest than cell fill colors and font colors, you can use conditional formatting to display icon sets.

1. Select the column or data range.
2. Open the New Formatting Rule dialog box by clicking the Conditional Formatting dropdown button and selecting New Rule. (You can use the Icon Sets option in the dropdown menu if default settings are acceptable.)
3. In the Format Style dropdown menu, select Icon Sets.
4. Choose the type of icons you want in the Icon Style dropdown.
5. In the lower half of the dialog, you can set specific criteria for how each icon is displayed. In this example, we want:

• Donors who gave 5,000 and up to show a full yellow star
• Donors who gave 500-5,000 to show a half yellow star
• Donors who gave 500 and less to show an empty star

You can display icons based on percentages and custom formulas by changing the Value and Type criteria.

Highlight an Entire Row or Column

When you are looking at data that has many columns, highlighting just one cell might not be enough for you to see the whole picture. Instead, highlight the whole row!

How to do it:

1) Select the entire data range (excluding column headings).

2) Open the New Formatting Rule dialog box by clicking the Conditional Formatting dropdown button and selecting New Rule.

3) Select Use a formula to determine which cells to format.

4) Enter the formula that describes your conditions. In this example, we want to highlight all rows of customers from Illinois so our formula will be: =$D2="Illinois"

D2 is the first cell in our data that has the text we are looking for. Because the conditional logic works like copy/paste in Excel as it is applied to each cell in the range, you need to put the $ symbol in front of the column letter to lock the column reference so it doesn't shift as the rule evaluates each cell. Do not put it in front of the row number as you want the formula to evaluate each new row.

5) Hit OK to close the dialog box and apply the formatting.

Click Manage Rules to view and edit the conditional formatting formulas if needed. Notice that the Applies to field includes the range that we selected at the beginning.

Calculate and Highlight Date Data

A frequent task for any business that manages personal information is to calculate ages from birthdates. Using the technique above to highlight an entire row, we can use the following formula in the Format values where the formula is true field to help us see which members on the list are 65 or older:

=INT(YEARFRAC($G2,TODAY()))>65

YEARFRAC calculates the number of years between the birthdate and today's date, and INT rounds the result down to a whole number so the comparison checks for age 65 or older.

Learn more about date functions here.

Tips for Managing Conditional Formatting Rules

Once you start building multiple conditional formatting rules, knowing how to manage them becomes essential. Here are some practical tips for staying in control:

• View and edit rules: Go to Home > Conditional Formatting > Manage Rules to see every rule applied to the current selection or the entire worksheet. Select any rule and click Edit Rule to modify its conditions or formatting.
• Understand rule priority: Rules are evaluated from top to bottom in the Manage Rules dialog. If two rules apply to the same cell, the rule higher in the list takes precedence. Use the up and down arrows to reorder rules as needed.
• Use "Stop If True": Check the Stop If True box next to a rule if you want Excel to stop evaluating additional rules once that rule's condition is met. This prevents conflicting formats from stacking on the same cell.
• Delete rules you no longer need: Select a rule in the Manage Rules dialog and click Delete Rule to remove it. You can also go to Conditional Formatting > Clear Rules to remove all rules from selected cells or the entire sheet.
• Copy formatting with Format Painter: If you have built a set of conditional formatting rules on one range, use the Format Painter tool on the Home tab to copy those rules to another range without rebuilding them from scratch.

Common Conditional Formatting Mistakes to Avoid

Even experienced Excel users run into issues with conditional formatting. Watch out for these common pitfalls:

• Mixing up relative and absolute references: In formula-based rules, forgetting the $ symbol before a column letter means the reference shifts as Excel evaluates each cell, which can produce unexpected results. Conversely, locking both the column and row (like $D$2) means every cell checks the same single cell instead of evaluating row by row.
• Overlapping rules that conflict: When multiple rules apply to the same range, their formatting can override each other in confusing ways. Review rule priority in the Manage Rules dialog and use Stop If True where appropriate.
• Applying rules to the wrong range: If your conditional formatting highlights cells you did not expect (or misses cells it should catch), check the Applies to field in Manage Rules. It is easy to accidentally select too many or too few cells when creating a rule.
• Forgetting to update rules when data changes: If you insert new rows or columns, your conditional formatting range may not expand automatically. Revisit the Applies to field to make sure it still covers your full dataset.
• Too many rules on large datasets: Each conditional formatting rule requires Excel to evaluate every cell in the range. Applying dozens of rules to thousands of rows can slow down your workbook. Consolidate rules where possible and remove any that are no longer needed.

Build Your Excel Skills with Pryor Learning

Mastering Excel conditional formatting is one of the fastest ways to make your spreadsheets clearer and more actionable, but it is just one of many powerful features Excel has to offer. Pryor Learning offers live and on-demand Excel training that covers conditional formatting, formulas, pivot tables, data visualization and more. Whether you are just getting started or looking to sharpen advanced skills, structured training helps you work faster and with greater confidence.

Explore Pryor's Excel Training Courses