Hosne Ara
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).
- 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.
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.
- 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.
Read More: Conditional Formatting with Formula for Multiple Conditions in Excel
Similar Readings:
- Conditional Formatting Multiple Text Values in Excel (4 Easy Ways)
- How to Use Conditional Formatting Based on VLOOKUP in Excel
- How to Do Conditional Formatting Based On Another Cell Range in Excel
- Excel Conditional Formatting Formula with IF
- Conditional Formatting on Multiple Rows Independently 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
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 [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.
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