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.

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.

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.

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.

For getting such output, insert the following formula.

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.

Immediately, you’ll get the following output.

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.

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.

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

Similar Readings:

• If a Cell Is Blank then Copy Another Cell in Excel (3 Methods)
• How to Fill Blank Cells with 0 in Excel (3 Methods)
• Deal with Blank Cells That Are Not Really Blank in Excel (4 Ways)
• Excel VBA: Check If Multiple Cells Are Empty (9 Examples)
• How to Find and Replace Blank Cells in Excel (4 Methods)

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.

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.

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

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

• Excel VBA: Find the Next Empty Cell in Range (4 Examples)
• Formula to Return Blank Cell instead of Zero in Excel (With 5 Alternatives)
• VBA to Count Blank Cells in Range in Excel (3 Methods)
• How to Skip Blank Rows Using Formula in Excel (8 Methods)
• Autofill Blank Cells in Excel with Value Above (5 Easy Ways)