Featured Article

"Excel: Color Code Based on Another Cell's Value"

Russell Aug 07, 2026

Ever felt the need to quickly identify cells in Excel based on their values? Color coding can make this task a breeze. If you're using the value from another cell as a reference, fear not! This guide will walk you through how to color code in Excel based on the value of another cell - no complex formulas or macros needed.

How to change background color in Excel based on cell value
How to change background color in Excel based on cell value

Before we dive in, ensure you have the latest version of Excel or at least Excel 2010. If you're using a Mac, the process is slightly different, but we've got you covered in this guide. Now, let's get started!

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

To color code cells based on another cell's value, we'll use Excel's Conditional Formatting feature. It's like giving Excel rules to follow - apply a certain color when the cell value meets specific conditions.

Color Coded Drop-Down Lists in Excel‼️ #excel
Color Coded Drop-Down Lists in Excel‼️ #excel

In this case, our condition is: if the value in a cell is the same as the value in another specified cell, apply a color. Let's see how to set this up.

Using the 'Duplicate Values' Rule

How to color code excel cells based on value
How to color code excel cells based on value

The 'Duplicate Values' rule in Conditional Formatting is perfect for our task. It applies formatting to cells that contain values that also exist in a range of other cells. Here's how to use it:

1. **Select the cells you want to format**. Let's say these are cells A1 to A10.

2. **Access Conditional Formatting**: In Excel 2010+, go to 'Home' > 'Conditional Formatting' > 'Highlight Cells Rules' > 'Duplicate Values'.

Change Cell Color based on Dropdown Selection | Excel Tutorial
Change Cell Color based on Dropdown Selection | Excel Tutorial

3. **Set the criteria**: In the dialog box, you can choose the cells to check for duplicate values. Click in the box and select the cell with the value you want to match (e.g., B1).

4. **Choose the formatting**: Select the color or fill you want to apply when the value matches. You can use the arrow next to the fill button to choose from various preset colors or click 'More Colors...' for custom colors.

Using a Formula to Apply the Color

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

If the 'Duplicate Values' rule doesn't suit your needs, or you have a complex condition, you can use a formula to apply the color. This involves using something like the IF or IFS function in Excel.

For example, if you want to color cells based on an exact match or a multiple of another cell's value, you can use this formula:

the color code is shown in this screenshot
the color code is shown in this screenshot
How to Change Background Color Based on Cell Value in Excel?
How to Change Background Color Based on Cell Value in Excel?
How to Shade or Color Alternate Rows in Excel
How to Shade or Color Alternate Rows in Excel
Change Cell Color Based on the Percentage in Excel
Change Cell Color Based on the Percentage in Excel
How to Sum Cells by Color in Excel (Get it to update automatically)
How to Sum Cells by Color in Excel (Get it to update automatically)
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
Excel: Change the row color based on cell value
Excel: Change the row color based on cell value
The If Statement is Based on the Cell Color in Excel
The If Statement is Based on the Cell Color in Excel
Excel's Color Palette Compatibility Solution
Excel's Color Palette Compatibility Solution
How to Automatically Color Code in Excel
How to Automatically Color Code in Excel
Change the Gridline Color in Excel Spreadsheets - 2 Ways!
Change the Gridline Color in Excel Spreadsheets - 2 Ways!
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 Cell Color Based on the Percentage in Excel
Change Cell Color Based on the Percentage in Excel
How to Change Legend Colors in Excel (3 Steps)
How to Change Legend Colors in Excel (3 Steps)
Change Cell Colour using Conditional Formatting
Change Cell Colour using Conditional Formatting
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 create color - coded drop - down menu
excel create color - coded drop - down menu
How to use Color Scales in Excel
How to use Color Scales in Excel
📊 Color Coded Dropdowns in Google Sheets
📊 Color Coded Dropdowns in Google Sheets
Sum by Colour in Excel in One Click
Sum by Colour in Excel in One Click

1. **Start by selecting your cells** (e.g., A1 to A10).

2. **Access Conditional Formatting** as mentioned above.

3. **Choose 'Use a formula to determine which cells to format'** and enter this formula: `=IF(A1=B1,"Format as you want","")`. Replace 'Format as you want' with your chosen color or fill.

4. **Click 'OK'**. Your cells will now be formatted based on the formula we've created.

Applying Colors to Different Areas in Mac Excel

If you're using Excel for Mac, the process has a couple more steps, but it's still straightforward. Remember to start with your cells selected:

1. **Access the 'Format' menu** at the top of your screen and click 'Conditional Formatting'.

2. **Choose 'Formatted only cells that contain'** and then **'cell values equal to'**.

3. **In the box that appears, type the cell reference you want to match** (e.g., A1).

4. **Choose the formatting you want to apply** and click 'OK'.

5. **Click 'Done'** to close the Conditional Formatting dialog box.

And there you have it! You've now learned how to apply color to Excel cells based on the value in another cell. This technique can save you countless hours browsing data, so embrace it! Don't just stop at colors - explore other formatting options like borders, fills, and icons to make your spreadsheets truly stand out. Happy coloring!