Conditional Formatting in Spreadsheets

Note: If you would like to learn about pivot tables in spreadsheets or graphs in spreadsheets, please feel free to check out both the tutorials.

Introduction

A spreadsheet is a great tool that provides you with a wide variety of functionality to handle and store your data. You can use pivot tables to organize and analyze your data or use graphs in spreadsheets to visualize your data. Similarly, you can use Conditional Formatting functionality of spreadsheets to highlight the data with the help of various rules and styles.

Conditional formatting in spreadsheets allows you to highlight cells based on specific criteria which can further help you to glance at your data in a better way and make it more realistic for humans to read it.

But what is Conditional Formatting? Let's find out.

Conditional Formatting allows you to change the characteristics of the cell, like background color, style of text based on rules you set, automatically. You do not have to set the conditions manually which can save a lot of your time. The conditions are based on an if/then statement.

For example: If a particular column or cell, values are higher than 315, then change the background color of those cell/cells to red.

There are three critical components in Conditional Formatting in Spreadsheets namely Range, Format Rules and Formatting Style and you will be learning about them in detail.

You will learn all of this in great detail with examples taken from the dataset, but before that let's quickly understand the data you will be using in today's tutorial!

About the Data

You will start with a dataset that is organized into a row and column format. This particular dataset shows the top 10 companies in the Fortune Global 500 in 2017 which is based on the highest gross revenues column.

You will learn to apply conditional formatting on the above dataset and understand various components of Conditional formatting in Google Spreadsheets which will allow you to gain insights from the data by highlighting the cells.

Note: Please make sure you have a valid Gmail account, once you have an account all you need to do is click on this link and create a new blank spreadsheet.

The data is available here.

So, without any further ado, let's learn about Conditional Formatting in Google Spreadsheets!

Conditional Formatting

Now comes the exciting part, let's find out how you can apply conditional formatting in google spreadsheets.

You first select the data you would like conditional formatting to be applied to, which is nothing but the Range; it can be done both manually or automatically. For now, you will select one cell which has no data in it.

Then you click on Format tab and select Conditional Formatting. This will tell the spreadsheet to activate conditional formatting on the cell specified earlier as a range. You can change the range later also.

Next, you select the Format cells if.. under Format rules which for now you will keep as is empty. Format Rule is nothing but an event based on which the cells are highlighted. It is very similar to an if-else condition in programming.

Finally, you select a Format style which can be both default styles and custom styles and click Done.

In the next few figures, you will be learning to use all the terms mentioned above on data in spreadsheets.

In the above figure, the selected Range is an empty G6 cell on which the conditional formatting will be applied.

Apply to Range

The range allows you to select a subset of data by either selecting the subset manually or automatically. When you have ample amount of data, manually selecting the subset can be a hassle and to ease things out you can choose cell number start and end which will automatically select the subset of the data.

• Next what you do is select the complete data and then click on Format tab and select Conditional Formatting, but this time you will select format rules such as Is not empty since the selected region is not empty.

You can see from the above figure that since you selected the complete data, i.e., A2: F11 range, you now have all the data background colored.

Selecting a range manually and then applying conditional formatting is the way to go when you have a small amount of data much like the one you are using in this tutorial.

However, what if you have an ample amount of data? Let's find out!

Applying Conditional Formatting on a large amount of data

It's pretty simple when you have ample amount of data, you select Conditional formatting and then under the Apply to range tab specify the cells on which you want to apply conditional formatting.

• If you want to apply conditional formatting on a single cell, you can just out the tag of the cell under the Apply to range tab, for example: B6.

• If you want to apply conditional formatting to an extensive range of cells, you can specify the tag of the first and the last cell separated by a colon, for example: A5: A11.

• You can select multiple ranges by clicking the icon to the right of the range tab and selecting Add another range.

From the above figure, you can observe that the selected data range is B5:E11 and hence, conditional formatting has been applied to only that region. Similarly, you can click on Add another range to select multiple ranges.

Next, let's move on to Formatting Style and explore it.

Formatting Style

Formatting style is pretty self-intuitive since it's similar to selecting a style for anything else in a spreadsheet.

