How to Compare in Excel? (10 Practical Examples)

Compare in Excel means comparing two or multiple cells, columns, worksheets, or workbooks in Excel to find out matches and differences.

In this free Excel tutorial, we will learn how to compare cells, columns, worksheets, and workbooks using Excel’s features, functions, and VBA.

In the following overview image, we have compared two different lists containing the available products in a store and ordered products. If the ordered products are in the list of available products, they are remarked as “Available” and if they are not in the list, they are remarked as “Not Available”.

In this blog post, we will learn how to compare two or more columns for row matches and also compare by highlighting rows with similarities and differences. We will compare two cells from different worksheets and workbooks as well. Later on, we will apply a VBA code to dynamically compare two lists of cells based on user input.

⏷Compare Two Columns Row-by-Row
   ⏵Compare Numeric Values
   ⏵Compare Text Strings (Case Sensitive/Insensitive)
   ⏵Comparing Date Values in Excel
⏷Compare Multiple Columns for Row Matches
⏷Compare Two Lists for Similarities and Differences
⏷Compare Two Lists and Pulling Matched Data
⏷Compare and Highlight Row Similarities and Differences
⏷Compare Any 2 Cells and Write Remarks in Excel
⏷Comparing 2 Columns in Different Sheets
⏷Comparing 2 Different Excel Workbooks
⏷Statistical Comparison in Excel
⏷Using VBA to compare 2 Columns Based on User Input

Why Do We Compare in Excel?

We compare data for identifying differences, detecting errors, tracking changes, ensuring consistency, etc. This process helps users analyze information, make informed decisions, and maintain accurate and reliable datasets.

For instance, in financial spreadsheets, comparing budgeted figures with actual expenditures helps identify variances, facilitating financial analysis and decision-making. Additionally, in project management, comparing planned timelines with actual progress allows project managers to track changes, identify delays, and ensure that the project stays on schedule.

1. Compare Two Columns Row-by-Row in Excel

Here we will use Excel formulas to compare and return values from two columns using the IF and EXACT functions. We will compare two columns of numeric values, two columns of text strings, and two columns of date values.

1.1 Compare Numeric Values

In this section, we’ll compare two columns of numeric values using the IF function. In the following dataset, we have a list of 10 electronic products, along with their current and previous prices listed accordingly. Now we will compare these prices and evaluate whether the prices have changed or not.

So follow the steps below to complete the task:

Steps:

• Inside cell E7, write the following formula:

• Press ENTER. This will return “Change” if the prices are not the same or will return “No Change” if the prices remain constant.

• Use the Fill Handle to copy the formula in the other cells below.

The rows with “No Change” remarks are highlighted for better visualization.

1.2 Compare Text Strings (Case Sensitive/Insensitive)

In this section, we’ll compare text strings in two columns in Excel based on case sensitivity using the IF and EXACT functions.

In the dataset below, we have taken a list of famous brands and the names of their owners. All the brand names are given after their owners’ names. Now we will compare these text strings and evaluate if the strings are matched or not.

Follow the steps below to compare case-insensitive text strings:

Steps:

• Write the following formula inside cell D7:

• Press ENTER and use AutoFill to copy the formula down.

Only the misspellings will result in “No Match”. Otherwise, all the names are matched regardless of their case sensitivity.

To compare case-sensitive text strings, use the formula:

Only the exact matches with the right spellings and case sensitivities are matched.

1.3 Comparing Date Values in Excel

Here, we will compare dates in two columns in Excel using the IF function.

In the following dataset, we have a list of 10 projects along with their due dates and submission dates. Now we will compare these dates and evaluate if the projects were submitted before the deadlines or not.

To evaluate that, follow the steps below:

Steps:

• Type the following formula inside cell E7:

• Press ENTER and use the Fill Handle to copy the formula into the rest of the cells.

The projects that were submitted before the deadline are remarked with “On Time”. And the projects that were submitted past the due date are remarked with “Delayed”.

2. Compare Multiple Columns for Row Matches

Here, we will compare multiple columns for row matches in Excel, combining the IFS, AND, and OR functions.

In the following dataset, we have a list of 10 electronic products along with their prices in the years 2021, 2022, and 2023. Now we will compare these 3 columns in Excel and evaluate whether these prices are fully stable, almost stable, or unstable.

