How to Use Conditional Formatting in Google Sheets

When sifting through a large data set in Google Sheets, you may find it useful to add color or other formatting to visually identify certain information. If you want to automatically change the format of cells when they meet particular conditions, conditional formatting is your best bet.

Conditional formatting automatically formats cells with color or text styling if they meet a predefined criteria, or rule, set by the user. 

Learn how to apply conditional formatting in Google Sheets and troubleshoot common errors.

How to do conditional formatting in Google Sheets

Here's a quick summary of the steps you'll take to apply conditional formatting:

1. Select the range you want to format. 

2. Go to Format in the top menu. Select Conditional formatting. 

3. Select either Single color or Color scale. 

4. Apply a formatting rule (or rules) and a formatting style. 

5. Click Done. 

Let's take a closer look at each step.

Ready to deepen your knowledge of Google Sheets conditional formatting?

To begin, you'll need your tab open to your spreadsheet. If you’re not already working with your own data set and want to follow along with our examples, make a copy of this template to practice. 

1. Select the range you want to format. 

The first step is to select the data range you would like to format. A range can consist of a single cell or multiple adjacent cells. 

To select your range, click the first cell (top-left) in the range, hold the Shift key, then click the last cell (bottom-right) in the range. Alternatively, you can click the first cell in your range and drag your cursor to the last cell of your range. Your range is now highlighted. In the example below, the range in column F (“Box office earnings”) is highlighted, so the financial data contained within it can be selected for conditional formatting. Note that the highlighted range is also displayed in the upper left corner of the sheet, where “F2:F47” is displayed.

2. Click 'Format,' then 'Conditional formatting.'

Once you’ve selected your data range, click Format in the top menu. Then select Conditional formatting.

3. Select either 'Single color' or 'Color scale.' 

Once you’ve clicked Conditional formatting, a menu will appear along the right side of the spreadsheet. You can select from two types of conditional formatting: single color and color scale. Pick the option that works for your needs: 

• Single color applies one color or format to cells that meet a specific condition, such as applying red to any cell containing a number below zero. You can also add multiple formatting rules to your data range, so that other colors are applied to cells in the same range that meet different conditions. For example, you can apply green to any cell containing a number above zero.

• Color scale applies a color gradient to your data range, so that specific colors are associated with the maximum, minimum, and midpoint of your range. The numbers between these three values will be a gradient color. For example, you may apply a color scale that colors maximum values green, minimum values red, and the midpoint white, so you can identify financial figures that fall below or above expectations. In color scale, you can apply multiple formatting rules to your range, but usually only one formatting rule is required as all cells within the range will be colored by a gradient. 

4. Apply a formatting rule (or rules) and the formatting style. 

The formatting conditions for Single color and Color scale are slightly different from one another. Learn what you need to know about each one:

'Color scale' formatting rules

Color scale will apply a color gradient to each of the cells contained within your range as specified by the criteria for the applied rule.

In the Color scale menu, you can control the range to which you’ll apply conditional formatting, the color gradient that will be applied to the range, the value types that will be formatted, and the minimum, maximum, and midpoint values that will be formatted. 

• Apply to range allows you to select a data range to which you will apply the conditional formatting rule. If you didn’t select the range in the previous step, this is another way to do so. 

• Format rules is where you will select the specific rule you will apply to your data range. Select either a preset color scale or compose your own, and preview what the gradient will look like when applied to your range.

• Below the preview, you’ll be able to set the minimum, middle, and maximum values to which you’ll be applying your formatting rule, as well as the color you’d like applied to each. These are called the Minpoint, Midpoint, and Maxpoint, and each can be set as either a value, number, percent, or percentile. 

In the image below, the range selected is F2:F47, the preview shows a red to green gradient, and the minpoint, midpoint, and maxpoint have all been specified as numbers within the range. The lowest value will be red, the middle value will be white, and the maximum value will be green.

'Single color' formatting rules

Single color applies a single color to any cell that meets the condition specified by a rule.

