Formatting Issues

Highlight Column B Based on Column D in Excel

Tauseeq MagsiTauseeq Magsi Nov 18, 2026 869 views

Question details

Highlight cells in column B when the corresponding cell in column D contains text or a date and the cell in column B is not numeric.

Highlight Column B Based on Column D in Excel
Product
Microsoft Excel and WPS Spreadsheets
Device & OS
not provided
Scenario
Apply conditional formatting to column B using values from column D in the same row.
Observed behavior
Each qualifying cell in column B should be highlighted, while the header row is excluded.
Before you start

Check that row 1 is the header row and that the values to evaluate are in columns B and D. Dates stored as spreadsheet dates are numeric, so the rule also matches other numeric values in column D.

Solution 1Recommended

Create a formula-based conditional formatting rule in Excel

Use this rule to check each row in column D and highlight the corresponding nonnumeric cell in column B.

1
Select the target range

Enter B1:B1000 in the Name Box and press Enter, or select another range in column B that starts at B1. Make sure B1 is the active cell.

2
Create a rule

On the Home tab, select Conditional Formatting > New Rule > Use a formula to determine which cells to format.

3
Enter the formula

In the formula box, enter =AND(ROW($B1)>1,OR(ISTEXT($D1),ISNUMBER($D1)),NOT(ISNUMBER($B1))). The row references adjust for each cell in the selected range.

4
Choose a highlight color

Select Format, open the Fill tab, choose a color, and select OK. Select OK again to apply the rule.

Formula separators: If your regional settings use semicolons as list separators, replace the commas with semicolons: =AND(ROW($B1)>1;OR(ISTEXT($D1);ISNUMBER($D1));NOT(ISNUMBER($B1))).
WPS Spreadsheets

Apply the same row-based formatting in WPS Spreadsheets

Use a formula-based conditional formatting rule in WPS Spreadsheets to highlight cells in column B according to values in column D.

  1. 1. Open and select the range: Open the workbook in WPS Spreadsheets. Enter B1:B1000 in the Name Box and press Enter, with B1 as the active cell.
  2. 2. Start a conditional formatting rule: Go to Home > Conditional Formatting > New Rule, then choose the option to use a formula to determine which cells to format.
  3. 3. Enter the formula: In the formula field, enter =AND(ROW($B1)>1,OR(ISTEXT($D1),ISNUMBER($D1)),NOT(ISNUMBER($B1))).
  4. 4. Set the format: Select Format, choose a fill color, and confirm the dialogs to apply the rule to the selected range.
Highlight cells based on another cell in the same rowOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula also match numbers in column D?

Spreadsheet dates are stored as numbers, so ISNUMBER($D1) matches dates as well as other numeric values. The rule cannot distinguish dates from other numbers on that basis alone.

Will a blank cell in column B be highlighted?

Yes. A blank B cell is not numeric, so it will be highlighted if the corresponding D cell contains text or a number. To exclude blank B cells, add $B1<>"" as another condition inside AND.

How do I apply the rule to more rows?

In Excel, go to Home > Conditional Formatting > Manage Rules and extend the Applies to range, for example to =$B$1:$B$5000. Keep the formula referenced to row 1 when the range starts at B1.

Why is the header row not highlighted?

The condition ROW($B1)>1 excludes row 1. Remove that condition if row 1 contains data and should also be evaluated.

What if the formula is rejected?

Check your regional list separator. If formulas on your system use semicolons, replace each comma in the rule with a semicolon.