Conditional formatting in Google Sheets allows you to highlight key data automatically based on rules you set. Whether you’re marking exam results, tracking performance, or analysing trends, it simplifies your work by visually emphasising important points.
This quick guide walks you through setting up basic and custom formatting to make your Sheets more effective and visually engaging.
How to set up a conditional formatting rule
Let’s look at how we set up a conditional formatting rule. Here we have some exam results and the teacher wants to automatically highlight the marks with 60% or below.
1. Select the range of data to format.
2. Right-click and choose View more cell actions > Conditional formatting.
3. Under Format cells if…, choose Less than or equal to, and type 0.6 as the value.
4. Choose a red fill colour and click Done.
Now, all cells with values of 60% or below are highlighted in red, making it easy to spot failing scores.
How to set up multiple formatting rules
This time the teacher also wants to show the exam marks that passed in green. To distinguish between passes and fails, you can create two rules. Here’s how:
1. Select the same range of data as before. Right-click and open the Conditional Formatting panel as before.
2. For the first rule, under Format cells if…, select Less than or equal to, and type 0.6. Choose a red fill colour and save.
3. Click Add another rule and choose Greater than. Enter 0.6 and select a green fill colour. Save the rule.
Your data is now colour-coded: green for passes and red for fails. This clear visual layout simplifies and speeds up analysis.
Colour Scales for Gradual Highlights
Colour scales allow you to visualise your data without setting multiple rules. Here’s how:
1. Select the range of data and go to Format > Conditional formatting.
2. Switch to the Colour scale tab.
3. In the Format rules section, pick a default colour scale or customise it. Here let’s use the red to green scale. Then let’s set the midpoint to 50, so that the marks that are in the middle of the spread of data will be white.
Leave the Minpoint as red and the Maxpoint as green. The marks at the lower end will be dark red, slightly higher marks are light red. Then on the other end, the highest marks are in dark green, and slightly lower marks are light green.
So, very quickly we can see visually the spread of the data.
Advanced Formatting with Custom Formulas
Custom formulas offer flexibility for more advanced needs. For example, to highlight entire rows where students scored below 50%:
1. Select the data (excluding headers).
2. Open the Conditional Formatting panel and choose Custom formula is.
3. Enter =$D2<0.5 and apply a red fill colour.
How It Works
The formula checks if the value in column D is less than 0.5 (50%). The $ before D ensures the column stays fixed while the rule evaluates each row, so it fills the cells in columns A to D because D is less than 0.5.
Conditional formatting in Google Sheets transforms raw data into clear, visual insights. Start with simple rules and explore custom formulas to unlock the full potential of your Sheets. Let your data tell the story!
Dive it this in more detail in this video:
Want to learn more about Google Sheets? Check out these posts.
Want to learn more about Google Workspace and Apps Script? The books below are available on Amazon. Just click on a book! (Affiliate links).
a
