How to Highlight Row If Cell Is Not Blank

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:

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.

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

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.

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

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.

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.

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

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

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.

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.

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

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

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:

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.

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

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.

Related Articles

• How to Highlight Entire Row in Excel with Conditional Formatting
• How to Highlight Active Row in Excel 

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