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
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
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!
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
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.
G6 is now colored and the rule applied to it is
- Next what you do is select the complete data and then click on
Formattab and select
Conditional Formatting, but this time you will select format rules such as
Is not emptysince 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 rangetab, for example:
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:
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 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 styleselect the
Defaultoption, 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 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 not emptyhere. To reiterate over it once again, selecting
is emptywill apply conditional formatting only on those cells which have null values (empty) and similarly, selecting
is not emptywill 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
China as the text, which means that only those cells which have
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
It should highlight all the cells which have
Revenue greater than or equal to
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.
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
You will start by selecting your whole dataset (
A2: F11), then click on
Format and select
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
- 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
E2which is in
Revenuecolumn 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
Revenuecolumn 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
Similarly, you can highlight rows that have
China by creating a custom formula as
=$C2="china". Make sure to put double quotes when creating a formula that has text in it.
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.
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.
Learn more about Spreadsheets
Data Science in Finance: Unlocking New Potentials in Financial Markets
5 Common Data Science Challenges and Effective Solutions
A Data Science Roadmap for 2024