How to Use Conditional Formatting in Google Sheets

Spreadsheets have a place in many workflows. Whether you're using them to make calculations, analyze data, or simply type project data in a structured format, you probably have already used a spreadsheet app.

But one reason that spreadsheets seem intimidating is that they can seem kind of bland. Huge rows and columns of black text on white backgrounds can make it difficult to glance at data and find meaning in it. That's why we add formatting to spreadsheets to help make the format more useful.

Conditional formatting helps us adjust formatting based on the contents of a cell. For example, a cell could show with red text if it's below a certain value, and green if it's above a certain value. Adding these conditional formats will keep the spreadsheet readable and adaptable, even as the data changes.

You can use this tutorial as a complete guide to using Google Sheets conditional formatting. You'll learn to add styles that adjust on-the-fly to the data that's inside them. Let's learn more.

Conditional Formatting in Google Sheets (Watch & Learn)

In the screencast below, I'll show you several examples of using Google Sheets conditional formatting, a technique that changes the format of a cell based on what's inside of it. You'll learn that conditional formats automatically update if and when the data changes.

You'll see that Google Sheets conditional formatting make spreadsheets more intuitive. Even as data updates, your formatting will adjust dynamically. Read the rest of the tutorial for a step-by-step guide.

Formatting in Google Sheets

Before we dive into creating conditional formats, let's first talk about the purpose of adding basic formatting to a spreadsheet.

Big walls of data are hard to browse through and understand without any formatting. We add formatting to a spreadsheet so that it's easier to understand the data inside of it. 

Simple formatting tweaks like bolding column headers can help you visually see where the data starts and ends. Other tools like borders, font sizes, and formatting styles are all crucial to make it easier to understand data. 

Adding formatting in Google Sheets is all about adding meaning to your spreadsheets. If you don't have any experience formatting your Google Sheets, check out the guide below. It'll help you.

• 
  Google Sheets
  
  
  How to Edit & Format Cells in Google Sheets Spreadsheets
  
  
  Andrew Childress

1. Create a Google Sheets Conditional Format

Adding Google Sheets conditional formatting is all about adding meaning to your spreadsheets. 

Picture a scenario where you're preparing reports for members of your team or supervisor. This is certainly one of the most popular use-cases for spreadsheet apps like Google Sheets: to take data, find meaning, and pass it off to those that use it.

Many times, the users of this data are limited in their time to review it. In many large organizations, users receive so many reports and presentations that the time spent reviewing each one is minimal.

That's why each report has to be meaningful. And meaningful spreadsheets contain formatting that helps the reader understand the data. Let's add conditional formats to a spreadsheet in Google Sheets.

1. How to Add Conditional Formatting in Google Sheets

To add Google Sheets conditional formatting, it helps to have some sample data already in your spreadsheet. For my example, I'm going to use some numeric data in a table that I can apply conditional formatting to.

Get started by highlighting the data that you want to add conditional formatting to. Then, go to the Format menu in Sheets and choose Conditional Formatting.

On the right side of Sheets, you'll see a panel open that's titled Conditional Format Rules. This panel drives how conditional formats are applied to cells in Google Sheets. As part of conditions, each row contains the logic that's needed to decide how to treat each cell based on its contents.

2. How to Format Cells in Google Sheets Using Conditions

Let's apply our first conditional formatting in Google Sheets. Start off by opening a spreadsheet in Google Sheets, and highlighting a list of data that you want to add custom formatting to. Open the Format > Conditional Formatting menu. This opens the rules panel, ready to adjust the data inside your selection.

The first dropdown that you should adjust is the Format Cells if... dropdown. This is the logic that Sheets depends upon for applying cell formatting. Click on this dropdown in order to see all of the rules that you can apply to conditional formatting in Google Sheets.

For our first example, let's pick Greater Than from the dropdown options. You can input a value, such as 1000, to check each cell for. In the screenshot below, review the example of the first conditional format rule that I've built.

Then, you need to add the cell format that Sheets will apply. Remember, you're adding it to the condition, so this is the format that'll only show when the condition is met. 

Let's set this to show red if the values are less than 500. We'll need to use the Add another rule button to set this second rules. Then, use the Less Than option, input 500, and choose a red background for those cells. See an illustration of this Google spreadsheet conditional formatting in the screenshot below.

Now, each cell has two rules:

• Light up green if the value inside of it is more than $1,000


• Light up red if the value inside of it is less than $500

These two rules aren't in conflict, because a cell could never meet both of the rules at the same time. You can continue to add other conditional formatting rules to check for other data inputs as needed.

One option that you should also check are the cells that the conditional format applies to. In the Apply to Range box, you can click on the grid icon to re-select the cells that your conditional formatting applies to. You can easily extend the range of cells that your conditional formatting impacts using this easy option.

Even though this is just one example, the skills you've learned have laid the groundwork for any conditional formats that you want to apply in the future. Keep adding other rules to customize the way that formatting shows based on cell values.

As you're building rules, make sure to check out other options from the Format cells if... dropdown. You can choose other data checks like looking for specific text, certain dates, or ranges of values.

2. Google Sheets Relative Conditional Formatting

One of my favorite features of conditional formatting in Google Sheets is to apply Relative Conditional Formatting to cells. Google Sheets simply calls this Color Scale on the Conditional Format Rules panel that we've been working in throughout this tutorial.

The usefulness of the Color Scale formatting option is that it's dynamic and updates based on the data in the selection. For example, you could apply Color Scale formatting to a list of data and the formatting would light up based on all of the values in the selection. The highest values could show as dark green, while low values could show in red. Let's walk through an example.

With some data already selected, switch to the Color Scale section in the rules panel. Then, use one of the dropdown options from the Preview dropdown to apply a color scale to the data.

As you can see in the examples below, Sheets applied a spectrum of colors. The values were "scored" on a basis relative to other data in the same selection.

That's it! Sheets automatically analyzes the data that's in your selection and it applies the formatting on a relative basis. It'll re-adjust as the values in the column change.

Recap & Keep Learning

No matter what types of project you're working on or what data you're working with, a spreadsheet can be the perfect tool to wrangle your data and find meaning in it. With a combination of formats and conditional formatting in Google Sheets, you can add meaning that makes a report intuitive to the recipient.

Check out the tutorials below for more guides to using Google Sheets to build great-looking spreadsheets:

• 
  Google Sheets
  
  
  20 Awesome Google Sheets (Spreadsheet) Tips & Tricks
  
  
  Andrew Childress


• 
  Spreadsheets
  
  
  How to Use Google Sheets to Make Your First Spreadsheet
  
  
  Andrew Childress


• 
  Google Sheets
  
  
  18+ Best Google Sheets Spreadsheet Tutorials for 2018
  
  
  Andrew Childress


• 
  Google Sheets
  
  
  20 Free Google Sheets Business Templates to Use in 2018
  
  
  Andrew Childress

What do you want to learn in Google Sheets? Or, how do you use conditional formatting in your favorite spreadsheet app? Let me know in the comments section below.