How to Use Excel Conditional Formatting for Stage and Gate Mismatches
Question details
The user needs to set up a conditional formatting rule to compare project stages with their corresponding gates and highlight mismatches, blank entries, or invalid gate values.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing a project tracker where specific stages (e.g., Stage 1) require corresponding gate values (e.g., Gate 0), and any discrepancies need visual highlighting.
- Observed behavior
- Basic formulas fail to highlight empty cells correctly, and manual checking is inefficient. A robust formula is needed to catch all mismatch scenarios, including blanks.
Ensure you have a clear mapping table of your expected Stage and Gate combinations (for example, in columns E and F) set up in your worksheet before creating the conditional formatting rule.
Apply a VLOOKUP and IFERROR Conditional Formatting Rule
Use a combination of IFERROR and VLOOKUP to accurately compare stages and gates against a mapping table, highlighting mismatches, invalid entries, and empty cells.
By utilizing VLOOKUP, you can cross-reference your current stage against the expected mapping. Wrapping this in an IFERROR function ensures that if a stage doesn't exist or a cell is blank, it triggers the conditional formatting instead of failing silently.
Highlight the range of cells in your Gate column that you want to apply the formatting to, for example, B1:B8.
Navigate to the Home tab on the ribbon, click on 'Conditional Formatting', and select 'New Rule'.
Choose 'Use a formula to determine which cells to format'. In the formula bar, enter: =IFERROR(VLOOKUP($A1,$E$2:$F$4,2,0)<>$B1,TRUE). Adjust the range $E$2:$F$4 to match where your actual mapping table is located.
Click the 'Format' button, choose a noticeable Fill color (like red or yellow) to highlight the mismatches, and click 'OK' to apply the rule.
Avoid String Extraction Formulas for Blank Cells
If you previously used a formula like VALUE(RIGHT(...)), here is why it fails on empty cells and how to adjust your approach.
Easily Apply Conditional Formatting in WPS Office
WPS Spreadsheet offers full support for advanced conditional formatting, VLOOKUP, and logical functions. You can manage your project stages and gates seamlessly in a highly compatible, free environment.
- 1. Open your project tracker: Launch WPS Spreadsheet and open your document containing the stages and gates.
- 2. Select the data: Highlight the cells you want to format (e.g., your Gate column).
- 3. Apply Conditional Formatting: Navigate to the Home tab, click on Conditional Formatting > New Rule, enter your VLOOKUP formula, set the desired highlight color, and click OK.

Frequently Asked Questions
Why doesn't my conditional formatting highlight empty cells?
If your conditional formatting formula results in an error when referencing a blank cell (for example, when using text extraction functions on an empty string), Excel simply ignores the cell. Wrapping your formula in an IFERROR function and setting the error result to TRUE forces the formatting to apply to those blank or invalid cells.
Will this conditional formatting rule work if my file is on SharePoint?
Yes, conditional formatting rules built with standard built-in functions like VLOOKUP and IFERROR are fully supported and will function normally when the workbook is stored, edited, or shared via SharePoint.
How do I adjust the formula if my mapping table is on a different worksheet?
You can easily prefix the range reference with the sheet name. For example, change $E$2:$F$4 to 'Mapping Sheet'!$E$2:$F$4 in your VLOOKUP formula within the Conditional Formatting rules manager.




