Comparing multiple sheet Excel files
BC version 3 or 4
"I was comparing my Excel files in BC but it is only showing one sheet. I want to compare all of the sheets in my Excel .XLS or .XLSX file"
By default the Table Compare session type is used for Excel .xls and .xlsx files. This session type only supports comparing a single sheet in Beyond Compare. There are a couple of methods available to compare the different sheets of your Excel file.
Using Excel to switch current sheet
Beyond Compare's Table Compare session will open and compare the current sheet in the Excel file.
- Open your .XLS or .XLSX file in Excel.
- Switch the visible sheet to the sheet you wish to compare.
- Save the Excel file.
- Open and compare the file in Beyond Compare.
Using the Text Compare session
Beyond Compare offers an additional download to open and compare your Excel files in a Text Compare session. This does not offer the same sorting and alignment options as Table Compare, but it will convert all the sheets into a single comma-separated Text file, with each sheet separated by a blank line. You can download and install the MS Excel Text Compare file format here: Microsoft Excel Workbooks
Once installed, there will be two MS Excel formats in your Tools | File Formats dialog. The format with the document icon () is the Text Compare format. The format with the data icon () is the Table Compare format. The topmost format in this list is the format used automatically to compare your files. The other must have that session type explicitly selected.
Lastly, you can combine these two methods in a custom File Format. There are two main draw backs to using this method:
- All sheets are combined into a single sheet and, if sorted, the rows are mixed together.
- Extra whitespace rows are introduced between each sheet and, if sorted, generally sort to the top.
If these are acceptable, or you plan to configure Beyond Compare so that they do not impede your comparison:
- Follow the above directions and install the MS Excel Text Compare file format.
- Open the Tools | File Formats dialog.
- Create a new Table Compare file format. Name it MS Excel All Sheets.
- Save the format.
- Navigate to the MS Excel Text Compare file format (). In the Conversion tab, select and copy the command line.
- Edit your new MS Excel All Sheets format. In the Conversion tab, paste the command line from the Text Compare's MS Excel format into the Loading field, with Editing Disabled.
- Re-save the MS Excel All Sheets file format.