How To Match Data from Two Excel Sheets In 3 Easy Methods
Mar 06, 2023
Would you like to match data from two different Excel sheets?
It might seem complex, but matching data from two Excel sheets is easier than you think.
You'll soon find yourself joining rows of data together like an expert in just a few simple steps.
This article will cover how to:
- Compare two Excel sheets in separate Excel files.
- Compare two Sheets in the same workbook.
- Highlight the differences between two Excel sheets with conditional formatting.
Read more: How to make a cycle plot chart in Excel.
Compare Two Excel Sheets in Separate Excel Files.
There's a built-in feature on Microsoft Excel to compare two separate Excel Files or two Excel Sheets in the same workbook, and it's the View Side by Side option.
This is only recommended when you have a small dataset. Manually comparing Excel files or Excel Sheets can take a lot of time, especially for extensive data sets. However, this can be an excellent option for the right scenarios.
For example, if you have two files with information from different months and want to know which file values are different.
Read more: The difference between Excel workbook and worksheet.
You can open two files simultaneously using the side-by-side view. This means you can compare the data without going back and forth between them.
How to align the two files side by side and compare them.
- Open the Excel Files you want to compare.
- Find the View Tab.
- Choose the View Side by Side option. You can only do this when two or more Excel files open simultaneously.
- When you click View side by side, it will show the two files next to each other. You can edit or compare them while they are next to each other.
- If you want to arrange the files in a vertical line, click on the Arrange All option in the View tab.
- This will open up a new dialog box called Arrange Windows. In this new box, select the option for Vertical.
Compare Two Sheets in the Same Workbook.
If you want to compare two sheets in the same workbook, use the 'New Windows' feature in Excel. This allows you to open two instances of the same Excel workbook.
- Open the Excel workbook with the Excel sheets you want to compare.
- Find and Select the View Tab.
- Click on the New Window option in the Window group. This will open a new window with the same Excel workbook.
- Navigate to the View tab and click on Arrange All. This will open a dialog box where you can choose Arrange the Windows option.
- You can compare data in columns by selecting the Vertical option or compare data in rows by selecting the Horizontal option, and lastly, click OK.
Read more: How to do a search on an Excel spreadsheet.
Highlight the Differences Between Two Excel Sheets with Conditional Formatting.
- Select the data in the sheet that you want to change. For example, select the data in the July sheet to check how prices have changed from June to July.
- Select the Home Tab
- Click the Conditional Formatting in the Styles Group.
- Click the New Rule option.
- Click "Use a formula to determine which cells to format" in the New Formatting Rule Dialog Box.
- In the formula field, enter this: =B2<>June!B2
- Click on the Format button. In the Format Cells dialog box, click on the Fill tab and select the color you want to use to highlight the data that does not match.
- Click OK.
The steps above help you see any changes in the data set on both sheets.
Read more: How to calculate confidence interval in Excel.
Final Thoughts on How to Match Data From Two Excel Sheets
Now that you've learned how to match data from different sheets, you can easily compare data, make it more organized, and match up perfectly.
You can visit our home page for more easy-to-follow how-to and step-by-step guides. Check the links in related articles for further details about Excel/Google Sheets templates!
Frequently Asked Questions on How to Match Data From two Excel Sheets:
What is the best way I can compare two data sets in Microsoft Excel?
A dual-axis line chart is an excellent way to compare two data sets. The chart has a second y-axis to help you see the data points' differences.
Why should I use the Match function in Excel?
The Match function looks for a specified item in a range of cells and then tells you where that item is located in the range.
How can I determine if two data sets are similar?
A t-test is a way to compare the average values of two different groups of data. It helps us determine if the data came from the same population.
Related Articles:
How to Merge Cells In Google Sheets: Everything You Need to Know
How to Delete Sheets in Excel: Deleting Multiple Sheets at Once
Want to Make Excel Work for You?ย Try out 5 Amazing Excel Templates & 5 Unique Lessons
We hate SPAM. We will never sell your information, for any reason.
