How to Highlight Cells Matching a Selected Word Using Conditional Formatting
Question details
The user wants to dynamically highlight a range of cells based on whether they match or contain a specific text value entered in a reference search cell.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Highlighting data dynamically to visually locate specific words or phrases in a large dataset without manually searching.
- Observed behavior
- Cells in the target range need to change format automatically when their content matches the search word defined in a designated input cell.
Identify the exact range of cells you want to highlight and designate a specific empty cell that will act as your dynamic search box.
Use Formula-Based Conditional Formatting
Apply a custom formula in the conditional formatting rules to highlight cells that either perfectly match or partially contain the target word.
By utilizing a formula-based rule, you can create a dynamic search feature. Changing the text in your designated search cell will instantly update the highlighted cells in your target range without needing to edit the formatting rules again.
Click and drag to select the range of cells you want to apply the highlighting to (for example, D8:G16).
Navigate to the Home tab on the top ribbon, click on 'Conditional Formatting', and select 'New Rule' from the dropdown menu.
In the New Formatting Rule dialog box, select the option that says 'Use a formula to determine which cells to format'.
If you want an exact match, enter the formula: =AND($D$21<>"",D8=$D$21). If you want to highlight cells that contain the word anywhere in the text, enter: =AND($D$21<>"",ISNUMBER(SEARCH($D$21,D8))).
Click the 'Format' button, go to the 'Fill' tab to choose a background color, and click 'OK' twice to apply the rule.
Easily Highlight Data with WPS Spreadsheet
WPS Spreadsheet provides powerful and intuitive conditional formatting tools to help you visualize your data instantly. You can easily apply custom formulas to highlight exactly what you need without complex setup.
- 1. Open your file: Launch WPS Office and open your spreadsheet document.
- 2. Select data and access formatting: Highlight your target data range, go to the Home tab, and click Conditional Formatting.
- 3. Create a new rule: Select 'New Rule', pick the formula option, and input your exact match or partial match formula.
- 4. Apply and test: Set your preferred highlight color, click OK, and type a word in your search cell to see instant results.
Frequently Asked Questions
Why isn't my conditional formatting formula working?
Ensure that your search cell reference is absolute (using dollar signs, e.g., $D$21) and your target range's first cell reference is relative (e.g., D8). Also, check for accidental trailing spaces in your search cell or your data.
Can I make the conditional formatting ignore case sensitivity?
Yes. The SEARCH function used in the partial match formula is already case-insensitive. If you specifically need a case-sensitive match instead, you should replace the SEARCH function with the FIND function.
How do I clear the conditional formatting rules if I no longer need them?
Select your highlighted range, go to the Home tab, click Conditional Formatting, choose Clear Rules, and then select 'Clear Rules from Selected Cells'.
