Formula Errors

Fix an Excel Conditional Formatting Formula for Values Greater Than 1

Steven ParkerSteven Parker Nov 18, 2026 869 views

Question details

Simplify a conditional formatting rule that checks whether each cell's value is greater than 1.

Fix an Excel Conditional Formatting Formula for Values 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.
Before you start

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.

Solution 1Recommended

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.

1
Select the range

Select the cells to format, starting with the top-left cell. For example, select A1:A10 with A1 as the active cell.

2
Create a formula rule

Go to Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format.

3
Enter the condition

Enter =A1>1, replacing A1 with the active cell in your selection. Leave out dollar signs so the reference adjusts for each cell.

4
Choose the format

Select Format, choose the desired fill or other formatting, and click OK to save the rule.

Use a relative cell reference in the Excel rule
Check the applied range: To review the rule, go to Home > Conditional Formatting > Manage Rules and check that Applies to covers the intended cells.
WPS Spreadsheets

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. 1. Open the spreadsheet: Open WPS Spreadsheets and use File > Open to select your workbook.
  2. 2. Select the range: Select the cells to format, starting with the top-left cell; for example, select A1:A10 with A1 active.
  3. 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. 4. Apply the format: Choose the desired formatting in the rule dialog, then confirm to apply it to the selected range.
Create formula-based conditional formatting rules for selected cellsOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceWork with a free, lightweight office suite
QA img-9

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.