Featured Article

Color Code in Excel based on Another Column's Value

Russell Aug 07, 2026

Did you know you can color code cells in Excel based on the value of another column? This simple yet powerful technique can greatly improve data analysis and visualization. Let's dive into the process, step by step.

Color-Coded Drop-Down Lists in Excel – Make Data Entry Smarter
Color-Coded Drop-Down Lists in Excel – Make Data Entry Smarter

Before we start, ensure that your Excel version is 2010 or later, as conditional formatting, which we'll be using, was introduced in this version. Now, let's explore how to color code in Excel based on the value of another column.

How To Change Background Or Font Color Based On Cell Value In Excel?
How To Change Background Or Font Color Based On Cell Value In Excel?

Understanding Conditional Formatting

Conditional formatting is a feature in Excel that allows cell formatting to change based on the value of the cell. It's the key to our color-coding task. Let's understand it better through two subtopics.

Excel Color Coded Dropdown‼️Use Amazing Excel Features #excel #exceltips #shorts #exceltricks
Excel Color Coded Dropdown‼️Use Amazing Excel Features #excel #exceltips #shorts #exceltricks

Rule Formatting vs. Icon Sets

Excel offers two types of conditional formatting: rule formatting and icon sets. Rule formatting uses rules to apply specific formatting to cells, such as fill color, font color, or border color, based on whether the cell value meets the rule criteria. On the other hand, icon sets use icons to represent the cell value. For our purpose, we'll focus on rule formatting, specifically fill color.

Excel's Color Palette Compatibility Solution
Excel's Color Palette Compatibility Solution

For instance, if you have a column of numbers and you want to highlight all numbers above 500 in red, you can use a rule to apply the fill color red to those cells. Let's set this up in our next subtopic.

Setting Up a Rule for Color Coding

Here's how to set up a rule for color coding based on the value of another column:

the color code is shown in this screenshot
the color code is shown in this screenshot
  1. Select the range of cells you want to format.
  2. Click on 'Home' > 'Conditional Formatting' > 'New Rule'.
  3. In the 'New Formatting Rule' dialog box, select 'Use a formula to determine which cells to format'.
  4. Enter the formula. Assume the column you want to base your formatting on is 'A'. In the 'Format values where this formula is true:' box, enter '=$A1>500' to highlight all values over 500. The '1' in the formula refers to the first cell in the selected range.
  5. Click on the 'Format' button, select the fill color you want (in this case, red), and click 'OK'.
  6. Click 'OK' again to close the 'New Formatting Rule' dialog box.

The cells that meet the rule are now highlighted in the color you chose.

Expanding Color-Coding Rules

How to Shade or Color Alternate Rows in Excel
How to Shade or Color Alternate Rows in Excel

You can expand this simple rule into more complex ones to create a heat map or a gradient of colors. This can be particularly useful for data visualization and pattern recognition.

For example, you can create a heat map using the 'Color Scales' option instead of a single color. Or you can create a traffic light-style system with low values in green, medium in yellow, and high in red. Each scenario involves creating multiple rules with different threshold values.

the color coded drop down in excel is shown with an arrow pointing to it
the color coded drop down in excel is shown with an arrow pointing to it
Change the Gridline Color in Excel Spreadsheets - 2 Ways!
Change the Gridline Color in Excel Spreadsheets - 2 Ways!
How to Automatically Color Code in Excel
How to Automatically Color Code in Excel
excel create color - coded drop - down menu
excel create color - coded drop - down menu
How to Change Legend Colors in Excel (3 Steps)
How to Change Legend Colors in Excel (3 Steps)
How to highlight cell if value exists in or equals to cell in another column in Excel?
How to highlight cell if value exists in or equals to cell in another column in Excel?
📊 Color Coded Dropdowns in Google Sheets
📊 Color Coded Dropdowns in Google Sheets
Change Cell Color Based on the Percentage in Excel
Change Cell Color Based on the Percentage in Excel
21K views · 125 reactions | New Excel tips and tricks: color coded drop-down list in Excel ! Excel tutorials, Excel functions, Excel formula, Data Validation, Data entry, Data formatting #Excel #exc | Vision Computers | Facebook Data Validation, Drop Down List, Excel Formula, Excel Tutorials, Data Entry, Microsoft Excel, Color Coding, Coding
21K views · 125 reactions | New Excel tips and tricks: color coded drop-down list in Excel ! Excel tutorials, Excel functions, Excel formula, Data Validation, Data entry, Data formatting #Excel #exc | Vision Computers | Facebook Data Validation, Drop Down List, Excel Formula, Excel Tutorials, Data Entry, Microsoft Excel, Color Coding, Coding
#Excel #Charts: Custom Data Labels that change colors dynamically
#Excel #Charts: Custom Data Labels that change colors dynamically
Sum by Colour in Excel in One Click
Sum by Colour in Excel in One Click
Change Cell Color based on Dropdown Selection | Excel Tutorial
Change Cell Color based on Dropdown Selection | Excel Tutorial
Create a Column Chart to Display Variance with Different Colors
Create a Column Chart to Display Variance with Different Colors
a green box with the words highlight every other row in excel next to it
a green box with the words highlight every other row in excel next to it
Excel Custom Number Format Guide • My Online Training Hub
Excel Custom Number Format Guide • My Online Training Hub
an excel spreadsheet showing the number of columns in each column and how to use them
an excel spreadsheet showing the number of columns in each column and how to use them
How to Change Background Color Based on Cell Value in Excel?
How to Change Background Color Based on Cell Value in Excel?
the ultimate guide to color mixing info sheet
the ultimate guide to color mixing info sheet
How To Use Excel Fill Color Shortcut: A Quick And Easy Guide
How To Use Excel Fill Color Shortcut: A Quick And Easy Guide

To modify your rule in the future, just adjust the formula and formatting as needed. For example, if you want to highlight values above 600 instead of 500, simply change '$A1>500' to '$A1>600'.

In conclusion, color coding in Excel based on the value of another column can be achieved easily with conditional formatting. This tool can greatly enhance data understanding and analysis. So, the next time you find yourself buried in a sea of numbers, consider how a little color might help you navigate the waves.