Conditional Formatting If Cell is Not Blank

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will discuss using Conditional Formatting in a cell if it is not blank. Often, when you work on spreadsheets, there might be cells that do not contain any data. In such cases, you may want to ignore blank cells by highlighting cells that are not blank. Luckily, Conditional Formatting in Excel has several ways to highlight cells with content.

Table of Contents hide

Conditional Formatting If Cell is Not Blank: 4 Easy Methods

1. Use Conditional Formatting ‘Highlight Cell Rules’ Option If Cell is Not Blank

2. Apply Simple Arithmetic Formula to Conditional Formatting If Cell is Not Blank

3. Appy LEN Function in Conditional Formatting for Non-Blank Cell

4. Excel NOT and ISBLANK Functions in Conditional Formatting If Cell is Not Blank

Conclusion

1. Use Conditional Formatting ‘Highlight Cell Rules’ Option If Cell is Not Blank

Using the Highlight Cell Rules option, you can directly highlight non-blank cells. Suppose, we have a dataset containing students’ names and test scores. However, several names and test scores are missing in this dataset. Now, if we want to highlight the cells that contain data, the following are the steps.

Steps:

First, select the entire dataset (B5:D13).

Next, go to Home > Conditional Formatting (Styles group).

Now, from the Conditional Formatting drop-down, go to Highlight Cell Rules > More Rules.

Then, the ‘New Formatting Rule’ window will show up. Here, ‘The Format only cells that contain’ option is selected by default. After that, choose the ‘No Blanks’ option from the ‘Format only cells with’ drop-down.

Click on the Format.

Next, choose the highlight color from the Fill tab.

Click OK > OK to close the dialog boxes.
Finally, you will see all the cells containing data have been highlighted.

2. Apply Simple Arithmetic Formula to Conditional Formatting If Cell is Not Blank

In Conditional Formatting, you can use functions and simple arithmetic formulas. Now, we will use a simple arithmetic formula to highlight non-blank cells. So, here are the steps involved in this method.

Steps:

Select the entire dataset (B5:D13) at first.
Next, go to Home > Conditional Formatting > New Rule.

Then the New Formatting Rule window will show up. After that, choose the Rule Type: Use a formula to determine which cells to format. Type the below formula in the field: Format values where this formula is true. Later, click on the Format button and choose the highlight color from the Fill.

=B5<>""

Click OK > OK to close the dialog boxes.
Finally, you will see all the cells containing data have been highlighted.

3. Appy LEN Function in Conditional Formatting for Non-Blank Cell

In this method, we will use the LEN function to highlight non-blank cells in Excel.

Steps:

First, select the entire dataset (B5:D13).
Next, go to Home > Conditional Formatting > New Rule.
Then the New Formatting Rule window will show up. After that, choose the Rule Type: Use a formula to determine which cells to format. Now, type the below formula in the field: Format values where this formula is true. Later, click on the Format button and choose the highlight color from the Fill.

=LEN(B5)>0

Here, the LEN function returns the number of characters in a string. So, when the function sees that the number of characters in a particular cell is greater than zero, it replies that the cell is not blank. And, later Conditional Formatting highlights the non-blank cells depending on the result of the LEN formula.

Click OK > OK to close the dialog boxes.
Finally, the following is our output.

Related Content: Excel Conditional Formatting Formula

4. Excel NOT and ISBLANK Functions in Conditional Formatting If Cell is Not Blank

Likewise Method 3, this time we will use a combination of functions instead of using a single one. For instance, we will use the NOT function along with the ISBLANK function.

Steps:

First, select the entire dataset (B5:D13).
Next, go to Home > Conditional Formatting > New Rule.
Then the New Formatting Rule window will show up. After that, choose the Rule Type: Use a formula to determine which cells to format. Type the below formula in the field: Format values where this formula is true. Later, click on the Format button and choose the highlight color from the Fill.

=NOT(ISBLANK(B5))

Here, the ISBLANK function checks whether Cell B5 is blank or not and returns FALSE, as B5 contains data. Next, the NOT function reverts the result of the ISBLANK formula; which means FALSE is converted to TRUE. Lastly, combinedly, the formula checks for the non-blankness of the cell. Later, Conditional Formatting highlights the cells depending on the result of the formula.

Click OK > OK to close the dialog boxes.
Finally, you will see all the non-blank cells are highlighted.

Related Content: How to Use Conditional Formatting in Excel

Download the Practice Workbook

You can download the practice workbook that we have used to prepare this article.

Check If Cell is Not Blank.xlsx

Conclusion

In the above article, I have tried to discuss the methods elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

2 Comments

Jeff Feb 25, 2023 at 1:14 AM

Which method is best?
- Reply
  Rafiul Hasan Feb 27, 2023 at 10:12 AM
  
  Hi JEFF,
  Thanks for your concern. Actually, all the methods here explained are very easy to understand. Considering the situation, you have to choose the best-suited method for serving your purpose. If you don’t want to use any formula, then you can use Method 1.
  
  Regards,
  Rafiul | ExcelDemy Team

Conditional Formatting If Cell is Not Blank