Formatting Issues

How to Apply Conditional Formatting to Percentage or Currency Columns in Excel

Huma Ashraf Ch Sep 27, 2026 869 views

Question details

The user needs to correctly configure conditional formatting rules for columns that contain percentage or currency values without the conditions failing.

How to Apply Conditional Formatting to Percentage or Currency Columns

Product: Microsoft Excel and SharePoint
Device & OS: not provided
Scenario: Setting up rules to highlight cells based on numeric conditions in data formatted with currency symbols or percentage signs.
Observed behavior: Rules often fail because the system evaluates the underlying stored numerical values (e.g., 0.02 for 2%) rather than the formatted text displayed on the screen.

Before you start

Verify that your cells are formatted as actual Numbers, Percentages, or Currencies rather than Text, as conditional formatting relies on true numeric values to function properly.

Solution 1Recommended

Use Decimal Values for Percentage Rules in Excel

Because Excel stores percentages as decimals, you must use the decimal equivalent when setting up your conditional formatting rules.

A common mistake when formatting percentages is typing a whole number into the rule criteria. For instance, Excel interprets 2% as 0.02. If you enter '2' in your condition, Excel will look for 200%, causing the highlight rule to fail.

Select the Data Range

Highlight the cells or columns containing your percentage data.

Open Conditional Formatting

Navigate to the Home tab, click on 'Conditional Formatting', and select 'New Rule'.

Enter the Decimal Condition

Choose 'Format only cells that contain'. In the rule criteria, input the decimal equivalent of your percentage. For example, to highlight values greater than 2%, type 0.02 or exactly 2%.

Apply and Save

Click 'Format' to choose your highlight color, click 'OK', and then 'OK' again to apply the rule.

Use Decimal Values for Percentage Rules in Excel

Currency Values: For currencies like Pounds (£) or Dollars ($), the underlying value is the raw number. To highlight a value greater than £2, simply use the number 2 in your condition.

Solution 2

Apply JSON Column Formatting in SharePoint

Use a custom JSON schema to apply conditional colors to SharePoint columns based on their underlying numeric values.

Powerful Spreadsheet Formatting

Easily Manage Conditional Formatting with WPS Spreadsheet

WPS Office provides an intuitive and seamless interface for applying complex conditional rules to your percentage and currency data. It handles underlying decimal values exactly like Excel, ensuring accurate highlights without a steep learning curve.

1. Open Your Workbook: Launch WPS Office and open your spreadsheet containing the percentage or currency data.
2. Highlight Your Data: Select the specific cells, rows, or columns you want to conditionally format.
3. Set the Formatting Rule: Go to the Home tab, click 'Conditional Formatting', and choose 'Highlight Cells Rules'.
4. Input Criteria and Apply: Enter your numeric or decimal threshold (e.g., 0.05 for 5%), pick a visual style, and hit OK.

100% compatibility with Microsoft Excel conditional formatting rules and .xlsx files.User-friendly manager for highlighting percentages, currencies, and data bars.Completely free, lightweight, and fast performance for large datasets.Familiar user interface ensuring seamless migration from other office suites.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting rule for percentages not working?

This happens because spreadsheets store percentages as decimal values. If you want a rule for values greater than 50%, entering '50' looks for 5000%. You must enter '0.5' or '50%' directly in the formatting rule box.

Does conditional formatting change the underlying currency value?

No. Conditional formatting only changes the cell's visual appearance (such as font color or background fill) based on the criteria. The actual numerical currency value remains unchanged and can still be used in calculations.

How do I format negative currency values in SharePoint automatically?

You can use JSON column formatting. By selecting 'Format this column' and entering a JSON script with an evaluation like "=if(@currentField < 0, '#FF0000', '')", SharePoint will automatically apply a red background to any negative currency input.

Document & File Issues