Formatting Issues

How to Fix Excel Conditional Formatting for Values Ending in H

Muhammad TalhaMuhammad Talha Nov 2, 2026 869 views

Question details

Users need to fix conditional formatting rules that evaluate inconsistently or incorrectly when cell values contain numbers followed by the letter 'h' (e.g., 6.42h).

How to Fix Excel Conditional Formatting for Values Ending in H
Product
Excel
Device & OS
not provided
Scenario
Applying conditional formatting rules to highlight duration values or times formatted with a trailing 'h'.
Observed behavior
Excel treats entries like 6.42h and 10.83h as text rather than numbers, causing conditional formatting rules to either compare only the numeric portion improperly or fail to evaluate the cells entirely.
Before you start

Before adjusting your formatting rules, check whether your data was manually typed with the 'h' or exported from another system, as you will need to clean the text data before applying new rules.

Solution 1Recommended

Convert Text to Numbers and Apply a Custom Number Format

The best practice is to store the duration as a pure number and use a custom format to visually add the 'h' suffix. This ensures conditional formatting evaluates the values accurately.

When numbers are mixed with text characters in a cell, Excel treats the entire cell as text. This prevents standard math and conditional formatting from working correctly. By separating the visual display from the actual data, you solve the problem entirely.

1
Remove the existing 'h' character

Select the cells containing the text entries. Press Ctrl + H to open the Find and Replace dialog. Type 'h' in the 'Find what' box, leave the 'Replace with' box empty, and click 'Replace All'.

2
Open Format Cells dialog

With the cells still selected, right-click and choose 'Format Cells', or press Ctrl + 1.

3
Apply Custom Format

Navigate to the 'Number' tab, select 'Custom' from the Category list, and enter 0.00"h" or General"h" into the Type box. Click OK.

4
Set Conditional Formatting

Go to Home > Conditional Formatting, and apply your desired rules normally, using standard numeric formulas (e.g., Cell Value < 6).

Convert Text to Numbers and Apply a Custom Number Format
Data Integrity Maintained: Using custom formats keeps the underlying data numeric, allowing for accurate calculations, chart generation, and error-free conditional formatting.
Data Formatting in WPS Office

Handle Complex Formatting Easily with WPS Spreadsheet

WPS Office Spreadsheet fully supports custom number formatting and conditional rules, making it easy to manage duration values without running into calculation errors.

  1. 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the duration data.
  2. 2. Clean your data: Use Find and Replace (Ctrl+H) to remove the 'h' from your cells to turn them into pure numbers.
  3. 3. Apply Custom Format: Press Ctrl+1 to open Format Cells, select Custom, and type 0.00"h" to restore the visual suffix.
  4. 4. Set Conditional Formatting: Navigate to Home > Conditional Formatting and set your rules using straightforward numeric conditions.
100% compatible with Microsoft Excel file formats (.xlsx).Intuitive conditional formatting interface to evaluate complex data.Lightweight software that processes large datasets quickly without lag.Free to download and use for your daily spreadsheet and office needs.
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel treat numbers with letters as text?

Excel's default behavior is to classify any cell containing a non-numeric character (like letters or symbols) as a text string. This prevents standard mathematical operations and numeric comparisons in conditional formatting.

Can I sum values that end in 'h'?

If the values are stored as text (e.g., typed manually as '6.42h'), the SUM function will ignore them. You must either use a custom number format (so the underlying data is numeric) or use an array formula to extract and sum the numeric portions.

How do I format time duration in Excel instead of decimal hours?

Instead of decimals like '6.42h', you can enter times like '6:25' and format the cells using the custom format [h]:mm. This tells Excel to track the total elapsed hours rather than restricting the display to a 24-hour clock.

Does removing the 'h' change my original data?

Yes, removing the 'h' changes the actual cell content from a text string to a numeric value. However, applying a custom number format restores the 'h' visually without affecting the underlying number, which is the recommended approach for accurate data analysis.