How do you highlight a cell if it is equal to another cell?

There might be a time when you have a requirement that needs you to highlight cells under a column based on the values in another column.

Table of Contents Show

How do you highlight a cell that equals another cell?
How do I color cells in Excel based on value of another cell?
How do you highlight a cell if it matches another cell in another sheet?
Can you conditional format a cell based on another cell?

E.g. for example, you want to highlight the cells in Price column based on whether they are paid or not. Let’s say when Paid is equals to 1, you will highlight the price as red, and if it is 0, you will highlight the price as blue.

To achieve, conditional formatting rules will need to be added.

Under Home tab | Styles | Conditional Formatting | Manage Rules
Create a new rule
Select “Use a formula to determine which cells to format”
Enter “=A2=1” as the formula
Format the Fill colour as Red
Click okay and apply it to your selection

This rule will fulfill the red color formatting of Price column when Paid column cells are equals to 1.

Repeat the same for blue color, but this time change to formula to “=A2=0”

This 2nd rule will fulfill the blue color formatting of Price column when Paid column cells are equals to 0.

And there you have it, you have successfully highlighted the individual cells in excel based on the contents of other cells

Conditional formatting makes it easy to highlight certain values or make particular cells easy to identify. This changes the appearance of a cell range based on a condition (or criteria). You can use conditional formatting to highlight cells that contain values which meet a certain condition. Or you can format a whole cell range and vary the exact format as the value of each cell varies.

The following example shows temperature information with conditional formatting applied to the top 20% and bottom 20% values:

And here's an example with 3-color scale conditional formatting applied:

Apply conditional formatting

Select the range of cells, the table, or the whole sheet that you want to apply conditional formatting to.
On the Home tab, click Conditional Formatting.
Do one of the following:

To highlight

Do this

Values in specific cells. Examples are dates after this week, or numbers between 50 and 100, or the bottom 10% of scores.

Point to Highlight Cells Rules or Top/Bottom Rules, and then click the appropriate option.

The relationship of values in a cell range. Extends a band of color across the cell. Examples are comparisons of prices or populations in the largest cities.

Point to Data Bars, and then click the fill that you want.

The relationship of values in a cell range. Applies a color scale where the intensity of the cell's color reflects the value's placement toward the top or bottom of the range. An example is sales distributions across regions.

Point to Color Scales, and then click the scale that you want.

A cell range that contains three to five groups of values, where each group has its own threshold. For example, you might assign a set of three icons to highlight cells that reflect sales below $80,000, below $60,000, and below $40,000. Or you might assign a 5-point rating system for automobiles and apply a set of five icons.

Point to Icon Sets, and then click a set.

Apply conditional formatting to text

Select the range of cells, the table, or the whole sheet that you want to apply conditional formatting to.
On the Home tab, click Conditional Formatting, point to Highlight Cells Rules, and then click Text that Contains.
In the box next to containing, type the text that you want to highlight, and then click OK.

Create a custom conditional formatting rule

Select the range of cells, the table, or the whole sheet that you want to apply conditional formatting to.
On the Home tab, click Conditional Formatting, and then click New Rule.
Select a style, for example, 3-Color Scale, select the conditions that you want, and then click OK.

Format only unique or duplicate cells

Select the range of cells, the table, or the whole sheet that you want to apply conditional formatting to.
On the Home tab, click Conditional Formatting, point to Highlight Cells Rules, and then click Duplicate Values.
In the values in the selected range pop-up menu, click unique or duplicate.

Copy conditional formatting to additional cells

Select the cell that has the conditional formatting that you want to copy.
On the Home tab, click Format
, and then select the cells where you want to copy the conditional formatting.

Find cells that have conditional formatting

If only some part of your sheet has conditional formatting applied, you can quickly locate the cells that are formatted so that you can copy, change, or delete the formatting of those cells.

Click any cell.
If you want to find only cells with a specific conditional format, start by clicking a cell that has that format.
On the Edit menu, click Find, and then click Go To.
Click Special, and then click Conditional formats.
If you want to locate only cells with the specific conditional format of the cell that you selected in step 1, under Options, click Same.

Clear conditional formatting from a selection

Select the cells that have the conditional formatting that you want to remove.
On the Home tab, click Conditional Formatting, point to Clear Rules, and then click the option that you want.
Tip: To remove all conditional formats and all other cell formats for selected cells, on the Edit menu, point to Clear, and then click Formats.

Change a conditional formatting rule

You can customize the default rules for conditional formats to fit your requirements. You can change comparison operators, thresholds, colors, and icons.

How do you highlight a cell that equals another cell?

Select the range of cells, the table, or the whole sheet that you want to apply conditional formatting to. On the Home tab, click Conditional Formatting, point to Highlight Cells Rules, and then click Duplicate Values. In the values in the selected range pop-up menu, click unique or duplicate.

How do I color cells in Excel based on value of another cell?

How to highlight cells in excel based on the contents of other....

Under Home tab | Styles | Conditional Formatting | Manage Rules..

Create a new rule..

Select “Use a formula to determine which cells to format”.

Enter “=A2=1” as the formula..

Format the Fill colour as Red..

Click okay and apply it to your selection..

How do you highlight a cell if it matches another cell in another sheet?

Re: Highlight duplicate cell in another sheet On the Home tab of the ribbon, select Conditional Formatting > New Rule... Select 'Use a formula to determine which cells to format'. Substitute the real name of Sheet 1. Activate the Fill tab.

Can you conditional format a cell based on another cell?

Excel's predefined conditional formatting, such as Data Bars, Color Scales and Icon Sets, are mainly purposed to format cells based on their own values. If you want to apply conditional formatting based on another cell or format an entire row based on a single cell's value, then you will need to use formulas.