How to Apply Conditional Formatting with Formulas in Excel | Quiz: Excel Quiz

Conditional formatting is a powerful feature in Excel that allows you to change the appearance of a cell or a range of cells based on certain criteria. You can use conditional formatting to highlight, color, or format cells that meet a specific condition, such as values that are above or below a threshold, duplicates, errors, outliers, or dates.

One of the most flexible and versatile ways to apply conditional formatting is by using formulas. Formulas can give you more control and creativity over the conditions and the formatting rules. You can use any Excel function, operator, or reference in your formulas to create dynamic and customized conditional formatting.

In this article, we will show you how to apply conditional formatting with formulas in Excel, using some practical examples and tips.

How to Apply Conditional Formatting with Formulas

To apply conditional formatting with formulas, follow these steps:

1. Select the cell or range of cells that you want to format conditionally.
2. On the Home tab, in the Styles group, click Conditional Formatting.
3. Click New Rule to create a new formatting rule.
4. In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
5. In the Format values where this formula is true box, enter the formula that defines the condition. The formula should return a logical value of TRUE or FALSE, depending on whether the condition is met or not.
6. Click Format to choose the formatting style that you want to apply to the cells that meet the condition. You can choose from various options, such as font, fill, border, number, or icon sets.
7. Click OK to confirm the formatting rule.
8. Repeat steps 3 to 7 to create more formatting rules with formulas, if needed.
9. Click OK to close the Conditional Formatting Rules Manager dialog box.

Read More Articles

• Power Query M Formula Language: The Ultimate Guide 2025
• Power Query M Formula Language in Excel – Complete Guide 2025
• Power Query M Language Examples: Key Formulas & Usage 2025
• Why is the Power Query Used: Guide to Data Analytics 2025
• How to use Power Query with Excel – Complete Guide 2025

Examples of Conditional Formatting with Formulas

Here are some examples of how to use conditional formatting with formulas in Excel, along with the formulas and the formatting styles used.

Example 1: Highlight Cells Based on Another Cell Value

Suppose you have a table of sales data, and you want to highlight the cells in column B that are greater than the value in cell E1. You can use the following formula to create a conditional formatting rule:

=B2>$E$1

This formula compares the value in each cell in column B with the value in cell E1, and returns TRUE if it is greater, or FALSE otherwise. Note that we use an absolute reference ($E$1) for the cell E1, so that it does not change when the formula is applied to other cells.

Example 2: Highlight Rows Based on a Criteria

Suppose you have a table of employee data, and you want to highlight the entire rows that contain the word “Manager” in column C. You can use the following formula to create a conditional formatting rule:

=SEARCH("Manager",$C2)>0

This formula uses the SEARCH function to find the position of the word “Manager” in each cell in column C, and returns a positive number if it is found, or an error value if not. The formula then compares the result with zero, and returns TRUE if it is positive, or FALSE otherwise.

Example 3: Highlight the Top 10% Values

Suppose you have a table of test scores, and you want to highlight the top 10% values in column B. You can use the following formula to create a conditional formatting rule:

=B2>=PERCENTILE($B$2:$B$21,0.9)

This formula uses the PERCENTILE function to calculate the 90th percentile of the values in the range B2:B21, which is the threshold for the top 10% values. The formula then compares each value in column B with the threshold, and returns TRUE if it is greater than or equal to it, or FALSE otherwise.

Excel Tips for Conditional Formatting with Formulas

Here are some tips and best practices for using conditional formatting with formulas in Excel:

• Make sure that your formulas are valid and do not contain any errors. If your formula returns an error value, such as #N/A or #VALUE, the conditional formatting will not work properly.
  
• Use relative and absolute references appropriately in your formulas. Relative references (such as B2) will change when the formula is applied to other cells, while absolute references (such as $B$2) will remain constant. You can use the F4 key to toggle between different types of references in your formula.
  
• Use named ranges to make your formulas more readable and easier to manage. Named ranges are descriptive names that you can assign to cells or ranges in Excel. For example, you can name the range B2:B21 as Scores, and use it in your formula instead of the actual reference. To create a named range, select the cell or range, and type the name in the Name Box on the left of the formula bar.
  
• Use the Conditional Formatting Rules Manager to view, edit, delete, or manage your formatting rules. You can access it by clicking Conditional Formatting > Manage Rules on the Home tab. You can also use it to change the order, scope, or stop condition of your rules.
  
• Use the Format Painter to copy and paste your formatting rules to other cells or ranges. You can access it by clicking Format Painter on the Home tab, or by pressing Ctrl+C and Ctrl+V. You can also double-click the Format Painter button to apply the formatting to multiple selections.