Apply Relative Conditional Formatting Formulas to Many Cells in Excel
Question details
Apply a formula-based conditional formatting rule to many cells without creating a separate rule for each cell.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to format a large range based on cell values using relative formula references.
- Observed behavior
- A single rule applies the formula across the selected range, adjusting relative references for each cell.
Identify the full range to format and its top-left cell. Write the formula as it should be evaluated for that first cell, and decide which row or column references need to stay fixed.
Create One Formula-Based Rule for the Entire Excel Range
Use a single conditional formatting rule to evaluate each cell in a large range.
In Excel, select the cells to format, such as K2:K1000.
Go to Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format.
Enter =K2=0, click Format, choose the desired formatting, and click OK. The reference K2 is relative to the first cell in the selected range.
Open Home > Conditional Formatting > Manage Rules and confirm that Applies to contains =$K$2:$K$1000. Adjust the range if needed, then click OK.

Apply a Relative Conditional Formatting Formula in WPS Spreadsheet
Use a formula-based conditional formatting rule in WPS Spreadsheet to format a large range while relative references adjust for each cell.
- 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
- 2. Select the cells: Select the range to format, such as K2:K1000.
- 3. Add a formula rule: Go to Home > Conditional Formatting > New Rule and choose the option to use a formula to determine which cells to format.
- 4. Set the condition and format: Enter =K2=0, choose a format, and confirm the rule. Check its applied range in Conditional Formatting > Manage Rules.

Frequently Asked Questions
Do I need a separate conditional formatting rule for every cell?
No. Select the full range before creating the rule. Excel evaluates the formula for each cell, adjusting relative references from the first cell in the range.
How do I highlight a whole row based on two columns?
Select the rows to format, such as A2:N1000, and create a formula rule using =AND($N2=0,$L2>14). The fixed column references keep the test on columns N and L for each row.
Where can I change the range covered by an existing rule?
In Excel, go to Home > Conditional Formatting > Manage Rules. Select the rule, edit its Applies to range, and click OK.
Why is my rule checking the wrong cells?
Check that the formula is written for the top-left cell of the Applies to range. Also check dollar signs: $K$2 fixes both the column and row, while K2 allows both to adjust.




