How to Highlight Row If Cell Is Not Blank (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

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.

Dataset for Highlighting Row If Cell Is 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:

Selecting New Rule in Conditional Formatting Menu

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

Making Formula Combining NOT And ISBLANK Functions to Highlight Non-Blank Row

Here, we will start looking at the B5 cell. We have frozen the B column. For this reason, the formula will look from B5 to B14 cells and find out the non-blank cells. By ISBLANK Function, we will search for blank cells and using the NOT Function, we are finding out the remaining non-blank cells.
  • 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.

Selecting Color for Non-Blank Cells

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

Confirming Formatting for Non-Blank Cells

  • As a result, you will get the highlighted rows if the Customer Name is present in your dataset.

Result after Inserting Formula Combining NOT And ISBLANK Functions to Highlight Non-Blank Row


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.

Placing New Rule Option to Insert Formatting

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

Forming Formula Using IF Function to Spotlight Row with Non-Blank Cell

Here, we will start looking at the G5 cell. We have frozen the G column. For this reason, the formula will look from G5 to G14 cells and find out the non-blank cells. Using the IF Function, we are giving the cell value TRUE or FALSE and applying the formatting condition.
  • 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

Result After Forming Formula Using IF Function to Spotlight Row with Non-Blank Cell


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.

Inserting New Rule With Conditional Formatting Option

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

Creating Formula Merging AND & NOT Functions to Highlight Row If Cell Is Not Blank

Here, we will start looking at the B5 cell. We have frozen the B column. For this reason, the formula will look from B5 to B14 cells and find out the non-blank cells. Similarly, we have found the non-blank cells of the G column. Finally, if both contain non-blank cells, then we are applying the formatting.
  • 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.

Result After Applying Formula Merging AND & NOT Functions to Highlight Row For Non-Blank Cell


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.

Selecting New Rule with Conditional Formatting Menu

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

Selecting Non-Blanks Before Formatting Cells

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

Choosing Color for Filling Non-Blank Cells

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

Confirming Before Formatting For Non-Blank cells

  • As a result, you will get the non-blank cells of your dataset highlighted.

Result After Highlighting Non-Blank

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.

Selecting New Rule to Create Formula

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

Creating Formula Highlighting Non-Blank Row Using NOT and ISBLANK Functions

Here, the NOT and ISBLANK functions will find out the non-blank cells. B5 is the reference cell. You can give any cell number from your dataset as the reference cell.
  • As a result, you will see that all the non-blank cells of your dataset are highlighted.

Result After Applying Formula Highlighting Non-Blank Row Using NOT and ISBLANK Functions

Read More: How to Select Row in Excel If Cell Contains Specific Data (4 Ways)


Similar Readings:


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.

Taking New Rule to Create Formula

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

Building Formula Using IF Function to Highlight Non-Blank Cell

Here, the IF function will give TRUE if the cell is not blank and give FALSE if the cell is blank.
  • As a result, you will see that all the non-blank cells of your dataset are highlighted.

Result After Applying Formula to Highlight Non-Blank Cell

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.


Related Articles

Prantick Bala

Prantick Bala

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

4 Comments
  1. How to highlight the enter row if all cells in selection of that row are blank?

    • Reply Avatar photo
      Rubayed Razib Suprov Aug 21, 2022 at 11:10 AM

      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.

  2. 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?

    • Reply Avatar photo
      Rubayed Razib Suprov Aug 21, 2022 at 11:49 AM

      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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo