Excel Formula to Compare Two Pairs of Cells and Apply Colors
Question details
The user needs a formula to compare two sets of cells simultaneously, outputting 'Yes' or 'No', and wants to apply different cell colors based on that specific text output.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Evaluating multiple data points across rows to check if a specific threshold is met, and visually highlighting the outcome for easier data tracking.
- Observed behavior
- The user wants to return 'Yes' if L4 is greater than M4 OR if J4 is greater than K4, and otherwise return 'No', followed by automatically filling the cell with green or red depending on the result.
Ensure your data is organized in consistent columns without merged cells in the ranges you want to compare, as merged cells can prevent formulas and formatting rules from dragging down correctly.
Use IF and OR Functions Combined with Conditional Formatting
Use a nested logical formula to output the specific text conditions, then apply standard conditional formatting rules to colorize the 'Yes' and 'No' outputs automatically.
The OR function checks if at least one of the given conditions is true. Combining it with the IF function allows you to translate TRUE or FALSE into custom text outputs, which conditional formatting can easily target.
Select the target cell where you want the result (e.g., N4). Enter the formula: =IF(OR(L4>M4,J4>K4),"Yes","No"). Press Enter to see the result.
Keep the cell selected, navigate to the Home tab on the top ribbon, click on 'Conditional Formatting', then hover over 'Highlight Cells Rules' and select 'Text that Contains'.
In the dialog box, type 'Yes'. Click the format dropdown, choose 'Custom Format', and select a Green background color under the Fill tab. Click OK to apply the rule.
With the cell still selected, repeat the process: go to 'Conditional Formatting' > 'Highlight Cells Rules' > 'Text that Contains'. This time, type 'No' and choose a Red background fill. Click OK.
Click the small green square at the bottom-right corner of cell N4 (the fill handle) and drag it down the column to apply both the formula and your new formatting rules to the rest of your data.
Compare Data and Apply Colors Effortlessly in WPS Spreadsheet
WPS Spreadsheet offers robust logical formulas and highly customizable conditional formatting, allowing you to build dynamic, color-coded data trackers efficiently.
- 1. Open Data in WPS Spreadsheet: Launch WPS Office, open your spreadsheet file, and select the cell where you want the logical result to appear.
- 2. Input the Logical Formula: Type =IF(OR(L4>M4,J4>K4),"Yes","No") into the formula bar and press Enter to generate the text condition.
- 3. Apply Conditional Formatting: Navigate to Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, and easily assign green for 'Yes' and red for 'No'.
Frequently Asked Questions
Can I compare more than two pairs of cells with this formula?
Yes. You can add multiple logical tests inside the OR function separated by commas. For example, =IF(OR(L4>M4, J4>K4, P4>Q4), "Yes", "No") will evaluate three separate conditions and return Yes if any of them are true.
How do I apply the conditional formatting without showing the Yes/No text?
You can skip the IF formula entirely and apply formatting directly to the data cells using a formula rule. Go to Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =OR(L4>M4,J4>K4) and set the green fill. Create a second rule using =NOT(OR(L4>M4,J4>K4)) for the red fill.
Why does my conditional formatting stay the same color when I drag the formula down?
This usually happens if you accidentally used absolute references (like $L$4) in your conditional formatting rules. Check your formatting rules via 'Manage Rules' and ensure the formulas within the rule use relative references (like L4) so they automatically adjust for each new row.
