Prantick Bala
The Conditional Formatting feature in Excel gives you different options to highlight cells of your dataset based on different conditions. You can highlight rows based on non-blank cells by using conditional formatting. Following this article, you will get to know your 3 methods to Highlight row if cell Is not blank.
Download Practice Workbook
3 Suitable Methods of Highlighting Row If Cell Is Not Blank
Let’s say we have a dataset of applicants for loans. There are some missing data for some of the applicants. Now, we want to highlight the cells where data are not missing, that is data cells are not blank.
1. Make Formula Combining NOT And ISBLANK Functions to Highlight Non-Blank Row
We can use the NOT function and the ISBLANK function altogether to highlight non-blank rows. Here, we want to highlight rows if the customer name is available in the dataset. The blank cells in the name column indicate that the customer name is unavailable. Let’s follow the steps below to learn the method.
STEPS:
- Firstly, select your dataset.
- After selecting, go to Home >> Conditional Formatting >> New Rule.
- Instantly, the New Formatting Rule window will appear.
- In this window, select Use a formula to determine which cells to format from the Select a Rule Type box.
- After that, a box named Format values where the formula is true will appear below the Select a Rule Type box.
- So, type the following formula in this box
=NOT(ISBLANK($B5))- Then, select the formatting style by clicking on the Format button.
- After clicking on Format, a new window named Format cells will appear.
- In the newly opened window, look for the Fill tab.
- From the Fill tab of this window select a color for filling the formatted cells.
- Here, you can choose any color you prefer. You can also change the Font, Border, and Number type from other tabs of this window.
- After choosing the style, click on OK.
- Now, you can see your selected formatting style in the Preview box of the New Formatting Rule window.
- Finally, click on OK to apply this formatting to your dataset.
- As a result, you will get the highlighted rows if the Customer Name is present in your dataset.
2. Form Formula Using IF Function to Spotlight Row with Non-Blank Cell
Here, you can use the IF function to highlight the rows if the non-blank cells are present. In this method, we want to highlight rows if the purpose of the loan is available in the dataset. The blank cells in the Loan Purpose column indicate that the loan may be risky. Let’s follow the steps below to learn the method.
STEPS:
- Firstly, select your dataset and move to Home >> Conditional Formatting >> New Rule.
- Instantly, the New Formatting Rule window will appear.
- In this window, select Use a formula to determine which cells to format from the Select a Rule Type box.
- After that, a box named Format values where the formula is true will appear below the Select a Rule Type box.
- So, type the following formula in this box
=IF($G5="",FALSE,TRUE)- Then, select the formatting style by clicking on Format button.
- Previously, we have shown how to choose the style for formatting.
- After choosing the formatting color, click on OK to confirm.
- As a result, you will get the highlighted rows if the Loan Purpose is present in your dataset.
- So, we have successfully shown how to highlight row if cell is not blank
3. Create Formula Merging AND & NOT Functions to Highlight Row If Cell Is Not Blank
If you need to highlight rows based on the blank cells from particular columns you can use the AND function. Suppose, we want to find out the applicants who have jobs for giving the loan. So we need to highlight those rows where the cells of the Customer Name and the Job columns are not blank.
- Firstly, select your dataset and move to Home >> Conditional Formatting >> New Rule.
- Instantly, the New Formatting Rule window will appear.
- In this window, select Use a formula to determine which cells to format from the Select a Rule Type box.
- After that, a box named Format values where the formula is true will appear below the Select a Rule Type box.
- So, type the following formula in this box
=AND(NOT(ISBLANK($B5)),NOT(ISBLANK($F5)))- Then, select the formatting style by clicking on Format button.
- Previously, we have shown how to choose the style for formatting.
- After choosing the formatting color, click on OK to confirm.
- As a result, you will see, only the rows where the cell of column A and the cell of column B are not blanks are highlighted.
3 Suitable Methods of Highlighting Cells If Cell Is Not Blank
We will use the same dataset for showing the methods of highlighting cells if the cell is not blank. We will show you 3 easy methods. You can follow any of these to implement your task. Let’s follow the methods to below to learn the process.
1. Highlight Non-Blank Cell Without Using Formula
The easiest way to highlight rows with non-blank cells is by using Format only cells that contain rule from the New Formatting Rule window of Conditional Formatting. Here, you don’t need to insert any formula. Let’s follow the steps below to learn the method.
STEPS:
- Firstly, select your dataset.
- After selecting, move to Home >> Conditional Formatting >> New Rule.
- Instantly, the New Formatting Rule window will appear.
- From this window, select Format only cells that contain from the Select a Rule Type box.
- After that, a box named Format only cells with will appear below the Select a Rule Type box.
- Now, select No Blanks from this box.
- Then, you need to set the formatting style.
- To do so, click on Format.
- After clicking on Format, a new window named Format cells will appear.
- In the newly opened window, look for the Fill tab.
- From the Fill tab of this window select a color for filling the formatted cells.
- Here, you can choose any color you prefer. You can also change the Font, Border, and Number type from other tabs of this window.
- After choosing the style, click on OK.
- Now, you can see your selected formatting style in the Preview box of the New Formatting Rule window.
- Finally, click on OK to apply this formatting to your dataset.
- As a result, you will get the non-blank cells of your dataset highlighted.
Read More: Highlight Row If Cell Contains Any Text
Method 2: Create Formula Highlighting Non-Blank Row Using NOT and ISBLANK Functions
We can use the NOT and ISBLANK functions altogether to highlight non-blank rows.
STEPS:
- Firstly, select your dataset and move to Home >> Conditional Formatting >> New Rule.
- Instantly, the New Formatting Rule window will appear.
- In this window, select Use a formula to determine which cells to format from the Select a Rule Type box.
- After that, a box named Format values where the formula is true will appear below the Select a Rule Type box.
- So, type the following formula in this box
=NOT(ISBLANK(B5))- Then, select the formatting style by clicking on Format button.
- Previously, we have shown how to choose the style for formatting.
- After choosing the formatting color, click on OK to confirm.
- As a result, you will see that all the non-blank cells of your dataset are highlighted.
Read More: How to Select Row in Excel If Cell Contains Specific Data (4 Ways)
Similar Readings:
- [Fix]: Unable to Unhide Rows in Excel (4 Solutions)
- How to Hide Rows in Excel (6 Effective Methods)
- How to Group Rows by Cell Value in Excel (3 Simple Ways)
- Shortcut to Unhide Rows in Excel (3 Different Methods)
- Unhide All Rows Not Working in Excel (5 Issues & Solutions)
Method 3: Build Formula Using IF Function to Highlight Non-Blank Cell
You can also use the IF function to highlight the non-blank cells of your dataset.
- Firstly, select your dataset.
- Then, move to Home >> Conditional Formatting >> New Rule.
- Instantly, the New Formatting Rule window will appear.
- In this window, select Use a formula to determine which cells to format from the Select a Rule Type box.
- After that, a box named Format values where the formula is true will appear below the Select a Rule Type box.
- So, type the following formula in this box.
=IF(B5="",FALSE,TRUE)- Then, select the formatting style by clicking on Format button.
- Previously, we have shown how to choose the style for formatting.
- After choosing the formatting color, click on OK to confirm.
- As a result, you will see that all the non-blank cells of your dataset are highlighted.
Read More: How to Color Alternate Rows in Excel (8 Ways)
Read More: How to Highlight Active Row in Excel (3 Methods)
Conclusion
In this article, we have shown how to highlight row if cell is not blank. We have also shown how to highlight non-blank cells. There is a practice workbook at the beginning of the article. Go ahead and give it a try. To read similar articles, check out the ExcelDemy website. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.
How to highlight the enter row if all cells in selection of that row are blank?
Thanks a lot for your question. Here, if all of the cells on a single row are blank in your dataset, they will be highlighted if you follow this tutorial. If you have any further issues, feel free to inform us again.
Each time you used the word “Row” you really meant “Cell”. Your title suggests you would show how to highlight the ENTIRE Row if a specific cell was blank. Can you do that?
Thanks a lot for your question, Aaron. Here we are going to use a separate formula for highlighting the entire row based on whether any of the values in the row are blank or not. Notice the image below, we got blank cell in C8,C11, and C14
2. First, go to the Conditional Formatting, and set the rules as shown below.
3. Then select only the B4:D4, and copy the formatting style of this cell.
4. Paste the formatting style all over the range of B4:D15.
And now you can see that the rows now highlighted if there is any blank cell in the entire row.