In Google Sheets, you can highlight an entire row based on a cell by using a custom formula which combines ISNUMBER and SEARCH/FIND.
Learning how to conditional format a row based on one cell can in Google Sheets can help you quickly identify and analyse data that meets specific criteria, making it easier to make informed SEO decisions.
In this example, let’s try to highlight any rows that contain keywords with “best” in it.
Step 1: Open the Google Sheets document that you want to work with.
First, open the document that you would like to work with and analyse. Clean up the data to remove any unnecessary information.
Step 2: Highlight the rows that you want to format.
You can do this by clicking on the row number on the left-hand side of the Google Sheets document. To select multiple rows, hold down the “Ctrl” key (or Cmd ⌘ on Mac) and click on multiple row numbers.
Or you can click on the first row, then hold down “Ctrl” + Shift + Down Arrow key (or Cmd ⌘ + Shift + Down Arrow on Mac) to quickly select all until the last rows.
Step 3: Click on the “Format” menu at the top of the screen and select “Conditional formatting.”
From the Menu, select Format > Conditional Formatting. Or simply click on the paint bucket and select Conditional Formatting.
Step 4: Choose “Custom formula is”
In the “Conditional format rules” sidebar that opens up on the right-hand side of your screen, change the “Format cells if” dropdown menu to “Custom formula is.”
Step 5: Enter the formula, using the $ sign to lock your column reference
In the input box, enter the follow formula:
=ISNUMBER(SEARCH(“text”,$Cell_Ref))
Here’s what this formula does:
- SEARCH: It looks for the text within the content of cell A1. If the specific text is found, it returns the starting position of it within the text, and if not found, it returns an error.
- ISNUMBER: This checks whether the result of the SEARCH function is a number. If the SEARCH function found the text, it will return a number (the starting position), and ISNUMBER will return TRUE. If it is not found, it will return an error, and ISNUMBER will return FALSE.
So, the final result in the cell where you place this formula will be either TRUE if the text is found in cell A1 or FALSE if it’s not found.
In this example, my custom formula would be:
=ISNUMBER(SEARCH(“best”,$A2))
⭐️ Pro Tips:
As compared to looking up a specific text, using ISNUMBER and SEARCH functions is often more versatile and powerful because it allows variations and plural forms. So you don’t need to perform multiple searches or manually review the data.
Step 6: Choose the formatting style
Below the custom formula, you can click on the Formatting style and choose how you would like to format your row.
You should be able to see the “Preview” of the conditional formatting sidebar shows the rows you selected highlighted in the colour you chose.
Step 7: Click “Done” button
Click on the “Done” button at the bottom of the sidebar to apply the formatting to your selected rows.
Voilà! This is how you highlight entire rows with conditional formatting in Google Sheets!
Conclusion
Conditional formatting is a powerful feature in Google Sheets that can make working with large datasets much easier. Highlighting entire rows with conditional formatting is a great way to quickly spot trends and patterns in your data.
With the simple steps outlined in this blog post, you can now easily apply this formatting to highlight the row based on cell value in your own Google Sheets documents.
Give it a try today and see how it can streamline your data analysis
