How to Highlight Row If Cell Is Not Blank

We have an Employee Dataset containing employee names, departments, designations, hiring dates, and annual salaries. In this dataset, we will highlight the rows depending on various conditions related to blank cells existing (or not existing) in rows or columns.

Employee Dataset

Read More: How to Highlight Row If Cell Contains Any Text in Excel


Method 1 – Highlighting a Row If the Row Contains No Blank Cells

We will highlight the rows where no cell is blank along the row, i.e. where every cell of the row has data.

Dataset to highlight non blank rows

Steps:

  • Select the entire dataset (B5:G23 here) where you want to apply your conditional formatting.
  • Go to the Home tab and choose the Conditional Formatting tool.

Accessing Conditional Formatting

  • Click on the New Rule… option. A New Formatting Rule window will appear.
  • Choose the option Use a formula to determine which cells to format from the Select a Rule Type: options.
  • Insert the following formula in the Format values where this formula is true: box.
=NOT(ISBLANK($B5))

Setting Conditional Formatting Rule to Highlight Non Blank Rows

  • Click the Format… button, and the Format Cells window will appear.
  • Choose your desired highlighting format. We chose to fill the row with a light blue color.

Applying Conditional Formatting Style

  • Click on the OK button. The New Formatting Rule dialog box will appear again with a preview of the formatting.

Finalizing Conditional Formatting

  • Click on the OK button.
  • You’ll get the result.

Highlighted Rows If Cell Not Blank

Read More: How to Highlight Every 5 Rows in Excel 


Method 2 – Highlighting a Row If the First Cell of the Row Is Not Blank but Some Other Cells Are Blank Along the Row

We will highlight rows that have data in the first column but contain blanks at some other cells along the row. But, if the first data is blank, then we will ignore the highlighting.

Dataset to highlight rows with first non blank cell and other blank cells along the row

Steps:

  • Select your dataset (cells B5:G23 here) where you want to highlight the rows.
  • Click on the Home tab and go to Conditional Formatting.

Accessing Conditional Formatting

  • Click on the New Rule… option to open the New Formatting Rule window.
  • Choose the option Use a formula to determine which cells to format option from Select a Rule Type: options.
  • Insert the following formula in the Format values where this formula is true: text box.
=AND(NOT(ISBLANK($B5)),COUNTA($B5:$G5)<6)

Setting Conditional Formatting rule to highlight rows with first non blank cell and some blank cells along the row

  • Click the Format button.
  • In the Format Cells window, select your preferred Fill color (or other formatting options) and click OK.

Setting Conditional Formatting Format

  • You will find your finalized formatting with all selected options and chosen format displayed in the Preview: box.
  • Click OK.

Finalizing Conditional Formatting

  • Here’s the result.

Highlighted Rows with first non blank cell but some blank cells along the row

Formula Breakdown:

=AND(NOT(ISBLANK($B5)),COUNTA($B5:$G5)<6)

=AND(NOT(ISBLANK($B5)),FALSE)// COUNTA($B5:$G5)<6 counts non-blank cells between cells B5:G5 and then compares the value if it is less than 6.

=AND(TRUE,FALSE)// NOT(ISBLANK($B5)) checks if cell B5 is blank or not and returns TRUE if not blank.

= FALSE// AND(TRUE,FALSE) returns true if all parameters are true and FALSE if any parameter is false.

Note: Here, we have used 6 as the comparing value in the formula, because we have 6 columns in our dataset. If you have other number of columns, use other values as this comparing value.

How to Highlight a Row If Any Cell of the Row Contains a Specific Value in Excel

Case 1 – The Cell Contains a Specific Text

We’ll highlight the rows that contain a specific employee name, department, or designation based on a dynamic choice of selection by the user. We’ll create a dropdown list with Data Validation and then apply conditional formatting to highlight the desired rows.

Steps:

  • Click on cell I5.
  • Go to Data tab and select Data Validation.

Accessing Data Validation tool

  • The Data Validation window will appear. Choose the option List from Allow: dropdown options and reference cells C4:E4 in the Source: text box, as we want employee name, department, and designation in our dynamic list.

Applying Data Validation

  • Click on the OK button and you will get your desired dropdown list containing Full Name, Department, and Designation options.
  • Choose any option that you want as a criterion to match to highlight rows.

Applied Data Validation

  • Click on cell I6 and insert the following formula:
="Match "&$I$5

Dynamic Matching Column Heading

  • Put the value you want to match in I7. Let’s say we want to match for Manager.

Match Value Input

  • Select the entire dataset.
  • Go to Conditional Formatting and select New Rule…

Accessing Conditional Formatting tool

  • The New Formatting Rule window will appear. Choose the Use a formula to determine which cells to format option from Select a Rule Type: and enter the following formula in the Format values where this formula is true: text box.
