您计算机上的不同文件夹中有两个名称相似的Excel文件。您如何确定文件是同一Excel 工作簿(Excel workbook)的重复文件还是不同版本?在本教程中,我们将向您展示如何比较两个Excel文件,即使您的计算机上没有安装Excel 。
这些比较工具可以帮助您在Excel 工作表(Excel worksheet)中发现不一致的数据、过时的值、损坏的公式、不正确的计算以及其他问题。
1.比较两个Excel工作(Side)表:并排查看(View Side)
如果您可以一目了然地阅读工作表的数据,请在单独的窗口中打开它们,并使用 Excel 的“并排查看”功能进行并排比较。
- 打开包含两个工作表的Excel 文件(Excel file),转到View选项卡,然后选择New Window。
- 在新窗口中,选择或切换到要比较的(第二个)工作表。
调整窗口大小或重新排列窗口,以便您可以在计算机显示屏上并排查看两个工作表。同样(Again),此方法最适合比较只有几行或几列的Excel工作表。(Excel)
- 如果您更愿意使用 Excel 的比较工具将两个窗口并排放置,请选中“查看”选项卡并选择“并排(View Side by Side)查看”图标。
Excel将立即在您的计算机屏幕上水平排列两个工作表。在此横向视图(landscape view)中比较纸张可能有点困难,因此请继续下一步将方向更改为vertical/portrait arrangement。
- 再次前往“查看”选项卡,然后选择全部排列(Arrange All)。
- 在“排列”窗口中选择垂直,然后选择(Vertical)确定(OK)。
这会将两个工作表并排放置在屏幕上。您还需要启用一项(s one)设置以使比较更容易。
- 点击同步滚动(Synchronous Scrolling)并确保它被突出显示。这使您可以同时滚动浏览两个工作表,从而对数据集进行同步的逐行比较。
如果两个工作表的顶行都是标题,请确保将它们冻结,以便在滚动浏览时它们不会与数据集的其余部分一起移动。
- 选择Freeze Panes并选择Freeze Top Row。对第二个工作表重复此步骤。
- 选择重置窗口位置(Reset Window Position)以将比较方向(comparison orientation)恢复为横向格式。
- 完成比较后,选择并排查看(View Side by Side)可将工作表恢复为其初始大小。
现在您可以滚动浏览两个工作表并逐行比较它们。此功能的主要优点是它内置于所有Excel版本中。但是,您仍然需要做大量的工作——即找出具有不同数字、宏、公式等的单元格。
2. 使用在线工具(Online Tools)比较两个Excel文件
有一些基于 Web 的工具可以提供Excel 比较(Excel comparison)服务。如果您的计算机上没有安装Excel ,您会发现这些工具很有用。Aspose 的这个Excel 比较工具(Excel Comparison tool by Aspose)是比较两个Excel文件的好网络工具。(web tool)
上传第一个框中的第一个(主要)Excel 文件(Excel file),拖动第二个框中的另一个文件,然后选择立即比较(Compare Now)按钮。
如果文件有多个工作表,请在工作表选项卡中选择要比较的工作表(Sheets tab)。如果两个工作表的单元格具有不同的值或内容,Aspose Excel 比较(Aspose Excel comparison)工具将以黄色突出显示差异。
这些基于网络的工具的局限性在于它们大多强调不同的价值。他们不能突出显示不一致的公式(highlight discrepant formulas)、计算等。
3.使用“电子表格比较”比较两个(Compare Two) Excel文件
电子表格比较(Spreadsheet Compare)是比较两个Excel文件或工作表的可靠软件。遗憾的是,它目前仅适用于Windows设备。它作为独立程序(standalone program)提供,也内置在Office版本/包中的Microsoft Excel中: (Microsoft Excel)Office Professional Plus(2013 和 2016)或 Microsoft 365。
在 Excel 中使用电子表格比较(Use Spreadsheet Compare in Excel)
如果您的Excel 应用程序(Excel app)是上述Office软件包的一部分,您可以通过“查询”加载项访问电子表格比较工具(Spreadsheet Compare tool)。如果您的Excel 应用程序(Excel app)中没有“查询”选项卡,请按照以下说明将其打开。
- 在菜单栏上选择文件。(File)
- 选择侧边栏上的选项(Options)。
- 在侧边栏中选择Add-ins ,在“管理”下拉菜单中选择COM Add-in ,然后选择(COM Add-in)Go。
- 选中查询(Inquire)框并选择确定(OK)。
注意:如果您在(Note:)COM 加载项页面(COM Add-ins page)中没有找到“查询”复选框,则您的 Excel 或Office版本不支持电子表格比较(Spreadsheet Compare)。或者,您组织的管理员可能已禁用该功能。安装预安装了电子表格比较的(Spreadsheet Compare)Office版本或联系您组织的管理员。
- 在单独的窗口中打开要比较的两个Excel文件,前往菜单栏中的查询选项卡,然后选择(Inquire)比较文件(Compare Files)。
- Excel会自动将第一个和第二个文件分别添加到“比较”和“到”对话框中。选择交换文件(Swap Files)交换主要和次要文件,或选择比较(Compare)开始比较。
这将在新窗口中启动电子表格比较,突出显示数据集中的任何差异。(Spreadsheet Compare)具有不同值的常规单元格将具有绿色突出显示。带有公式的单元格被格式化为紫色,而带有宏的单元格的填充颜色为绿松石色(turquoise fill color)。
选择导出结果(Export Results)以生成结果副本并将其作为Excel 文档(Excel document)保存到您的计算机。
该报告将指示具有不同数据集的工作表和单元格引用,以及新旧数据的确切值。
您可以与您的同事、团队或其他协作处理该文件的人共享 Excel 报告。(share the Excel report)
将电子表格比较用作独立程序(Use Spreadsheet Compare as a Standalone Program)
如果您的 Excel 或 Office 版本没有电子表格比较插件(Spreadsheet Compare add-in),请从开发人员的网站安装独立软件。(standalone software)安装设置文件(setup file)时,请确保选中注册并激活 Excel 中的加载项(Register and activate the add-in in Excel)框。
安装后,启动电子表格比较(Spreadsheet Compare)并按照以下步骤使用该程序比较Excel文档。
- 在“主页”选项卡中选择比较文件。(Compare Files)
- 选择“比较(旧文件)”对话框(dialog box)旁边的文件夹图标(folder icon),将要比较的第一个文档添加到该工具。将第二个文件添加到“到(较新的文件)”框中,然后选择“(box and select)确定(OK)”继续。
电子表格比较(Spreadsheet Compare)将处理文件并以绿色突出显示具有不同值的单元格。
发现差异
“并排查看”比较工具是Office 家庭或学生用户(Office Home or Students users)最可行的选择。如果您使用Excel for Microsoft 365或Office Professional Plus(2013 或 2016),则可以使用内置的“电子表格比较”工具。但是,如果您不在计算机上使用Excel,则基于 Web 的Excel 比较(Excel comparison)工具将完成工作。这真的很容易。
How to Compare Two Excel Files and Highlight the Differences
There are two sіmilarly-named Excel files in different folders on your computer. How do you determine if the files are duplicates or different versions of the same Excel workbook? In this tutorial, we’ll show you how to compare two Excel files, even if you don’t have Excel installed on your computer.
These comparison tools can help you spot inconsistent data, outdated values, broken formulas, incorrect calculations, and other problems in your Excel worksheet.
1. Compare Two Excel Sheets: View Side by Side
If you can peruse the sheet’s data at a glance, open them in a separate window and perform a side-by-side comparison using Excel’s “View Side by Side” feature.
- Open the Excel file housing both worksheets, head to the View tab, and select New Window.
- In the new window, select or switch to the (second) worksheet you want to compare.
Resize or rearrange the windows so you have a side-by-side view of both worksheets on your computer’s display. Again, this method is best for comparing Excel worksheets with only a few rows or columns.
- If you’d rather use Excel’s comparison tool to place both windows side-by-side, check the “View” tab and select the View Side by Side icon.
Excel will immediately arrange both worksheets horizontally on your computer’s screen. It might be a bit difficult to compare sheets in this landscape view, so proceed to the next step to change the orientation to a vertical/portrait arrangement.
- Head to the “View” tab again and select Arrange All.
- Select Vertical in the “Arrange” window and select OK.
That’ll stack both worksheets in a side-by-side position on your screen. There’s one more setting you need to enable to make the comparison a lot easier.
- Tap Synchronous Scrolling and make sure it is highlighted. This lets you scroll through both worksheets simultaneously, allowing for a synchronous row-by-row comparison of your dataset.
If the top rows of both worksheets are headers, make sure you freeze them so they don’t move with the rest of the dataset when you scroll through.
- Select Freeze Panes and select Freeze Top Row. Repeat this step for the second worksheet.
- Select Reset Window Position to revert the comparison orientation to landscape format.
- When you’re done with the comparison, select View Side by Side to revert the worksheets to their initial sizes.
Now you can scroll through both worksheets and compare them line-by-line. The major advantage of this feature is that it’s built into all Excel versions. However, you’ll still have to do a bunch of the work—i.e., spotting cells with varying figures, macros, formulas, etc.
2. Compare Two Excel Files Using Online Tools
There are web-based tools that offer Excel comparison services. You’ll find these tools beneficial if you don’t have Excel installed on your computer. This Excel Comparison tool by Aspose is a good web tool for comparing two Excel files.
Upload the first (primary) Excel file in the first box, drag the other file in the second box, and select the Compare Now button.
If the files have multiple sheets, select the sheets you want to compare in the Sheets tab. If both sheets have cells with different values or content, the Aspose Excel comparison tool will highlight the differences in yellow.
The limitation of these web-based tools is that they mostly highlight different values. They can’t highlight discrepant formulas, calculations, etc.
3. Compare Two Excel Files Using “Spreadsheet Compare”
Spreadsheet Compare is a reliable software for comparing two Excel files or worksheets. Sadly, it’s only available for Windows devices at the moment. It comes as a standalone program and is also built into Microsoft Excel included in the Office versions/packages: Office Professional Plus (2013 and 2016) or Microsoft 365.
Use Spreadsheet Compare in Excel
If your Excel app is part of the above-mentioned Office packages, you can access the Spreadsheet Compare tool via the “Inquire” add-in. If there’s no “Inquire” tab in your Excel app, here’s how to turn it on.
- Select File on the menu bar.
- Select Options on the sidebar.
- Select Add-ins in the sidebar, select COM Add-in in the “Manage” drop-down menu, and select Go.
- Check the Inquire box and select OK.
Note: If you don’t find an “Inquire” checkbox in the COM Add-ins page, your Excel or Office version doesn’t support Spreadsheet Compare. Or perhaps, your organization’s admin has disabled the feature. Install the Office versions with Spreadsheet Compare pre-installed or contact your organization’s admin.
- Open both Excel files you want to compare in a separate window, head to the Inquire tab in the Menu bar, and select Compare Files.
- Excel will automatically add the first and second files to the “Compare” and “To” dialog boxes, respectively. Select Swap Files to interchange the primary and secondary files, or select Compare to start the comparison.
That’ll launch Spreadsheet Compare in a new window, highlighting any disparity within your dataset. Regular cells with different values will have a green highlight. Cells with formulas are formatted purple while cells with a macro will sport a turquoise fill color.
Select Export Results to generate and save a copy of the results to your computer as an Excel document.
The report will indicate the sheet(s) and cell references with varying datasets, as well as the exact values of the old and new data.
You can share the Excel report with your colleagues, team, or other people working collaboratively on the file.
Use Spreadsheet Compare as a Standalone Program
If your Excel or Office version doesn’t have the Spreadsheet Compare add-in, install the standalone software from the developer’s website. When installing the setup file, make sure you check the Register and activate the add-in in Excel box.
After installation, launch Spreadsheet Compare and follow these steps to use the program to compare the Excel documents.
- Select Compare Files in the “Home” tab.
- Select the folder icon next to the “Compare (older files)” dialog box to add the first document you want to compare to the tool. Add the second file to the “To (newer files)” box and select OK to proceed.
Spreadsheet Compare will process the files and highlight cells with varying values in green.
Spot the Differences
The “View Side by Side” comparison tool is the most viable option for Office Home or Students users. If you’re using Excel for Microsoft 365 or Office Professional Plus (2013 or 2016), the built-in “Spreadsheet Compare” tool is at your disposal. But if you don’t Excel on your computer, web-based Excel comparison tools will get the job done. It’s really that easy.