How to Find and List Excel Cells Highlighted by Conditional Formatting
Question details
The user wants to find cells containing a specific text string within a large range, apply conditional formatting to them, and automatically extract and list those matching values onto a separate worksheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting specific text data from a large 2D dataset to a new destination sheet without manually copying highlighted cells.
- Observed behavior
- The goal is to dynamically list all cells from a large range that contain a specific word onto a separate worksheet, effectively creating an automated list of the conditionally highlighted values.
Ensure you know the exact source data range (e.g., Sheet 1!A2:Z50) and the specific target word you are trying to extract before setting up the formula.
Use LET, TOCOL, and FILTER Functions to Extract Matching Cells
This method dynamically searches the original data range for a specific word and lists the matching values on a new sheet without relying on cell colors. It requires modern Excel (Microsoft 365) that supports dynamic arrays.
Instead of trying to extract data based on conditional formatting colors—which standard Excel formulas cannot read—you can search the raw values directly. By combining the LET, TOCOL, FILTER, and SEARCH functions, you can flatten a large 2D range into a single column and filter out only the cells that contain your target text.
Open the worksheet where you want the list to appear and select the top cell where the extracted data should start.
Enter the following formula: =LET(v,TOCOL('Sheet 1'!A2:Z50,1),FILTER(v,ISNUMBER(SEARCH("white",v)),""))
Replace 'Sheet 1'!A2:Z50 with your actual worksheet name and data range. Replace the word "white" with the specific text string you are searching for.
Press Enter. The formula will automatically generate a dynamic spill range containing all matching values from your original dataset.
Extract and Manage Your Data Easily with WPS Spreadsheet
WPS Spreadsheet offers powerful data extraction features, including support for advanced dynamic array functions and conditional formatting. You can easily highlight, filter, and extract data across worksheets to streamline your workflow.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your large dataset.
- 2. Highlight target data: Go to the Home tab, click 'Conditional Formatting', select 'Highlight Cells Rules' > 'Text that Contains', and input your specific word.
- 3. Set up the destination sheet: Switch to a new worksheet and click on the cell where you want to output the data list.
- 4. Apply the extraction formula: Use text-filtering formulas to directly reference the original dataset and extract cells containing the target value.
- 5. Review your list: Press Enter to dynamically generate the list. The list will automatically update if the source data changes.
Frequently Asked Questions
Can I use a formula to filter data directly by cell color?
No, standard Excel formulas cannot read or filter based on background cell colors (including conditional formatting colors). You must filter based on the underlying value or rule that triggered the color, or use the 'Filter by Color' feature in the Data tab.
Why am I getting a #CALC! error with the FILTER function?
The #CALC! error typically occurs when the FILTER function finds no matching results. You can prevent this error by providing a default value in the third argument of the function, such as "No matches" (e.g., FILTER(array, include, "No matches")).
How do I easily highlight cells containing specific text?
Select your entire data range, navigate to Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Enter the specific word you are looking for, choose a highlight style from the dropdown, and click OK.
