How to Apply Conditional Formatting in Excel If Another Cell Is Blank: 5 Methods

In the dataset below, the Sales Report for January is provided, and some cells are blank.

Dataset


Method 1 – Using a Simple Formula

Using Simple Formula

Steps:

  • Select the C5:C14 cell range.
  • Click on the New Rule option from the Conditional Formatting tool located in the Styles ribbon of the Home

Using Simple Formula

  • In the dialog box, New Formatting Rule, choose the option Use a formula to determine which cells to format as a Rule Type.
  • Insert the following formula in the Format values where this formula is true cell:

=B5=""

B5 is the first cell of the order date.

  • Click on the Format option from the lower-right corner of the dialog box.

Using Simple Formula

  • Move the cursor over the Fill option and choose a highlighting color.

Using Simple Formula

  • Press OK to get the blank highlighted cells to have adjacent cells.

Using Simple Formula

Conditional formatting highlights cells C5, C9, and C13 because cells B5, B9, and B13 are blank.


Method 2 – Using the OR Function If Another Cell Is Blank 

Highlight the specific cells from the F5:F14 cell range that have at least 1 blank cell of all fields using the OR function.

excel conditional formatting if another cell is blank Using the OR Function

Stages:

  • Insert the following formula:
    =OR(B5="",C5="", D5="", E5="")

    • B5, C5, D5, and E5 are the first cells of the date, item, price, and quantity fields, respectively.

The OR function shows correctly if at least one argument in any cell is blank. Conditional formatting highlights the cells that have at least 1 blank cell.

excel conditional formatting if another cell is blank Using the OR Function

  • You’ll get the following output.

excel conditional formatting if another cell is blank Using the OR Function


Method 3 – Utilizing the Combination of OR and ISBLANK Functions

Stages:

  • Insert the following formula:
    =OR(ISBLANK(B5),ISBLANK(C5),ISBLANK(D5),ISBLANK(E5))

    • The ISBLANK function returns true for a specific cell when the cell is blank. So, the function works as the same feature of double quotes (“”). The OR function returns true for all arguments.

excel conditional formatting if another cell is blank Utilizing the Combination of OR and ISBLANK Functions

  • Press OK to get the following highlighted cells.

excel conditional formatting if another cell is blank Utilizing the Combination of OR and ISBLANK Functions


Method 4 – Using the COUNTBLANK Function If Another Cell Is Blank

Using the COUNTBLANK Function

Stages:

  • Select the entire dataset.
  • Insert the following formula:

=COUNTBLANK($B5:$F5)

F5 is the sales in a particular order date, which is found by multiplying the price and quantity.

excel conditional formatting if another cell is blank Using the COUNTBLANK Function

  • You’ll get the following highlighted rows.

excel conditional formatting if another cell is blank Using the COUNTBLANK Function


Method 5 – Applying the COUNTIF Function

Stages:

  • Select the entire dataset.
  • Enter the following formula:

=COUNTIF($B5,"")

excel conditional formatting if another cell is blank Applying the COUNTIF Function

  • You’ll get the following output – the entire row is highlighted except the blank cell.

excel conditional formatting if another cell is blank Applying the COUNTIF Function


Download Practice Workbook


Related Articles


<< Go Back to If Cell is Blank Then | Excel Cells | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo