Conditional Formatting: Formulas for Cell Containing Specific Text with Percentage Threshold

Conditional Formatting Formulas: Cell Containing Specific Text and Percentage Threshold

Conditional formatting is a powerful tool in spreadsheet software that allows users to automatically apply formatting to cells based on their values. In this article, we will focus on a specific conditional formatting rule that combines cell containment of specific text with a percentage threshold.

Background

Conditional formatting rules can be created based on formulas. These formulas allow for more complex and specific conditions to be met before formatting is applied. In this article, we will focus on a conditional formatting rule that applies formatting to a cell if it contains specific text and its value is above or below a certain percentage threshold.

Example

Let's consider a simple example to illustrate this concept. Suppose we have a spreadsheet that tracks sales data for a company. In this spreadsheet, we have two columns: "Region" and "Sales Amount". We want to apply conditional formatting to the "Sales Amount" column to highlight any regions where the sales amount is less than 10% of the total sales amount for all regions.

Creating the Conditional Formatting Rule

To create this conditional formatting rule, we would follow these steps:

1. Select the cells in the "Sales Amount" column that you want to apply the formatting to.
2. Go to the "Home" tab in the ribbon and click on "Conditional Formatting" in the "Styles" group.
3. Select "New Rule" from the drop-down menu.
4. In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format" under "Select a Rule Type".
5. In the "Format values where this formula is true" box, enter the following formula:
   =$A2<>""

   This formula checks if the cell in the "Region" column (column A) is not empty. If it is not empty, then the formula returns TRUE, and the conditional formatting rule will be applied to the corresponding cell in the "Sales Amount" column.

6. Click on the "Format" button to specify the formatting that you want to apply to the cells that meet the condition. For example, you could change the fill color to red.
7. Click "OK" to apply the formatting.
8. Now, we need to add the percentage threshold to the rule. To do this, go back to the "Conditional Formatting" drop-down menu and select "Manage Rules". In the "Conditional Formatting Rules Manager" dialog box, select the rule that you just created and click on "Edit Rule".
9. In the "Edit Formatting Rule" dialog box, change the formula to the following:
   =AND($A2<>"" , $B2/SUM($B$2:$B$10)<0.1)

   This formula checks if the cell in the "Region" column (column A) is not empty and if the sales amount in the corresponding cell in the "Sales Amount" column (column B) is less than 10% of the total sales amount for all regions. If both conditions are met, then the formula returns TRUE, and the conditional formatting rule will be applied to the cell.

10. Click "OK" to apply the updated formatting rule.

In this article, we have covered a specific conditional formatting rule that combines cell containment of specific text with a percentage threshold. By using formulas in conditional formatting rules, we can create more complex and specific conditions to be met before formatting is applied. This can be useful in many different scenarios, such as highlighting data that falls outside of a certain range or identifying cells that contain specific text.

References

• Use formulas with conditional formatting
• Conditional Formatting Based on Another Cell
• Excel Formulas for Conditional Formatting