How to Color Code Cells in Excel (3 Methods)

Q: Does Excel provide predefined color coding templates?

Yes, Excel offers predefined color coding templates through Conditional Formatting options like Color Scales, Data Bars, and Icon Sets. These templates provide quick solutions for common formatting needs.

We’ll use a sample dataset of students and their grades to color code cells.

how to color code cells in excel

Why Is Color Coding Cells in Excel Important?

Visual clarity: This can help you to distinguish between different categories or values, increasing readability.
Pattern recognition: Colors help you identify patterns within the data, like understanding the different magnitudes of a spread of data.
Highlighting exceptions: By assigning specific colors to cells that meet certain conditions, you can identify exceptions or outliers in the data.
Customization for specific needs: You can tailor the color coding to suit their specific needs, whether it’s for financial analysis, project management, or any other type of data manipulation.

Color Code Cells with Conditional Formatting in Excel: Step-by-Step Guide

We will use the following spreadsheet to color code the Marks column according to its values.

sample spreadsheet

Select the range of cells to color code.
Go to the Home tab.
Select Conditional Formatting, choose Highlight Cell Rules, and select Greater Than….

navigating to conditional formatting options from the home tab

The Greater Than… option is selected to set the ‘greater than’ logical criteria. You can set this criteria according to your needs.
In the Greater Than dialog box:
- Input a value that you’re checking against.
  We set it to 80.
- Select the Custom Format… option from the ‘with’ drop-down menu.
In the Format Cells dialog box, go to the Fill tab
Select the desired fill color and click OK.

setting cell formatting to color code

You can use other tabs (Number, Font, Border, and Fill) to apply different formatting.
Back in the Greater Than dialog box, press OK.

Using Conditional Formatting to Color Code Cells

The color code criteria we have used here are the green background fill and white font color in the italics style.

How to Color Code Cells in Excel

Case 1 – Color Cells Containing Excel Formulas

Some cells in the Grade column contain formulas. We’ll quickly find them with conditional formatting.

sample spreadsheet with formula in cells

Select the range of cells.
Go to the Home tab, select Conditional Formatting, and choose New Rule….
In the New Formatting Rule dialog box:
- In Select a Rule Type: choose Use a formula to determine which cells to format.
- Under Edit the Rule Description, enter this formula:=ISFORMULA(D5)
- Press Format….
Set the desired format in the Format Cells dialog box and click OK.
A shade of blue is selected as the background Fill color in this example.
Back in the New Formatting Rule dialog box, click OK.
All the cells that match the criteria in the selected range have been color coded in Excel.

Color Coding Cells Containing Formulas in Excel

If you apply a formula to another cell in that range, the cell also becomes color-coded.

adding new formula to affected cells also color codes them

Case 2 – Color Cells Based on Text Value

In the following spreadsheet, the cells containing the string A+ in the Grade column will be highlighted.

sample spreadsheet

Select the range of cells.
Go to Conditional Formatting and select New Rule….
In the New Formatting Rule dialog box:
- Choose Use a formula to determine which cells to format.
- Under Edit the Rule Description, enter this formula:=$D5=”A+”
  The formula checks for the string ‘A+’ starting from cell D5 and down the column.
- Press Format….
Set the desired format in the Format Cells dialog box and click OK.
Back in the New Formatting Rule dialog box, click OK.

All the cells that match the criteria in the selected range have been color-coded.

result of color coding cells in Excel using conditional formatting

Download the Practice Workbook

Color Code Cells.xlsx

Frequently Asked Questions

Does Excel provide predefined color-coding templates?

Yes, Excel offers predefined color-coding templates through Conditional Formatting options like Color Scales, Data Bars, and Icon Sets. These templates provide quick solutions for common formatting needs.