How To Color Code Microsoft Excel Cells Based On Values

Color coding cells in Microsoft Excel involves automatically assigning distinct colors to cells based on their numeric or textual values. This formatting approach serves as a visual cue, enhancing the interpretation and analysis of data for improved efficiency.

In this tutorial, we will learn how to color code Microsoft Excel cells based on values. To color code cells in Excel the most common method is to utilize the built-in Conditional Formatting feature. By utilizing the conditional formatting feature we can easily color code cells based on custom rules according to the requirement.

Let’s say we have sales data for some products. We want to color-code the sales that are above $300.

Method 1: Utilizing the Conditional Formatting

Step 1 – Select the Cells

Select the cells which you want to color code.

Step 2 – Perform a Click on the Conditional Formatting Button

Perform a click on the conditional formatting button in the Styles section of the Home tab.

Step 3 – Choose the “New Rule” Option

Choose the “New Rule” option to create a customized rule based on your requirement.
You may choose a built-in rule from the drop-down menu.

Step 4 – Choose the “Format only cells that contain” Option

Choose the “Format only cells that contain” option.

Step 5 – Set the Criteria in the “Format only cells with:” Section

Set the criteria.
In this case, we will choose “Cell Value” in the first drop-down menu.
Choose “greater than” in the second field.
Now enter the value in the third field i.e. $300.

Step 6 – Perform a Click on the Format Button

Perform a click on the Format button.

Step 7 – Navigate to the Fill Tab and Choose the Color

Navigate to the Fill tab in the dialog box.
Choose the color.
Perform a click on OK.
The cells will be color-coded based on the set criteria.

Step 8 – Again Apply Conditional Formatting to Color Code the Cell with Values Less than $300

Utilizing the same steps, format the cells that have a sales value of less than $300.

Method 2: Utilizing the Quick Analysis Tool

Step 1 – Select the Cells

Select the cell which you want to color code.

Step 2 – Perform a Click on the Quick Analysis Tool Icon

Perform a click on the Quick Analysis tool icon that appears on the right end of the selection.

Step 3 – Choose the Color Scale Option

Choose the Color Scale option from the menu.
This formatting approach will apply a color gradient to the cells based on their values, ranging from red for the lowest values to green for the highest values.

Method 3: Utilizing the Find and Replace Feature

With the Find and Replace feature, it is possible to color code cells, but it is limited to text values only. To achieve this, we will use the same data set and apply color coding based on the category. The Electronics category will be colored blue, the Furniture category orange, and the Clothing category green.