By being an Excel wizard, you can indeed save a heck lot of your time and can do complex tasks easily. Did you know that you can compare excel files with some simple tweaks and options? If not, then this article is for you.
So, do you want to know how to compare two excel files? If yes then you are certainly at the right place. Comparing the two data graphically can also make the data more representable and easy to understand. You can create charts to show the data. Here is how to create charts in Excel. So, let’s get started with the same:
How to Compare Two Excel Files?
Method 1: Using compare feature.
If you are using Microsoft Excel 2013 edition, then you can easily access the compare feature. So, as it is clearly stated that the prerequisites of this method are Excel 2013, and two workbooks that you want to compare. This is it.
Step #1: You know the obvious step, right. You need to open both the workbooks on Excel and then proceed to the next steps which will guide you how to compare two excel files?
Step #2: Once you have both the workbooks opened in your excel dashboards then you require navigating to the inquire tab. Once you are there, then you need to select “Compare Files” option from the ribbon itself.
This will open a “Select files to compare” dialog box for you.
On the dialog box, all that you require is to select both the Excel files that you want to compare. You will see two fields as “Compare” and “To”.
Step #3: After successfully going through the previous steps, you now require clicking on “Compare” button which is positioned at the bottom of the “Select files to compare” dialog box.
Now, your part has come to an end. Excel will now do the rest for you. It will highlight all the differences like formulas, data, formatting and others. There is a total of fourteen factors where the Excel compare tool pays utmost heed to. To be more precise, it highlights the difference based on these 14 factors. However, you can certainly choose what differences should be emphasized and what not.
The fourteen areas are:
2: Entered Values.
4: Calculated Values.
5: SysGen Formulas.
8: SysGen Formula errors.
9: SysGen Names.
10: SysGen names errors.
11: Cell format.
12: Cell Protection.
13: Sheet/Workbook protection.
14: Data connection.
This way you can easily compare two excel files. You can also compare two excel data using Bar graphs. Bar graphs can make the representation more appealing. If you don’t know how to make a bar graph, see the link.
Method 2: Using Side by Side.
Unlike the previous method which does the needful for you, this method will allow you to compare your workbooks manually. You will have to check manually for the all the differences by scrolling down for each and every page of the two workbooks. This method will also work on the previous version of Microsoft Excel. So, let’s get started:
Step #1: In this very first step, you need to launch both the workbooks. Once you are done with the same, then you are ready to go to the next steps.
Step #2: Now, from the top navigation bar, you need to navigate to the “View” tab. Once you have navigated the same then, you need to click on it.
Step #3: From the “view” ribbon, you now need to locate “View Side by Side”. The “view side by side” option can be seen in the “Window” section of the view tab. This will open a dialog box for you. On the dialog box, you have to select the workbook which you want to compare with the currently active workbook. Once you are done with the same, then all that you require is to click on “OK” button.
Step #4: The previous step of yours will open the two workbooks side by side. Now, you can manually scroll and can easily spot the differences. When you scroll the window, both the workbooks will be scrolled together in sync. This way you can easily compare the two file and spot the contrast.
Additionally, if you want to turn off the synchronous scrolling, then you can go for the “Synchronous Scrolling” option from the Window section and turn it off.
Step #5: The moment you are done with the comparison of two Excel files then you now need to turn off the View side by side option and revert to the normal mode. You can easily do this by clicking again on “View Side by Side” option and this way you will be back to the normal mode.
[Additional Information]: How to create drop-down menu in excel?
It may seem a little off topic, but we thought that you might be interested in know how to create a drop-down menu in Excel? This is why you are brought to this additional information section. So, here we go:
Step #1: Open Excel sheet and select the cell or range for which you want to drop-down list. You can even select multiple cells or even a full column. If you want to select a handful of columns then you will have to press control while selecting the desired ones.
Step #2: After step #1, you now need to select Excel data validation. For this, you will have to follow the following route: Data tab -> Data tools. This will result in an appearance of a list from which you are required to select “Data Validation.”
Step #3: A new window will appear after you select “Data Validation” in the previous step. From the window, you have to select “list” in the allow box.
After this type those entities which you want to appear on your list.
Make sure that you have checked the box next to which “In-cell dropdown” is written.
Step #4: Now, as the final step, you are required to click on OK and this way you can easily create a drop-down menu in excel.
We hope that this might be of some help for you. You can also compare two datas by calculating the average. Here is you can calculate the average in Excel.
This brought us to the end of this article on “How to compare two excel sheets?”
Do you know any other method by which you can easily compare two excel sheets? If yes, then do tell us by dropping a line through the comments section. Your addition is highly valuable.
If you liked this article then do not forget to share it. Thank you.