{"id":30822,"date":"2023-09-11T19:26:21","date_gmt":"2023-09-11T17:26:21","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=30822"},"modified":"2024-06-10T21:38:54","modified_gmt":"2024-06-10T19:38:54","slug":"compare-two-sheets","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/compare-two-sheets\/","title":{"rendered":"3 Ways to Compare Two Sheets in Excel for Efficient Data Comparison and Consolidation"},"content":{"rendered":"

\"3<\/a><\/p>\n

Microsoft Excel<\/a> is a versatile tool that is used to check for data consistency and accuracy<\/strong>. One of the biggest challenges<\/strong> is to compare data<\/a> that are present in two different workbooks<\/strong>. This article explores the different methods<\/strong> in Excel to compare two sheets<\/a> to make your data consistent and avoid discrepancies.<\/p>\n

Let\u2019s look at these methods thoroughly!<\/p>\n

Download the Excel Workbook below to follow along and understand how to compare two sheets in Excel \u2013<\/strong><\/h4>\n

Download excel workbook<\/span>Compare-Two-Sheets.xlsx<\/span><\/a><\/h4>\n

 <\/p>\n

#1 – View Side by Side in Different Workbooks<\/span><\/strong><\/h3>\n

When you need to quickly compare two sheets, the View Side by<\/a> Side feature in Excel can be incredibly useful<\/strong>. It displays the two sheets side by side<\/strong> (either horizontally or vertically) for easy comparison. It is a quick and easy method for visual comparisons<\/strong> of small datasets, eliminating the hassle of constantly switching between sheets.<\/p>\n

Suppose we have sales for the months of January and February displayed in two different worksheets<\/strong> and we want to compare the values. Follow the steps below to achieve this result –<\/p>\n

STEP 1: <\/span><\/strong>Open both Excel workbooks.<\/p>\n

STEP 2: <\/span><\/strong>Go to View<\/strong> > View Side by Side<\/strong>.<\/p>\n

\"3<\/a><\/p>\n

The two worksheets will be displayed together in the same window horizontally<\/strong>.<\/p>\n

\"3<\/a><\/p>\n

STEP 3: <\/span><\/strong>Go to View<\/strong> > Arrange All.<\/strong><\/p>\n

\"3<\/a><\/p>\n

STEP 4: <\/span><\/strong>In the Arrange Windows dialog box, select Vertical<\/strong>.<\/p>\n

\"3<\/a><\/p>\n

The worksheets will be displayed vertically<\/strong> in the same window.<\/p>\n

\"3<\/a><\/p>\n

Now, you can easily compare the sales data in 2 months as they are displayed side by side.<\/p>\n

 <\/p>\n

#2 – View Side by Side in Same Workbook<\/span><\/strong><\/h3>\n

If your data is the same workbook but in a different worksheet, even then you can use the view side-by-side option. Follow the steps below to know how –<\/p>\n

STEP 1: <\/span><\/strong>Open the Excel workbook.<\/p>\n

STEP 2: <\/span><\/strong>Go to View<\/strong> > New Window<\/strong>.<\/p>\n

\"3<\/a><\/p>\n

This will open the same file in a different window.<\/p>\n

STEP 3: <\/span><\/strong>Go to View<\/strong> > View Side by Side<\/strong>.<\/p>\n

\"3<\/a><\/p>\n

This will open the same file and it can be viewed side by side.<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

#3 – Compare using Conditional Formatting<\/span><\/strong><\/h3>\n

Suppose we have extracted sales data from two different sources<\/strong> and we want to compare if there are any discrepancies<\/strong> between the two. This can be achieved by using conditional formatting and highlighting the differences<\/strong>.<\/p>\n

Follow the steps<\/strong> below –<\/p>\n

STEP 1:<\/span><\/strong> Open the Excel workbook containing the worksheets.<\/p>\n

\"3<\/a><\/p>\n

STEP 2:<\/span><\/strong> In the sheet where you want to highlight differences, select the cell range<\/strong> where you want the conditional formatting to be applied.<\/p>\n

\"3<\/a><\/p>\n

STEP 3:<\/span><\/strong> Go to the Home<\/strong> > Conditional Formatting<\/strong> > New Rule<\/strong>.<\/p>\n

\"3<\/a><\/p>\n

STEP 4:<\/span><\/strong> In the New Formatting Rule dialog box, choose Use a Formula to determine which cells to format<\/strong>.<\/p>\n

\"3<\/a><\/p>\n

STEP 5:<\/span><\/strong> Input the following formula –<\/p>\n

=B2<>Sheet2<\/strong>!B2<\/strong><\/p>\n

\"3<\/a><\/p>\n

STEP 6:<\/span><\/strong> Click on the Format<\/strong> button.<\/p>\n

\"3<\/a><\/p>\n

STEP 7:<\/span><\/strong> In the Format Cells dialog box, go to the Fill<\/strong> tab. Here, select the light red color<\/strong> that you want to use to highlight the differences and click OK<\/strong>.<\/p>\n

\"3<\/a><\/p>\n

STEP 8: <\/span><\/strong>Double-check the result in the Preview<\/strong> section. Click OK<\/strong>.<\/p>\n

\"3<\/a><\/p>\n

Now, the cell values in Sheet1 that do not match the corresponding cell values in Sheet2 will be highlighted with the selected light red fill color<\/strong>, allowing you to easily spot differences and compare two<\/a> sheets.<\/p>\n

\"3<\/a><\/p>\n

Please Note – This method cannot be used if the data is present in two different workbooks.<\/p>\n

 <\/p>\n

Conclusion<\/span><\/strong><\/h3>\n

In Excel, comparing data across different worksheets or workbooks can be a significant challenge<\/strong>. This article discusses three techniques<\/strong> to compare two sheets \u2014View Side by Side in the same or different workbooks and compare using Conditional Formatting.<\/p>\n

Whether you need a quick visual comparison or automated highlighting of differences<\/strong>, Excel equips you with the tools to achieve it. We can choose the method that best suits our specific needs<\/strong> and the complexity of the data that we are comparing.<\/p>\n

Further learning:<\/strong><\/p>\n