Conditional Formatting If Cell is Not Blank

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.


Download the Practice Workbook

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


4 Easy Methods for Conditional Formatting If Cell is Not Blank

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).

Conditional Formatting If Cell is Not Blank

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

Conditional Formatting If Cell is Not Blank

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

Conditional Formatting If Cell is Not Blank

  • 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.

Conditional Formatting If Cell is Not Blank

  • Click on the Format.

Conditional Formatting If Cell is Not Blank

  • 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.

Read More: Conditional Formatting for Blank Cells in Excel (2 Methods)


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.

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

  • 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<>""

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

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

Read More: Conditional Formatting with Formula for Multiple Conditions in Excel


Similar Readings:


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

Appy LEN Function in Conditional Formatting for Non-Blank Cell

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))

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

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 [Ultimate Guide]


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.


Related Articles

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo