How to Color Code Cells in Excel (3 Methods)

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

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.

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

• 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.

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

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.

• 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.

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

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.

• 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.

Download the Practice Workbook

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.

Are there limitations to color coding in Excel?

While color coding is a powerful tool, excessive use may lead to visual clutter and even slow Excel down.

Related Articles

• Uses of CELL Color A1 in Excel
• VBA to Change Cell Color Based on Value in Excel
• How to Change Cell Color Based on a Value in Excel
• How to Fill Color in Cell Using Formula in Excel
• How to Fill Cell with Color Based on Percentage in Excel
• Excel Formula to Color Cell If It Has Specific Value
• How to Change Text Color with Formula in Excel
• How to Apply Formula Based on Cell Color in Excel

<< Go Back to Color Cell in Excel | Excel Cell Format | Learn Excel