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

## Conditional Formatting If Cell is Not Blank: 4 Easy Methods

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

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

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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.