Copy Excel Conditional Formatting Across Columns with Relative References
Question details
Copy conditional-formatting rules across columns so their cell references change automatically.

- 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.
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.
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.
In Excel, select cells C5:D5, then choose Home > Conditional Formatting > Manage Rules.
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.
Click New Rule again, choose the formula option, and enter =C5<C4. Click Format, choose a green format, and confirm.
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.

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. Open your workbook: Open WPS Spreadsheets and use File > Open to select your workbook.
- 2. Select the range: Select C5:D5, then open Home > Conditional Formatting and choose New Rule.
- 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. 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.

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.




