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

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 4 methods to highlight row if the cell is not blank.

Let’s say we have a dataset of the applicants for loan. 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


Download Practice Workbook


4 Methods to Highlight Row If Cell Is Not Blank

Method 1: “Format only cells that contain” Rule

The easiest way to highlight rows with non-blank cells is using Format only cells that contain rule from the New Formatting Rule window of Conditional Formatting. First, select your dataset and go to Home > Conditional Formatting > New Rule.

CONDITIONAL FORMATTING

Now, the New Formatting Rule window will be opened. 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. At this time, select No Blanks from this box.

Now, you need to set the formatting style. To select your preferred formatting style click on Format.

NEW RULE BOX

After clicking on Format, a new window, Format cells, will appear. From the Fill tab of this window select a color for filling the formatted cells. I’ve chosen light blue for this example. You can choose any color you prefer. You can also change the Font, Border, Number type from other tabs of this window, though it is not necessary. After choosing your formatting style click on OK.

FORMATTING STYLES

Now, you can see your selected formatting style in the Preview box of the New Formatting Rule window. Click on OK to apply this formatting to your dataset.

Highlight Row If Cell Is Not Blank

At last, you will get the non-blank cells of your dataset highlighted.

Highlight Row If Cell Is Not Blank

Read More: Highlight Row If Cell Contains Any Text


Method 2: NOT And ISBLANK Function to Highlight Non-Blank Row

We can use the NOT function and the ISBLANK function altogether to highlight non-blank rows. First, select your dataset and go to Home > Conditional Formatting > New Rule.

CONDITIONAL FORMATTING

Now, the New Formatting Rule window will be opened. 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. Type the following formula in this box

=NOT(ISBLANK(A6))

Here the NOT and ISBLANK functions will find out the non-blank cells. A6 is the reference cell. You can give any cell number from your dataset as the reference cell.

After that select the formatting style following the procedure shown in Method 1 and click on OK.

Highlight Row If Cell Is Not Blank

As a result, you will see that all the non-blank rows of your dataset are highlighted.

HIGHLIGHTED CELLS

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


Similar Readings:


Method 3: Highlight Row with Non-Blank Cell by IF function

Instead of using the NOT and the ISBLANK functions, you can use the IF function to highlight the non-blank cells of your dataset. First, follow the steps shown in Method 2 to open the  Format values where the formula is true box. Now, insert the following formula in that box,

=IF(A6="",FALSE,TRUE)

Here, the IF function will give TRUE if the cell is not blank and give FALSE if the cell is blank.

After that select the formatting style following the procedure shown in Method 1 and click on OK.

IF

As a result, you will see that all the non-blank rows of your dataset are highlighted.

Highlight Row If Cell Is Not Blank

Read More: How to Color Alternate Rows in Excel (8 Ways)


Method 4: AND Function to Highlight Row If Particular 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.

First, follow the steps shown in Method 2 to open the Format values where the formula is true box. Then, insert the following formula in that box,

=AND(NOT(ISBLANK($A6)),NOT(ISBLANK($F6)))

Here, the AND function joins the two different conditions. The formula will be only true when the cells of column A and column F are not blanks.

After that select the formatting style following the procedure shown in Method 1 and click on OK.

Highlight Row If Cell Is Not Blank

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.

Cells with conditional formatting

Read More: How to Highlight Active Row in Excel (3 Methods)


Conclusion

You can highlight the row if the cell is not blank by any of the above described methods according to your needs. If you feel any type of confusion, please feel free to leave a comment.


Related Articles

Prantick

Prantick

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

ExcelDemy
Logo