Color coding your spreadsheet can make your data easier to understand and can help to highlight key information.
With conditional formatting, you can create a color code to automatically change cell colors based on values.
In this tutorial, I will show you how to automatically color-code cells based on values in Google Sheets by using conditional formatting.
Table of Contents
What is Conditional Formatting?
The best way to automatically highlight certain cells’ different colors depending on their values is with conditional formatting.
Conditional formatting allows you to create a rule in your spreadsheet, and for every cell within the selected range that the rule is true for, you can set up a custom format.
With conditional formatting, you can create rules to format cells if your value meets these criteria. Here are the options you can choose from for formatting rules:
- Is empty
- Is not empty
- Text contains
- Text does not contain
- Text starts with
- Text ends with
- Text is exactly
- Date is
- Date is before
- Date is after
- 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
- Custom formula is
Changing Cell Colors Based on Values
Next, I will show you how to use conditional formatting to change cell colors based on values.
To do this, I will be using this example data where I have the test scores for various students. I will be creating rules to highlight each range of test scores a different color.
Here is how this is done:
1. First highlight the range of cells that you want to color code
2. Next, in the top menu select Format>Conditional Formatting
3. The Conditional format rules tab should appear on the right side of your screen. Click on the box below “Format cells if” to change the formatting rules
4. In my example, I am using the rule “Is between”. You can use whatever rule is appropriate for your data
5. Next you will be entering the values for your formatting rules. My first rule will be set to change the color of a cell that is between 40-49. So in the boxes, I place my numeric range of 40 in the first box and 49 in the second box.
6. Next you can select the formatting rules that apply to the cells that meet the criteria you set in the previous step. In my example, I am changing the fill color, so I select the color that I want my cells to change to when they meet the rule I created
7. When you are done selecting your formatting you can click the green Done button at the bottom of the page. If you have additional rules you want to create, select the “Add another rule” option and continue creating as many rules as you desire
8. I have created multiple rules in my spreadsheet to change the colors of multiple cells based on the values. Here are the rules that I have in my sheet:
9. Your spreadsheet should now be color-coded according to the conditional formatting rules you have set up. Here is how mine looks:
Closing Thoughts
Color coding your spreadsheet with conditional formatting is a quick and easy way to make your data easier to understand and highlight certain data.
You have a ton of options for the different rule types you can set up in your sheet, so you can make all kinds of data stand out.
Another option is that instead of creating a custom rule, you can also just select Color Scale in conditional formatting to quickly apply a color rule to your data. Try out both methods of creating your own rules and using the Color Scale option to see what works best for your needs.
More Google Sheets Tutorials:
How to Make Negative Numbers Red
How to Use Conditional Formatting Based on the Value of Another Cell
How to Alternate Row Colors