In this Excel tutorial, we will show you how to highlight rows if no cell is blank along the row. Besides, we will highlight rows if the first column of a row contains data but some other cells along the row contain no data. We will also show you how to highlight rows if specific cells contain specific values.
Using different Excel functions and formulas with Excel conditional formatting feature, it is possible to highlight rows if the cells of those rows are not blank (partially or fully).
Note: We have used Office 365 when preparing this article. But, you can apply the procedures in all versions available in Excel.
Highlighting rows if the cell is not blank is very important for large and random datasets to find and input missing data.
Why Highlight Row If Cell Is Not Blank?
Sometimes, when working with large datasets, we find blank and non-blank cells are present randomly and simultaneously. In these cases, we need to find those cells to check the missing data and make the dataset uniform. In this regard, it would be of great help if we could apply some features to highlight the non-blank cells automatically. That is why we need to highlight rows if a cell is not blank. We can do this by applying conditional formatting through various formulas depending on specific cases.
How to Highlight Entire Row If Cell Is Not Blank in Excel
In this section, we shall work on these two cases: 1) highlight rows that are filled with data (no blank cells in the row), and 2) highlight rows if the first cell of the row is not blank but some other cells of the row may be blank.
Here, we have an Employee Dataset containing employees’ names, departments, designations, hiring dates, and annual salaries. In this dataset, we will highlight the rows depending on various conditions like no cell is blank along the row, or the first cell is non-blank but some other cell is blank along the row, a specific cell containing specific values, and so on.
Read More: How to Highlight Row If Cell Contains Any Text in Excel
1. Highlighting Row If Row Contains No Blank Cell
Here, I will use Excel NOT() and ISBLANK() Functions with Conditional Formatting feature to highlight rows if the rows contain no blank cells.
For the following dataset, we will highlight the rows where no cell is blank along the row, i.e. where every cell of the row has data.
For example:
When we are done with the process, these rows will be highlighted:
ID-22001 | Marilyn Pittman | Executive | CEO | 17-Feb-21 | $3,000,000.00 |
ID-22005 | Rodney Gomez | Sales | Representative | 25-May-21 | $100,000.00 |
As these rows don’t have any blank cells.
Follow these steps:
Steps:
- Select the area of your dataset (B5:G23 here) where you want to apply your conditional formatting to highlight non-blank rows.
- Now, go to the Home tab >> Conditional Formatting tool >> You will find the New Rule option.
- Click on the New Rule… option. As a result, a New Formatting Rule window will appear. Here, choose the option Use a formula to determine which cells to format from Select a Rule Type: options.
- Insert the following formula in the Format values where this formula is true: box and you will get the Format button.
=NOT(ISBLANK($B5))
- Click the Format… command >> Format Cells window will appear.
- Choose your desired highlighting format from here. We chose to fill the row with a light blue color. To do this, go to the Fill tab here and choose your desired color.
- Click on the OK button >> The New Formatting Rule dialog box will appear again with the formatting you’ve finalized with a preview in the Preview: box.
- Click on the OK button. You see the following image: all the rows of the entire dataset where no cell is blank are highlighted with a blue shaded fill.
Read More: How to Highlight Every 5 Rows in Excel
2. Highlighting Row If First Cell of Row Is Not Blank but Some Other Cells Are Blank Along the Row
In this example, 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.
For example:
When we are done with the process, we will get the following rows highlighted:
ID-22002 | Samantha Carson | Sales | 12-Apr-21 | $600,000.00 | |
ID-22006 | Jaime Schultz | Assistant Manager | 15-Feb-21 | $100,000.00 |
These rows contain data in the first column but contain some blanks along the row.
Follow the steps below.
Steps:
- Select your dataset area (cell B5:G23 here) where you want to highlight the rows >> Click on the Home tab >> Conditional Formatting tool >> You will see the New Rule… option.
- Clicking on New Rule… option will 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)
- After clicking the Format… button of the New Formatting Rule window, the Format Cells window appears. We want to highlight the desired rows with a light blue shaded color. To do this, go to the Fill tab here >> choose your desired fill color (Blue, Accent 5, Lighter 80% here).
- Click on the OK button and you will find your finalized formatting with all selected options and chosen format displayed in the Preview: box.
- Click OK. You will find your desired result, that is, all the rows that contain data in the first cell, but contain other blank cells along the row are highlighted.
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.
How to Highlight Row If Any Cell of the Row Contains a Specific Value in Excel
Here, we will highlight the rows based on specific values contained by specific cells along the row. We will mainly focus on highlighting the rows that contain specific text or specific values or range of values etc.
1. Cell Contains Specific Text
In this example, we would highlight the rows that contain a specific employee name, department, or designation based on a dynamic choice of selection by the user. So, we would create a dropdown list with Data Validation and then apply conditional formatting to highlight the desired rows.
For example, if we choose the Designation column as criteria and match Manager with the given dataset, the following rows will be highlighted.
ID-22002 | Samantha Carson | Sales | Manager | 12-Apr-21 | $600,000.00 |
ID-22007 | Abel Alvarado | Marketing | Manager | 24-Mar-21 | $600,000.00 |
As these rows match the designation as Manager.
Go through the steps below.
Steps:
- To create a dynamic choice list in cell I5, click on the cell >> go to Data tab >> You will find Data Validation tool.
- After clicking on the 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.
- 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 criteria (Designation here) to match to highlight rows.
- To declare a heading for the next input based on this dropdown selection, click on cell I6 and insert the following formula.
="Match "&$I$5
- Say, you want to match the Manager designation to highlight your dataset rows. Put that value in cell I7.
- Select your desired dataset area (B5:G23 here) >> go to Home tab >> Conditional Formatting tool >> You will get the New Rule… option.
- Click on the New Rule… option >> New Formatting Rule window will appear. Choose the option Use a formula to determine which cells to format option from Select a Rule Type: options 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)))
- To apply your desired formatting, click on the Format… button. Say, you want to highlight your rows with a light blue shaded fill color. To do this, go to Fill tab in the appeared Format Cells window >> choose your desired color (Blue, Accent 5, Lighter 80% here).
- Click on the OK button and you will find the New Formatting Rule window with your set formula and preview shown in the Preview: box.
- Click on the OK button to get all the rows highlighted that contain Manager as the Designation value. You can simply select other dropdown options and input other values as well to highlight those rows.
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
2. Cell Contains Specific Partial Text
In our dataset, we can see that we have designations as both Manager and Assistant Manager. Now, we want to highlight rows that contain either of these designations. So, we need to find a partial match for the Manager and then apply conditional formatting to highlight the rows.
For example: If we choose Designation as a match column and find Manager partially, then we will get the below rows highlighted.
ID-22002 | Samantha Carson | Sales | Manager | 12-Apr-21 | $600,000.00 |
ID-22004 | Chester Paul | Sales | Assistant Manager | 19-Jan-21 | $100,000.00 |
These rows contain the designation of Manager or Assistant Manager.
Follow the steps below.
Steps:
- Create a dropdown list like the previous method to set available criteria options such as Full Name, Department and Designation.
- To show the partially matched column heading, insert the following formula in cell I6.
="Partial "&$I$5
- Insert your desired partial match input in cell I7.
- Select your dataset area (B5:G23 here) >> go to Home tab >> Conditional Formatting tool >> You will find the New Rule… option.
- Click on the New Rule… option and 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)))
- Click on the Format… button to apply your desired format. Let, you want to highlight the rows with light shaded blue color.
- After clicking the Format… button, the Format Cells window appears. Go to Fill tab >> choose Blue, Accent 5, Lighter 80% color.
- Click on the OK button to finalize your New Formatting Rule window where you will see all the chosen details with your desired format in the Preview: box.
- Click on the OK button again and you will find that all the rows with partially matched Manager designation are highlighted with a light-shaded blue color fill.
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
3. Specific Cell Contains Specific Number
The previous two examples showed highlighting rows with matches to string. In this example, we will find matches with numeric values and highlight the rows with desired conditions. In the Annual Salary column, we can see various values of salaries. Now, we will highlight rows that contain specific salaries or a specific range of salaries.
For example, if we select criteria as equal and insert value as $600,000, then the following rows will be highlighted:
ID-22002 | Samantha Carson | Sales | Manager | 12-Apr-21 | $600,000.00 |
ID-22007 | Abel Alvarado | Marketing | Manager | 24-Mar-21 | $600,000.00 |
These employees get an annual salary 600,000.
Steps:
- To set the criteria, a dropdown list will be useful. To do this, select cell I6 >> go to Data tab >> You will find the Data Validation tool.
- Click on the Data Validation tool and 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. Choose your desired criteria (= for our example) operator from the dropdown.
- Insert your desired specific value in cell J6 (600000 here).
- To apply conditional formatting, select your dataset (B5:G23 here) >> go to Home tab >> Conditional Formatting tool >> You will get the New Rule… option.
- Clicking on the New Rule… option will bring the New Formatting Rule window. Choose the option Use a formula to determine which cells to format option from Select a Rule Type: options 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)))))
- Click on the Format… button. Format Cells window appears. Choose your highlighting format as your wish.
- If you want to highlight rows with blue shaded fill color, go to Fill tab from Format Cells window and choose Blue, Accent 5, Lighter 80% from the color palette.
- Click on the OK button to finalize your formatting and you can visualize it in the Preview: box.
- Clicking on the OK button again will result in highlighting all the rows of your dataset that contain an Annual Salary equal to 600,000 with Blue, Accent 5, Lighter 80% fil. You can select any other criteria from the dropdown and insert any other value to highlight rows according to your own target.
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 Practice Workbook
You can download our practice workbook from here for free!
Conclusion
So, in this article, we have shown you how to highlight rows if no cell is blank along the row. We have also shown highlighting rows in case the first cell of the row has data but some other cells are blank along the row. Besides, we have shown you how to create a dynamic list of matching columns and highlight rows based on specific values of any selected dropdown. We applied the same highlighting for partial matches too. We have highlighted rows based on not only text values but also based on numerical values. Hope this article will help you greatly if you need to highlight rows if a cell is not blank or any other specific condition.
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.
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?
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.