Course
Conditional Formatting
Learn to use Excel conditional formatting to communicate key insights with clarity and impact. This accelerated video training guides you step-by-step.Read more
Search results
Looking for more? Try chatting with the Exceljet Chatbot.
Video
What is conditional formatting?
In this lesson we show you what conditional formatting is by applying a simple rule to a set of sales numbers.Read more
Video
Conditional formatting based on a different cell
In this video, we'll look at how to apply conditional formatting to one cell based on values in another, using a formula...Read more
Video
How to apply conditional formatting with a formula
In this video, we'll look at how to use a simple formula to apply conditional formatting. Formulas allow you to create more flexible and powerful conditional formats...Read more
Video
How to create a conditional formatting rule
In this video we show you how to create a basic conditional formatting rule.Read more
Article
Conditional formatting with formulas
Although Excel ships with many conditional formatting "presets", these are limited. A more powerful way to apply conditional formatting is with formulas, because formulas allow you to apply rules that use more sophisticated logic. This article shows 10 examples, including how to highlight rows,...Read more
Video
How to highlight rows with conditional formatting
In this video, we'll show you how to use a formula with conditional formatting to highlight an entire row in a multi-column list...Read more
Formula
Conditional formatting based on another cell
Excel contains many built-in "presets" for highlighting values with conditional formatting, including a preset to highlight cells greater than a specific value. However, by using your own formula, you have more flexibility and control.
In this example, a conditional formatting...Read more
Formula
Video
How to build a search box with conditional formatting
In this video, we'll look at a way to create a search box that highlights rows in a table, by using conditional formatting, and a formula that checks several columns at once...Read more
Formula
Conditional formatting date past due
In this example, we want to apply three different colors, depending on how much the original date varies from the current date:
- Green if the variance is less than 3 days
- Yellow if the variance is between 3 and 10 days
- Red if the variance is greater than 10 ...Read more
Formula
Conditional formatting column is blank
When conditional formatting is applied with a formula, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the active cell when the rule is created is assumed to be cell E5, with the range E5:E14 selected.
As the formula is...Read more
Formula
Conditional formatting based on another column
In this example, a conditional formatting rule highlights cells in the range D5:D14 when the value is greater than corresponding values in C5:C14. The formula used to create the rule is:
=$D5>$C5The rule is applied to the entire range D5:...Read more
Formula
Highlight approximate match lookup conditional formatting
This formula uses 4 named ranges, defined as follows:
width=K6
height=K7
widths=B6:B11
heights=C5:H5Conditional formatting is evaluated relative to every cell it is applied to, starting with the active cell in the selection, which is cell...Read more
Formula
Conditional formatting highlight target percentage
Conditional formatting rules are evaluated in order.
- For each cell in the range B5:B12, the first formula is evaluated. If the value is greater than or equal to 90%, the formula returns TRUE and the green fill is applied. If the value is not greater than or equal to 90%, the ...Read more
Article
Test conditional formatting with dummy formulas
If you've ever tried to apply conditional formatting with a formula, you know the hardest part is making sure the formula actually works. Here's an easy way to test the formula, before you use it in a rule.Read more
Video
Conditional formatting with two variable inputs
In this video, we'll look at how to extend a conditional formatting formula so that so that it checks more than just one condition...Read more
Video
How to create zebra stripes with conditional formatting
In this video, we'll look at how to use conditional formatting to shade every other row in a table. This is sometimes called "zebra striping"...Read more
Video
How to test conditional formatting with dummy formula
In this video, I'll show you how to quickly test your conditional formatting rules with dummy formulas...Read more
Video
How to use a formula with conditional formatting
In this video, we show you how to create a conditional formatting rule that uses a formula to highlight cells that meet specific conditions not included in Excel's presets...Read more
Video
How to test a conditional formatting formula
In this video, we'll look at an easy way to test a conditional formatting formula before you create the rule...Read more
Formula
Conditional formatting dates overlap
Consider for a moment how overlapping dates work. For a project to overlap the dates of other projects, two conditions must be true:
- The start date must be less than or equal (<=) to at least one other end date and the list.
- The end date for the project must be ...Read more
Video
Shade groups of rows with conditional formatting
In this video, we'll look at how to use conditional formatting to shade alternating groups of rows - groups of 3 rows, groups of 4 rows, and so on. ...Read more
Formula
Add business days to date
In this example, the goal is to calculate a workday n days in the future while excluding weekends and optionally holidays. For convenience, start (B5), days (B8), and holidays (B11:B13) are named ranges. The dates in columns...Read more
Pivot Table
Pivot table conditional formatting
To apply conditional formatting to a pivot table, create a new conditional formatting rule and pay particular attention to the "apply rule to" settings as described below. In the example shown, there are two rules applied. The green shows the top 5 values using a rule like this:
...Read more
Video
How to delete conditional formatting rules
In this video we show you how to delete a conditional formatting rule, and how to clear all rules at once.Read more
Video
How to check line length with conditional formatting
In this lesson, we'll show you how to use conditional formatting and a formula that uses the LEN function to highlight text that is too long...Read more
Video
Conditional formatting formula in a table
In this video we'll look at how to apply conditional formatting to a table with a formula...Read more
Video
Conditional formatting with one variable input
In this video, we look at how to make conditional formatting variable, so that you can change how formatting is applied without editing a rule. This elegant technique helps you build killer spreadsheets that are fun to use.
...Read more
Video
How to use Icon sets with conditional formatting
In this video, we show you how to create a conditional formatting rule that uses icons to visualize numbers.Read more