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 be required 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 with the necessary explanation.

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

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.

Let’s dive into the methods.

1. Using Simple Formula

We can use Conditional Formatting to highlight Blank cells in Excel. 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.

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.

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.

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.

Move the cursor over the Fill option and choose a color that you want to use in highlighting.

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

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

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.

To get such output, insert the following formula.

Here, B5, C5, D5, and E5 are the starting cells of the order date, item, price, and 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.

Immediately, you’ll get the following output.

Read More: How to Find If Cell is Blank in Excel

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.

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.

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

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.

After selecting the entire dataset, insert the following formula.

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

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

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.

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

Download Practice Workbook

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

• How to Find & Count If a Cell Is Not Blank
• How to Calculate in Excel If Cells are Not Blank
• How to Return Value If Cell is Blank
• If a Cell Is Blank then Copy Another Cell in Excel
• Excel If Two Cells Are Blank Then Return Value
• How to Check If Cell Is Empty in Excel
• How to Check If Cell Is Empty Using Excel VBA
• Excel VBA: Check If Multiple Cells Are Empty
• If Cell is Blank Then Show 0 in Excel

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