How To Add Conditional Formatting To Multiple Sheets Google Drive

Provisional formatting in Google Sheets allows you to apply automated formatting to cells in spreadsheets that meet sure criteria. Check out some practical examples and master conditional formatting in Google Sheets.

Conditional formatting in Google Sheets makes it easy for you to highlight specific cells that meet a specific criteria. For instance, you can change the background color of a jail cell to yellow if the cell value is less than a sure number. Or y'all can choose to highlight an unabridged row or column if sure conditions are met.

Highlight Individual Cells

For this example, we have a sales nautical chart that lists the names of salespeople, their state and the full sales target. We would like to highlight individual cells in the State column if the salesperson is from California.

Go to the Format carte du jour, choose Conditional Formatting, and click Add Condition. Hither, cull the range equally B2:B and the format condition as Text is Exactly. And then, enter the text CA in the text box, cull a custom background colour and click Done.

Highlight Cells in Google Sheets

Highlight Entire Row

For the same Sales nautical chart, nosotros would at present like to highlight unabridged rows where the sales target is more than than $8,000.

Inside the formatting dominion, prepare the range as A2:C since we would like to apply formatting to the unabridged table. Side by side, cull Custom Formula is for the formatting rules condition and set the criteria as =$C2>8000.

If you would like to highlight rows where the sales target is within a range, say between $5000 and $7000, you can add the =ISBETWEEN($C2, 5000,7000) formula in the criteria box.

Highlight Rows in Google Sheets

The $ in $C2 applies the formula to the unabridged cavalcade C while the missing $ in front of the number 2 allows it to increment.

If y'all desire to highlight rows where the sales target is more than the boilerplate sales target, you tin can either utilise =IF(AVERAGE($C2:C)<$C2,ane) or =$C2>average($C2:C) formula in the criteria box.

If you wish to highlight a row that contains the maximum value for sales, you tin use the =MAX() formula in the criteria box.

Also see: Highlight Indistinguishable Rows in Google Sheets

Formatting based on ii cells

In the aforementioned Sales table, we would like to highlight salespersons who are responsible for a specific state (say, "CA") and who have a sales target of more than $5,000.

We can achieve this by applying multiple conditions using the AND function as shown below:

Multiple Criteria Formatting

Conditional Formatting base on Date

Our table has a list of invoice and the date when the invoice is due. Nosotros'll apply conditional formatting to highlight invoices that are by due for more than xxx days and send them e-mail reminders.

Invoice Due

In some other example, we have a list of students and their appointment of nascence. We can use Date functions similar to highlight students who are older than 16 years erstwhile and whose date of nativity is in the current calendar month.

            =AND(Yr(TODAY())-Twelvemonth($B2)>=sixteen,MONTH($B2)=Month(TODAY()))

Heatmaps - Format Cells by Color Scale

Our next workbook contains a listing of U.s. cities and their average temperatures for various months. Nosotros tin apply Colour Scales to hands understand the temperature trends across cities. The higher values of the temperature are more red in colour and the lower values are more green in colour.

Temperature Color Scale

Mark Rows Containing one of the values

With conditional formatting in Google Sheets, you can easily highlight rows that contain a specific value. For example, y'all can highlight all rows that contain the value CA in the State column.

However, if you want to highlight rows that contain one of multiple values, you tin can either use the OR function or, amend still, use Regular Expressions with the custom formula.

This formula will highlight all rows that contain either CA or NY or FL in the State column.

            =REGEXMATCH(UPPER($B:$B), "^(CA|NY|FL)$")

RegEx Conditional Formatting

Alternatively, you may take a list of states listed in some other canvas and use Lucifer with INDIRECT to highlight rows that comprise ane of united states.

            =MATCH($B1, INDIRECT("'Listing of States'!A1:A"),0)

Apply Conditional Formatting to Entire Column

Until now, nosotros have explored examples of highlighting individual cells or entire rows when certain conditions are satisfied. However, you can use provisional formatting to highlight unabridged columns of a Google Sail.

In this example, we take sales for different years per geographic region. When the user enters the year in cell A9, the respective column is highlighted in the sales table. The custom formula will be =B$i=$A$9. Notice that the $ is used with the number in the cell reference since the check is made just in the offset row.

Conditional Formatting Column in Google Sheets

Conditional Formatting with Google Apps Script

If yous were to apply the aforementioned provisional rules to multiple Google Spreadsheets in one go, it is recommended that yous automate Google Apps Script else it will have more time to apply the formatting manually.

                          const              applyConditionalFormatting              =              (              )              =>              {              const              sheet              =              SpreadsheetApp.              getActiveSheet              (              )              ;              const              colour              =              SpreadsheetApp.              newColor              (              )              .              setThemeColor              (SpreadsheetApp.ThemeColorType.              BACKGROUND              )              .              build              (              )              ;              const              rule1              =              SpreadsheetApp.              newConditionalFormatRule              (              )              .              setRanges              (              [canvass.              getRange              (              'B:B'              )              ]              )              .              whenTextEqualTo              (              'CA'              )              .              setUnderline              (              true              )              .              setBold              (              true              )              .              setBackground              (color)              .              build              (              )              ;              const              rule2              =              SpreadsheetApp.              newConditionalFormatRule              (              )              .              setRanges              (              [sail.              getRange              (              'A1:C15'              )              ]              )              .              whenFormulaSatisfied              (              '=$C1>5000'              )              .              setBackground              (              'green'              )              .              setFontColor              (              '#00FF00'              )              .              build              (              )              ;              const              conditionalFormatRules              =              sail.              getConditionalFormatRules              (              )              ;              conditionalFormatRules.              push              (rule1)              ;              conditionalFormatRules.              button              (rule2)              ;              sail.              setConditionalFormatRules              (conditionalFormatRules)              ;              }              ;

Please check the documentation of ConditionalFormatRuleBuilder for more than details. This will also help you re-create conditional formatting rules from one spreadsheet to some other.