Formatting Issues

How to Apply Conditional Formatting to Percentage Cells in Spreadsheet

Algirdas JasaitisAlgirdas Jasaitis Nov 11, 2026 868 views

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.

How to Apply Conditional Formatting to Percentage Cells
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the Target 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.

2
Open the Conditional Formatting Menu

Navigate to the Home tab on the top ribbon, click on 'Conditional Formatting', and choose 'New Rule' from the dropdown menu.

3
Enter the Evaluation Formula

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.

4
Define the Percentage Format

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.

Use a Formula in Conditional Formatting to Target Percentage Cells
Relative Cell Reference: Ensure you use a relative reference (e.g., A1) rather than an absolute reference (e.g., $A$1) in your formula. This allows the conditional formatting rule to correctly check each cell in your selected range individually.
Advanced Spreadsheet Formatting

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. 1. Open your document in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the mixed number formats.
  2. 2. Access Conditional Formatting: Highlight your data range, navigate to the Home tab, and select 'Conditional Formatting' followed by 'New Rule'.
  3. 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.
Full compatibility with Microsoft Excel formulas and conditional formatting rules (.xlsx).Intuitive and easy-to-navigate interface for managing complex datasets.Free to use, lightweight, and supports seamless migration of all your existing workbooks.
microsoft office alternative - wps office

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.