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.


Download Practice Book

Download the practice book here.


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

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 sale of the first month with the sale of the second month and highlight the greater value between them.


1. 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.

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

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

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

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

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

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

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

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

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

  • Next, select the cells of Column C.

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

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

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

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

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

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

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

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

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

Read More: How to Compare Two Columns or Lists in Excel


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:
=IF(C5>D5,"TRUE","FALSE")

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

  • After that, hit Enter to see the result.

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

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.

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


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.

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

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

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

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

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

  • 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:
=IF(E5="TRUE",C5)

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

  • 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.

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

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

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

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

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

  • 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:
=IF(E5="TRUE",D5)
  • Then, select Format and choose a color from there and click OK.

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

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

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

Read More: Excel formula to compare two columns and return a value (5 examples)


Similar Readings:


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.

Compare Two Columns and Highlight the Greater Value with MAX Function

  • After that, select Cell E5 and type the formula:
=MAX(C5,D5)

Compare Two Columns and Highlight the Greater Value with MAX Function

  • Hit Enter to see the result.

Compare Two Columns and Highlight the Greater Value with MAX Function

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.

Compare Two Columns and Highlight the Greater Value with MAX Function


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.

Compare Two Columns and Highlight the Greater Value with MAX Function

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

Compare Two Columns and Highlight the Greater Value with MAX Function

  • Select New Rule from the drop-down menu.

Compare Two Columns and Highlight the Greater Value with MAX Function

  • 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:
=IF(C5=E5,C5)
  • After that, select Format.

Compare Two Columns and Highlight the Greater Value with MAX Function

  • 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.

Compare Two Columns and Highlight the Greater Value with MAX Function

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

Compare Two Columns and Highlight the Greater Value with MAX Function

  • 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:
=IF(D5=E5,D5)
  • 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.

Related Content: Excel Compare Two Lists and Return Differences (7 Ways)


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:
=C5>D5

  • Hit Enter to see the result.

Here, the formula is checking 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:
=IF(C5>D5,C5)

  • 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:
=IF(D5>C5,D5)

  • 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.

Read More: How to Compare Two Columns in Excel for Missing Values (4 ways)


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.


Related Articles

 

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo