Compare Two Columns in Excel and Highlight the Greater Value (4 Ways)

In this article, we will learn how to compare two columns in Excel and highlight the greater value. Sometimes, we need to compare two columns in our Excel worksheet and highlight the higher value to represent our data more informatively. So that, the viewers can easily see the highlighted cell and understand the result. We can use various methods to compare data, but most of them use one column. Today, we will discuss the methods to compare two columns and highlight the greater value.

How to Compare Two Columns in Excel and Highlight the Greater Value: 4 Efficient Ways

To explain these methods, we will use a dataset that contains information about the sales amount of the first two months of some sellers. We will try to compare the sales of the first month with the sales of the second month and highlight the greater value between them.

1. Using Excel Conditional Formatting to Compare Two Columns and Highlight the Greater Value

Excel provides us with an excellent feature to compare and highlight cells together. It is Conditional Formatting. In this first method, we will use the conditional formatting option to perform our task.

Let’s follow the steps below to learn this method.

STEPS:

• In the beginning, select cells from Column D. We have selected Cell D5 to Cell D11.

• Secondly, go to the Home tab and select Conditional Formatting. A drop-down menu will occur.

• Thirdly, select Highlight Cells Rules and then, select Greater Than. It will open a Greater Than window.

• Now, write the formula below in the Greater Than window.

• Click OK to proceed.
• After clicking OK, you will see that the cells that contain greater values compared to Column C are highlighted.

• Next, select the cells of Column C.

• Once again, go to the Home tab and select Conditional Formatting.

• Select Highlight Cells Rules and then, select Greater Than from the drop-down menu.

• This time, write the formula below in the Greater Than window.

• Click OK to proceed.
• Finally, you will see results like below.

Read More: Excel formula to compare two columns and return a value

2. Use IF Function to Compare Two Columns and Highlight the Higher Value in Excel

In the second method, we will use the IF function to compare two columns. The IF function is very handy in Excel when you need to compare two columns and highlight the greater value. Here, we will use the same dataset. In addition, we will use an extra column.

2.1 Compare Two Columns

In this sub-method, we will compare the two columns at first. Let’s pay attention to the steps below to know this technique.

STEPS:

• In the first place, create an extra column in your dataset. Column E is our new column.
• Secondly, select Cell E5 and type the formula:

• After that, hit Enter to see the result.

Here, the IF Function is checking whether Cell C5 is greater than Cell D5. If it is true, then it displays TRUE in the output. And if Cell D5 is greater than Cell C5, then it shows False.

• Finally, use the Fill Handle to see results in all cells.

2.2 Highlight the Greater Value

Here, we will highlight the greater value comparing the two columns. Follow the steps to know the procedure.

STEPS:

• To highlight the greater value, select the cells of Column C. We have selected Cell B5 to Cell B11.

• After that, go to the Home tab and select Conditional Formatting. A drop-down menu will appear.

• Select New Rule from the drop-down menu. The New Formatting Rule window will occur.

• Here, select Use a formula to determine which cells to format from the Select a Rule Type field.
• Then, write the formula in the Format values where this formula is true field:

• After typing the formula, select It will open the Format Cells window.
• Select Fill from the Format Cells window and choose a color that you want to use to highlight the cells.
• Click OK to proceed. Also, click OK in the New Formatting Rule window.

• After clicking OK, you will see results like below.

• Now, select the cells of Column D. We selected Cell D5 to Cell D11.

• After selecting the cells, go to the Home tab and select Conditional Formatting.
• Then, select the New Rule. The New Formatting Rule window will appear.
• Select Use a formula to determine which cells to format from the Select a Rule Type field.
• Type the formula below in the Format values where this formula is true field:

• Then, select Format and choose a color from there and click OK.

• Finally, click OK in the New Formatting Rule window to see results like below.

3. Compare Two Columns and Highlight the Greater Value with MAX Function

In this method, we will use the MAX Function to compare two columns. The MAX Function returns the largest value in a set of values. It also ignores values and texts. It is very effective when you are working with numeric values. We will use the same dataset with an extra column.

