قالب وردپرس درنا توس
Home / Tips and Tricks / How to mark a line in Google Sheets using conditional formatting

How to mark a line in Google Sheets using conditional formatting



  Microsoft Excel Logo

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.

 Select the information in the table, click Format, and then click

In the right pane, click the drop-down menu under "Format Cells If" and select Custom Formula is ".

 Click the drop-down menu and choose

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

 Enter your formula you want to use to search for data. Be sure to use the dollar sign before the column letter. This ensures that the formula analyzes only the specified column.

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.

 After adjusting the formatting style, click

. And that's exactly how the series of films published before 1980 is highlighted in green.

 The result shows that only films released after 1980 were dyed with a green background.

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] *)")

 An example of an advanced formula that everyone uses Director is found the first name


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.


Source link