Follow the steps below to compare the prices in three different columns:

Steps:

• Write the following formula inside cell F7:

• Press ENTER and use Autofill to copy the formula down.

The prices that are different in all 3 years are remarked as “Unstable”.

The prices that are constant throughout any two years and different in only one year are remarked as “Almost Stable”.

Finally, the prices that have remained constant for all three years are remarked as “Fully Stable”.

3. Compare Two Lists for Similarities and Differences in Excel

In this section, we will compare two lists for similarities and differences in Excel by combining the IF and COUNTIF functions.

In the dataset below, we have two separate lists. One contains the ordered products, and the other contains the products that are currently available in the store.

Now, we will compare these lists and determine which ordered products are currently available in the store.

Follow the steps below:

Steps:

• Inside cell E7, write the following formula:

• Press ENTER and use the Fill Handle to copy the formula in the other cells below.

The available products are highlighted for better visualization.

Now, if any new item is listed as available in the store and matches the list of ordered products, it will be automatically marked as “Available” as well as highlighted.

4. Compare Two Lists and Pulling Matched Data

In this part, we will compare two lists and pull matched data in Excel using the VLOOKUP function.

We have a list of 10 electronic products along with their prices in the following dataset. In cell E7, we will make a drop-down list in Excel with some product names that are not all available in the Electronic Product list. Now, we will select any of the products from the search product list to compare their value with the electronic product lists. For any matched value in the following lists, it will pull the price data of the electronic product, and for any unmatched value, it will return “Not Available”.

To complete the task, follow the steps below:

Steps:

• Inside cell F7, type the following formula:

• Press ENTER.

The following GIF shows the corresponding prices getting changed as we select different products. If we select a product that is not included in the given list, it will show “Not Available”.

Note: We can alternatively use any of the following two formulas:

Or,

5. Compare and Highlight Row Similarities and Differences

Here, we will compare two columns in Excel for match and highlight row similarities and differences using Conditional Formatting in Excel.

In the dataset below, we have a column containing the previous price and another column containing the current prices of 10 electronic items. Now, we will compare these columns and highlight the rows containing the products with changed prices.

Follow the steps to accomplish the task:

Steps:

• Select the range B7:D16.
• Go to Home => Conditional Formatting in the Styles group => Select New Rule…

A dialogue box named New Formatting Rule pops out.

• Under Select a Rule Type:, select Use a formula to determine which cells to format.
• Under Format values where this formula is true:, write the following formula:

• Click on Format…

A new dialogue box named Format Cells pops out.

• Click on the Fill tab and select any suitable fill color.
• Click on OK.

• Again, click on OK inside the New Formatting Rule dialogue box.

This highlights the rows containing the products with the changed prices.

6. Compare Any 2 Cells and Write Remarks in Excel

In this section, we will compare and return remarks if 2 cells match in Excel. In the following dataset, we have a list of 10 electronic products along with their monthly sales. The cell C18 holds the value of the maximum sales. The formula for calculating the maximum sales is:

Now we will compare the maximum sales in C18 with the sales of the selected product from the drop-down list in cell C19 and get the correct remark.

To carry out the task, follow the steps below:

 Steps:

• Write the following formula inside cell C20:

• Press ENTER.

The following GIF shows that, and the remark changes by comparing two cells as we select different products.

7. Compare 2 Columns in Different Excel Worksheets

In this part, we will compare two columns in different Excel worksheets using the IF function.

In the dataset below, we have the previous prices of 10 electronic products in one worksheet and the current prices of the same products in another worksheet.

Now we will compare these two columns and determine whether the prices have changed or not.

To complete the task, follow the steps below:

Steps:

• Go to the ComparingSheets_2 sheet => Select cell D7 => Write the following formula:

• Press ENTER and use the Fill Handle to copy the formula into the rest of the cells.

The current prices that are different from the previous prices are remarked with “Yes” and the current prices that are similar to the previous prices are remarked with “No”.

8. Compare 2 Different Workbooks in Excel

In this part, we will compare two columns in different Excel workbooks using the IF function.

