You have been given files Excel that represent the variations of some data in certain situations or periods of time, and you have to analyze them: this operation could be complex and time-consuming, if you think you have to look at the data cell by cell, but luckily you don't necessarily have to do it. Don't you know what I'm referring to? Then let me help you out.
In today's guide, I'll walk you through how to compare two excel files through some tips that will help you easily reach your goal. In fact, I will show you how to place two workbooks side by side, so that you can compare the data manually (an operation that you should perform only and only if you have very little data in a simple structure) and then I will show you a procedure that will allow you to use a component Microsoft Excel add-on to analyze the data in two spreadsheets in order to view and export only the differences between the two files in a few simple clicks.
I bet you can't wait to start looking through all the tips I've written for you, right? Very well! Then let's not waste any more precious time! What you have to do is simply give me a few minutes of free time to read the procedures that I will show you in the next chapters. I'm sure that, by the end of reading, you too will have learned how to compare two Excel files in order to speed up your work. All I have to do is wish you a good reading and, above all, a good job!
Tile Two Worksheets
The simplest method to compare two Excel worksheets is to tiling the windows of the program so that you can analyze the data of the two documents visually. Obviously, this procedure can only be useful if you have an Excel file with little data.
If you need to analyze workbooks with little data in them, first open both Excel files and then go to the screenshot of one of them in Excel. At this point, click on the tab View and premium tasto Tile, located in the section Window. By doing so, the two Excel files will be arranged on the screen side by side, in order to compare the data entered.
If the files are not arranged next to each other, click on the button Arrange everything, located in the section Window of the card View. In the box that is shown to you, then select the option Side by side and premium tasto OK.
By default it is also enabled Synchronous scrolling: if you scroll through a spreadsheet, the same operation will automatically be performed on the other side by side sheet, so that you can compare files without ever losing sight of the data you are scrolling in both workbooks.
Via the button Arrange everything, the window arrangement display can be changed by setting that horizontal, in case the vertical tiling is not comfortable to carry out this comparison work.
If, on the other hand, the Excel files to be compared have a lot of data, the solution indicated in the previous lines can be long and inefficient, as it is susceptible to errors. In this case, I recommend that you read the tips in the next chapter of this tutorial, which can help you even in this more complex situation.
Compare Files Mode
Su Microsoft Excel there is a feature called Compare Files (Compare Files) which allows you to compare the data of two spreadsheets that have a similar structure, but which report partially or completely different data. In the next few lines, I will show you how to enable it and how to best use it.
Enable the Add-on
The mode Compare Files may not be enabled by default in Microsoft Excel. In this regard, it is necessary to enable it by acting in the software settings and activating a pre-installed add-on. To do this, click on the tab Fillet Excel and, in the screen you see, select the item options from the left sidebar. If you have performed this procedure correctly, you will be shown a panel, through which you will have to select the tab Additional components, located on the left.
Now, on the right, you will be offered a table with all the add-ons currently enabled on Microsoft Excel. Among these, ensure that the voice is present Inquire, showing that the add-on is already active.
If not, move to the area below and click on the drop-down menu next to the wording Manage. At this point, select the item COM Add-ins and then press the button vai. In the box that is shown to you on the screen, put a check mark on the item Inquire and then press the button OK.
Very well! We are almost there! With the procedure just performed, you will have enabled a new tab in the toolbar of Microsoft Excel, called Inquire. Before reaching this tab, make sure the files to compare are both open in Microsoft Excel.
Compare Two Files
Now that you've enabled the add-in, it's time to start the file comparison mode in Microsoft Excel. To do this, after opening both files, click on the tab Inquire Excel and press the key Compare FilesIn section Compare.
In the box that is shown to you, use the drop-down menu to select both files to compare and press the button Compare. By doing so, you will be shown a new screen in which the different data present in the two spreadsheets will be compared.
Analysis of the Results
After enabling file comparison in Excel, the cells that have undergone changes will be colored to show you these changes visually. The cells will take on different colors based on the type of change that has occurred.
In the lower left area, there is a legend which explains the meaning of each coloring. By placing a check mark on a color, if the comparison tool detects that that specific variation has occurred, the cell with that color will be shown.
For example, by activating the box Entered Values, will be shown in color green all those cells where values have been manually entered and which have different data from one sheet to another.
Just to give you another example, the box Calculated Values highlights in blue all those cells in which there is an automatic calculation of the data, for which there are differences between the two sheets. In the area below, however, a summary of all the changes that were found by the comparison tool of Microsoft Excel. Clicking on one of the results will highlight the relevant cell in both sheets, in the central area of the screen.
In the lower right area, through a graph, it is possible to view the amount of variations that have been found for each type of comparison.
In case you want to export the results of the comparison between the two files, using the button Export Results it is possible to download a report in Excel format, in which the variations of the data that have been found will be reported on each row.
Please note: There is also an online version of Excel that works directly from the browser, however this does not include tools to compare two workbooks. The same goes, alas, for the mobile application of Excel for Android and iOS (free on all devices with dimensions equal to or less than 10.1 ″)