=IF($I$5=$C$4,$C5=$I$7,IF($I$5=$D$4,$D5=$I$7,IF($I$5=$E$4,$E5=$I$7)))

Applying Conditional Formatting rule

  • To apply your desired formatting, click on the Format… button. Choose a Fill color and apply other styles as needed.
  • Click OK.

Choosing Conditional Formatting style

  • You’ll get the formatting preview shown in the Preview: box.
  • Click on OK.

Finalizing Conditional Formatting

  • We’ll get all the rows highlighted that contain Manager as the Designation value.
  • You can select other dropdown options and input other values as well to highlight those rows.

Highlighted rows containing specific text fully

Formula Breakdown:

=IF($I$5=$C$4,$C5=$I$7,IF($I$5=$D$4,$D5=$I$7,IF($I$5=$E$4,$E5=$I$7)))

=IF($I$5=$C$4,$C5=$I$7,IF($I$5=$D$4,$D5=$I$7,IF($I$5=$E$4,$E5=$I$7))) // IF($I$5=$E$4,$E5=$I$7) checks if cell I5 (Designation) matches cell E4 (Designation). If it is True, then it checks if cell E5 (CEO) matches cell I7 (Manager). Otherwise, it returns False.

=IF($I$5=$C$4,$C5=$I$7,IF($I$5=$D$4,$D5=$I$7,False)) // IF($I$5=$D$4,$D5=$I$7) checks if cell I5 (Designation) matches cell D4 (Department). If it matches then it checks if cell D5 (Executive) matches cell I7 (Manager).

=IF($I$5=$C$4,$C5=$I$7,False,False)) // IF($I$5=$C$4,$C5=$I$7) checks if cell I5 (Designation) matches cell C4 (Full Name). If it matches, then it checks if cell C5 (Marilyn Pittman) matches cell I7 (Manager). Otherwise, it returns False.

=False


Case 2 – Cell Contains Specific Partial Text

In our dataset, we can see that we have designations as both Manager and Assistant Manager. We want to highlight rows that contain either of these designations.

Steps:

  • Create a dropdown list like the previous case to set available criteria options such as Full Name, Department, and Designation.

Dropdown to match column

  • To show the partially matched column heading, insert the following formula in cell I6:.
="Partial "&$I$5

Partial match column heading

  • Insert your desired partial match input in cell I7.

Inputting partial match data

  • Insert a new conditional formatting rule to the entire dataset.

Accessing Conditional Formatting tool

  • The New Formatting Rule window appears. Choose the option Use a formula to determine which cells to format from Select a Rule Type: options.
  • Insert the formula below in Format values where this formula is true: text box.
=IF($I$5=$C$4,SEARCH($I$7,$C5)>0,IF($I$5=$D$4,SEARCH($I$7,$D5)>0,IF($I$5=$E$4,SEARCH($I$7,$E5)>0)))

Applying new formatting rule

  • Click on the Format… button to apply your desired format, then click OK to leave the section.

Choosing formatting style for highlighting rows

  • Click on the OK button to finalize your New Formatting Rule window.

New Formatting Rule window with Preview

  • You will find that all the rows with partially matched Manager designations are highlighted.

Highlighted rows that partially matched Manager designation

Formula Breakdown:

=IF($I$5=$C$4,SEARCH($I$7,$C5)>0,IF($I$5=$D$4,SEARCH($I$7,$D5)>0,IF($I$5=$E$4,SEARCH($I$7,$E5)>0)))

=IF($I$5=$C$4,SEARCH($I$7,$C5)>0,IF($I$5=$D$4,SEARCH($I$7,$D5)>0,IF($I$5=$E$4,SEARCH($I$7,$E5)>0))) // IF($I$5=$E$4,SEARCH($I$7,$E5)>0) checks if cell I5 (Designation) matches cell E4 (Designation). If it is a match, then it will search cell I7 (Manager) appearances in cell E5 (CEO) and will count if this search result appears greater than zero times.

=IF($I$5=$C$4,SEARCH($I$7,$C5)>0,IF($I$5=$D$4,SEARCH($I$7,$D5)>0,False)) // IF($I$5=$D$4,SEARCH($I$7,$D5)>0 checks if cell I5 (Designation) matches cell D4 (Department). If it is a match, then it will search cell I7 (Manager) appearances in cell D5 (Executive) and will count if this search result appears greater than zero times.

=IF($I$5=$C$4,SEARCH($I$7,$C5)>0,False,False)) // IF($I$5=$C$4,SEARCH($I$7,$C5)>0) checks if cell I5 (Designation) matches cell C4 (Full Name). If it is a match, then it will search cell I7 (Manager) in cell C5 (Marilyn Pitman) and will count if this search result appears greater than zero times.

=FALSE