3.1 Compare Two Columns

At first, we will compare the values of the two columns using the MAX Function. Let’s follow the steps below.

STEPS:

• Create an extra column at the beginning. Column E is our extra column.

• After that, select Cell E5 and type the formula:

• Hit Enter to see the result.

Here, the MAX Function is comparing the value between Cell C5 and Cell D5. Then shows the greater value in the Helper column.

• Finally, use the Fill Handle to see results in all cells.

3.2 Highlight the Greater Value

To highlight the greater value, we will use conditional formatting. Let’s observe the steps below.

STEPS:

• In the beginning, select the values of Column C. We have selected Cell C5 to C11 here.

• After that, go to the Home tab and select Conditional Formatting. A drop-down menu will occur.

• Select New Rule from the drop-down menu.

• Instantly, the New Formatting Rule window will appear.
• Select Use a formula to determine which cells to format from the Select a Rule Type field.
• Then, write the formula in the Format values where this formula is true field:

• After that, select Format.

• After selecting Format, the Format Cells window will occur. Select Fill and choose a color to highlight the cells. Then, click OK to proceed. Also, click OK in the New Formatting Rule window.

• After clicking OK, you will see results like below.

• To highlight the greater values of Column D, select Cell D5 to Cell D11.

• Now, go to the Home tab and select Conditional Formatting.
• Then, select the New Rule from there. It will open the New Formatting Rule window.
• Select Use a formula to determine which cells to format from the Select a Rule Type field.
• Then, write the formula in the Format values where this formula is true field:

• Select Format to choose a color and click OK to proceed. Again, click OK in the New Formatting Rule window.

• Finally, you will see results like below.

4. Insert Formula to Compare Two Columns in Excel and Highlight the Greater Value

In this last method, we will use a simple formula to compare the column values. To highlight the values, we will again use Conditional Formatting. Let’s pay attention to the steps below to know more.

4.1 Compare Two Columns

Here, we will compare the two columns at the beginning. Let’s follow the steps below.

STEPS:

• In the first place, insert a helper column and type the formula:

• Hit Enter to see the result.

Here, the formula checks whether the value of Cell C5 is greater than Cell D5. If Cell C5 is greater than Cell D5, then it will display TRUE in the output. Otherwise, it will show False.

• In the end, use the Fill Handle to see results in all columns.

4.2 Highlight the Greater Value

In this sub-method, we will try to highlight the greater values with conditional formatting. Let’s observe the steps below.

STEPS:

• Select the values of Column C at first. Here, we have selected Cell C5 to Cell C11.

• After that, go to the Home tab and select Conditional Formatting.

• A drop-down menu will appear. Select New Rule from there. It will open the New Formatting Rule window.

• Now, select Use a formula to determine which cells to format from the Select a Rule Type field.
• Then, write the formula in the Format values where this formula is true field:

• After typing the formula, select It will open the Format Cells window.
• Select Fill from the Format Cells window and choose a color that you want to use to highlight the cells.
• Click OK to proceed. Also, click OK in the New Formatting Rule window.

• After clicking OK, the greater values of Column C will be highlighted.
• Again, select the values of Column D to highlight. We have selected Cell D5 to Cell D11.

• Now, follow the same steps to open the New Formatting Rules field.
• After that, select Use a formula to determine which cells to format from the Select a Rule Type field.
• Write the formula in the Format values where this formula is true field:

• Select Format to choose the color and click OK.
• Finally, you will see results like below after clicking OK in the New Formatting Rule window.

Download Practice Book

Download the practice book here.

Conclusion

We have discussed 4 easy and quick methods to compare two columns in Excel and highlight the greater value. I hope these methods will help you to solve your problems. Furthermore, we have also added the practice book at the beginning of the article. You can also download it to exercise more. Last of all, if you have any queries or suggestions, feel free to ask in the comment section below.

<< Go Back to Columns | Compare | Learn Excel