Home Web AppsGoogle Sheets How to use Conditional Formatting in Google Sheets

How to use Conditional Formatting in Google Sheets

by Dan Millward
How to use conditional formatting in Google Sheets

Conditional formatting is a really useful tool as it allows you to immediately analyse data with visuals. When you have lots of data on a spreadsheet it can easily become a sheet of noise. Conditional formatting in Google Sheets helps you to make certain data highlighted, so you can easily see the data you need to be concerned about.

In this guide on how to use conditional formatting in Google Sheets, we’ll take an in-depth look at what conditional formatting is, why you may want to use it, and how to do conditional formatting in Google Sheets.

WHAT IS CONDITIONAL FORMATTING

Conditional formatting allows you to easily spot trends in data. This is really important if you have a lot of data to look at. However, it is not just useful for that. Conditional Formatting in Google Sheets is also very useful if you want to add rules to specific data.

By using conditional formatting it helps you to keep track of the data on the spreadsheet. It provides an immediate visual, which makes it much easier to spot trends or issues.

WHEN WOULD YOU USE CONDITIONAL FORMATTING

There is no specific rule on when you should use conditional formatting. However, conditional formatting is suitable in a situation where you need to be made aware of a situation.

For example, you may have a spreadsheet that keeps track of stock. The last thing you want when selling goods is to run out. So you may want to set-up a conditional formatting rule that gives you a clear indication of your stock levels. Doing this with conditional formatting will immediately highlight any stock issues you have.

HOW TO USE CONDITIONAL FORMATTING IN GOOGLE SHEETS

There are different ways to use conditional formatting and this will all depend on what you’re trying to achieve. We’ll now go over different ways of using conditional formatting in Google Sheets, so you can get a good understanding of how it works and how useful it can be in the real world.

Example 1 – Achieving Sales

In this example we’ll look at how using conditional formatting shows you whether a sales team are achieving their weekly targets. To use conditional formatting you need some rules to work with. In this situation, the rules would be linked to the number of sales in a week.

Below you can see the number of sales each salesperson have achieved during the week. The sales throughout the week are added up in Column G. Although, you can work with the data you have it is not immediately obvious who is underperforming and who is above target.

Number of sales in a week without conditional formatting

Number of sales in a week without conditional formatting

You need some rules

To make it easier to spot who is performing and who is underperforming you need some rules. Whenever you’re going to use conditional formatting in Google Sheets, you have to provide some data for the conditional formatting to work. Below we’ve got the number of sales that are expected and these rules can be used to create the conditional formatting.

  • 49 sales or below – Underperforming
  • 50 to 99 sales – Needs improvement
  • 100 sales or more – Hitting targets

Now you have the rules to work with you can use these rules to add conditional formatting to your spreadsheet. In the above spreadsheet, you can see a sales team, which shows there daily sales and weekly sales. The conditional formatting we will add to the spreadsheet will instantly show if each salesperson is achieving their weekly targets.

Below you can see the same spreadsheet, but with conditional formatting added to column G. You can immediately see how this is beneficial in seeing who is on target and who needs further assistance to achieve sales. Now we will go through each step needed to add the conditional formatting.

Google Sheets conditional formatting

Sales data after conditional formatting has been added

HOW TO ADD CONDITIONAL FORMATTING IN GOOGLE SHEETS

We can see from the spreadsheet above that the conditional formatting will be done in column G. This is the column that provides the data for the weekly sales.

To get the desired result we need to add three different conditional formatting rules.

Conditional formatting – Rule 1

  1. Highlight cells G2 to G9
  2. In the Format menu click on Conditional Formatting

To the right of the spreadsheet, you will see the Conditional Formatting menu appear, which will look similar to the one below.

Conditional Formatting Menu in Google Sheets

APPLY TO RANGE

You can see from the above image that the cells we highlighted are showing in the ‘Apply to range’ field. If we had not highlighted the cells first, you can easily select the cells by changing the cell range in this field.

FORMAT RULES

Now we need to apply the rules to make the conditional formatting show who is performing and underperforming.

  1. Change ‘format cells if’ to ‘less than or equal to’
  2. In the box below this enter the value ’49’
  3. Use the fill option in the ‘formatting style’ and change to a light shade of red

Your Conditional Formatting options should look similar to what you can see below:

Adding conditional formatting in Google Sheets

You will notice the conditional formatting has been applied to two of the cells where the total number of sales during the week are 49 sales or below.

Conditional Formatting – Rule 2

We now need to add the second rule to capture any salespeople falling into the ‘needs improvement’ category.

  1. Click on Add another rule
  2. Change ‘format cells if’ to ‘is between’
  3. Two fields will now appear below this instead of one
  4. In the first field enter 50
  5. In the second field enter 99
  6. Use the fill option to change the colour to a light shade of orange

Your conditional formatting menu should look similar to the below:

Adding a further conditional formatting rule in Google Sheets

You will notice the rule ‘is between’ provides you with two fields to enter data. The first field you enter the lowest number and in the second field, you enter the highest number. You will also notice this has now changed the data in the spreadsheet, so any cells in column G between 50 and 99 should now be highlighted in a shade of orange.

Conditional Formatting – Rule 3

We now need to add the final rule, which will complete all the conditional formatting needed.

  1. Click on Add another rule
  2. Change ‘format cells if’ to ‘greater than or equal to’
  3. In the value field type in ‘100’
  4. Using the fill option to change the colour to green

Your conditional formatting menu should look similar to below:

Adding the final conditional formatting rule in Google Sheets

That’s it – all rules have been added

You can now click on the ‘Done’ button as all the conditional formatting rules have been added.

You should now see that all of the data in column G has been highlighted depending on the rules we have set. This immediately shows the salespeople who are underperforming, needs improvement and who is on target.

This is just one example, but you can use conditional formatting for many different scenarios.

Related Articles