Formatting Issues

Fix Excel Conditional Formatting Not Working with Currency Values

Maira Mehtab Sep 20, 2026 868 views

Question details

The user is unable to successfully apply conditional formatting rules to cells meant to display currency values, specifically failing to highlight amounts between a target range.

Product: Excel
Device & OS: not provided
Scenario: Setting up conditional formatting rules based on numerical ranges (like 'Between $5,000 and $10,000') on financial data.
Observed behavior: Conditional formatting fails to highlight the intended cells because manually typed dollar signs cause Excel to store the numbers as text strings, making mathematical evaluations impossible.

Before you start

Check your cells to see if the currency symbol was typed manually or applied via cell formatting. If a small green triangle appears in the top-left corner of the cell, the application is warning you that a number is currently stored as text.

Solution 1Recommended

Remove Manual Currency Symbols and Reapply Number Formatting

Convert text-based currency values back to numbers by removing manually typed dollar signs so conditional formatting can recognize the data.

When dollar signs, commas, or other symbols are manually typed into a cell alongside numbers, Excel treats the entire cell's contents as text. Conditional formatting rules that rely on numerical ranges (such as 'Between', 'Greater Than', or 'Less Than') cannot evaluate text strings.

Select the Data Range

Highlight all the cells containing the problematic currency values that are not responding to the formatting rule.

Open Find and Replace

Press the shortcut `Ctrl + H` on your keyboard to open the Find and Replace dialog box.

Remove the Dollar Symbols

In the 'Find what' field, type the dollar sign symbol (`$`). Leave the 'Replace with' field completely empty.

Execute the Replacement

Click 'Replace All'. This action instantly strips out the text characters, converting the remaining digits back into standard numerical values.

Apply Native Currency Formatting

With the numbers still selected, navigate to the 'Home' tab, click the 'Number Format' dropdown, and choose 'Currency' or 'Accounting'. This applies the symbol visually without changing the underlying numerical data.

Reapply Conditional Formatting

Try applying your conditional formatting rule again. The mathematical conditions will now successfully evaluate the true numbers.

Check for Embedded Commas: If the conditional formatting is still not working after removing the dollar signs, you may also need to repeat the Find and Replace process for manually typed commas (,) that might also be forcing the number into a text format.

Efficient Spreadsheet Data Management

Easily Format and Highlight Data with WPS Spreadsheet

WPS Spreadsheet provides powerful cell formatting and conditional formatting tools to easily manage large datasets, ensuring numeric data is correctly identified and visually highlighted exactly when you need it.

1. Open Your Document: Launch WPS Spreadsheet and open your financial dataset.
2. Format Cells Properly: Select your data range, press `Ctrl + 1` to open the 'Format Cells' dialog, and apply the 'Currency' format correctly.
3. Access Conditional Formatting: Navigate to the 'Home' tab on the top ribbon and click on 'Conditional Formatting'.
4. Set Range Rules: Select 'Highlight Cells Rules' > 'Between', and enter your desired numerical range (e.g., 5000 and 10000) to apply the visual style.

100% compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Built-in error checking to instantly spot numbers stored as textIntuitive conditional formatting dialogs for quick rule setupFree, lightweight, and fast performance for large datasets

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show a green triangle in my currency cells?

A green triangle in the top-left corner indicates a background error checking warning. In this context, it usually means a number is formatted or stored as text. Clicking the warning icon beside the cell allows you to easily select 'Convert to Number'.

Can I apply conditional formatting to text values instead?

Yes, you can use conditional formatting rules like 'Text that Contains' or 'Equal To' to highlight specific text strings. However, mathematical comparisons like 'Greater Than' or 'Between' strictly require the cell data to be recognized as numerical values.

Does the VALUE function help with numbers stored as text?

Yes, if you don't want to use Find and Replace, you can use the `=VALUE()` function in an adjacent blank column to convert text strings representing numbers back into actual numeric values. You can then copy and paste those results as values over the original data.

Document & File Issues