Home Google Conditional Formatting on Google Sheets: Everything You Need to Know

Conditional Formatting on Google Sheets: Everything You Need to Know

These days, a lot of people prefer Google Sheets to Microsoft Excel for many reasons like zero cost, multi-user access, cloud storage, and most importantly you can access spreadsheets on the go from your smartphone. In addition to being the #1 user-friendly spreadsheet tool, Google Sheets also offer a cool feature called Conditional Formatting that helps users to quickly analyze complex or lengthy sheets.

In this post, let’s take a look at some of the ways to use conditional formatting on Google Sheets.

What is Conditional Formatting

It is a feature that allows you to modify/change the aspect of a cell, based on a single or set of rules. You can change several aspects of a cell, like its background color, text type (Bold/Italic/Underline). Regarding rules, you can either use the existing ones available on Google Sheets or you can create your own rules as well.

Based on your requirements, you can apply conditional formatting to a single cell, group of cells or to an entire row/column.

Related: How to View and Remove Authorized Apps From Google Account

Rules – What Does it Mean

Before thinking of using conditional formatting, first, you need to understand the structure of rules. Every rule consists of the following three elements:

  • Range – Defines the scope of the rule. For example, let’s say that you would like to format cells from B2 to B22, based on a condition. In this case, the range is set to 20 cells.
  • Condition – Defines trigger events. In the example above, let’s assume that you need to format the cells if its value is greater than 10.
  • Style – Defines the style of the cell. Like, change the background color of cells to blue.

So? The rule for the above example is something like this: If any value of the cell from B2:B22 is greater than 10, then change its background color to blue.

How to Use Conditional Formatting on Google Sheets

Now, let’s see in detail the various types of conditional formatting that can be used on a cell, row or column in Google Sheet.

Conditional Formatting with Numbers

Let’s imagine that you are evaluating the monthly timesheet of someone in your company. And, you need to find out whether he/she has logged in for at least 7 hours on all working days for that month. The timesheet looks similar to the one below:

Google Sheet Sample Employee Monthly Timesheet

Let’s see how to use conditional formatting for this scenario.

  1. Open the timesheet file and navigate to Format->Conditional formatting.
  2. Here, you will see a new window titled Conditional format rules on the right side of your screen. By default, Single color tab will be selected:Google Sheet conditional formatting window
  3. In the drop-down box titled Apply to range,  click Table icon, select the range as C4:C33 and click OK.
  4. Under Format rules drop-down box, select Less than.
  5. Next, enter 7 in the box titled Value or formula.
  6. Then, under Formatting style, set Fill Color to your desired color. Instead of changing the background color of the cell, you can modify text color or set other formatting options like Bold, Italic, Underline and Strikethrough.Google sheet condition formatting numbers
  7. Finally, click Done.

Google sheet conditional formatting number result

Now, if you observe the above image, days for which working hours are less than 7 are highlighted (Column C).

For conditional formatting with numbers, you can use any of the conditions listed below:

  • Greater than
  • Greater than or equal to
  • Less than
  • Less than or equal to
  • Is equal to
  • Is not equal to
  • Is between
  • Is not between

Conditional Formatting with Text

Let’s say your employee database sheet has hundreds of entries and you wish to highlight employees residing in a particular location (eg. Virginia). You can easily do that by using Conditional Formatting with Text.

  1. Open the employee database file and navigate to Format->Conditional formatting and look for the window titled Conditional Formatting on the right side of your screen.
  2. In the drop-down box titled Apply to range,  click Table icon, select the range as D4:D104 and click OK.
  3. Under Format rules drop-down box, select Text contains and type Virginia in the box provided below.Google sheet conditional formatting text
  4. Then, under Formatting style, you can change either the text/background color of cell or other options like Bold, Italic, Underline or Strikethrough. In this example, Fill Color is set to Green.
  5. Click Done. Now, all employees who are located in Virginia are highlighted as given below:

Google sheet condition formatting numbers result

Other available options for conditional formatting with text are:

  • Text starts with
  • Text ends with
  • Text is exactly
  • Text contains
  • Text does not contain

Also read: What is Sharing Violation Error in Excel and How to Solve It

Conditional Formatting with Color Scale

