# How to Apply a Conditional Formatting Formula to a Range

Yes, you can apply a conditional formatting formula to multiple cells or an entire range in Excel. This allows you to create flexible rules based on specific criteria you define with an Excel formula.

How to Apply a Conditional Formatting Formula to a Range

Applying a formula-based rule is a powerful way to highlight cells based on complex conditions, comparisons with other cells, or specific functions. Here's the step-by-step process:

1. Select Your Target Range: Start by highlighting the exact cells, the entire table, or the whole sheet where you want this conditional formatting formula to be active. This selected area is where Excel will apply the rule. This aligns with the initial instruction from the reference: "Select the range of cells, the table, or the whole sheet that you want to apply conditional formatting to."
2. Open Conditional Formatting Options: Go to the Home tab in the Excel ribbon. Following the reference: "On the Home tab, click Conditional Formatting."
3. Create a New Rule: Click on the Conditional Formatting button and then select New Rule... from the dropdown menu that appears. The reference also specifies: "Click New Rule."
4. Choose the Formula Rule Type: In the "New Formatting Rule" dialog box, you will see a list of rule types. Click on the option that says "Use a formula to determine which cells to format". (Note: The provided reference then mentions selecting a style like "3-Color Scale"; however, for applying a formula, you must explicitly choose this "Use a formula" rule type).
5. Enter Your Formatting Formula: In the text box labeled "Format values where this formula is true:", type your Excel formula.
   • Key Insight: The formula you write is evaluated relative to the top-left cell of your selected range. If your selected range starts at cell B2, the formula =C2>100 in the conditional format rule will check if C2 is greater than 100 for cell B2. When Excel applies the rule to cell B3, it will check =C3>100, and for cell C2, it will check =D2>100 (unless you use absolute references like $C2). Use relative (C2), absolute ($C$2), or mixed ($C2, C$2) references carefully to ensure the formula checks the correct cells for each position within your selected range.
6. Set the Desired Format: Click the Format... button. This opens the standard "Format Cells" dialog box where you can choose the font color, fill color, border style, etc., that should be applied when your formula evaluates to TRUE.
7. Confirm and Apply: Click OK in the "Format Cells" dialog and then click OK in the "New Formatting Rule" dialog.

Excel will now evaluate your formula for every cell within the range you initially selected. If the formula results in TRUE for a specific cell, the format you defined will be applied to that cell.

Practical Example: Highlighting Based on Another Cell

Suppose you have data in column A, and you want to highlight a cell in column A only if the corresponding cell in column B has a value greater than 50.

• Select Range: Highlight the cells in column A you want to format (e.g., A1:A10).
• New Rule: Go to Home > Conditional Formatting > New Rule.
• Select Formula Type: Choose "Use a formula to determine which cells to format".
• Enter Formula: Type the formula: =B1>50
  • Since our selected range starts at A1, the formula is evaluated relative to A1. The relative reference B1 means that for cell A1, it checks B1; for A2, it checks B2; and so on.
• Format: Click Format, choose a fill color (e.g., yellow).
• OK: Click OK twice.

Now, any cell in the range A1:A10 will be highlighted yellow if the value in the cell next to it in column B (e.g., B1 for A1, B2 for A2) is greater than 50.