How to Apply Conditional Formatting to Excel Cells with Values and Percentages
Question details
The user needs to apply conditional formatting to a cell that displays both a numeric value and a percentage, but the concatenated formula output is treated as text.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Formatting cells that combine values and percentages using formulas.
- Observed behavior
- Conditional formatting fails because the concatenated text output cannot be reliably compared as a number by Excel's formatting rules.
Ensure you have the original reference cells containing the raw numeric data used for your calculations, as conditional formatting rules will need to reference these rather than the text output.
Use Underlying Numeric Calculations for Conditional Formatting
Apply conditional formatting rules using the original numeric formula rather than the concatenated text result.
When you combine a number and a percentage in a single cell using the ampersand (&) or CONCATENATE function, Excel converts the result into a text string. Since conditional formatting relies on numeric values for greater-than or less-than comparisons, it cannot evaluate this text. The solution is to base your formatting rule on the hidden numeric calculation.
Highlight the cells containing your combined value and percentage formula.
Go to the 'Home' tab, click on 'Conditional Formatting' in the Styles group, and select 'New Rule'.
In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format'.
Enter the underlying numeric calculation as your formula. For example, if your text formula is based on B4-F4, enter =B4-F4>0 to format positive results.
Click the 'Format' button to choose your desired fill color or font style, then click 'OK' twice to apply the rule.
Easily Manage Complex Formulas and Formatting with WPS Office
WPS Spreadsheet provides robust support for complex formulas, TEXT functions, and conditional formatting rules, making it easy to handle cells that combine values and percentages.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your combined formulas.
- 2. Access Conditional Formatting: Highlight the target cells and click 'Conditional Formatting' under the Home tab.
- 3. Create a new rule: Select 'New Rule' and use the 'Use a formula' option to input your numeric calculation.
- 4. Set style and apply: Set your formatting style and click OK to apply the rule seamlessly.
Frequently Asked Questions
Why does conditional formatting ignore my cell with a number and percentage?
When you combine a number and a percentage using a formula, the output becomes a text string. Conditional formatting rules that look for numeric values (like greater than zero) will ignore text strings.
How can I display accounting format and percentages together?
You can use the TEXT function to format both parts before combining them. For example, use =TEXT(A1, "_($* #,##0.00_)") & " " & TEXT(B1, "0.00%") to display an accounting value alongside a percentage.
Can I format just the percentage part of the text in a cell?
No, conditional formatting applies to the entire cell. If a cell contains a formula outputting text, you cannot apply different colors to different parts of that text string automatically.
