Formula Errors

Why Excel Conditional Formatting Shows the Same Formula for Every Row

Jason MillerJason Miller Nov 14, 2026 868 views

Question details

Understand why Excel shows the same conditional-formatting formula for multiple cells in an Applies To range.

Why Excel Conditional Formatting Shows the Same Formula for Every Row
Product
Microsoft Excel
Device & OS
not provided
Scenario
A conditional-formatting rule compares values in columns Q and V across multiple rows.
Observed behavior
The rule dialog may display the formula based on the first cell of the range, while Excel adjusts relative row references as it evaluates each row.
Before you start

Identify the rule's Applies To range and its first cell. Check whether the formula uses dollar signs to lock columns or rows.

Solution 1Recommended

Check the formula relative to the first cell in the Applies To range

Use this check to confirm that one displayed formula is evaluated correctly for each row.

Excel does not need to display a separate formula for each row. It evaluates relative references according to each cell's position in the Applies To range.

1
Open the rule manager

Select a cell in the formatted range, then choose Home > Conditional Formatting > Manage Rules. If needed, select This Worksheet to see the rule.

2
Check the range

Find the rule and inspect its Applies To box. For this example, the range is =$Q$3:$Q$300, so Q3 is the first cell.

3
Inspect the formula

Select the rule and choose Edit Rule. For the example range, a formula of =$Q3<$V3 locks columns Q and V but leaves the row numbers relative.

4
Verify the row behavior

Compare the values in Q3 and V3, then Q4 and V4. The rule evaluates Q3 against V3 for row 3 and Q4 against V4 for row 4, even if the dialog still displays =$Q3<$V3.

Check the formula relative to the first cell in the Applies To range
Absolute row references: A formula such as =$Q$3<$V$3 locks row 3 as well as the columns, so it does not adjust to compare each row's values.
Work with conditional formatting in WPS Office

Compare values across rows with WPS Spreadsheet

Use a formula-based conditional-formatting rule in WPS Spreadsheet to compare columns Q and V for each row.

  1. 1. Open the spreadsheet: Open WPS Spreadsheet and use Open to select your workbook.
  2. 2. Select the range: Select cells Q3:Q300, starting with Q3.
  3. 3. Create the rule: Choose Home > Conditional Formatting > New Rule, then select the option to use a formula to determine which cells to format.
  4. 4. Enter the formula and format: Enter =$Q3<$V3, choose a format, and confirm the rule. Check a few rows to see that the formatting follows each row's comparison.
Apply one conditional-formatting formula across multiple rowsUse relative row references and locked column referencesOpen and edit Microsoft Excel XLSX and XLS files
microsoft office alternative - wps office

Frequently Asked Questions

Why does the rule still show row 3 when I select Q4?

The Edit Formatting Rule dialog may show the formula relative to the first cell in the Applies To range. If the range starts at Q3 and the formula is =$Q3<$V3, Excel evaluates it as Q4<V4 for Q4.

What do the dollar signs in =$Q3<$V3 mean?

The dollar signs lock columns Q and V. Because there is no dollar sign before either row number, the row references adjust as the rule is evaluated down the range.

Where can I see the cells affected by a conditional-formatting rule?

In Excel, go to Home > Conditional Formatting > Manage Rules and inspect the rule's Applies To box. Choose This Worksheet if the rule is not listed for the current selection.

What happens if I use =$Q$3<$V$3 instead?

Both the columns and row 3 are locked, so the rule compares Q3 with V3 throughout the range rather than comparing values on each row.