You can select the style in two ways:

• Default: Under Formatting style select the Default option, and you will see six style options, and you can choose any one of them. In this tutorial, we will be using the default style, which is turning the background of a cell to a seafoam green color.

• Custom: If the style you are looking for is not in the default options you can create a custom style for your data. Creating a custom style is very similar to setting a style for anything else in Google spreadsheets, Google presentation or google document. You can make the text bold, italicize it, underline, change the font and fill the background with different colors.

Feel free to play around with different Formatting style since it can be useful when you want to set different rules within your dataset, setting a different style for a different rule can be a nice distinguishing and highlighting factor of your data.

Let's move on to the final segment of this tutorial which is Format rules and explore it in a bit detail.

Format Rules

Format rules are like the heart of conditional formatting since it acts as a trigger for conditional formatting.

• We have already looked at two format rules i.e.is empty and is not empty here. To reiterate over it once again, selecting is empty will apply conditional formatting only on those cells which have null values (empty) and similarly, selecting is not empty will apply conditioning formatting only on those cells which do not have null values (not empty).

Note: If you want to remove your conditional formatting from any cell/cells, click into a cell/cells with a rule applied, open the conditional formatting toolbar, and then click the trash icon next to the rule to remove the rule.

• Text: Formatting based on the text will change the cell depending on what text you type into it. There are various text-based formatting that you can use:

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

Let's select the entire Headquarters column and click on Format tab and select Conditional formatting. By default, it will apply conditional formatting on the complete column.

Then in the Format rules under Format cells if... click on the drop-down menu, and select Text contains which will then let you put the text.

Note: The text you enter is not case-sensitive.

Let's select China as the text, which means that only those cells which have Headquarters as China will be selected.

As you can see that only the cells that have china as their headquarters were selected. Isn't that amazing and how simple it is.

• Numbers: Formatting based on numbers can be done in various ways:

- 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

Let's quickly chose any one of the number based rules and find out how it works.

You will first select the entire Revenue column and click on the Format tab and select Conditional formatting. Then, in the Format rules, you will select Greater than or equal to and enter a value 255.

It should highlight all the cells which have Revenue greater than or equal to 255.

So as you can observe from the above figure that the cells that had a Revenue of greater than or equal to 255 are highlighted.

Feel free to play around with other conditional formatting based on numbers.

By far you have seen that you can select a column and the cells get highlighted when they satisfy the Format rules you set.

But what if you would like to select the entire row based on a condition applied on a column? Let's find out in the next and final segment of this tutorial.

Custom Formula

Till now you have seen that based on a range and condition, cells in a particular column gets highlighted, but to highlight the entire row associated to a specific cell in that column you need to use custom formula.

You will start by selecting your whole dataset (A2: F11), then click on Format and select Conditional Formatting.

In the Format rules select Custom formula is and mention the column (with some additional information) on which you would like to apply the formula and based on that condition all the rows associated with the column that meets the criteria will be highlighted.

Let's say you would like to highlight all the rows which have a Revenue more than or equal to 255, in the custom formula you will type =$E2>=255.

• Let's decode =$E2>=255 : To put it simply, this formula has a specific syntax to it. On a general level, it means that the cell E2 which is in Revenue column should be targeted and only the values greater than or equal to 255 should be selected. Hence, the cells from other columns like Year, Name, Headquarters, etc. associated with the Revenue column having a value greater than or equal to 255 will also be highlighted.

As you can see from the above figure, now whole rows are being highlighted for Revenue greater than or equal to 255.

Similarly, you can highlight rows that have Headquarters as China by creating a custom formula as =$C2="china". Make sure to put double quotes when creating a formula that has text in it.

Go Further

Congratulations on finishing the tutorial.

This tutorial was an excellent introduction to Conditional Formatting in Spreadsheets which covered most of the components of conditional formatting.

References:

• How to Use Conditional Formatting in Google Sheets

If you would like to learn more about conditional formatting in spreadsheets, take DataCamp's Data Visualization in Spreadsheets course.

Please feel free to ask any questions related to this tutorial in the comments section below.