Formatting Issues

How to Create Formula-Based Conditional Formatting in Excel for Mac

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to set up conditional formatting in Excel for Mac to dynamically color cells based on specific percentage thresholds: red for below 0%, white/unfilled for 0% to 10%, and green for above 10%.

Product: Excel for Mac
Device & OS: Mac
Scenario: Highlighting data cells dynamically using custom percentage ranges.
Observed behavior: Cells need to display red when less than 0, green when greater than 0.1 (10%), and remain unfilled when between 0 and 0.1.

Before you start

Select the target range of cells you want to format and ensure they are formatted as percentages or numbers before applying the conditional rules.

Solution 1Recommended

Use Classic Conditional Formatting Rules

Instead of using predefined color scales, manually create classic rules to specify exact thresholds and colors for your data.

Predefined color scales automatically calculate gradients based on the highest and lowest values in your range. To enforce strict cutoffs (like exactly 0% and 10%), you must use classic conditional formatting rules.

Clear existing formatting

Select the data range you want to format. Go to the 'Home' tab and ensure the default cell background color is set to 'No Fill'.

Create the first rule for negative values

Go to 'Home' > 'Conditional Formatting' > 'New Rule'. Choose 'Classic' from the Style dropdown menu, and select 'Format only cells that contain'. Set the condition to 'Cell Value' 'less than' '0'. Choose a custom format with a red fill.

Create the second rule for values above 10%

Click 'New Rule' again. Select the 'Classic' style and 'Format only cells that contain'. Set the condition to 'Cell Value' 'greater than' '0.1'. Choose a custom format with a green fill.

Leave the middle range unfilled

Because the default background is 'No Fill', any values falling between 0 and 0.1 (0% to 10%) will automatically remain white or unfilled without needing an additional rule.

Understanding Percentages in Rules: In Excel formulas and conditional formatting conditions, percentages are evaluated as decimals. Therefore, 10% must be written as 0.1 in the rule settings.

Data Visualization Made Easy

Apply Conditional Formatting Easily in WPS Spreadsheet

WPS Spreadsheet provides an intuitive Conditional Formatting tool fully compatible with Excel rules, allowing you to highlight data ranges instantly based on custom percentage thresholds.

1. Select your data: Open your workbook in WPS Spreadsheet and highlight the data range you want to format.
2. Access Conditional Formatting: Navigate to the 'Home' tab and click on 'Conditional Formatting', then select 'New Rule'.
3. Set the negative value rule: Select 'Format only cells that contain'. Set the condition to 'Cell Value < 0' and apply a red background fill.
4. Set the positive value rule: Add another rule using 'Format only cells that contain'. Set the condition to 'Cell Value > 0.1' and apply a green background fill.

Fully compatible with Microsoft Excel formats (.xlsx)Free and lightweight office suiteIntuitive conditional formatting interfaceCross-platform support for Mac, Windows, and Linux

microsoft office alternative - wps office

Frequently Asked Questions

Why are my percentage conditional formatting rules not working?

Ensure you are using decimal values in your rules rather than whole numbers. For instance, 10% should be entered as 0.1, and 50% as 0.5. Also, verify that your cell format is correctly set to Percentage.

Can I use color scales instead of classic rules for this?

Color scales apply gradients across the entire range based on the relative high and low values in your dataset. For strict, fixed cutoffs (like exactly 0% and 10%), classic rules are more accurate and highly recommended.

How do I edit or delete an existing conditional formatting rule on Mac?

Go to 'Home' > 'Conditional Formatting' > 'Manage Rules'. From the rule manager dialog box, you can view all rules applied to your current selection or the entire worksheet, edit their conditions, or delete them entirely.

Document & File Issues