How to Format Column B Based on Column A Values in WPS Spreadsheets
Question details
The user wants to apply conditional formatting so cells in column B are filled red when the corresponding value in column A is negative and green when it is positive.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Conditional formatting across two columns, with column B's fill driven by the value in column A.
- Observed behavior
- Column B cells should display red for negative column A values and green for positive column A values.
Open the workbook in WPS Spreadsheets and identify the exact range in column B to format. Check which row is the active cell in the selection so the formula reference uses the correct starting row.
Create Formula-Based Conditional Formatting Rules for Column B
Use this method when you need the cell fill in column B to change dynamically according to the sign of the matching value in column A.
This approach uses a fixed column reference and a relative row reference, so each cell in column B is compared with the cell in the same row of column A.
In WPS Spreadsheets, select the column B range to format, such as B1:B100, making sure B1 is the active cell. If your selection starts at B2, adjust the formula row reference to 2.
Go to the Home tab, click Conditional Formatting, and choose New Rule.
Select "Use a formula to determine which cells to format," enter =$A1<0, click Format, choose a red fill on the Fill tab, and confirm.
With the same range still selected, open Conditional Formatting > New Rule again, enter =$A1>0, choose a green fill, and confirm.
Apply Cross-Column Conditional Formatting in WPS Spreadsheets
Use WPS Spreadsheets to apply Excel-style formula rules that fill column B red or green based on negative or positive values in column A. The familiar conditional formatting workflow keeps your .xlsx workbook compatible and easy to share.
- 1. Open your workbook: Launch WPS Spreadsheets and open the Excel or WPS workbook containing columns A and B.
- 2. Select the column B range: Click and drag to select the cells in column B you want to format, ensuring the active cell is the first row in the selection.
- 3. Create the two formula rules: On the Home tab, go to Conditional Formatting > New Rule, use =$A1<0 with a red fill, then create a second rule with =$A1>0 and a green fill.
- 4. Confirm and save: Click OK to apply both rules, review the colors, and save the file as .xlsx to maintain Microsoft Excel compatibility.
Frequently Asked Questions
Why does my conditional formatting rule not color column B correctly?
Check that the formula uses a relative row reference such as =$A1 and that the active cell in the selected range is the same row as the formula reference. If the selection starts at B2, use =$A2 instead.
Can I apply this rule to the entire column B?
Yes. Select the entire column B or a large range such as B1:B1000, keep B1 as the active cell, and use =$A1 in both rules. Blank cells in column A normally remain unfilled.
How do I manage or remove these conditional formatting rules?
Go to Home > Conditional Formatting > Manage Rules, select the worksheet range, and edit or delete the red and green rules.
Will the formatting update if column A values change?
Yes. Formula-based conditional formatting rules recalculate automatically, so column B cell colors change when the corresponding column A value changes.
Can I use different colors or conditions?
Yes. In the rule dialog, choose a different fill color or adjust the formula, for example =$A1=0 to target zero values.
