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

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: How to Apply Conditional Formatting for Blank Cells in Excel


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:Applying Conditional Formatting for Multiple Conditions in Excel


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


Download the Practice Workbook

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.


Related Articles

<< Go Back to Conditional Formatting with Multiple Conditions | Conditional Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

2 Comments
  1. Which method is best?

    • 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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo