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

Perhaps you may have a larger dataset with numerous blank cells. Using a popular highlighting tool i.e. Conditional Formatting, you are easily able to highlight the blank cells. But you may require to highlight any specific cell or cell range or even the entire dataset if a particular cell is blank. In this article, we’ll show you 5 handy methods to highlight cells if another cell is blank in Excel using conditional formatting also with the necessary explanation.


Download Practice Workbook


5 Methods to Use Conditional Formatting in Excel If Another Cell Is Blank

Let’s introduce today’s dataset where the Sales Report of the January month is provided. But some cells are blank intentionally. Now, we need to utilize conditional formatting to highlight the cell if another cell is blank.

Dataset

Let’s dive into the methods.


1. Using Simple Formula

In the beginning method, you’ll see the way of using conditional formatting to highlight a column or cell range if some contiguous cells are blank. For example, you need to highlight the Column C or C5:C14 cell range as there exist some blank cells (some dates) in Column B.

Using Simple Formula

To highlight the cell range, firstly you need to select the C5:C14 cell range and click on the New Rule option from the Conditional Formatting tool which is located in the Styles ribbon of the Home tab.

Using Simple Formula

Immediately, you’ll see the following dialog box namely New Formatting Rule, and choose the option Use a formula to determine which cells to format as a Rule Type. Eventually, insert the following formula under the space of Format values where this formula is true.

=B5=""

Here, B5 is the starting cell of the order date.

However, 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 color that you want to use in highlighting.

Using Simple Formula

After pressing OK, you’ll get the highlighted cells having blank cells of the immediately adjacent cells.

Using Simple Formula

As you see, conditional formatting highlights the C5, C9, and C13 cells because the B5, B9, and B13 cells are blank respectively.

Read More: How to Delete Blank Cells in Excel and Shift Data Up


2. Conditional Formatting to Column Using the OR Function If Another Cell Is Blank 

Furthermore, if you want to apply conditional formatting to a column if some blank cells are available in the entire dataset. For instance, some blank cells exist in each field (3 blank cells in order date, 2 blank cells in the item, 1 blank cell in price and quantity fields). Now, we want to 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

For getting such output, insert the following formula.

=OR(B5="",C5="", D5="", E5="")

Here, B5, C5, D5, and E5 are the starting cell of the order date, item, price, quantity fields respectively.

However, you may want to explore how the formula works.

Simply we can say that the OR function returns true if at least any argument is true (if any cell is blank). Thus conditional formatting highlights the cells that have at least 1 cell are blank.

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

Immediately, you’ll get the following output.

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

Related Content: How to Remove Blanks from List Using Formula in Excel (4 Methods)


3. Utilizing the Combination of OR and ISBLANK Functions

Likewise, with the use of the OR function, you may get the same result with the combined application of the OR and ISBLANK functions.

To accomplish the task, just use the following formula.

=OR(ISBLANK(B5),ISBLANK(C5),ISBLANK(D5),ISBLANK(E5))

Here, 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 (“”). Later, the OR function returns true for all arguments.

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

You’ll get the following highlighted cells if you press OK.

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

Related Content: How to Set Cell to Blank in Formula in Excel (6 Ways) 


Similar Readings:


4. Conditional Formatting to Row Using the COUNTBLANK Function If Another Cell Is Blank

In addition, you may need to highlight the whole row if any cell is blank in the row. Luckily, you can do this using the COUNTBLANK function. Actually, the function counts the number of blank cells in a given cell range.

As we are dealing with the entire dataset, we have selected the whole dataset (B5:F14). However, if you want to highlight a specific row, you may select the specified row only.

Using the COUNTBLANK Function

After selecting the entire dataset, insert the following formula.

=COUNTBLANK($B5:$F5)

Here, F5 is the sales in a particular order date found by the multiplication of price and quantity.

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

Within a very short time, you’ll get the following highlighted rows.

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

Read More: Find, Count and Apply Formula If a Cell is Not Blank (With Examples)


5. Applying the COUNTIF Function 

The immediate previous method has a drawback as it also highlights the blank cell itself. But if you need to highlight the whole rows except the blank row, you may apply the COUNTIF function.

Just the following formula after selecting the entire dataset.

=COUNTIF($B5,"")

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

Instantly, you’ll get the following output where the entire row is highlighted except the blank cell only.

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

Read More: How to Remove Blank Cells Using Formula in Excel (7 Methods)


Conclusion

This is how you can use conditional formatting in Excel if another cell is blank. Right now, choose anyone based on your requirements. Anyway, if you have any queries or recommendations, please share them in the comments section below.


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo