Reading values in one tab are easy when it comes to excel. But looking up values in two different tabs is not easy for some people. However, there is a way that you can match data using a formula to make your work easier. You will learn several ways on how to match data in two worksheets in this tutorial.
The first thing that you think of when you get two same workbook versions is to compare them. Then you will probably merge these two worksheets after comparing them for differences. Another great advantage of comparing data in two worksheets is that it helps you spot issues such as wrong formatting, inconsistent formulas, duplicate records and broken links.
1. Viewing them side by side
If you are not dealing with very large sheets, you can view them side by side. This method is quick and easy and you can compare two sheets in a workbook or two different workbooks visually.
For example, if you want to compare two sales report for different moths side by side and want to figure out the best selling products then you need to follow these steps.
Steps
1. Open the workbooks that need to be compared
2. Click on the View tab
3. Click on the view side by side button and that's it
The two windows will be displayed horizontally by default. However, if you can split them vertically by clicking then arrange all buttons then select vertical.
You can also arrange them in tiled or cascade windows when comparing more than two worksheets. Turn on the synchronous scrolling option on if you want to scroll down the two worksheets simultaneously. You can find them synchronous scrolling option right under the side by side option on the view tab.
2. Comparing two sheets in the same workbook
If the sheets you want to compare are in the same workbook then you need to perform these steps.
1. Open your excel file
2. Then click on the view tab, open window group and then click new window button
3. The same file will be opened in a different window
4. Click the corresponding button on the ribbon to enable side by side view.
5. Now choose the first sheet on window 1 and then window 2 choose the second sheet
3. Using third-party tools
Microsoft Excel comes with a handful of features and it also allows integration of third-party tools that make work easier. The built-in tools can match data in two worksheets but not as comprehensively as the third-party tools. So if you are targeting to spot all the differences in formatting, formulas, or values then you need to use the following tools.
1. Change pro for excel
2. xlCompare
5. synchronizer Excel Compare
Third-party tools are very efficient when it comes to comparing excel sheets. They are easy to use and specifically for updating, comparing, and merging excel sheets. The above three techniques are very effective when it comes to matching data in two Excel worksheets. Also, Softwares are very helpful especially when one is dealing with huge chunks of data.