How to Use One Conditional Formatting Rule for Multiple Text Values in WPS Office Excel
Question details
The user wants to apply conditional formatting to a range of cells so that several different text values are highlighted with one single formula-based rule, instead of creating a separate rule for each value.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has a range, such as A1:EX1, and needs to highlight cells that contain any of several specified keywords, including SN1D, SN1N, SN3D, and SN3N.
- Observed behavior
- One conditional formatting rule should evaluate each cell independently and apply the same formatting whenever the cell matches any of the specified text values.
Select the target range before opening the Conditional Formatting rule manager, and note the active cell address so the formula uses the correct relative reference. Check that the keywords in the formula match the cell contents exactly, including any spaces or capitalization that matters in your data.
Create One OR-Based Conditional Formatting Rule for Multiple Keywords
Use this method when you need a single rule to highlight exact matches for several text values across a range, without maintaining multiple separate formatting rules.
The OR formula returns TRUE when the tested cell equals any of the listed keywords, so one rule can handle all the values at once. Use relative references, such as A1, so the rule automatically adjusts for every cell in the selected range.
Select the range you want to format, for example A1:EX1. Keep A1 as the active cell in the selection so the formula can start from the correct relative reference.
Go to the Home tab, click Conditional Formatting, and select New Rule.
In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
In the formula box, enter =OR(A1="SN1D",A1="SN1N",A1="SN3D",A1="SN3N").
Click Format, choose the fill color or other formatting you want, and click OK twice to confirm the rule.
Apply One Conditional Formatting Formula to Multiple Text Values in WPS Spreadsheets
WPS Office Spreadsheets can directly perform this task with a single formula-based conditional formatting rule. You do not need extra Excel plugins or separate rules for each keyword.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office and open the spreadsheet that contains the cells you want to format.
- 2. Select the range and note the active cell: Select the target range, such as A1:EX1, and keep the first cell active for the relative formula reference.
- 3. Create a formula-based rule: Click Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format.
- 4. Enter the formula and format: Type the OR formula with your keywords, click Format to choose a fill color, and click OK to apply the rule.
Frequently Asked Questions
Can one conditional formatting rule really match several different text values?
Yes. Use a formula-based rule with the OR function, such as =OR(A1="SN1D",A1="SN1N",A1="SN3D",A1="SN3N"), and apply it to the selected range.
Why does the formula use A1 instead of $A$1?
A relative reference like A1 lets WPS Office Excel adjust the formula for each cell in the selected range. If you use $A$1, the rule checks only that fixed cell instead of evaluating each cell independently.
What should I do if I need to match partial cell text instead of exact cell values?
For partial text matches, use a formula such as =OR(ISNUMBER(SEARCH("SN1D",A1)),ISNUMBER(SEARCH("SN1N",A1))). Open Home > Conditional Formatting > New Rule and enter this formula in the rule box.
How can I edit or remove this conditional formatting rule later?
Go to Home > Conditional Formatting > Manage Rules, select the rule from the list, and choose Edit Rule or Delete Rule to make changes.
