Set Conditional Formatting by Column Number and Month in WPS Spreadsheets
Question details
Create conditional-formatting rules that color entire columns when their column header month number is below a lower threshold or greater than the current-month value.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The worksheet uses month or column-number values in row 5 and comparison values in fixed cells such as D1 and J1.
- Observed behavior
- Columns in the selected range should receive different fill colors according to the month value in each column header.
Confirm that the month or column-number headers are in row 5 and that the lower threshold and current-month values are stored in D1 and J1. Select a formatting range whose first column is B before creating the rules.
Create conditional-formatting rules using relative column references
Use this method when each column must be evaluated by its own value in row 5 while the threshold cells remain fixed.
The column reference in B$5 is relative, so it changes to C$5, D$5, and later columns across the applied range. The comparison cells $D$1 and $J$1 are absolute, so every column uses the same threshold values.
In WPS Spreadsheets, select the required data range beginning in column B, such as B6:M100. Include only the cells that should receive the fill color.
Go to Home > Conditional Formatting > New Rule, choose Use a formula to determine which cells to format, and enter =B$5>$J$1. Click Format, choose the fill color for columns after the current month, and confirm the rule.
With the same range selected, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =B$5<$D$1, choose a different fill color, and click OK.
Open Home > Conditional Formatting > Manage Rules and verify that both rules apply to the intended range starting with column B. Keep B$5 relative and keep $D$1 and $J$1 absolute.
Highlight Month-Based Columns Easily with WPS Spreadsheets
WPS Spreadsheets lets you create formula-based conditional-formatting rules to shade columns according to month headers, current-month values, and threshold cells. Its familiar spreadsheet interface makes it easy to apply, review, and adjust rules across your worksheet.
- 1. Open the worksheet in WPS Spreadsheets: Launch WPS Office and open the .xlsx or .xls workbook that contains your month headers and comparison cells.
- 2. Select the target data range: Highlight the cells below the month-header row that should change color, starting from the first formatted column.
- 3. Create a formula rule: Choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, then enter the required relative-column formula.
- 4. Set the fill color and apply the rule: Click Format, select a fill color, and confirm the Applies To range in Conditional Formatting > Manage Rules.
Frequently Asked Questions
Why does the formula use B$5 instead of $B$5?
B$5 locks the header row but leaves the column relative. When the rule applies across the range, WPS Spreadsheets checks C$5 for column C, D$5 for column D, and so on. Using $B$5 would make every column check only B5.
Why are D1 and J1 written with dollar signs?
$D$1 and $J$1 are absolute references. They keep every conditional-formatting calculation tied to the same lower-threshold and current-month cells as the rule is evaluated across columns.
How do I apply the rules to more rows?
Go to Home > Conditional Formatting > Manage Rules and edit the Applies To range. For example, change B6:M100 to B6:M500 while keeping the formula based on the header row, such as B$5.
What happens if both conditional-formatting rules are true?
If the threshold values overlap, both rules can evaluate as true. Open Home > Conditional Formatting > Manage Rules to reorder the rules, adjust the thresholds, or use a more specific formula so the fill colors do not conflict.