Read More: How to Highlight Every Other Row in Excel


Case 3 – A Specific Cell Contains a Specific Number

We will highlight rows that contain specific salaries or a specific range of salaries.

Steps:

  • Select cell I6 and go to Data tab.
  • Choose the Data Validation tool.

Accessing Data Validation tool

  • The Data Validation window appears. Choose List in Allow: dropdown options.
  • Insert the following symbols with separated commas (>=, >, <, <=, =)  in the Source: text box.
  • Click on OK and the data validation is active.

Applying Data Validation

  • Choose your desired criteria (= for our example) operator from the dropdown.

Criteria operator dropdown

  • Insert your desired specific value in cell J6 (600000 here).

Input match value

  • To apply conditional formatting, select your dataset and go to Conditional Formatting, then select New Rule…

Accessing Conditional Formatting tool

  • Choose the option Use a formula to determine which cells to format from Select a Rule Type: and insert the following formula in the Format values where this formula is true: text box.
=IF($I$6="=",$G5=$J$6,IF($I$6=">",$G5>$J$6,IF($I$6=">=",$G5>=$J$6,IF($I$6="<",$G5<$J$6,IF($I$6="<=",$G5<=$J$6)))))

Setting Conditional Formatting rule

  • Click on the Format… button. Choose your highlighting format as your wish.
  • Click OK.

Setting Conditional Formatting style

  • Here’s the preview.
  • Click OK.

Finalizing Conditional Formatting

  • Here are the results of the sample we used.
  • You can select any other criteria from the dropdown and insert any other value to highlight rows according to your own target.

Highlighted rows that matches specific number or range of numbers

Formula Breakdown:

=IF($I$6=”=”,$G5=$J$6,IF($I$6=”>”,$G5>$J$6,IF($I$6=”>=”,$G5>=$J$6,IF($I$6=”<“,$G5<$J$6,IF($I$6=”<=”,$G5<=$J$6)))))

=IF($I$6=”=”,$G5=$J$6,IF($I$6=”>”,$G5>$J$6,IF($I$6=”>=”,$G5>=$J$6,IF($I$6=”<“,$G5<$J$6,IF($I$6=”<=”,$G5<=$J$6))))) // IF($I$6=”<=”,$G5<=$J$6) checks if cell I6 (=) contains “<=” operator. If it is True, then it checks if cell G5 (3,000,000) is less than or equal to cell J6 (600,000).

=IF($I$6=”=”,$G5=$J$6,IF($I$6=”>”,$G5>$J$6,IF($I$6=”>=”,$G5>=$J$6,IF($I$6=”<“,$G5<$J$6,False)))) // IF($I$6=”<“,$G5<$J$6) checks if cell I6(=) contains “<” operator. If it is True, then it compares if cell G5 (3,000,000) is less than cell J6 (600,000).

=IF($I$6=”=”,$G5=$J$6,IF($I$6=”>”,$G5>$J$6,IF($I$6=”>=”,$G5>=$J$6,False,False)))) // IF($I$6=”>=”,$G5>=$J$6) checks if cell I6 (=) contains “>=” operator. If it is True, then it checks if cell G5 (3,000,000) is greater than or equal to cell J6 (600,000).

=IF($I$6=”=”,$G5=$J$6,IF($I$6=”>”,$G5>$J$6,False,False,False)))) // IF($I$6=”>”,$G5>$J$6) checks if cell I6 (=) contains “>” operator. If it is True, then it checks if cell G5 (3,000,000) is greater than cell J6 (600,000).

=IF($I$6=”=”,$G5=$J$6,False,False,False,False)))) // IF($I$6=”=”,$G5=$J$6) checks if cell I6 (=) contains “=” operator. If it is True, then it checks if cell G5 (3,000,000) is equal to cell J6 (600,000).

= FALSE


Download the Practice Workbook


Related Articles


<< Go Back to Highlight Row | Highlight in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

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

  3. Is there a way to format a row until a point, instead of the entire row? For example, if you typed something into column B, the same formatting rule would be applied to columns B through J in that row (stopping at J), instead of the entirety of the row. Method 1 would be perfect for my needs if the conditional formatting rule didn’t continue onwards into infinity. Or is that too complicated for Excel to do?

    • Reply Avatar photo
      Naimul Hasan Arif Jul 27, 2023 at 10:40 AM

      Hello JAKE,
      Thanks for your response.
      The simplest way to highlight rows till a specific cell, instead of the entire row is to select the range till that column before applying the conditional formatting.

      Then, you can apply any of the above methods and have your desired result.

      If you have already applied the conditional formatting and want to change the selection range, go to the Home tab and click on Conditional Formatting. After that, select Manage Rules…

      Now, change the range from the Applies to section and click on OK.

      You will have the selection changed according to your desired cell.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo