Why Excel Conditional Formatting Shows the Same Formula for Every Row
Question details
Understand why Excel shows the same conditional-formatting formula for multiple cells in an Applies To range.

- 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.
Identify the rule's Applies To range and its first cell. Check whether the formula uses dollar signs to lock columns or rows.
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.
Select a cell in the formatted range, then choose Home > Conditional Formatting > Manage Rules. If needed, select This Worksheet to see the rule.
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.
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.
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.

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. Open the spreadsheet: Open WPS Spreadsheet and use Open to select your workbook.
- 2. Select the range: Select cells Q3:Q300, starting with Q3.
- 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. 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.

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.




