How to use conditional formatting in Google Sheets

Conditional formatting allows you to automatically add color and style to Google Sheets to make it easier to analyze data on it.

• Google Sheets - Google Sheets
• Google Sheets for iOS
• Google Sheets for Android

Conditional formatting is when you automatically format cell styles in a spreadsheet based on the data in each cell. Google Sheets allows you to use conditional formatting to apply different font styles, fill colors, and styles to make spreadsheets easier to read.

All you need to do to use conditional formatting is to create some rules for the spreadsheet as shown below.

How to use conditional formatting in Google Sheets

• When to use conditional formatting?
• How to create conditional formatting in Google Sheets
• Google Sheets Format Types Supported
  • Text
  • Dates
  • Numbers
• Customizable conditional formulas
• Formatting styles supported in Google Sheets
• Conditional formatting with color scale

When to use conditional formatting?

Google Sheets conditional formatting can be useful in a lot of situations. The biggest benefit is that it saves you time and ensures consistency, and it also encourages you to set up and style your spreadsheet in advance.

You can use conditional formatting to distinguish the data types in a set (for example, grouping employees by department). You can also use it to draw attention to values ​​that need to be addressed like negative returns. Occasionally, it also helps to reduce obsolete data records such as past operations.

How to create conditional formatting in Google Sheets

The following simple example will help you understand how conditional formatting in Google Sheets works. Start with a new spreadsheet and follow these steps:

1. Enter some sample values ​​in some rows.

 

2. Highlight the range of cells you want for conditional formatting.

3. From the Format menu , select Conditional formating .

 

4. Change Format cells if… from the drop-down menu to Is equal to .

5. Enter one of the example values ​​in the Value or formula box .

6. Select the format you want to add as cell fill color.

 

7. Click Add another rule .

8. Replace the previous value with another value in the spreadsheet and choose a different color and format for that value.

9. Repeat the previous two steps to see more values. Each time, use a different format.

10. Once done, click the Done button to see a list of the rules you have created.

 

Google Sheets Format Types Supported

Google Sheets supports a wide range of conditional formats for users to choose from. They are built around three main data types: text, date, and number.

Text

The simplest condition for text, empty , depends on whether the cell contains any value or not.

To check for the existence of a piece of content, use contains . Can this condition check for a certain portion of text or use pattern matching per ? stands for any character and * stands for 0 or more other characters.

Finally, for more structured text combinations, starts with , ends with or exactly narrow down the possible combinations.

Dates

Dates are easy to use although they support some predefined values ​​beyond what the user chooses. Use conditional formatting to find the date before, after, or equal to the relevant date selection (tomorrow, previous year). You can also compare them with a selected date.

Numbers

Finally, you can use conditional formatting to find numbers that are equivalent, greater than, less than, or in the middle of a range of other numbers.

Customizable conditional formatting formulas

Custom formulas make conditional formatting more powerful because they can highlight the entire range, even cell references outside the formatted range. For better understanding, refer to the example below.

Create a table with 2 columns in Google Sheets. To highlight an entire row in the data that has more than 4 fruit items in stock:

Select the data range (A2:B5) .

Open the Conditional format rules window .

Select Custom formula is as the condition.

In Value , enter =$B2>4 .

For a normal formula in a cell, it must start with an = sign . The $ sign before column B makes it an absolute reference, so this comparison always refers to data from the given column. Row (2) is relative to the first row of the data range. So, for each row, the comparison takes place with each value in the same row.

Formatting styles supported in Google Sheets

You can select a limited set of preset styles in Google Sheets by clicking the Default text in Formatting style .

Besides, although the formatting options are quite limited, they serve the most practical purposes.

First, you can configure any of the standard font style combinations: bold, italic, underline, and strikethrough. In particular, they are also helpful in indicating valid or invalid values.

Another style you can apply regarding color: both text (front) and fill. Standard color pickers are also available, including customizing the color you want.

Conditional formatting with color scale

One method of visualizing numeric values ​​is the color scale. It will help you to assign colors from the palette according to the value associated with each cell. The color will be applied to the background color - Fill color .

For example, this trick is often used in graphs and maps. A darker color may indicate a lower value.

Google Sheets' implementation is limited but very easy to use. Select the number range but this time change the tab near the top of the Conditional format rules from Single color to Color scale .

In Format rules , you can define the nature of the color scale. Click Preview to view and select predefined color palettes. You can customize that color scale by choosing different values ​​or percentages to represent the lowest color score, maximum color score and neutral color score. The color pickers next to each point give you more control over the palette.

Above is how to do conditional formatting in Google Sheets. Hope the article is useful to you.