If you need to isolate data in Google Sheets based on certain criteria in a cell, you can use conditional formatting to highlight entire rows in your spreadsheet. Here’s how to do it.
Fire up your browser, go to Google Sheets, and open the spreadsheet with the datasheet you want to apply conditional formatting to highlight specific rows.
RELATED:Beginner’s Guide to Google Sheets
Select all the cells inside the table and click Format > Conditional Formatting in the toolbar.
In the panel that opens on the right, click the drop-down menu under «Format Cells If» and select «Custom Formula».
In the Value or Formula text box that appears, enter a formula to highlight the specific data you want to highlight in that table. For our table, we will use a formula that highlights the entire row if the production year is before 1980. It looks like this: =$D3<1980
The first part of the formula (=$D3) tells Sheets that we want to start exploring data from cell D3. The dollar sign at the beginning is required and indicates that the column (D) is fixed, but the row (3) is flexible. This allows the formula to validate the data in the entire column.
The second part (<1980) of the formula is the condition that the data must meet in order to return True. For our example, we're just looking for films released before 1980.
Note. If you want your result to include the entered date, type <=1980 to return also all data released in 1980.
Then select the type of formatting you want to apply when the conditions are met. You can apply Bold, Italic, Underline, Strikethrough, Font Color, or Cell Color to your results. By default, each row is filled with a light green color.
After you choose how rows will be displayed when your formula finds a match, click Finish. You can close the panel or create another rule to also apply conditional formatting.
And just like that, the rows of films released before 1980 are highlighted in green.
Although this is a simple formula example, there are no restrictions on how you can use this extremely useful tool. You can even use advanced formulas and functions to match data in a table.
For example, if we want to find every director named "George", we can use the function REGEXMATCH and a short regex to do it. It will look something like this: =REGEXMATCH($C3, "\AGeorge\s*([^\n\r]*)")
This is it! Using conditional formatting in Google Sheets, you searched for specific columns of data and then highlighted the entire row with a custom formula.
It's the perfect tool for creating complex spreadsheets with beautifully formatted data that grab everyone's attention.
