Chart & Visualization Issues

Find the Start and End of Conditional Formatting Ranges in Excel

Rana GarciaRana Garcia Nov 18, 2026 868 views

Question details

Identify the beginning and end of conditionally formatted blocks and find blank cells without manually scrolling through a large dataset.

Find the Start and End of Conditional Formatting Ranges in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains values that trigger conditional formatting and related times in another column.
Observed behavior
The user wants to locate highlighted block boundaries and return times associated with blank cells.
Before you start

Select a highlighted cell and open Home > Conditional Formatting > Manage Rules to check the rule and its applied range. The examples below assume numeric values below 10 in E2:E18 are highlighted, related times are in B2:B18, and E1 and E19 are blank boundary cells.

Solution 1Recommended

Mark the first and last rows of highlighted blocks

Use two helper columns to identify both boundaries, including blocks that contain only one row.

These formulas test the values behind the formatting rule rather than the displayed fill color. Adjust the references and condition if your rule uses a different column or threshold.

1
Mark block starts

Enter =IF(AND(ISNUMBER(E2),E2<10,NOT(AND(ISNUMBER(E1),E1<10))),"Start","") in F2, then fill it down through F18.

2
Mark block ends

Enter =IF(AND(ISNUMBER(E2),E2<10,NOT(AND(ISNUMBER(E3),E3<10))),"End","") in G2, then fill it down through G18.

3
Navigate to a marker

Select column F or G, press Ctrl+F, and search for Start or End. In Find options, set Look in to Values, then use Find Next or Find All to navigate to the matching rows.

Mark the first and last rows of highlighted blocks
Single-row blocks: A block containing one qualifying row receives both a Start marker in column F and an End marker in column G.
Work with spreadsheet boundaries

Find highlighted block boundaries in WPS Spreadsheets

Open your workbook in WPS Spreadsheets and use helper formulas to mark the first and last rows that meet your conditional formatting rule.

  1. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 2. Check the formatting rule: Select a highlighted cell and open Conditional Formatting > Manage Rules to confirm the condition and applied range.
  3. 3. Add boundary formulas: For values in E2:E18 that qualify when they are numeric and below 10, enter the Start formula in F2 and the End formula in G2 from the solution above, then fill both formulas down to row 18.
  4. 4. Find the markers: Select column F or G and use Find to search the displayed values for Start or End.
Mark the start and end of qualifying blocks with formulasOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet tools to search for boundary markersWork with a free, easy-to-use office suite
microsoft office alternative - wps office

Frequently Asked Questions

How do I find the rule behind a highlighted cell?

Select the cell in Excel, then open Home > Conditional Formatting > Manage Rules. Check the rule's condition and the Applies to range before adapting the helper formulas.

Do the formulas detect the cell's fill color?

No. They test the underlying values against the assumed rule. If the formatting rule changes, update the condition in both helper formulas to match it.

How can I find a block that contains only one highlighted row?

The row will have Start in column F and End in column G when you use both helper formulas. Search either column to locate it.

Why does the blank-cell FILTER formula include cells with formulas?

The condition E2:E18="" matches both empty cells and formulas that return an empty string. If you need only truly empty cells, use =FILTER(B2:B18,ISBLANK(E2:E18),"No blank cells") in a version of Excel that supports FILTER.