Find the Start and End of Conditional Formatting Ranges in Excel
Question details
Identify the beginning and end of conditionally formatted blocks and find blank cells without manually scrolling through a large dataset.

- 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.
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.
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.
Enter =IF(AND(ISNUMBER(E2),E2<10,NOT(AND(ISNUMBER(E1),E1<10))),"Start","") in F2, then fill it down through F18.
Enter =IF(AND(ISNUMBER(E2),E2<10,NOT(AND(ISNUMBER(E3),E3<10))),"End","") in G2, then fill it down through G18.
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.

Return times associated with blank cells
Use FILTER to list the times from column B whose corresponding cells in column E are blank.
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. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 2. Check the formatting rule: Select a highlighted cell and open Conditional Formatting > Manage Rules to confirm the condition and applied range.
- 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. Find the markers: Select column F or G and use Find to search the displayed values for Start or End.

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.