In Single color, you can specify the range to which you’d like the formatting rule applied, the rule itself, and the format for cells that meet the conditions set by the rule.

• Apply to range allows you to specify the range to which you’ll apply the rule.

• The Format rules section contains a subsection called Format cells if…, which allows you to choose the condition that a cell must meet for the formatting to be applied. In total, you can apply nineteen rules  to your data range.

• The Formatting style subsection allows you to pick the format of the cell that meets the condition, including its color or whether you’d like it to be in bold, italics, underlined, or struck-through. 

In the example below, the range selected is F2:F47, the selected rule indicates that formatting is only applied to cells containing a number greater than $6,000,000,000, and the formatting style is just the color green. This means the color green will appear on any cell within the range containing a number greater than $6,000,000,000. 

5. Click 'Done.' 

Once you’ve selected the rules and formatting you’d like applied to your data range, click Done. Conditional formatting will now be applied to the range you selected. If you would like to add another rule to your data range, select + Add another rule and repeat the process. If you’d like to delete the conditional formatting, click the trash bin beside the rule.

Google sheets conditional formatting rules glossary 

You can use 19 different rules within single-color conditional formatting. If you’re wondering which one to choose, consider what each one does:

• Is empty: The cell contains nothing. 

• Is not empty: The cell contains something.

• Text contains: The cell contains text with a specified element (e.g., text contains “sold”).

• Text does not contain: The cell contains text without a specified element (e.g., text does not contain “sold”). 

• Text starts with: The cell contains text that starts with a specified element (e.g., text starts with “Tuesday”).

• Text ends with: The cell contains text that ends with a specified element (e.g., text ends with “ready for processing”). 

• Text is exactly: The cell contains specific text (e.g. text is exactly “processing”). 

• Date is: The cell contains a specified date (e.g., 11/27/1992). 

• Date is before: The cell contains a date before a specified date (e.g., before 12/04/1991).

• Date is after: The cell contains a date after a specified date (e.g., after 05/27/2016).

• Greater than: The cell contains a value greater than a specified value (e.g., greater than 42). 

• Greater than or equal to: The cell contains a value greater than or equal to a specified value (e.g., greater than or equal to $123,456,789.00). 

• Is equal to: The cell contains a value that is exactly equal to a specified value (e.g., is equal to $123,456,789.00). 

• Is not equal to: The cell contains a value that is not equal to a specified value (e.g., is not equal to 42). 

• Is between: The cell contains a value between two other values (e.g., is between 1 and 100). 

• Is not between: The cell contains a value that is not between two specified values (e.g., is not between 30 and 70). 

• Custom formula is: This option allows you to input a custom formula that describes your rule. 

Conditional formatting based on another cell

Sometimes, you may want to format a range based on the value contained within a single cell, such as when you want to identify values within a column that are greater than, equal to, or less than a number contained within another cell. Take these steps: 

1. Highlight your data range.

2. Click Format > Conditional formatting. 

3. Select Single color. 

4. Under Format rules, select Custom formula is as the applied rule. 

5. Input your desired formula. Some formulas you might use, include: =$[cell]>=$[cell$] =Not(ISBLANK([cell])) =$[cell]=”[text]”

6. Select the formatting style you’d like applied to the cells that meet the rule’s condition. 

7. Click Done. 

Want to use conditional formatting to highlight duplicates?

Learn how to highlight duplicates in Google Sheets using the steps above with a custom formula.

Troubleshooting

One common error when using conditional formatting is your rule not applying to all the cells you want it to.

To troubleshoot, check the range in Apply to range and make sure you have the cells you want formatted within them. The range must be separated by a colon, such as A1:A15. In this example, A1 is the first cell of column A, A15 is the fifteenth cell of column A, and the colon denotes that you are specifying a range between the two. 

Learn more Google Sheets and data analytics on Coursera

Conditional formatting in Google Sheets is just one example of the ways you can manipulate data. Interested in strengthening your abilities to work with data using Google Sheets? Enroll in the Google Data Analytics Professional Certificate. You’ll learn more about spreadsheets and other key analysis tools.