In the dataset below, we have the previous prices of 10 electronic products in one workbook and the current prices of the same products in another workbook.

Now we shall compare these two columns from the workbooks “Compare in Excel” and “Compare in Excel Sub1” and determine whether the prices have changed or not.

To complete the task, follow the steps below:

Steps:

• Go to the Compare in Excel Sub1 workbook => Comparing Workbooks_2 worksheet => Select cell D7 => Write the following formula:

• Press ENTER and use the Fill Handle to copy the formula into the rest of the cells.

The current prices that are different from the previous prices are remarked with “Yes” and the current prices that are similar to the previous prices are remarked with “No”.

Note: In the formula inside the IF function, we have used the folder path appropriate for us. However, the users should use their own folder path where the files are actually located.

9. Statistical Comparison in Excel

Here we will do a statistical comparison of two data sets in Excel. In the following dataset, we have six monthly sales quantities of wireless and wired headphones in two different columns. We will calculate the Mean, Standard Deviation, Coefficient of Variation, and Range of these two different lists.

To calculate the Mean of Wired Headphones use the formula below:

The formula to calculate the Standard Deviation of Wired Headphones is:

To calculate the Coefficient of Variation of Wired Headphones use the formula below:

To calculate the Range of Wired Headphones use the formula below:

In the same way, we have calculated the Mean, Standard Deviation, Coefficient of Variation, and Range for the Wireless Headphones.

Now we shall compare these 4 parameters and determine which set of data has a greater value for each of the parameters.

To do the job, follow the steps below:

Steps:

• Inside cell C19, type the following formula:

• Press ENTER and use the Fill Handle to copy the formula into the rest of the cells.

10. Comparing 2 Columns Based on User Input Using Excel VBA

Here we will use VBA Macro to compare two columns. Here, the users will enter the column names to compare and then input the column name where they want to get the output.

To complete the task, follow the steps below:

Steps:

• Click on Developer => Visual Basic.

A new window named Microsoft Visual Basic for Applications will open.

• Inside the Visual Basic window, click on Insert => Module.

A blank module will open for you to write the VBA code.

• Inside the module, write the following code:

Sub CompareColumnsWithUserInput()
Dim sheet As Worksheet
Dim rowEnd As Long
Dim i As Long
Dim comparecolumn1 As String
Dim comparecolumn2 As String
Dim outputcolumn As String

' Set the worksheet
Set sheet = ThisWorkbook.Sheets("Comparing Using VBA")

' Get user input for columns to compare
comparecolumn1 = InputBox("Enter the name of the first column to compare (e.g., A):")
comparecolumn2 = InputBox("Enter the name of the second column to compare (e.g., B):")

' Get user input for result column
outputcolumn = InputBox("Enter the name of the column to output the result (e.g., C):")

' Find the last row in the specified columns
rowEnd = sheet.Cells(sheet.Rows.Count, comparecolumn1).End(xlUp).Row

' Loop through each row and compare values
For i = 7 To rowEnd ' Assuming data starts from row 7, modify as needed
If sheet.Cells(i, comparecolumn1).Value = sheet.Cells(i, comparecolumn2).Value Then
sheet.Cells(i, outputcolumn).Value = "Not Changed"
Else
sheet.Cells(i, outputcolumn).Value = "Changed"
End If
Next i
MsgBox ("The changed prices are detected!")
End Sub

• Save the file and close the Visual Basic window.
• Now click on Developer => Macros.
• Inside the dialogue box named Macro, select the code you have just written and click on Run.
• Three dialogue boxes will appear one by one. In the first two dialog boxes, we will input the two column names to compare. In the last dialog box, we’ll input the column name where we want to get the output.

This will remark the changed prices as “Changed” and the unchanged prices as “Not Changed”. Changed prices are highlighted for better visualization.

Download Practice Book

This article has shown how to compare in Excel. The ten examples have discussed the techniques for comparing two or more columns, cells, worksheets, and workbooks. We have shown how to write remarks, pull matched data, highlight similarities or differences, etc. for showing comparisons in Excel. From now on, you can dynamically compare two lists of cells based on user input using VBA Macro. Leave a comment for any further queries.

Compare in Excel: Knowledge Hub

<< Go Back to Learn Excel