MS Excel - Microsoft Excel, Manage Rules

In Microsoft Excel, Manage Rules refers to the Conditional Formatting Rules Manager, which allows you to create, edit, delete, prioritize, and organize conditional formatting rules in your worksheet or workbook. Conditional formatting automatically changes the appearance of cells (like color, font, or icons) based on specific conditions.

1. What Is Conditional Formatting?

Conditional Formatting in Excel is used to highlight cells or format data based on specific rules or criteria, such as:

  • Highlighting cells greater than a certain number.

  • Coloring duplicate values.

  • Creating data bars, color scales, or icon sets.

The Manage Rules option helps you control these rules.

2. How to Access “Manage Rules”

  1. Go to the Home tab.

  2. In the Styles group, click Conditional Formatting.

  3. Select Manage Rules.

This opens the Conditional Formatting Rules Manager dialog box.

3. Options in the “Manage Rules” Window

When you open the Conditional Formatting Rules Manager, you’ll see several key options:

a) Show Formatting Rules For

  • Dropdown options:

    • Current Selection → Shows rules applied only to selected cells.

    • This Worksheet → Shows rules for the entire worksheet.

    • This Table/PivotTable → Shows rules applied to a table or PivotTable.

b) Rule List

  • Displays all existing rules in the selected range or sheet.

  • Each rule shows:

    • The formula or condition.

    • The format applied.

    • The range where the rule applies.

c) Buttons in the Rules Manager

Button Function
New Rule Create a new conditional formatting rule.
Edit Rule Modify an existing rule’s condition or format.
Delete Rule Remove the selected rule.
Duplicate Rule Make a copy of an existing rule to modify easily.
Move Up / Move Down Change the priority of rules (Excel applies rules from top to bottom).
Stop If True Stops applying other rules if the selected rule is true.

4. How to Create a New Rule

  1. Open Manage Rules.

  2. Click New Rule.

  3. Choose a rule type, such as:

    • Format cells based on their values.

    • Use a formula to determine which cells to format.

    • Apply data bars, icon sets, or color scales.

  4. Set your condition and choose a format.

  5. Click OK → The rule is added to the list.

5. Editing an Existing Rule

  1. Select the rule in the list.

  2. Click Edit Rule.

  3. Modify the formula, condition, or formatting.

  4. Click OK.

6. Deleting a Rule

  1. Select the rule from the list.

  2. Click Delete Rule.

  3. Confirm deletion.

7. Prioritizing Rules

  • If multiple rules apply to the same cells, Excel follows the rule order.

  • Use the Move Up and Move Down buttons to reorder them.

  • If you want Excel to stop checking other rules once a rule is applied, enable Stop If True.

8. Example

Scenario: You want to highlight:

  • Cells greater than 80 in green.

  • Cells less than 50 in red.

Steps:

  1. Select your data range.

  2. Go to Home → Conditional Formatting → Manage Rules.

  3. Create two rules:

    • Rule 1 → Formula: =A1>80 → Format: Green Fill.

    • Rule 2 → Formula: =A1<50 → Format: Red Fill.

  4. Click OK → Excel highlights accordingly.