Featured Article

How to Color Code in Google Sheets Based on Value

Russell Aug 07, 2026

Color coding in Google Sheets can significantly enhance data visualization and understanding. By applying different colors to cells based on their values, you can quickly identify patterns, spot outliers, and make data analysis more engaging. In this guide, we'll explore how to.color-code in Google Sheets based on cell values, using conditional formatting.

How to get the HTML color codes in Google Sheets - Plus every color in the color picker!
How to get the HTML color codes in Google Sheets - Plus every color in the color picker!

Before diving into the specifics, ensure your Google Sheets is up-to-date. Google regularly updates its features, and while the core process remains consistent, some screens might look slightly different if you're using an older version.

📊 Color Coded Dropdowns in Google Sheets
📊 Color Coded Dropdowns in Google Sheets

Understanding Conditional Formatting

Conditional formatting in Google Sheets allows you to automatically format cells based on their values. It's a powerful tool that makes your data stand out and tells a story at a glance.

How to Color Code in Google Sheets - ModernSchoolBus.com
How to Color Code in Google Sheets - ModernSchoolBus.com

The first step in color-coding your data is to understand conditional formatting and how to apply it. We'll guide you through the process, ensuring you've mastered the basics before diving into color-coding based on specific conditions.

Applying Conditional Formatting

Google Sheets: Alternate Colors - Teacher Tech with Alice Keeler
Google Sheets: Alternate Colors - Teacher Tech with Alice Keeler

To apply conditional formatting, follow these steps:

  • Select the cells you want to format.
  • Click on Format in the menu.
  • Hover over Conditional formatting and click on Highlighted cells rule...

Choosing a Format Rule

an image of the back side of a poster with different font and numbers on it
an image of the back side of a poster with different font and numbers on it

In the dialog box that appears, you'll find various formatting options. For color-coding, we're interested in the following rules:

  • Format cells if...: This is where you define the condition based on which you want to apply the formatting.
  • with... formatting style: Here, you choose the formatting you want to apply - in this case, light red fill or any other fill color.

Color-coding Based on Cell Values

the internet's prettiest shades for all kinds of things in the world
the internet's prettiest shades for all kinds of things in the world

Now that you're familiar with conditional formatting, let's apply it to color-code cells based on their values.

Say you have a dataset of scores and you want to highlight the failing scores (below 50) in red and the passing scores in green. Here's how you can achieve this:

google calendar dark color code aesthetic
google calendar dark color code aesthetic
𝚅𝚒𝚝𝚊𝚖𝚒𝚗 𝚂𝚎𝚊 𝙲𝚘𝚕𝚘𝚛𝚜
𝚅𝚒𝚝𝚊𝚖𝚒𝚗 𝚂𝚎𝚊 𝙲𝚘𝚕𝚘𝚛𝚜
Google Drive Organization
Google Drive Organization
the font and numbers in different colors are displayed on an iphone screen, with text below them
the font and numbers in different colors are displayed on an iphone screen, with text below them
color palette ideas
color palette ideas
The Ultimate Google Sheets Keyboard Shortcut Cheat Sheet for Beginners & Pros
The Ultimate Google Sheets Keyboard Shortcut Cheat Sheet for Beginners & Pros
an iphone screen showing the settings for different types of buttons and numbers on each button
an iphone screen showing the settings for different types of buttons and numbers on each button
a calendar with clouds above it and the words gray sky in blue, red, white and
a calendar with clouds above it and the words gray sky in blue, red, white and
the color chart for different types of hair dyes and how they are used to create them
the color chart for different types of hair dyes and how they are used to create them
How to Use Alternate Colors in Google Sheets
How to Use Alternate Colors in Google Sheets
the color palette is shown in different colors and sizes, including pinks, purples,
the color palette is shown in different colors and sizes, including pinks, purples,
pastel color palette with the words,'pastel color crush'in different colors
pastel color palette with the words,'pastel color crush'in different colors
an info sheet with different colors and shapes for the same color scheme, as well as text that reads dvr3 style
an info sheet with different colors and shapes for the same color scheme, as well as text that reads dvr3 style
Color-Coded Habit Tracker - 5 Weeks Visual Organization System
Color-Coded Habit Tracker - 5 Weeks Visual Organization System
Here Is How To Add Cute Colors To Your Calendar In Google Sheets. Spreadsheet Spreadsheettutorial
Here Is How To Add Cute Colors To Your Calendar In Google Sheets. Spreadsheet Spreadsheettutorial
the color chart for different colors and numbers on a blue, green, pink, purple,
the color chart for different colors and numbers on a blue, green, pink, purple,
How to Get the Absolute Value in Google Sheets
How to Get the Absolute Value in Google Sheets
the color scheme for different font and numbers
the color scheme for different font and numbers
the honey butter color code is shown in this graphic style, and includes different ingredients to choose from
the honey butter color code is shown in this graphic style, and includes different ingredients to choose from
an orange and yellow color chart with different colors
an orange and yellow color chart with different colors

Highlighting Failing Scores

To highlight failing scores in red, follow these steps:

  1. Select the cells containing the scores.
  2. Click on Format in the menu.
  3. Hover over Conditional formatting and click on Highlighted cells rule...
  4. In the dialog box, set the rule to Format cells if..., enter '<50' for the value or formula, and choose light red fill under with...
  5. Click Done.

Highlighting Passing Scores

To highlight passing scores in green, follow these steps:

  1. Assuming you've already applied the red fill for failing scores, you can add another rule for passing scores. Click on Format > Conditional formatting > Add another rule
  2. Set the rule to Format cells if..., enter '>=50' for the value or formula, and choose light green fill under with...
  3. Click Done.

Your data should now be color-coded based on the scores, making it easier to identify passing and failing marks at a glance.

Taking It Further: Color-scales and Data Bars

Conditional formatting offers more advanced options like color-scales and data bars, which can provide even more nuanced visualization of your data.

Color-scales

Color-scales apply multiple colors based on the range of values in a cell. They're perfect for showing the distribution of data. To apply a color-scale:

  1. Follow the same initial steps for applying conditional formatting.
  2. Choose Gradients under Format cells if...
  3. Select the color-scale you want to apply and adjust the min and max values as required.

Data Bars

Data bars fill cells with color bars that represent the relative value within a range. They're excellent for visualizing both positive and negative values. To apply data bars:

  1. In the conditional formatting dialog box, choose Data bars under Format cells if...
  2. Choose the data bar style you want to apply.

Experimenting with these advanced conditional formatting options can help you find the best visual representation of your data.

Color-coding in Google Sheets based on cell values can significantly enhance data visualization and understanding. By mastering these techniques, you can present your data more effectively, draw meaningful insights, and tell compelling stories. So, go ahead, give your data some color, and watch it come alive!"