If you need to isolate data in Google Sheets by criteria in a cell, you can use conditional formatting to highlight entire rows in your table. Do the following:
Start your browser, go to Google Sheets, and open a spreadsheet with a data table to which you want to apply conditional formatting to highlight specific rows.
RELATED: The Beginner's Guide to Google Sheets
Select all cells in the table, and then click Format> Conditional Formatting on the toolbar.
In the right pane, click the drop-down menu under "Format Cells If" and select Custom Formula is ".
In the text box labeled Value or Formula, enter the formula to highlight the specific data you want to isolate in this table. For our spreadsheet we use a formula that highlights an entire line if the year of publication is before 1980. It looks like this:
= $ D3 <1980
The first part of the formula (= $ D3) tells Sheets that the data from cell D3 should be examined. The dollar sign at the beginning is mandatory, stating that column (D) is fixed, but row (3) is flexible. In this way, the formula can check the data in the entire column.
The second part (<1980) of the formula is the condition the data must satisfy to return true. In our example, we are only looking for movies that were released before 1980.
Note: If your result should contain the date you entered, enter
<= 1980 . Then select the type of formatting you want to apply when the conditions are met. You can specify your results in bold, italics, underline, strikeout, font color, or cell color. By default, each row fills with a light green color.
After determining how to display the rows, if your formula finds a match, click Done. You can close the panel or create another rule to apply the formatting formatting as well.
. And that's exactly how the series of films published before 1980 is highlighted in green.
Although this is a simple example of a formula, there are no limits to using this very helpful tool. You can even use advanced formulas and functions to match data in the table.
For example, if we want to search every director by the first name "George", we can use the function
REGEXMATCH and a short regular expressions to do just that. It would look something like this:
= REGEXMATCH ($ C3, " AGeorge s * ([^nr] *)")
That was & # 39; s! Using conditional formatting in Google Sheets, you searched for specific columns of data and then highlighted the entire row using a custom formula.
This is the perfect tool to create complex spreadsheets with nicely formatted data that capture all caution.