Fix an Excel Conditional Formatting Formula for Values Greater Than 1
Question details
Simplify a conditional formatting rule that checks whether each cell's value is greater than 1.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to format cells in a selected range when their values exceed 1.
- Observed behavior
- The rule uses a complex IF formula with INDEX, ADDRESS, and INDIRECT instead of a simpler relative reference.
Identify the range you want to format and its top-left cell. The formula must refer to the active cell when you create the rule; the examples below assume that cell is A1.
Use a relative cell reference in the Excel rule
Use this simpler formula to check each cell in the selected range without IF, INDEX, ADDRESS, or INDIRECT.
Select the cells to format, starting with the top-left cell. For example, select A1:A10 with A1 as the active cell.
Go to Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format.
Enter =A1>1, replacing A1 with the active cell in your selection. Leave out dollar signs so the reference adjusts for each cell.
Select Format, choose the desired fill or other formatting, and click OK to save the rule.

Use INDIRECT to refer to the current cell
Use an R1C1-style reference if you prefer a formula that explicitly refers to the cell being evaluated.
Highlight Values Greater Than 1 in WPS Spreadsheets
Use a formula-based conditional formatting rule in WPS Spreadsheets to highlight values greater than 1 with a relative cell reference.
- 1. Open the spreadsheet: Open WPS Spreadsheets and use File > Open to select your workbook.
- 2. Select the range: Select the cells to format, starting with the top-left cell; for example, select A1:A10 with A1 active.
- 3. Add the formula: Go to Home > Conditional Formatting > New Rule, choose the formula-based rule option, and enter =A1>1. Replace A1 with the active cell if needed.
- 4. Apply the format: Choose the desired formatting in the rule dialog, then confirm to apply it to the selected range.

Frequently Asked Questions
Why don't I need an IF function in the rule?
The expression =A1>1 already returns TRUE or FALSE. Conditional formatting applies the chosen format when the expression is TRUE.
What formula should I use if my range starts at C3?
Select the range starting at C3 and enter =C3>1 in the conditional formatting formula rule. Use the active cell as the reference and omit dollar signs.
How can I check which cells the rule covers?
In Excel, go to Home > Conditional Formatting > Manage Rules and review the rule's Applies to range.
Does the rule highlight the active cell?
No. The formula checks each cell in the applied range against the value 1; it does not identify the currently active cell.




