Fix Excel Conditional Formatting That Applies Only to One Column in WPS Office
Question details
The user needs to make an Excel conditional formatting rule evaluate column C but apply across multiple columns by using the correct fixed and relative reference in WPS Office Spreadsheets.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is creating or fixing a conditional formatting rule so it highlights formatting across columns B and beyond based on the value in column C.
- Observed behavior
- The rule currently applies only to one column or evaluates the wrong column because the column reference is not locked, so formatting does not cover the intended Applies to range.
Open the worksheet in WPS Spreadsheets and identify the cell range that should receive the conditional format. Note the first row in the Applies to range because the formula's relative row must match that first row.
Use the Formula =$C1="c" to Lock the Column and Apply Formatting Across Columns
Use this solution when a conditional formatting rule should evaluate the value in column C but highlight formatting across multiple columns such as B and C.
In a formula-based conditional formatting rule, a dollar sign before the column letter fixes the column reference, while leaving the row number relative lets WPS Spreadsheets evaluate each row individually. Always write the formula from the perspective of the first cell in the Applies to range.
In WPS Spreadsheets, open your workbook and select the cells where the rule should apply, for example B1:C100 or the full data range.
Click the Home tab, click Conditional Formatting, and choose New Rule.
In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
Type =$C1="c" in the formula box. The dollar sign fixes column C, while the row number stays relative to each row.
Set the Applies to range to =$B$1:$C$100 or your target range, making sure the row number in the formula matches the first row of that range, then click OK.
Create Column-Locked Conditional Formatting in WPS Office Spreadsheets
WPS Office Spreadsheets uses the same Excel-style conditional formatting formula syntax, so you can apply =$C1="c" to the correct Applies to range and make the rule highlight across columns instead of only one column.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and open the Excel workbook or create a new spreadsheet that contains the data you want to format.
- 2. Select your data range: Highlight the full range that the conditional formatting rule should cover, such as B1:C100.
- 3. Add a formula-based rule: Go to Home > Conditional Formatting > New Rule, choose Use a formula to determine which cells to format, and enter =$C1="c".
- 4. Apply and verify: Set the Applies to range to the selected cells and click OK; confirm that the formatting now highlights across columns instead of only one column.
- 5. Save in Excel-compatible format: Save the file as .xlsx to keep the conditional formatting rules intact when reopening in Microsoft Excel or WPS Office.
Frequently Asked Questions
Why does my conditional formatting rule apply only to one column?
Usually the formula or Applies to range uses a relative column reference. Use =$C1="c" so column C stays fixed while the row changes for each row in the selected range.
Can I use =$C1="c" in WPS Office with an Excel file?
Yes. WPS Spreadsheets supports Excel-style conditional formatting formulas and preserves the rule when you save as .xlsx.
How do I make the Applies to range cover columns B and C in WPS Spreadsheets?
Select the full target range before creating the rule, or edit the Applies to field in Conditional Formatting > Manage Rules and enter =$B$1:$C$100.
Does the row number in the formula have to match the first row of the Applies to range?
Yes. If the Applies to range starts at row 1, use $C1; if it starts at row 2, use $C2 so the relative row evaluation is correct.
