Office for Mac Issues

How to Find Conditional Formatting Rules by Value in Excel for Mac

John Wilson Oct 9, 2026 869 views

Question details

The user needs to locate the 'Format all cells based on their values' conditional formatting rule in Excel for Mac, which uses different naming conventions than the Windows version.

How to Find Conditional Formatting Rules by Value in Excel for Mac

Product: Microsoft Excel for Mac
Device & OS: macOS
Scenario: Applying specific conditional formatting to a dataset based on the numerical or text values contained within the cells.
Observed behavior: The specific rule named 'Format all cells based on their values' does not appear in the Excel for Mac conditional formatting menu.

Before you start

Ensure you have highlighted the specific range of cells you wish to format before opening the Conditional Formatting menu.

Solution 1Recommended

Use Style Options for Value-Based Formatting

Apply built-in style options like Color Scales or Data Bars, which inherently format cells based on their underlying values, or use the 'Classic' rule style for specific criteria.

Excel for Mac replaces the traditional Windows phrasing with visual style options. By selecting these styles, the software automatically scales the formatting based on the maximum, minimum, and relative values in your selected range.

Select your data range

Click and drag to highlight the cells that contain the values you want to format.

Open Conditional Formatting

Navigate to the 'Home' tab on the Excel ribbon and click the 'Conditional Formatting' button.

Apply a Value-Based Style

From the dropdown menu, choose a style option such as '2-Color Scale', '3-Color Scale', 'Data Bar', or 'Icon Sets'. These will automatically format the cells according to their numerical values.

Use Classic for specific rules

If you need a specific rule (e.g., Format only cells that contain), click 'New Rule' from the Conditional Formatting menu. In the dialog box, click the 'Style' dropdown menu and select 'Classic' to reveal the traditional formatting options.

Pro Tip: When using the 'Classic' style, you can customize the cell highlighting color, font style, and borders precisely as you would in Excel for Windows.

Easily Apply Conditional Formatting by Value with WPS Spreadsheet

Avoid UI confusion across different operating systems. WPS Office provides a consistent, intuitive interface on Mac, making it incredibly easy to find and apply conditional formatting rules based on cell values without relearning menu layouts.

1. Open your file in WPS Spreadsheet: Launch WPS Office on your Mac and open the spreadsheet containing your data.
2. Highlight your data: Select the specific cells you want to format based on their values.
3. Access Conditional Formatting: Go to the 'Home' tab and click the 'Conditional Formatting' icon.
4. Set your value rules: Choose 'Highlight Cells Rules' to format based on exact values, or select 'Color Scales' to automatically format based on relative cell values.

Identical user interface across Mac and Windows devicesSeamless compatibility with Microsoft Excel (.xlsx) file formatsFree and lightweight office suite with powerful data visualization toolsBuilt-in robust conditional formatting options

microsoft office alternative - wps office

Frequently Asked Questions

Why is 'Format all cells based on their values' missing in Excel for Mac?

Excel for Mac uses different terminology and menu structures for some conditional formatting rules compared to the Windows version. The exact functionality still exists but is accessed through built-in styles like Color Scales and Data Bars, or via the 'Classic' setting in the New Rule dialog.

How do I format cells that contain specific text in Excel for Mac?

Select your cells, click 'Conditional Formatting' on the Home tab, and choose 'New Rule'. Change the Style dropdown to 'Classic', then select 'Format only cells that contain' from the condition menu to set your specific text criteria.

Will conditional formatting rules created on Excel for Windows work on a Mac?

Yes, any conditional formatting rules created and saved in an Excel workbook on a Windows PC will display and function perfectly when the file is opened in Excel for Mac. The differences lie solely in how you create or edit them through the Mac interface.

Office App Problems