Formatting Issues

How to Highlight 19 Consecutive YES Values in Excel

Tauseeq Magsi Oct 1, 2026 868 views

Question details

The user wants to automatically format cells that are part of a continuous run of at least 19 "YES" values across multiple dates, and remove the formatting if any cell in the sequence changes to "NO".

How to Highlight 19 Consecutive YES Values in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking availability, attendance, or scheduling compliance over a long period where a specific threshold of consecutive days is required.
Observed behavior: The user needs a dynamic visual indicator that highlights entire blocks of 19 or more consecutive "YES" entries while ignoring shorter runs or "NO" values.

Before you start

Ensure your schedule data is arranged in a continuous range without merged cells, and identify the top-left starting cell of your data range to properly configure the formula relative references.

Solution 1Recommended

Use Conditional Formatting with a Custom COUNTIF Formula

Apply a formula-based conditional formatting rule to detect 19-cell windows containing only YES values.

To highlight cells that belong to a run of 19 consecutive 'YES' values, you need a formula that checks the surrounding window of cells. By using the COUNTIF function, Excel can verify if a specific 19-cell block meets the condition and dynamically update the fill color.

Select the target data range

Click and drag to highlight the entire block of cells containing your schedule dates and YES/NO values (e.g., A2:S100). Keep note of the active cell (usually the top-left cell, like A2).

Open the Conditional Formatting menu

Navigate to the 'Home' tab on the Excel ribbon, click on 'Conditional Formatting' in the Styles group, and select 'New Rule' from the dropdown menu.

Enter the custom formula

Choose 'Use a formula to determine which cells to format'. In the formula box, enter your COUNTIF logic. For a basic forward-checking 19-cell window starting at A2, input: =COUNTIF(A2:S2, "YES")=19. To highlight every cell in the run, you may need a more advanced array formula or helper rows that check if the current cell falls within any qualifying 19-cell block.

Set the highlight format

Click the 'Format' button, go to the 'Fill' tab, select a distinct color (like bright green), and click 'OK' to apply the formatting to your selected range.

Use Conditional Formatting with a Custom COUNTIF Formula

Dynamic Updating: Because this formatting relies on a formula, changing a 'YES' to a 'NO' in your schedule will instantly break the 19-cell chain and remove the highlight from the non-qualifying cells.

Advanced Data Tracking

Easily Apply Conditional Formatting with WPS Spreadsheet

WPS Office provides a highly compatible and intuitive Spreadsheet tool, allowing you to use advanced conditional formatting formulas just like Microsoft Excel, entirely for free. You can seamlessly track schedules and manage complex data rules without a paid subscription.

1. Open your schedule file in WPS Office: Launch WPS Spreadsheet and open your existing Excel schedule workbook.
2. Select the range and add a new rule: Highlight your YES/NO data range, go to the Home tab, click 'Conditional Formatting', and select 'New Rule'.
3. Apply the COUNTIF formula: Select 'Use a formula to determine which cells to format', enter your COUNTIF formula, set the desired fill color, and click OK to instantly highlight your consecutive values.

Completely free and lightweight office suiteFull format compatibility with Microsoft Excel (.xlsx and .xls)Familiar user interface for managing conditional formatting rulesCross-platform support for Windows, Mac, Linux, and mobile

microsoft office alternative - wps office

Frequently Asked Questions

Why is the conditional formatting applying to the wrong rows or cells?

This usually happens when absolute references (like $A$2) are used incorrectly. Ensure your formula uses relative row references (e.g., A2) so the rule can correctly evaluate each individual cell as it moves across your selected range.

Can I use this method to highlight consecutive NO values instead?

Yes. You simply need to replace the "YES" string in your COUNTIF formula with "NO" (for example, =COUNTIF(A2:S2, "NO")=19). This is useful for tracking consecutive absences or unavailable days.

How do I remove the conditional formatting if I make a mistake?

Highlight the affected cells, go to the Home tab, click 'Conditional Formatting', hover over 'Clear Rules', and select 'Clear Rules from Selected Cells'.

Will this formula slow down my spreadsheet?

Applying complex formula-based conditional formatting over a massive range (e.g., hundreds of thousands of cells) can cause slight performance delays. Try to restrict the 'Applies to' range strictly to the cells containing your schedule data.

Document & File Issues