How to Use Conditional Formatting to Turn a Cell Green When Value is 0%
Question details
The user needs to apply multi-condition formatting to turn a cell green if its value is 0% and a corresponding cell in another column contains a specific text string.
- Product
- Spreadsheet Software
- Device & OS
- not provided
- Scenario
- Setting up row-based conditional formatting where the color of one cell depends on evaluating both its own value and the text value of another column in the same row.
- Observed behavior
- Requires establishing relative reference formulas in the conditional formatting rules manager to accurately check conditions row by row without breaking the formula logic.
Ensure you identify the exact range of cells you want to format and clear any conflicting conditional formatting rules previously applied to this range to prevent them from overriding your new rules.
Apply Conditional Formatting with AND Functions and Relative Row References
Set up formula-based conditional formatting rules using relative row references to correctly check multiple conditions per row across your dataset.
When applying conditional formatting based on another cell's value, it is crucial to lock the column using absolute references while leaving the row relative (e.g., $C2 instead of $C$2). The formula must only reference the first row of your target range, allowing the spreadsheet engine to automatically adjust it for subsequent rows.
Highlight the entire range of cells you want the formatting to apply to. For example, if your percentage data is in column L, select L2:L76.
Go to the 'Home' tab on the top ribbon, click on 'Conditional Formatting', and then select 'New Rule' from the drop-down menu.
In the dialog box, select the option 'Use a formula to determine which cells to format'.
Enter the formula =AND($C2="Labour",$L2=0%) in the formula bar. Click the 'Format' button, navigate to the 'Fill' tab, choose a Green color, and click 'OK'.
Repeat the process to create another New Rule. Use the formula =$C2="Labour". Click 'Format', choose a Red fill color, and click 'OK'.
Go to 'Conditional Formatting' and select 'Manage Rules'. Make sure the Green rule is positioned above the Red rule. Check the 'Stop If True' box next to the Green rule so it takes precedence.
Use WPS Spreadsheet for Advanced Conditional Formatting
WPS Spreadsheet provides a highly intuitive interface for managing complex conditional formatting rules, making data visualization quick, accurate, and completely seamless.
- 1. Open your dataset: Launch WPS Spreadsheet, open your file, and highlight the data column you want to format.
- 2. Access Formatting Tools: Navigate to the 'Home' tab, click 'Conditional Formatting', and select 'New Rule'.
- 3. Apply Formula: Select the formula option, input your multi-condition formula, set your green background format, and click OK to apply.
Frequently Asked Questions
Why is my conditional formatting applying to the wrong rows?
This usually happens when the cell reference in your formula doesn't match the first cell of your 'Applies to' range. Ensure that if your selected range starts at row 2, your formula references row 2 (e.g., $C2, not $C1).
How do I make one conditional formatting rule override another?
Open the Conditional Formatting Rules Manager. Rules at the top of the list have higher priority. You can use the Up and Down arrows to rearrange them, and check 'Stop If True' to prevent lower rules from applying if the top condition is met.
Can I use text strings in conditional formatting formulas?
Yes, but you must enclose the text strings in double quotation marks. For example, if you are checking for the word Labour, your formula must accurately include "Labour".
Why shouldn't I use a full range like C2:C76 in my formatting formula?
Conditional formatting automatically iterates through the 'Applies to' range cell by cell. If you specify an entire array in the formula for a single cell rule, it breaks the row-by-row evaluation logic, leading to errors.
