How to highlight row if cell contains text/value/blank in Excel?

doc highlight row if cell contains 1

...

For example we have a purchase table in Excel, now we want to find out the purchase orders of apple and then highlight the entire rows where the orders of apple are in as the left screen shot shown. We can get it done easily with Conditional Formatting command or Kutools for Excel's features in Excel.

Highlight entire row if cell contains specific text/value/blank with Conditional Formatting

Highlight entire row if cell contains specific text/value with Kutools for Excel

Highlight entire row if cell contains one of specific values in another column


arrow blue right bubbleHighlight row if cell contains specific text/value/blank with Conditional Formatting

To highlight entire rows of cells containing the specific text, value or just be blank with the Conditional Formatting command in Excel, you can do as following:

1. Select the purchase table without its column headings.

2. Click the Home > Conditional Formatting > New Rule. See the first screenshot below:
doc highlight row if cell contain 3

3. In the coming New Formatting Rule dialog box, (see the second screenshot above):
(1) Click to select the Use a formula to determine which cells to format in the Select a Rule Type box;
(2) In the Format values where this formula is true box, enter =$B2="Apple";
(3) Click the Format button.
Notes:
(1) In the formula of =$B2="Apple",$B2 is the cell you will check if contains the specific text or value, and "Apple" is the specific text, you can change them as you need. And this formula can only find out cells only containing the specific text or value.
(2) If you want to highlight rows if cells are blank, enter =$B2="" in the Step 3-(2).
(3) If you want to highlight rows if cells begin with specific text, you need to enter =LEFT($B2,5)="Apple"; or to highlight rows if cells end with specific text, enter =RIGHT($B2,5)="Apple".

4. Now the Format Cells dialog box is opening. Go to the Fill tab, specify a background color, and click the OK button.
doc highlight row if cell contain 4

5.  Click OK button to close the New Formatting Rule dialog box.

Then all rows containing the specific content cells in the selected range are highlighted.

Select entire rows if cells contain certain text/values in Excel

Kutools for Excel's Select Specific Cells feature can help you quickly select the cells, entire rows, or entire columns if cells contains the certain value in Excel. Full Feature Free Trial 30-day!
ad select row if contains

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

arrow blue right bubbleHighlight row if cell contains specific text/value with Kutools for Excel

With Kutools for Excel's Select Specific Cells feature, we can select the rows if cells contain specific text or value, and then highlight these rows easily in Excel.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!

1. Select the column where you will find out cells if contain specific text or value.

2.  Click the Kutools > Select > Select Specific Cells.

3.  In the opening Select Specific Cells dialog box (see above screenshot),
(1) Check the Entire row in the Selection type section;
(2) In the Specific Type section, click the first box and specify the Contains from the drop down list, and then enter the specific text into the following box.
(3) Click the OK button.

4. In the second Select Specific Cell dialog box, click the OK button. And then all cells with the entire rows which contain the specific text or value are selected.

5. Click the Fill Color button on the Home tab, and then specify a highlight color from the drop down list.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now


arrow blue right bubble Highlight entire row if cell contains one of specific values in another column

Sometimes you may need to highlight the entire row only if its cell contains one of specific values in another column. Kutools for Excel’s Compare Ranges utility provides another workaround to highlight entire rows if cells contain one of specific values easily.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!

1. Click Kutools > Select > Select Same & Different Cells.

2. In the Select Same & Different Cells dialog box, Please do as follows:

(1) In the Find values in box, please specify the Column whose cells you will check if containing one of specific values;
(2) In the According to box, please specify the column containing the specific values;
(3) In the Based on section, please check the Each row option;
(4) In the Find section please check the Same Values option;
(4) In the Processing of results dialog box, please check the Fill backcolor option and specify the fill color;
(5) Check the Select entire rows option.

3. Click the OK button to apply this utility. And then another Compare Ranges dialog box comes out and show us how many rows have been selected. Just click the OK button to close it.

And then you will see if a cell contains one of specific values in another column, the entire row containing this cell will be highlighted and filled with the specific color.
doc highlight row by other column 4

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now


arrow blue right bubbleDemo: highlight row if cell contains certain value or one of specified values

Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

Automatically highlight active row and column with Kutools for Excel

