Formatting Issues

How to Exclude Blanks and Currency in Excel Conditional Formatting

Steve K Sep 28, 2026 869 views

Question details

The user needs to apply date-based conditional formatting to a cell range without incorrectly highlighting blank cells, text strings, or currency entries.

How to Exclude Blanks and Currency in Excel Conditional Formatting

Product: Microsoft Excel
Device & OS: not provided
Scenario: Applying expiration or date-tracking conditional formatting rules to a specific column range (e.g., J15:J102).
Observed behavior: Standard date conditional formatting rules (like less than TODAY) evaluate blank cells as zero, incorrectly triggering the highlight. The user wants to restrict the formatting to only valid date numbers.

Before you start

Identify the exact range of cells you want to format (for example, J15:J102) and note the top-left cell reference, as you will need it for your formula's relative reference.

Solution 1Recommended

Use the ISNUMBER Formula for Custom Conditional Formatting

Applying a custom formula with the ISNUMBER function combined with an AND statement ensures that date formatting rules are only triggered for actual numerical date values, successfully ignoring blank cells and text strings.

When applying date conditions such as '<=TODAY()', the spreadsheet program often treats blank cells as zeros (which equate to January 0, 1900 in date serials) and incorrectly highlights them. Wrapping the logic in an AND statement alongside the ISNUMBER function prevents this issue.

It is crucial to use a relative reference for the top-left cell of your selected range so the formula dynamically adapts to the rest of the cells.

Select the target range

Highlight the entire cell range you want to format, such as J15:J102. Make sure you know which cell is the top-left cell (J15 in this case).

Create a new formatting rule

Go to the Home tab on the ribbon, click on 'Conditional Formatting', and select 'New Rule' from the dropdown menu.

Choose formula-based formatting

In the New Formatting Rule dialog box, choose the option that says 'Use a formula to determine which cells to format'.

Enter the ISNUMBER date formula

Type the specific date formula using your top-left cell reference. For example, to highlight dates today or earlier, enter: =AND(ISNUMBER(J15),J15<=TODAY())

Apply formatting and save

Click the 'Format' button, choose your desired fill color or text styling, click 'OK' to confirm the format, and then click 'OK' again to apply the rule.

Use the ISNUMBER Formula for Custom Conditional Formatting

Formulas for different date intervals: You can adapt the formula for other date intervals. For the next 7 days: =AND(ISNUMBER(J15),J15>=TODAY(),J15<=TODAY()+7). For days 15 to 30: =AND(ISNUMBER(J15),J15>TODAY()+14,J15<=TODAY()+30).

Advanced Spreadsheet Editor

Apply Conditional Formatting Easily with WPS Spreadsheet

WPS Office provides an intuitive interface for managing complex conditional formatting rules. You can use all standard functions like ISNUMBER and TODAY() with 100% compatibility in WPS Spreadsheet.

1. Select your data range: Open your spreadsheet in WPS Office and highlight the range you want to apply rules to (e.g., J15:J102).
2. Open Conditional Formatting: Navigate to the Home tab, click on Conditional Formatting, and select 'New Rule'.
3. Apply the custom formula: Select 'Use a formula...', input your AND(ISNUMBER()) logic, pick your highlight color, and save.

Fully compatible with Microsoft Excel conditional formatting and custom formulas.Easily organize and edit multiple highlighting rules with the built-in Rules Manager.Free to use, lightweight, and features a familiar tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my spreadsheet highlight blank cells when using less than TODAY() rules?

Blank cells evaluate to zero when compared against a number or date in a formula. Since the date serial number 0 is much smaller than today's date serial number, standard 'less than' rules will falsely trigger on blanks unless you specifically exclude them using functions like ISNUMBER.

Do I need to use absolute references like $J$15 in my conditional formatting rule?

No. When applying a rule to a multi-cell range like J15:J102, you should use a relative reference like J15 or a mixed reference like $J15 (if applying across multiple columns based on a single column's value). This allows the application to automatically adjust the formula for every row in the selected range.

Will the ISNUMBER function also exclude currency values?

It depends on how the currency is stored. If the currency is stored as a text string (e.g., typed manually with a symbol that the system doesn't parse as a number), ISNUMBER will successfully ignore it. If it is stored as a standard number with Currency formatting applied, ISNUMBER will view it as a number, but typically, monetary values do not overlap with the highly specific serial numbers used for current dates.

Document & File Issues