In this Excel tutorial, we will explain everything about how to compare tables in Excel. We will discuss 6 different ways of comparing tables as well as demonstrate the steps of comparing two pivot tables in Excel. We will also show the use of VBA macro to compare two tables and highlight differences. Hopefully, this article will help you understand how to compare tables in Excel.
While preparing this article, we used Microsoft 365, but the operations apply to all versions.
Tables in Excel are used to organize and analyze data. They provide a structured format for managing and working with datasets. We frequently need to compare two tables in Excel when working with tables. There are various ways to compare tables in Excel.
⏷ Apply Conditional Formatting to Compare Tables and Highlight Differences
⏵ Use Unique Feature to Compare Two Tables
⏵ Use Formula to Compare Two Tables
⏷ Apply VBA to Compare Two Tables and Highlight Differences
⏷ Apply Power Query to Compare Two Tables and Merge All Values
⏷ View Two Tables Side by Side in Excel to Compare Them
⏷ Steps to Compare Two Pivot Tables
⏵ Create Two Pivot Tables in One Sheet
⏵ Insert the Formula to Calculate Differences Between Pivot Tables
⏷ Frequently Asked Questions
⏷ Compare Tables in Excel: Knowledge Hub
1. Apply Conditional Formatting to Compare Tables and Highlight Differences in Excel
You can apply the built-in unique feature or a custom formula to compare tables and highlight differences in Excel. In the custom formula, you can use the Not Equal (“<>“) operator or the COUNTIF function.
1.1 Using Unique Feature to Compare Two Tables
You can create a unique formatting rule to compare two tables and highlight the differences. To do so,
- Select full table cell range B4:F10.
- Then go to the Home tab >> Conditional Formatting >> Select New Rule.
- Select “Format only unique or duplicate values” from the Rule Type section under the New Formatting Rule dialog box.
- Select “unique” from the Format all section.
- Choose a background color using the Format button and click OK.
The final output looks like the following. As you can see, the differences between the two tables are highlighted.
1.2 Using Formula to Compare Two Tables
We can use the Not Equal (“<>”) operator or the COUNTIF function in the Conditional Formatting formula to compare two tables in Excel.
Using Not Equal (“<>”) Operator
- Select cells F5:F10.
- Then go to the Home tab >> Conditional Formatting >> Select New Rule.
- Select “Use a formula to determine which cells to format” under the Select a Rule Type section from the New Formatting Rule dialog box.
- Type the following formula in the Edit the Rule Description box and click on Format.
=F5<>C5
- Select a color from the Background Color section under the Fill tab.
- Click OK.
- Finally, click on OK and you are done.
Hence, we have compared two Excel tables and also highlighted the differences:
Using COUNTIF Function
The COUNTIF function counts the number of cells within a range that meet the given condition. The syntax of this function is given below:
=COUNTIF(range, criteria)
Click the image for a detailed view
Apply the following steps to compare two tables with this function in the Conditional Formatting formula.
- Select cell range C5:C10.
- Go to Home tab >> Conditional Formatting >> Select New Rule.
- Select “Use a formula to determine which cells to format” from the Select a Rule Type section under the New Formatting Rule dialog box.
- Type the following formula in the Edit the Rule Description box.
=COUNTIF(F5:F10,C5)=0- We are checking if our value from the C column is in the F column. We’ll get 0 if it’s not there.
- Afterward, we format the cells that are not in the F5:F10 range.
- Choose a background color from the Format section and click OK.
As a result, we’ve compared and highlighted the differences between the two tables in Excel:
2. Apply VBA to Compare Two Tables and Highlight Differences
If you want to compare two tables and highlight differences, you can use VBA macro to do the task. To use VBA macro,
- Firstly, from the Developer tab >> select Visual Basic.
- As a result, a new window will appear.
- Select Module from the Insert tab.
- Now insert the following code in that module.
Sub HighlightDifference()
Dim i As Long
For i = 1 To Cells(Rows.Count, "C").End(xlUp).Row
If Cells(i, "C") <> Cells(i, "F") Then
Cells(i, "F").Interior.Color = vbYellow
End If
Next i
End Sub
🔎 Code Breakdown:
Here,
- Dim i As Long → Declares a variable i to use as a loop counter.
- For i = 1 To Cells(Rows.Count, “C”).End(xlUp).Row
If Cells(i, “C”) <> Cells(i, “F”) Then
Cells(i, “F”).Interior.Color = vbYellow
End If
This Loop goes through rows in column C, starting from row 1 and going up to the last non-empty row. It checks if the value in the current row of column C is different from the value in the same row of column F. If values are different, it changes the background color of the cell in column F to yellow.
- Next i → Refers to move to the next iteration of the loop.
- Press F5 to run the code.
- Instantly, we’ll see the differences are highlighted in the second table.
3. Apply Power Query to Compare Two Tables and Merge All Values
To demonstrate how to use power query to compare two tables, we have modified the dataset a little bit. We have added a new column named Item ID to add more data to the dataset. First, we need to convert all our dataset ranges into tables.
- Click on any cell of the Products Record range >> go to Insert tab >> Select Table.
- From the Create Table window choose the range as B4:D13 >> tick on the option My table has headers >> click OK.
- As a result, you will see your Products Record dataset converted into a table.
- To name this table, click on any value inside the table >> go to the Table Design tab >> Properties >> write Products in the Table Name text box.
- Similarly, create two more tables named Shop_A and Shop_B from the individual Shop A and Shop B product records.
- Now, right-click on any cell inside the Products Record table and choose the Get Data from Table/Range option from the context menu.
- You can see that the Products table is shown in the Power Query.
- Now, at the Home tab of the Power Query, click on the arrow inside the Close & Load button >> choose the option Close & Load To.
- Put the radio button on the option Only Create Connection and click OK from the Import Data box.
- Now you have to repeat the previous procedures again individually for the Shop_A and Shop_B tables to import data from the given tables to Power Query.
- Right-click on the Queries pane area >> choose the New Query option >> Other Sources option >> Select Blank Query.
- As a result, a new query will be created.
- Rename it as “Merge All Values”.
- Click on the Merge All Values query and insert the following formula to compare two tables and merge values through the power query.
- Press Enter.
=List.Union({Shop_A[Name],Shop_B[Name]},Comparer.OrdinalIgnoreCase)
Thus, you have compared and merged two tables and got all the unique items from Shop A and Shop B.
4. View Two Tables Side by Side in Excel to Compare Them
We can arrange two Excel windows side by side using “View Side by Side” mode. Using this method, you can visually compare two workbooks or two sheets in a single workbook.
Let’s say we have two different workbooks of products recorded by Shop A and Shop B. We want to view both of them side by side. To open both these Excel files follow the steps below:
- Open both Excel files you want to compare.
- Go to the View tab>> Window group>> click the Arrange All option.
- Select Vertical from Arrange Windows and click OK.
- As a result, two Excel windows will appear side-by-side, as in the screenshot below.
Click the image for a detailed view
How Can You Compare Two Pivot Tables in Excel
In this part, we are going to talk about comparing two pivot tables in Excel. We will discuss the entire process in two steps. Please follow the steps below.
Step-01: Creating Two Pivot Tables in One Sheet
In this step, we will create two pivot tables in one sheet using the two datasets for Shop A and Shop B.
- Go to the Insert tab >> PivotTable drop-down >> Select From Table/Range.
- Select the range of the first table for Shop A in the Table/Range text box from the PivotTable from table or range dialog box.
- Select New Worksheet and click OK.
Click the image for a detailed view
- As a result, you will be taken to a new sheet where you will have two portions; PivotTable1, and PivotTable Fields.
- Drag down Item to Rows area and Price to the Values.
- Now, to insert the second table in this same sheet we have to insert the table again.
- After that, select cell A14 or any other cell where you want to enter the second PivotTable.
- Go to the Insert tab >> PivotTable dropdown >> Select From Table/Range.
- Select the range of the second table for Shop B in the Table/Range text box in the PivotTable from table or range dialog box.
- The Existing Worksheet option and the Location value will be selected automatically.
- Finally, click OK.
Click the image for a detailed view
- Like the previous pivot table, drag down Item to the Rows area and Price to the Values.
- Thus, we have inserted two pivot tables in one sheet.
- After that, we created a space for gathering the differences between different items following the second table.
Step-02: Inserting the Formula to Calculate Differences Between Pivot Tables
Now, in this step, we will insert the formula and calculate the differences. To do so,
- Type the following formula in cell B26.
=GETPIVOTDATA("Sum of Price",$A$4,"Item",D8)-GETPIVOTDATA("Sum of Price",$A$15,"Item",D8)- Drag down the Fill Handle.
- As a result, you will get the values for the rest of the cells and will be able to calculate all of the differences between the item prices for Shop A and Shop B.
Frequently Asked Questions
1. What are the built-in tools in Excel for comparing tables?
Answer: In Excel, you can use Conditional Formatting to visually highlight differences between tables by applying formatting rules. Functions like VLOOKUP, HLOOKUP, IF, COUNTIF, etc. can also help us find and extract matching or non-matching data between tables. You can use Data Validation to maintain consistency and identify discrepancies, while Table Filters and Sorting assist you in organizing data for comparison. PivotTables helps us summarize and analyze data from multiple tables, and Power Query aids us in data transformation and consolidation for comparisons.
2. How can I compare multiple columns between two tables in Excel?
Answer: When comparing multiple columns between two tables in Excel, you can use the VLOOKUP function to compare a specific column between two tables. By comparing the values in one table with the values in another, you can identify matches or differences. The VLOOKUP function can return a value if a match is found or an error if there is no match.
Download Practice Workbook
Conclusion
In this article, we have included all the information you need to know about comparing tables in Excel. Throughout this article, we showed various ways to compare tables including using the Not Equal (<>) operator, creating a unique formatting rule, implementing the COUNTIF function, using VBA Macro, etc. We also explained how to compare two pivot tables in Excel. Hope you found this article informative and useful. Let us know if you have any questions, comments, or suggestions.