Formatting with color scale will come in handy if you need to analyze data for levels like minimum, maximum and average. For example, a business seller can use this formula to quickly analyze the sales revenue in different locations. Let’s see how to do that.

  1. Open the file and navigate to Conditional Formatting window.
  2. Next, click tab titled Color scale.Google sheet conditional format color scale
  3. Then, select the range of cells for which you need to apply the rules.
  4. Here, you will see three drop-down boxes titled: Minpoint, Midpoint, Maxpoint. Based on your requirement you can select either Number, Percent or Percentile.
  5. In case you would like to set a specific value for Midpoint, Minpoint or Maxpoint, you can do that by entering the numbers in the corresponding boxes. In this example, we have selected 40 Percent for Midpoint.
  6. Next, select your desired colors for all the three checkpoints, if you don’t like the default color settings.
  7. Finally, click Done.

Google sheet conditional formatting color range result

More Conditional Formatting Options

Apart from number, text, color range, there are two more options available on the conditional formatting window:

Google sheet other conditonal formatting options

Is Empty/Is Not Empty

This is the simplest option in conditional formatting. If you want to highlight only a group of empty or non-empty cells, then you can use this feature. To locate this option, just click the drop-down box titled Format Cells in Conditional Formatting window.

Date

This formula will come in handy especially if you would like to compare your data over a specific range of dates or a single date by clicking the drop-down box titled Format cells if:

  • Date is
  • Date is before
  • Date is after

Once you select any of the above date options, you will see another drop-down box as shown below:

Google sheet conditional formatting date

Based on your requirement, you can select any of the options to create the date based rule.

Related: How to Create Google Forms Online for your Business?

How to Use Conditional Formatting Over an Entire Row in Google Sheet (Custom Formula)

So far, in all the examples mentioned above, single or multiple cells in a column were highlighted. But, what if you need to do conditional formatting of a row in Google Sheet instead of a cell?. Thanks to the Custom Formula option, you can easily do that.

Now, let’s consider the example mentioned in the section Conditional Formatting with Text. Instead of marking only the cell that contains text “Virginia”, let’s try to highlight the entire row.

  1. Navigate to Conditional Formatting window after opening your file.
  2. Then, you have to select the entire data set for range, since we need to apply conditional formatting over an entire now. In this example, we have selected range as A4:D20.
  3. Next, select the option Custom formula is, in the drop-down box titled Format rules.Google sheet conditional formatting custom formulas
  4. Here, you need to enter the formula as =$D4="Virginia" on the box provided below. Now, let’s try to understand how this custom formula works.= : Any custom formula should begin with this character.
    D4 : indicates your sample data. i.e D4 is the first cell of column D whose data is analyzed.
    =”Virginia”: defines the condition. Since we need to highlight rows of employees located in Virginia, = is used. In case you would like to mark employees who are not located in Virginia, you can use <>”Virginia”. Likewise, you can create conditions that involve numbers using symbols like >, <, >=, <=, etc.
  5. Finally, click Done.

Google sheet conditional formatting custom formula result

Did you see? Now, the entire row is highlighted instead of a single cell for the employees located in Virginia.

Well, we have explained just simple examples of using conditional formatting in Google sheets. Depending on your need, you can create any custom formula you like to get a quick analysis of a huge amount of data.

Disclaimer: MashTips is supported by its audience. When you purchase through links on our site, we may earn an affiliate commission at no extra cost to you.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Free Newsletter

Read More...

How to Enable Dark Mode for Google Photos App on Android

Google has slowly been getting all its apps Android Q ready by giving them dark modes. In a very Google-like fashion, there's not much...

How to Add Lock Screen Profile Picture to Android 10

Android OS always takes the cake when it comes to personalizing the smartphone. While Google always gives emphasis on the security aspects, it also provides...

How to Make Calls Direct from Google Chrome

It wouldn't be wrong to term today's generation as the texting generation. Even the older generations are texting more and more. However,...

How to Run Linux (Including Ubuntu) On Windows

Wondering whether you should switch to Linux? These days, you don't have to give up Windows to use Linux. In fact, you can run...

How to Fix Chrome’s Continue Where You Left off Feature Not Working

There is a handy and useful feature in Chrome called 'Continue where you left off'. What it does is save your work, that is...

How to Get Sound Measurement with Android Decibel Meter Apps

Have you ever been in a very quiet environment and wondered what the decibel numbers the place must have? Of course, you haven't. But...