EDUCBA

Home Excel Excel Resources Excel Tips Compare Two Columns in Excel

Compare Two Columns in Excel

By Madhuri ThakurMadhuri Thakur

Compare two columns feature image

Compare Two Columns in Excel (Table of Contents)

Compare Two Columns in Excel

Comparing two columns in Excel is comparing the values in those columns. There are different ways to do this. In a first way, we can select the parallel cells of the columns, which we want to compare by separating them with an equal sign (“=”). This will give us the output as TRUE and FALSE. If a cell of the columns is matched, then we will get TRUE, else FALSE. In another way, we can use the IF function to match the column cells the way we do by putting an equal sign to get the message we want when columns are matched.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

How to Compare Two Column Data?

There are a lot of ways to do that. They are:

You can download this Compare Two Columns Excel Template here – Compare Two Columns Excel Template
  • Compare data in columns – Row-Wise
  • Compare two columns Row-wise – Using Conditional Formatting.
  • Compare data Column wise – Highlight the matching data

Compare Two Column Data By Row-wise – Example #1

When we are looking for an exact match, use this technique. Let’s take the below example to understand this process.

We have given the below dataset where we need to analyze the sold product of two stores month-wise. This means, Is the same product purchased by the customer in the same month or not?

Compare two columns example 1.1

If the same product is being sold month-wise, the result should return as ‘TRUE’ otherwise ‘FALSE’.

For this, we will apply the below formula:

=B4=C4

Refer to the below screenshot:

Compare two columns example 1.2

The result is shown below:

Compare two columns example 1.3

Drag & drop this formula for the rest values, and the result is shown below:

Compare two columns example 1.4

Using Conditional Formatting – Example #2

By using conditional formatting, we can highlight the data in cells. If the same data point exists in both the columns row-wise, then we want to highlight those cells. Let’s take an example for this.

We have given year-wise performance data where we want to analyze if we have achieved the same performance in the same year.

Compare two columns example 2.1

Follow the below steps:

  • Select the whole data set range. Here we have selected cell range B4: C18.

Compare two columns example 2.2

  • Go to the HOME tab and click on Conditional Formatting under the Styles section. Refer to the below screenshot.

Using Conditional Formatting example 2.3

  • It will open a drop-down list of formatting options. Click on the New Rule option here. Refer to the below screenshot.

New Rule Option example 2.4

  • It will open a dialog box for New Formatting Rule, as shown in the below screenshot.

New Formatting Rule example 2.5

  • Click on the last option, “Use a Formula to determine which cells to format”, under the Select a Rule Type section. After clicking on this option, it will display a Formula field, as shown in the below screenshot. Set a formula under the Formula field. The formula we have used here is =$B4=$C4.Click on the Format button, and it will display the Format Cells window.

Set Formula example 2.6

  • Click on the Fill tab and choose the color from the color palette. Click on OK. Refer to the below screenshot.

Compare two columns example 2.7

The result is shown below:

Compare two columns example 2.8

Highlight the matching data – Example #3

Here we are comparing the data in the whole list column-wise. Let’s take the below example to understand this process.

Compare two columns example 3.1

  • Select the entire dataset.

Compare two columns example 3.2

  • Go to the HOME tab. Click on Conditional formatting under the Styles section. Click on Highlight Cells Rules, and it will again open a list of options. Now click on Duplicate Values from the list. Refer to the below screenshot.

Duplicate Values example 3.3

  • It will open a dialog box for Duplicate Values. Click for a Duplicate option in the left side field and choose the color for highlighting the cells. Refer to the below screenshot. Click on OK.

Compare two columns example 3.4

  • The result is shown below:

Compare two columns example 3.5

Things to Remember about Compare Two Columns in Excel

  • We also can highlight the data for the unique values also.
  • Through these techniques, we can easily compare the data in the dataset and highlight those cells.

Recommended Articles

This has been a guide to Compare Two Columns in Excel. Here we discuss Compare Two Columns in Excel and how to use the Compare Two Columns in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. Matching Columns in Excel
  2. Column Header in Excel
  3. Compare Two Lists in Excel
  4. Compare Two Columns in Excel for Matches