I was pleasantly surprised to find that Tortoise SVN diff lets me find the diff between two excel files. The cell which is different is highlighted in red. It works for small excel files.
But I have Excel sheets which have thousands of rows and quite a few columns.
Is there a way to quickly find the cells that have changed (as opposed to scrolling through each sheet slowly and finding the highlighted cells, which is time-consuming and error-prone)?
Here’s an adaptation of the these instructions that show how to replace TortoiseSVN’s diff operation for Excel spreadsheets with the Microsoft Office “Spreadsheet Compare” utility that comes with Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, or Microsoft 365 Apps for enterprise. (Spreadsheet Compare compares all worksheets in two workbooks.)
Create the following batch script. I named it sc.bat and stored it in
C:\Program Files (x86)\Microsoft Office\Office16\DCF
alongside the SPREADSHEETCOMPARE.EXE executable. (If you don’t have permission
to write to that directory, store it somewhere else, in which case you’ll need
to change the %~dp0 on line 5 of sc.bat to point to the above directory).
Note that the two filenames %~1 and %~2 MUST be on separate lines of
the text file, unquoted.)
@echo off
set SCTEMP="%TEMP%\sctemp~%RANDOM%.txt"
echo %~1 > "%SCTEMP%"
echo %~2 >> "%SCTEMP%"
"%~dp0\SPREADSHEETCOMPARE.EXE" "%SCTEMP%"
Open the TortoiseSVN settings window (right-click on any directory –> TortoiseSVN –> Settings), select "Difference Viewer" on the left, and click the "Advanced Settings" button on the right:
In the Advanced Difference Comparison Settings dialog that opens, go to the .xlsx extension, click on [Edit], and enter the following command (change the directory if you didn’t store sc.bat in the directory with SPREADSHEETCOMPARE.EXE):
"C:\Program Files (x86)\Microsoft Office\Office16\DCF\sc.bat" %base %mine
Click [OK] on three dialog boxes to get all the way out.
From this point on, when you use TortoiseSVN to compare a file (e.g. right-click on it and select “diff”, or double-click on it during a Commit dialog), you will pull up Microsoft’s Spreadsheet Compare utility.
To use this with Tortoise Diff you will need to export them both to CSV and diff the files, not the most ideal but helpful. If you have Office Professional Plus 2013 or Office 365 ProPlus you can use Excel Compare feature. There are other methods, which would likely be unwieldy with such large files, and third party tools, described here.
An idea: if you can store your spreadsheets in a text-based format -- e.g. .fods -- the default diff tool will suffice.
I followed circlepi314 instructions but it didn't work for me. I had to change the batch script to the following:
@echo off
set SCTEMP="%TEMP%\sctemp~%RANDOM%.txt"
echo %~1 > "%SCTEMP%"
echo %~2 >> "%SCTEMP%"
"C:\Program Files\Microsoft Office\root\Client\AppVLP.exe" "%~dp0SPREADSHEETCOMPARE.EXE" "%SCTEMP%"
Make sure SPREADSHEETCOMPARE.EXE is in the same folder as the batch script or it won't work. You can find the right folder by searching for Spreadsheet Compare in the start menu and openingen the properties of the shortcut.
Another idea: try some other diff tool that supports diffing Excel files
One of the best diff tool is Beyond Compare
Specialized Viewers
Beyond Compare is multifaceted, providing built-in comparison viewers for a variety of data types. Compare .csv data, Microsoft Excel workbooks, and HTML tables in a Table Compare session. Compare images in a Picture Compare session.
...
Table Compare
A Table Compare session compares delimited data files cell-by-cell. Data can be sorted and aligned on key fields, and unimportant columns can be ignored.
It's not free but it's great and is my favorite
For a free and open source solution you can convert to CSV/TSV and use WinMerge
Table Compare
- Shows CSV/TSV file contents in table format
- Text can be wrapped for each column
If you don't need to view in table format and just want to compare raw texts outside of functions then you can try Araxis Merge
Directly open and compare text from Microsoft Office (Word and Excel), OpenDocument, PDF and RTF files
Save time by directly loading and comparing popular file formats. Alternatively, you can copy text from other applications and paste it into a Merge text comparison window.
It can even compare formatted texts. Note that the comparison is read-only, you won't be able to save the edited Excel files
