Comparing two sheets in Excel can seem like a daunting task, but with the right steps, it’s a breeze. By using Excel’s built-in Compare and Merge Workbooks feature, you’ll be able to quickly identify differences between two sheets. Whether it’s for reconciling financial statements or checking for data discrepancies, this guide will help you through the process.
Step by Step Tutorial on Excel How to Compare Two Sheets
Before diving into the specifics, let’s get an overview of what we’re going to do. We’ll be using the ‘View Side by Side’ option, the ‘Inquire’ add-in, and conditional formatting to compare two sheets in Excel. These steps will help you find and highlight differences efficiently.
Step 1: Open both Excel files
Open the two Excel workbooks that contain the sheets you want to compare.
Having both files open is essential because you cannot compare sheets that are not active. Make sure you have permissions to access both files, and they are not set to ‘Read Only’ mode.
Step 2: Arrange the windows
Arrange the Excel windows so you can see both sheets side by side.
This can be done by clicking the ‘View’ tab and then clicking on ‘Arrange All’. Choose the option that best suits your screen setup – typically, ‘Vertical’ works well for comparing sheets side by side.
Step 3: Use ‘View Side by Side’ option
Under the ‘View’ tab, click on the ‘View Side by Side’ option.
This feature synchronizes the scrolling of both sheets, which means if you scroll down on one sheet, the other sheet scrolls too. This is particularly useful when comparing long datasets.
Step 4: Use the ‘Inquire’ add-in
If you have the ‘Inquire’ add-in installed, use it to compare differences.
To use ‘Inquire’, you need to have Office Professional Plus, Office 365 Professional, or Excel 2013 Standalone. Once you have it installed, you can find it under the ‘Inquire’ tab and select ‘Compare Files’ to start the comparison.
Step 5: Utilize conditional formatting
Use conditional formatting to highlight differences in values.
This is done by selecting the range of cells you want to compare and then going to ‘Home’ > ‘Styles’ > ‘Conditional Formatting’. Choose ‘Highlight Cell Rules’ and then ‘More Rules’ to set up custom rules for differences.
After completing these steps, you’ll have a clear understanding of the differences between the two Excel sheets. You will see variations highlighted or displayed in a comparison report, depending on the method you used.
Tips on Excel How to Compare Two Sheets
- Ensure that both sheets are laid out in a similar structure for a more accurate comparison.
- Use ‘Freeze Panes’ to keep row and column labels visible while scrolling through data.
- For large datasets, consider using the ‘Inquire’ add-in as it can handle extensive comparisons and generate reports.
- Double-check that any filters or sorting applied to the data are consistent across both sheets before comparing.
- Save a copy of the original files before using ‘Compare and Merge Workbooks’ as it can alter the data.
Frequently Asked Questions
Can I compare two sheets in different Excel files?
Yes, you can compare two sheets even if they are in different Excel files. Just open both files and arrange the windows to view them side by side.
How do I highlight differences in Excel?
You can highlight differences by using conditional formatting. Select the cells to compare, go to ‘Conditional Formatting’ and set custom rules for differences.
What is the ‘Inquire’ add-in and how do I get it?
The ‘Inquire’ add-in is a tool in Excel that helps you compare two sheets or workbooks. It’s available in certain versions of Excel, like Office Professional Plus or Office 365 Professional.
Can I compare more than two sheets at once?
Excel’s compare feature is limited to two sheets at a time. However, you can repeat the process to compare multiple pairs of sheets.
What if I don’t have the ‘Inquire’ add-in?
If you don’t have the ‘Inquire’ add-in, you can still use the ‘View Side by Side’ option and conditional formatting to compare sheets manually.
Summary
- Open both Excel files.
- Arrange the Excel windows side by side.
- Activate ‘View Side by Side’.
- Use the ‘Inquire’ add-in for comparison.
- Apply conditional formatting to highlight differences.
Conclusion
Comparing two sheets in Excel doesn’t have to be a headache. With the steps outlined above, you can easily spot discrepancies and ensure that your data is accurate and consistent. Whether you’re reconciling accounts or just ensuring that your data matches up, these techniques are essential for anyone working with complex datasets. Remember, always keep a backup of your original files, and approach the task with patience – especially if you’re dealing with large amounts of data.
If you’re ready to dive deeper into Excel’s capabilities, consider exploring other features like pivot tables, macros, and advanced formulas. The more you know, the more you can do with this powerful tool. So go on, give it a try, and excel at Excel!
Matthew Burleigh has been writing tech tutorials since 2008. His writing has appeared on dozens of different websites and been read over 50 million times.
After receiving his Bachelor’s and Master’s degrees in Computer Science he spent several years working in IT management for small businesses. However, he now works full time writing content online and creating websites.
His main writing topics include iPhones, Microsoft Office, Google Apps, Android, and Photoshop, but he has also written about many other tech topics as well.