Formatting Issues

Highlight Blank Excel Cells When Column M Contains a Number

John WilsonJohn Wilson Nov 12, 2026 869 views

Question details

Create a conditional formatting rule that highlights a blank cell in column N when the corresponding row in column M contains a numeric value.

Highlight Blank Cells in Excel When Column M Contains a Number
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants to visually identify rows where column M has a number but the related cell in column N has not been filled in.
Observed behavior
Cells in column N should display a red fill only when the matching cell in column M is numeric and column N is blank.
Before you start

Select the first data cell in column N and confirm that the formula row number matches that first selected row. For example, use row 2 in the formula when the rule applies from N2 downward.

Solution 1Recommended

Create a conditional formatting rule for truly blank cells in column N

Use this rule when column N cells are empty and should be highlighted only when the matching column M cell contains a number.

The formula evaluates each row in the selected column N range. Relative references let Excel check M and N on the same row automatically.

1
Select the target cells in column N

Select N2:N1000, or select the actual range in column N that you want to monitor. Ensure that N2 is the active cell if your data begins on row 2.

2
Open the formula-based rule

Go to Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format.

3
Enter the conditional formatting formula

In the formula box, enter =AND(ISNUMBER(M2),ISBLANK(N2)). Keep the column letters and row number relative so the rule evaluates each row correctly.

4
Set the red highlight format

Select Format, open the Fill tab, choose a red fill color, and select OK. Choose OK again to save the conditional formatting rule.

Create a conditional formatting rule for truly blank cells in column N
Rule result: A cell in column N is highlighted when its matching column M cell contains a number and the column N cell is genuinely empty.
WPS Spreadsheets

Highlight Missing Spreadsheet Entries with WPS Spreadsheets

WPS Spreadsheets lets you create the same formula-based conditional formatting rule to flag blank cells in column N whenever the matching cell in column M contains a number.

  1. 1. Select the column N range: In WPS Spreadsheets, select N2:N1000 or your required target range in column N.
  2. 2. Create a new conditional formatting rule: Go to Home > Conditional Formatting > New Rule and choose the option to use a formula for the rule.
  3. 3. Enter the row-based condition: Type =AND(ISNUMBER(M2),ISBLANK(N2)) and verify that the row number matches the first row of your selected range.
  4. 4. Apply a red fill: Choose Format, set a red Fill color, and confirm the rule to highlight incomplete rows.
Create formula-based conditional formatting rules for missing valuesUse =AND(ISNUMBER(M2),ISBLANK(N2)) to highlight blank cells by rowOpen and edit Microsoft Excel .xlsx and .xls files with compatible formattingUse a familiar spreadsheet ribbon and lightweight desktop experience
microsoft office alternative - wps office

Frequently Asked Questions

Why is my blank cell in column N not being highlighted?

Check that the conditional formatting Applies to range includes the cell and that the formula row matches the first row of that range. For a range beginning at N2, use M2 and N2 in the formula.

How do I highlight cells that contain formulas returning blank text?

Use =AND(ISNUMBER(M2),N2="") instead of ISBLANK(N2). This treats a formula result of an empty string as blank.

Can I apply this conditional formatting rule to an entire column?

Yes. Select a practical range such as N2:N1048576 or your table column, then create the rule using =AND(ISNUMBER(M2),ISBLANK(N2)). Avoid including the header row unless the formula is adjusted for it.

Will text values in column M trigger the red fill?

No. ISNUMBER(M2) returns TRUE only for numeric values, so text, empty cells, and most nonnumeric entries in column M will not trigger the rule.