Formatting Issues

Copy Excel Conditional Formatting Across Columns with Relative References

Tauseeq MagsiTauseeq Magsi Nov 18, 2026 870 views

Question details

Copy conditional-formatting rules across columns so their cell references change automatically.

Copy Excel Conditional Formatting Across Columns with Relative References
Product
Microsoft Excel
Device & OS
not provided
Scenario
Apply red or green formatting to cells in row 5 based on the value directly above each cell.
Observed behavior
Each column should evaluate its own cells rather than always referring to column C.
Before you start

Identify the top-left cell of the range you want to format and the cells each rule should compare. In this example, the range is C5:D5, and each cell is compared with the cell directly above it.

Solution 1Recommended

Set Relative Conditional-Formatting Formulas for the Entire Range

Use this approach to apply the same comparison to multiple columns without creating a separate rule for each column.

Excel evaluates each formula relative to the top-left cell of the Applies To range. The dollar signs in the range address keep the target range fixed; they do not prevent relative references inside the formulas from changing.

1
Select the target range

In Excel, select cells C5:D5, then choose Home > Conditional Formatting > Manage Rules.

2
Create the red rule

Click New Rule, choose Use a formula to determine which cells to format, and enter =C5>C4. Click Format, choose a red format, and confirm.

3
Create the green rule

Click New Rule again, choose the formula option, and enter =C5<C4. Click Format, choose a green format, and confirm.

4
Check the applied range

In the Rules Manager, set Applies To for both rules to =$C$5:$D$5, then click Apply or OK. Excel evaluates the rules in column D as =D5>D4 and =D5<D4.

Set Relative Conditional-Formatting Formulas for the Entire Range
Keep rows fixed when needed: Use mixed references such as C$5 and C$4 if the column should change but the row numbers must stay fixed. If the values are equal, neither example rule applies.
Work with conditional formatting in WPS

Apply Column-by-Column Conditional Formatting in WPS Spreadsheets

Use formula-based conditional formatting in WPS Spreadsheets to compare each cell with the value above it across a selected range.

  1. 1. Open your workbook: Open WPS Spreadsheets and use File > Open to select your workbook.
  2. 2. Select the range: Select C5:D5, then open Home > Conditional Formatting and choose New Rule.
  3. 3. Add the comparison rules: Choose the formula-based rule option. Enter =C5>C4 and select a red format; then create another rule with =C5<C4 and a green format.
  4. 4. Verify the range: Open Conditional Formatting > Manage Rules and check that both rules apply to C5:D5. Confirm the rules and test values in columns C and D.
Apply relative-reference rules across multiple columnsOpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet formatting controlsWork with a free, easy-to-use office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does my rule keep referring to column C?

The formula may use an absolute column reference such as $C$5. In Home > Conditional Formatting > Manage Rules, edit the formula to use C5 or C$5 so the column can change.

What is the difference between C5 and C$5?

C5 allows both the column and row to change relative to the formatted cell. C$5 lets the column change while keeping row 5 fixed.

Do I need separate rules for every column?

No. Set the Applies To range to include the columns you need and write the formula relative to the range's top-left cell. For C5:D5, =C5>C4 also evaluates as =D5>D4 in column D.

What happens when the two values are equal?

Neither =C5>C4 nor =C5<C4 is true when the values are equal. If you want a format for equality, add a separate rule using =C5=C4.