How to Apply Conditional Formatting to Percentage Cells in Spreadsheet
Question details
The user needs a method to dynamically format a range of cells so that only the cells containing percentages are displayed with zero decimal places, without altering standard decimal numbers.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Managing a dataset with mixed numerical formats where percentage values need clean, integer-level presentation without manually separating them from standard decimals.
- Observed behavior
- Currently, applying a blanket number format changes all numbers. The user wants to selectively format only the percentage cells to zero decimal places while keeping the exact decimal precision for non-percentage values.
Ensure you have identified the exact range of cells containing your mixed numerical data and take note of the top-left cell reference of your selection, as it is required for the formula to work correctly.
Use a Formula in Conditional Formatting to Target Percentage Cells
This method utilizes the CELL function to automatically detect cells formatted as percentages and applies a custom zero-decimal format exclusively to those matching cells.
The CELL function can evaluate the formatting type of a specific cell and return a text code. By checking if the format code starts with 'P' (which stands for Percentage), we can establish a conditional formatting rule that triggers specifically for percentages. This allows you to apply unique visual rules without affecting standard decimal numbers in the same column or range.
Highlight the entire range of cells you wish to format. Ensure the top-left cell of your selection (for example, A1) is the active cell.
Navigate to the Home tab on the top ribbon, click on 'Conditional Formatting', and choose 'New Rule' from the dropdown menu.
Select the option 'Use a formula to determine which cells to format'. In the formula input box, type =LEFT(CELL("format",A1))="P", making sure to replace A1 with the actual top-left cell of your selected range.
Click the 'Format' button, switch to the 'Number' tab, and select 'Percentage'. Adjust the 'Decimal places' to 0, then click 'OK' twice to save and apply the rule.

Easily Apply Advanced Conditional Formatting in WPS Spreadsheet
WPS Spreadsheet offers powerful built-in conditional formatting tools and comprehensive formula capabilities to help you manage complex data logic effortlessly. Customize your data visualization with zero learning curve.
- 1. Open your document in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the mixed number formats.
- 2. Access Conditional Formatting: Highlight your data range, navigate to the Home tab, and select 'Conditional Formatting' followed by 'New Rule'.
- 3. Apply the Formula Rule: Choose the formula evaluation option, input your CELL function, and specify the zero-decimal percentage format to instantly clean up your dataset.

Frequently Asked Questions
Why isn't the conditional formatting working for all cells in my selection?
This usually happens if you used an absolute reference (like $A$1) instead of a relative reference (like A1) in your formula. Ensure the cell reference exactly matches the top-left cell of your selection and contains no dollar signs, allowing the rule to evaluate each cell individually.
Does the CELL function work differently for other number formats?
Yes, the CELL function with the 'format' argument returns different text codes depending on the specific number format applied to the cell. For example, it returns 'P' for percentages, 'C' for currency, and 'D' for dates. You can adjust the formula to target these alternative formats.
Will applying this conditional formatting rule alter the actual numerical values of my data?
No, conditional formatting strictly changes how the data is visually presented on the screen. The underlying values, including the full decimal precision of your percentages, remain completely intact for any further calculations or data processing.
Can I apply this conditional formatting formula to multiple separate ranges at once?
Yes. You can hold down the Ctrl key to select non-contiguous ranges. However, you must ensure that the cell reference used in your formula corresponds to the currently active cell (usually the first cell of the final range selected) for the rule to evaluate accurately across all areas.




