This will let you pick your source in other words, your first column , which will then be processed into data in the drop-down menu. Select the first column of your list and press Enter. Click OK when the data validation window appears. You should see a box with an arrow on it, which will drop-down when you click the arrow.
Click the Insert and Reference tabs. Find Lookup in the list. When you double-click it, another box should appear and you can click OK. Pick something from the drop-down list. The info should automatically change. Method 3. This will take you to XL Comparator's website, where you can upload two Excel workbooks for comparison. Click Choose File. This will open a window where you can navigate to one of the two Excel documents you want to compare. Make sure to select a file for both fields.
Once you've selected this, a pop-up message should appear at the top of the page letting you know that the file upload process has begun and that larger files will take longer to process. Click Ok to close this message. Select the columns you want to be scanned. Underneath each file name is a drop-down menu that says Select a column.
Click on the drop-down menu for each file to select the column you want to be highlighted for comparison. Column names will be visible when you click the drop-down menu. Select contents for your result file. There are four options with bubbles next to them in this category, one of which you'll need to select as the formatting guidelines for your result document.
Select the options to ease column comparison. Click the checkbox for both before proceeding. This will take you to the download page for your result document. Download your comparison document. Once you've uploaded your workbooks and set your parameters, you'll have a document showing comparisons between data in the two files available for download.
Click the underlined Click here text in the Download the comparison file box. If you want to run any other comparisons, click New comparison in the bottom-right corner of the page to restart the file upload process. This won't separate the individual sheets permanently, it simply opens up a new instance of your document. This menu will list all the spreadsheets that are currently open. If you only have two open, they will be selected automatically. Make your selection and click OK.
You'll see both spreadsheets appear on the screen. If it's more convenient, you can use the Arrange All button to switch between a vertical and a horizontal configuration. Turning this on will ensure that when you scroll one window, the other will move in sync. This is essential if you're working with a large spreadsheet and you want to continue to check one against the other.
If the two sheets become unaligned for any reason, simply click Reset Window Position. In many cases, the best way of comparing two spreadsheets might simply be to put both of them on-screen at once. However, it is possible to automate the process somewhat. Using conditional formatting , we can make Excel check for any discrepancies between two sheets. Sodiq has written thousands of tutorials, guides, and explainers over the past 4 years to help people solve problems with Android, iOS, Mac, and Windows devices.
He also enjoys reviewing consumer tech products smartphones, smart home devices, accessories, etc. Read Sodiq's Full Bio. We hate spam too, unsubscribe at any time. Back To Top. Table of Contents. Subscribe on YouTube!
On the same step, you can choose the preferred match type :. In this example, we will look for Best match by using the default No key columns comparing mode: Finally, specify which differences to highlight and which to ignore, and how to mark the differences. Cell formatting is important to us, so we select Show differences in formatting. Hidden rows and columns are irrelevant, and we tell the add-in to ignore them: Click the Compare button and allow the add-in a couple of minutes to process your data and make the backup copies.
Backups are always created automatically, so you may not worry about the safety of your data. Review and merge differences Once the worksheets are processed, they are opened side-by-side in a special Review Differences mode, with the first difference selected:. To help you review and manage the differences, each worksheet has its own vertical toolbar. For the inactive worksheet on the left the toolbar is disabled.
To enable the toolbar, just select any cell in the corresponding sheet. By using this toolbar, you go through the found differences one-by-one and decide whether to merge or ignore them:.
As soon as the last difference is dealt with, you will be prompted to save the workbooks and exit the Review differences mode. If you have not finished processing the differences yet but would like to take a break for now, click the Exit Review Differences button at the bottom of the toolbar and choose either to:. That's how you compare two sheets in Excel with our tool hope you liked it : If you are curious to give it a shot, an evaluation version is available for download here.
It identifies added, deleted and changed data and allows you to quickly merge differences. In addition, it provides the following options:. DiffEngineX is another utility that can help you identify differences between cell values, formulas, comments, Excel names, and VBA code. It can compare two Excel workbooks or selected sheets.
DiffEngineX works with xls, xlsx, xlsm and xlsb files, and supports Excel , Excel , Excel and Excel With Change pro for Excel , you can compare two sheets in desktop Excel as well as on mobile devices with optional server-based comparison.
The key features of this tool are:. Apart from desktop tools and utilities, there exist a number of online services that let you quickly compare two Excel sheets for differences without installing any software on your computer. Probably it's not the best solution in terms of security, but if your Excel files do not contain any sensitive information why not use some free online service for immediate results?
For example, you can try XLComparator or CloudyExcel , or find some other similar services on the net. This is how the CloudyExcel service looks like:. You just upload the two Excel workbooks you want to compare, and click the Find Difference button at the bottom of the screen. In a moment, the differences in two active sheets will get highlighted with different colors:. Well, this is how you can compare Excel files for differences. If none of the solutions described in this tutorial is suitable for your task, check out the following resources that cover other aspects of Excel file comparison.
And if you know any other ways to compare two Excel files, your comments will be greatly appreciated. I thank you for reading and hope to see you on our blog next week! How to compare two Excel files for differences by Svetlana Cheusheva updated on December 16, Compare two Excel files by viewing them side-by-side Compare two Excel sheets for differences using formulas Highlight differences between 2 sheets with conditional formatting Compare and merge copies of a shared workbook Specialized tools to compare files in Excel Online services to compare two Excel sheets How to compare two Excel files by viewing them side by side If you have relatively small workbooks and a sharp eye for detail, this quick and easy way to compare Excel files might work well for you.
Compare 2 Excel workbooks Let's say you have sales reports for two months and you want to view both of them simultaneously to understand which products performed better this month and which did better last month. To open two Excel files side by side, do the following: Open the workbooks you want to compare. That's it! By default, two separate Excel windows are displayed horizontally. To split Excel windows vertically , click Arrange All button and select Vertical : As the result, two separate Excel windows will be arranged side by side, like in the below screenshot.
Arrange multiple Excel windows side by side To view more than 2 Excel files at a time, open all the workbooks you want to compare, and click the View Side by Side button.
0コメント