Does Excel have a tool like Word does for comparing two workbooks?

[JenniferMurphy]

I searched the archives and found several threads asking similar questions, but most of them had no replies, let alone answers.

I found this MSFT page, but it comes with this warning:

Important: Spreadsheet Compare is only available with Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, or Microsoft 365 Apps for enterprise.

Compare two versions of a workbook by using Spreadsheet Compare - Microsoft Support

Use Spreadsheet Compare to find differences between two Excel workbooks.

support.microsoft.com

I have Office 365, but I do not see the "Compare Files" option.

The Word tool is excellent. It allows me to select the level of detail to be compared and it flags each difference, which I can step through. Any chance Excel has anything similar?

 

[6StringJazzer]

It's not available in Office 365, only Microsoft 365 Apps for Enterprise. It's some sort of enhanced bundle. I don't have access to it so I can't comment on how it works.

I wrote a VBA tool to do this once but it's somewhat customized for what I needed to do. It's a bit more complicated to do this in Excel vs. Word. Word is a stream of text but Excel is structured data. It's hard to tell if you just changed some cells vs. inserted a row. It's certainly possible but it necessarily cannot simply work the same way as Word.

 

[JenniferMurphy]

It's not available in Office 365, only Microsoft 365 Apps for Enterprise. It's some sort of enhanced bundle. I don't have access to it so I can't comment on how it works.

I wrote a VBA tool to do this once but it's somewhat customized for what I needed to do. It's a bit more complicated to do this in Excel vs. Word. Word is a stream of text but Excel is structured data. It's hard to tell if you just changed some cells vs. inserted a row. It's certainly possible but it necessarily cannot simply work the same way as Word.

Yes, I understand that Excel has a more complicated "structure" than Word, but since the M$FT developers have been able to do it for 365 Apps, it's clear that they can do it. No? So it's not a question of complexity, but of customer service, which has never (NEVER) been their strong suit.

What happened to the much ballyhooed "promise" that all of the Office apps would have the same (or, at least, a similar) look and feel and the same features? More M$FT BS!

 

[JenniferMurphy]

PS: Jazzer: My frustration was meant for M$FT, not you. I appreciate your comments. Sorry if that was unclear.

 

[JenniferMurphy]

After a little research, I found some third party solutions. I haven't tried any of them, but I'll list them here in case anyone else wants to give them a try.

This website lists 4 that they recommend and they have quite a bit of information about the first two. Interestingly, the second one is their product.
How to compare two Excel files or sheets for differences

Here are those four:

Synkronizer Excel Compare​

How to compare two excel files - Synkronizer Excel Compare Tool
99 Euros. From Switzerland. Updates for 50% of retail. Limited to 1 workstation. No forum.

Ablebits Compare Sheets for Excel​

Solve 300+ daily tasks in Excel with Ablebits Ultimate Suite
$69, lifetime, free upgrades for 2 years, no forum.

xlCompare​

Effectively compare Excel files for differences using xlCompare
$100, UK?, no forum

Change pro for Excel​

Litera Compare | Litera
$69, Poland, no forum

At the end of the page, they list two online tools: CloudyExcel and XLComparator.

I first tried XLComparator. It seemed awkward.

Compare two Excel spreadsheets without programming macro nor installing any software on your computer

XL Comparator is a free tool to compare two Excel or CSV spreadsheets without programming Excel macros nor installing software on your computer.

www.xlcomparator.net

I then tried CloudyExcel.

Compare Excels, Analyse Excels | CloudyExcel

Online excel tools for reconcililation, reporting and testing. Compare Excel sheets!

www.cloudyexcel.com

It worked reasonably well. It did find changes to the contents of cells.

Here's a simple example. I created a book with this table:

Test CloudyExcel Old.xlsx
	C	D	E	F	G
5	Date	Product	Weight	Price	$/lb
6	02/02/23	A	7.50	$35.00	$4.67
7	02/02/23	B	12.00	$49.99	$4.17
8	02/02/23	C	25.00	$75.00	$3.00
Sheet1

Cell Formulas
Range	Formula
G6:G8	G6	=[@Price]/[@Weight]

I then made a copy of the book and changed the date in the first row:

Test CloudyExcel New.xlsx
	C	D	E	F	G
5	Date	Product	Weight	Price	$/lb
6	01/25/23	A	7.50	$35.00	$4.67
7	02/02/23	B	12.00	$49.99	$4.17
8	02/02/23	C	25.00	$75.00	$3.00
Sheet1

Cell Formulas
Range	Formula
G6:G8	G6	=[@Price]/[@Weight]

I uploaded the two files to CloudyExcel and ran a compare. This is what I saw. It correctly found the change.

You can download the difference file if you enter an email address and a password. I did that. The resulting .xls file had 2 sheets with these tables:

04.xls
	C	D	E	F	G
5	Date	Product	Weight	Price	$/lb
6	2023-02-02	A	7.5	35	4.666667
7	2023-02-02	B	12	49.99	4.165833
8	2023-02-02	C	25	75	3
sheet1

and

04.xls
	C	D	E	F	G
5	Date	Product	Weight	Price	$/lb
6	2023-01-25	A	7.5	35	4.666667
7	2023-02-02	B	12	49.99	4.165833
8	2023-02-02	C	25	75	3
sheet2

I ran a couple more tests. It did not find changes to cell formatting or the addition of a text box. More troubling, I changed last column of the table from expressions to values. It did not report any differences. So it's far from perfect, but then it's free and requires code to be installed.

M$FT could learn something from some of these folks, if they gave a d*mn. ymmv

 

[Jon von der Heyden]

yawn

 

[pjmorris]

yawn

good to see you're encouraging discussion. It is a pity that the 'Spreadsheet Compare' tool isn't more widely available I've found it invaluable when I needed it.

 

[Alex Blakenburg]

Just for completeness and for Enterprise users out there.
I think MS paid a fair amount of money for the product when they acquired Prodiance in 2011 a developer of risk management tools and intially planned to sell it as an add on and then decided to use it as a sweetner to take up the Enterprise version.
If you have Enterprise the add-in is called "Inquire" It is a strange marketing approach when so few Enterprise users know they actually have access to it since it is quite powerful.
A good summary is here:
Excel Add-ins - Inquire
And Wyn Hopkins has a youtube video on it here:

 

[6StringJazzer]

yawn

MVP? Moderator? And this post? Really?