When selecting a cell in Excel, normally only the active cell is highlighted. However, Kutools for Excel's Reading Layout utility can automatically highlight the entire row and column of active cell automatically. Full Feature Free Trial 30-day!
ad reading layout 1

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now


arrow blue right bubbleRelated Articles


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Sagar pachhai · 3 months ago
    If some row cells are blank does conditional formatting works?? I was trying but it could not select there was some cells blank ...

    • To post as a guest, your comment is unpublished.
      kellytte · 2 months ago
      Hi Sagar pachhai,
      To add conditional formatting for blank cells, you can use the formula similar as this =$B2="".
      FYI, 0 will be shown as blank if you have disabled the Show a zero in cell that have zero value option. And some formulas will also return blank too.
  • To post as a guest, your comment is unpublished.
    umairaziz · 1 years ago
    Please help....... In "Select specif cells" dialogue box, In contains field, I want to give a list instead of a word. and then highlight the entire rows which contains that either of the given text in list partially or completely . Is there a way to achieve this? Thank you!
    • To post as a guest, your comment is unpublished.
      kellytte · 1 years ago
      Hi Umair Aziz,
      In the third method introduced in this article (https://www.extendoffice.com/documents/excel/2525-excel-highlight-row-if-cell-blank-contains-value-text.html#kutools2 ), I have introduced how to highlight row if the column contains any one of values in a list.
  • To post as a guest, your comment is unpublished.
    Donbosoco · 1 years ago
    Hi , if i type the number 5 , that 5 rows should be need to highlighted. how to do this?
    Pls help
    • To post as a guest, your comment is unpublished.
      kellytte · 1 years ago
      Hi Donbosoco,
      Do you mean highlight every 5 rows alternatively? If so, you can apply Conditional Formatting with formula =MOD(ROW(),5)
      • To post as a guest, your comment is unpublished.
        kellytte · 1 years ago
        You can also apply Kutools for Excel’s Alternate Row / Column Shading feature to highlight every nth rows or columns easily.
  • To post as a guest, your comment is unpublished.
    Yoga · 2 years ago
    My excel is become little slow while filter the data for the conditional formatted column. Is there any better solution to do highlight dupes in the same column of excel?

    I very frequently use filter option for the conditional formatted columns.

    Please help me.
    • To post as a guest, your comment is unpublished.
      kellytte · 1 years ago
      Hi Yoga,
      Apply Kutools for Excel’s Select Duplicate & Unique Cells feature to find out and highlight duplicates in one column.
  • To post as a guest, your comment is unpublished.
    Jeff · 2 years ago
    How to you conditionally format cells with both numbers and text, based on the number. I Have a column that has entries such as 30 days, 12 days, 84 days...etc I would like to highlight the cells <= 30 green, and >=80 Red. Thank you for your help.
    • To post as a guest, your comment is unpublished.
      Greg · 2 years ago
      I did something similar but instead used evaluation statements based on the initial date for that item. In other words I would highlight the cell containing remaining days (30 days, 12 days, etc) based off a calculation of the current date and the initial date which was kept in the cell next to the remaining days. For me 10-30 was red text, 31-60 was yellow text and 0-9 was dark red fill with bold white text. Annoying yes but called attention to it.
  • To post as a guest, your comment is unpublished.
    Dylan · 2 years ago
    Let's say you have a row, and the first cell currently contains no value. The remaining cells contain values. Now, I wish to input a value into that empty cell. When I do, regardless of the data entered, I wish for the entire row to be highlighted. How do I go about doing this?
    • To post as a guest, your comment is unpublished.
      kelly.extendoffice@gmail.com · 2 years ago
      Hi Dylan,
      Do you mean highlight the entire row if the specified cell is nonblank? If so, you can:
      (1) Select the row
      (2) Apply conditional formatting with formula , and the formula is =ISBLANK($A2)=FALSE (A2 is the first cell )
  • To post as a guest, your comment is unpublished.
    MAYRA · 3 years ago
    Conditional Formatting
  • To post as a guest, your comment is unpublished.
    A user · 3 years ago
    Please remove the smooth scrolling, it's no longer necessary in modern browsers
  • To post as a guest, your comment is unpublished.
    Ravikanth Singh · 3 years ago
    Thank you very much for the help