How to Compare Two Cells and Change Color in Excel (2 Ways)

Often, to get over similar entries in a dataset, we compare two cells and change the color in Excel. It’s an easy way out to hover among entries without getting lost. Excel’s Conditional Formatting offers multiple ways to compare and color format cells. Also, Excel’s Go To Special feature can detect differences among row entries.

Let’s say, we have two different Product lists prepared by salesmen. The Product lists have products that are ordered on certain days. We want to compare these products on the same dates and change the color of them on their not being identical.

 In this article, we demonstrate multiple ways to compare two cells and change color in Excel.

How to Compare Two Cells and Change Color in Excel: 2 Ways

Method 1: Using Conditional Formatting

Conditional Formatting is an effective tool to impose conditions and change the color of cells in Excel. In the Excel Home tab, Excel offers Conditional Formatting in the Style section. Conditional Formatting allows a couple of rule types (i.e., Highlight Cells Rules, Top/Bottom Rules, and New Rules) in-built. For this method, we use Highlight Cells Rules and New Rules types.

1.1: Using Formula to Compare Cells and Change Color

New Rules type Conditional Formatting requires users to choose a New Formatting Rule type from multiple rule types.

Step 1: To apply the New Rule types Conditional Formatting, Go to Home > Select Conditional Formatting (from the Style section) > Choose New Rules (from the options).

Step 2: The New Formatting Rule window appears. Within the New Formatting Rule window, we need to select an option called Use a formula to determine which cells to format rule type as a Select a Rule Type.

Enter the following formula in the Format values where this formula is a true field.

The formula states that entries (from row 5) in Column C and Column D are not equal. And results in TRUE or FALSE comparing cells in these two columns. Then, Conditional Formatting changes the color of the cells if the formula returns TRUE.

Step 3: Now, Click on Format. Clicking on Format brings the Format Cells window. In the Format Cells window,

Choose a Fill Color (i.e., Green), then, Click on OK.

 Step 4: As you click OK in the Format Cells window, it takes you to the New Formatting Rule window again.

Again, Click on OK.

➤ In a moment, all cells that satisfy the formula (i.e., $C5 not equal to $D5), Conditional Formatting color formats them as shown in the following image.

The formula checks whether two cells (i.e., $C# and &D#) are identical or not. Of the two cells not being identical, Conditional Formatting fills those cells with colors you selected in the Format Cells window.

1.2: Using Specific Cells to Compare and Color Format

In Method 1.1 we use a not equal (<>) as a formula to compare two cells and apply color formatting. However, comparing two cells can also be achieved using the COUNTIF function. In the Conditional Formatting New Formatting Rule window, we can enter the COUNTIF formula to compare cells between columns.

The COUNTIF formula takes each D column entry as Range and counts it against each C column entry. Then, the formula equals that counting to Zero (0). If the counting results in Zero (0) for each incidence Excel returns TRUE otherwise False. And Conditional Formatting colors all the cells in the selected color that result in TRUE.

Steps: Repeat Step 1 to 4 of Method 1.1, just replace the not equal formula (i.e., =$C5<>$D5) with the COUNTIF one (i.e., =COUNTIF($D5:$D5,$C5)=0).

Click on OK.

 ➤ Since you click OK, Conditional Formatting applies color (i.e., Green) in cells that abide by the COUNTIF formula as depicted in the below image.

You can modify the COUNTIF formula with your own range, criteria, and equal to values.

1.3: Using Unique Entries to Compare Cells and Color

Another applicable option in Conditional Formatting is Highlight Cells Rules. Highlight Cells Rules option offers multiple in-built conditions. In this case, we can use the Duplicates Values condition to compare two cells and color format them.

Before applying the Duplicate Values condition, we modify the dataset in a way that there are Duplicate and Unique entries in both columns.

Step 1: Move to the Home tab > Select Conditional Formatting (from the Style section) > Click on Highlight Cells Rules > Select Duplicate Values (from the options).

Step 2: The Duplicate Values window appears. In the Duplicate Values window,

Select Unique values as Format cells that contain to color format cells.

Duplicate Values condition compares entries between two columns and color formats unique entries. You can choose Duplicate (as Format cells that contain) values to color format cells but it disqualifies the purpose.

Method 2: Using Go To Special to Compare Two Columns and Change Color

Excel’s Go To Special feature can select multiple items from a range of cells. One of the items offered in the Go To Special feature is Row Differences. It compares entries of the two columns (i.e., C and D) and selects entries that are different from the 1st one.

Step 1: Hover to the Home tab > Select Find & Select (from the Editing section) > Choose Go To Special (from the option).

Step 2: The Go To Special dialog box appears. In the Go To Special dialog box,

Select Row Differences under the Select section.

Click OK.

➤ Selecting Row Differences selects Unique entries of Column D comparing entries between Column C and D. The outcomes will be the same as shown in the following image.

Step 3: In the Home tab, Select a Fill Color (i.e., Theme Color) for the selected entries. After that all the different entries get the color changed.

Go To Special’s Row Differences compare rows and select the different entries that exist in the latter column. You can choose any theme color to color format the selection.

Download Excel Workbook

Conclusion

In this article, we use multiple Excel features to compare two cells and change color. For this purpose, Conditional Formatting is the handiest tool to compare cells and color format them. Also, Excel’s Go To Special feature can detect differences in entries between rows. Hope you find these above-described methods workable with your dataset. Comment, if you have further inquiries or have anything to add.

<< Go Back To Excel Compare Cells | Compare in Excel | Learn Excel