Please note: The below solution was based on an earlier version of Excel. It’s now been updated, so this post is pretty much obsolete.
The Problem
The other day I was editing a (might I add, terribly poor) translation for a client in Excel. It was when I got to delivery that I noticed I’d overlooked one instruction – “Please turn on track changes so we can update the TM”. Woopsie…
My first thought was – “Excel has a track changes function?”. Ah, so it does, from version 2010 at least. If you are using an older version, you may also find this post of particular use.
My next thought was – “Well, if Excel has a track changes function, it must have a compare documents function, just like Word does.” But no, for some bizarre reason known only to Microsoft, it does not.
Next, I went to Google with my problem and found the following add-on – but, alas, for some reason it did not work for me. You can always give this add-on a try since it is equally easy to uninstall again if it does not work or causes problems. It is called Spreadsheet Compare and is available on SourceForge.
Determined not to let my client down, I decided to find or develop a simple and effective solution using the tools provided in Microsoft Excel itself.
The Solution
The idea is to produce a combined file, where you have the comparison, the original document and your changed document all in the same file. The comparison worksheet(s) contain(s) code that will highlight where changes have been made.
NOTE: You should do this BEFORE changing or correcting the names of any worksheets. This method should also work in OpenOffice and most spreadsheets that use the same sort of formulas. Incidentally, the problem can also be solved using macros – but there you have the added complication of coding said macros and the fact macros are considered a security risk and therefore may be rejected by your client. This method’s strength is its (comparative) simplicity.
In easy steps…
- Open your corrected file.
- Save it as a copy, for example with “changes” in the name. This is to avoid losing your original file.
- Select the first worksheet in your file.
- If using an older version of Excel, you will see and need to click insert worksheets from a file…
- …select the original document you have edited (in this case the dodgy translation), then select all the sheets, then click okay.
- The original document will now be inserted, appended with “_2” on the end of each worksheet name. Your corrected worksheets will not be appended.
- Repeat steps 3-6 to acquire some identical worksheets, this time appended with “_3”. This is simply to get the right worksheet names in the right order in your file. We will be overwriting the content.
- The ones appended with “_3” will be your comparison worksheets.
- Now, go to your first worksheet (also the first of the comparison worksheets), and on the very first cell, insert the following code:
=IF($’Cover sheet’.A1=$’Cover sheet_2′.A1;”-“;”`”&$’Cover sheet’.A1&”´ was previously `”&$’Cover sheet_2′.A1″´”)
…but replacing “Cover sheet” with the name of the first worksheet. You must also be sure to keep those quote marks, especially if the name of the worksheet contains spaces or other special characters. - Now copy the formula in that cell, and paste it across the entire worksheet where there is currently text.
- Now, you will see a “-” wherever the content of a cell has not been changed, but it will show the current translation followed by the original translation in cells where there has been a change.
- Paste this formula into other worksheets in the same way, and by magic, the name of the sheets will be automatically updated in all cases. It’s that simple.
- The final result is a combined file, where all the changes to the worksheet can be viewed in the worksheets appended “_3”, the original file in the worksheets appended “_2”, and your corrected version in the worksheet names that have not been appended.
- Explain the above to your client, and they should be very happy – this method is arguably more clear than the tracked changes method, since all cells that are not changed are blended out (and marked with a “-” to indicate they have been checked).
In my case, the client was very happy with the solution provided and extra-pleased that I had gone to the trouble of solving the problem. Well, what more do you expect of English Rose Berlin? I do love my tech. ;)
Thanks! Now, where have you been, Rose?
I apologise greatly for the lack of posts over recent months. I have been:
- Working on my thesis
It is on NLP (natural language processing) in a closed game environment using a crowdsourced database of previous matched input to select appropriate character responses. Pretty soon I will be collecting my data, which will mean begging my dear readers to go to the website, where participants will find themselves in mock game environment where they are trapped in a cell and must talk to their cellmate in order to escape. More details soon. I am being supervised by the great Pieter Spronck, Tilburg University. - Editing a book
A recent pro bono project has been the somewhat extensive editing of The Bluefin Bonanza, the first publication by international marine conservation organisation The Black Fish. The book illuminates the corruption, criminality, politics, and science surrounding the lucrative – and often illegal – trade in bluefin tuna, one of the most endangered fish on the planet. A recommended read (and I should know!). - Moving country. Again.
In July this year I moved to Hamburg, Germany. What a lovely place it is! - Working, surprisingly
It may come as a shock to some friends, but I have had to keep working on translation projects in spite of all the above. With limited time, it is blogging that sadly had the lowest priority. BUT I have seen the error of my ways! You can count on more informative posts coming in on a regular basis from now on.
It’s good to be back…
I just used this and it worked great! Thank you!!!!!
If you have Excel 2013, you can compare two files with “Spreadsheet Compare 2013” which is included in Office 2013.
As of Excel 2016/Mac/en_US, all the punctuation is changed:
=IF(‘Cover Sheet’!A1=’Cover Sheet (2)’!A1,”-“,”-“&’Cover Sheet’!A1&”‘ was previously ‘”&’Cover Sheet (2)’!A1&”‘”)
One might also note that the web posting arrangement is making smart quotes, but the actual formula needs dumb quotes: all the back-ticks (“grave accents”) actually must be apostrophes; all the double-back-quotes must be ordinary (“double apostrophes”) quotes.
Well.
Just stumpled upon this blog.
IF you would like to do this with NO programming….
Create a NEW workbook, set to SHARE, thereby also Track Changes.
Copy and Paste content from FIRST workbook into sheet.
SAVE workbook
Copy and Paste content from NEW EDITION into SAME sheet, on top of first paste.
AND now you have a new file with changes tracked.
NB:
It is also possible to set the original workbook to track changes, and then copy paste from the last version.
Let me know if you need a Video Demo…
Best regards
Kim
That works in newer versions of Excel. The version of Excel I was using when dealing with this problem was an older one.
Hi Kim,
I tried this solution because I have a file that is largely text-based, but in pasting over the original with the new, the track feature tracks the entire document as having been changed. That does little to special-identify what has been altered between documents. Am I missing something?
Not sure why people like complicated solutions. You can simply put this formula in Sheet3 (other two sheets are being compared):
=IF(Sheet1!A1=Sheet2!A1, Sheet1!A1,CONCATENATE(“FALSE: “,Sheet1!A1,” is not “,Sheet2!A1))
And if you like to see “FALSE” cells easier, just use conditional formating to fill those cells to red.
I really liked your suggestion but am not being able to implement it would it be possible for you to help me on this. I would be grateful to you
Karick – just add a sheet 3 and paste that formula in all the boxes that are filled in the first 2 sheets and anything populated w/info are the updates from Sheet 1 vs Sheet 2. be sure your tabs are called Sheet 1 and Sheet 2. if you have two sep files; just copy your 2nd file into sheet 2
Interesting article. Thanks, Rose.
I have been using “Spreadsheet Compare 2013” that came with my MS Office 2013 Pro installation and for comparing Excel files, I find it pretty helpful.
Search for “Spreadsheet compare 2013” or try link below –
http://technet.microsoft.com/en-us/library/dn205148(v=office.15).aspx
Cheers.
Slight variation that I use is to show the contents for all cells. Then use a keyaord like “DIFF” with conditional formatting to highlight the changes. So the 3rd tab looks like the first one except differences are highlighted red and show before & after:
=IF(‘Cover sheet’!A1=’Cover sheet_2′!A1,‘Cover sheet’!A1,(“DIFF”&’Cover sheet’!A1&”‘ was previously ‘”&’Cover sheet 2′!A1))
[…] Conference Session on Voice Recognition Best Practices for Translating UI Items in Software Manuals Tech Tip: Compare two Excel documents without an add-on Please translate this short paragraph and return by email Article about forensic speech analysis […]
Hey Rose, thanks for this tip, worked like a charm with Kat’s formula and some adjustments.
Cheers!
I used Excel 2010 version and the code was as follows:
=IF(‘Cover sheet’!A1=’Cover sheet_2′!A1,”-“,(“‘”&’Cover sheet’!A1&”‘ was previously ‘”&’Cover sheet 2’!A1))
This worked perfectly for me. Glad I stumbled across this post.
Thanks. For Excel 2010 I have something along the lines of
=IF((sheetname!B3=sheetname_2!B3),”-“,(“‘” &sheetname!B3 &”‘ was previously ‘”&sheetname_2!B3 &”‘”))
working ok, if that’s any help to anyone. Then just copied it across all the other cells I wanted to check.
try a comma (,) instead of a semi colon in the formula. IF(x=1,”1″,”2″)
Thanks for this article, Rose! The SourceForge Add-In for Excel 2010 worked great for me.
This is great. Thanks. Although I didn’t use this idea this time, you did take me half way there, and I have put this in my notebook for later. I was looking for rows removed between two versions of a file.
I changed the font on one file, combined the files, and then used the Excel ‘remove duplicates’ since I had a unique key in one of the fields. Voila! There were the original 6810 lines of data and, at the end of the file, the 4 data lines removed, easily spotted by their different font.
There’s a program called Comparit. As far as I know, it’s free for personal use but not for commercial use. With it, you can compare two Excel files (or other file types for that matter) directly and it will show you the differences, including any inserted or deleted lines.
This is awesome. Thank you for sharing the method to compare two worksheets. I tried and it worked fine. I am glad that I found this post.
I guess this method does not highlight inserted/deleted rows. Is there a way to highlight them as well?
Not off the top of my head, but the above method will cause all rows/columns after the one that was inserted/deleted to show up as being changed, so it may help anyway.
I tried this on Excel 2010 and pasted the formula / followed the instructions exectly but got a formula error. I don’t know enough about formulas to fix, which is unfortunate as this looked useful.
Sorry, it worked for me and others, though perhaps it was earlier versions. Either way I’m afraid I don’t have time to work out exactly why it’s not working for you.
It works in 10 and 13. However in 10 and 13 the Worksheet names are not the same syntax.
=IF(‘JOY GLOBAL Marks Report’!B1=’JOY GLOBAL Marks Report (2)’!B1,”-“,(“”&’JOY GLOBAL Marks Report’!B1&”‘was previously'”&’JOY GLOBAL Marks Report (2)’!B1))
Notice in 2013 the Worksheet named _2 is now (2).
Have you checked whether your version of EXCEL uses comma (,) or semicolon (;) as the separater in formulas?
Tis is a regional setting in windows itself, but not only for Excel.
Basicly the reason for the semicolon is that we must distinguish from the decimal separator that in many contries is comma instead of dot.
You could change this setting from the control panel and it also affects other programs. Although I would not recommend changing this and just stick to whatever setting your system uses, based on the country you live in.
This feedback might come to late for yourself but might assist others having issues
Awesome! Thanks for the info. I’ll go through the other comments too.
Hi Rose, this looks useful but I’m stuck on the first Insert operation – on both Excel 2010 and 2013 I cannot see how to ‘insert worksheets from a file’. My right click insert options are Worksheet, Chart, and a few macros & dialogs. I can copy all source sheets by opening both files and selecting and dragging – this creates new sheets appended by (2) but your way appears simpler.
Could you explain how you do the original multiple sheet insert?
Hi Adam,
I originally wrote this post based on an earlier version of Excel (I think I was away at the time using my old XP laptop!). It isn’t actually possible in the new versions of Excel, it seems! The best option I found is to select multiple sheets (shift and right click) and then right click, select move or copy, and make sure you then click the “Create a copy” box. That way you can multiply how many you are copying each time, but it’s harder than in the older versions.
Nice one!…was very helpful
Hello,
In Step 4, where do I right-click to have the option to “insert worksheets from a file…” I’ve right-clicked on the name of the worksheet as well as in the body of file and can’t find that option anywhere.
Thanks
It’s changed in later versions of Excel, I’m afraid. You have to right click, select Insert, then Worksheet… and unfortunately you’ll have to follow the advice I gave elsewhere in the comments to insert more than one sheet at once.
Hello,
Can you please help with step 4? I want to compare two Excel 2010 files. But when I right-click on the worksheet name in the lower left, I don’t see “insert worksheets from a file…”. Is there another location I can find this?
Sorry! As written above… It’s changed in later versions of Excel.
You have to right click, select Insert, then Worksheet… and unfortunately you’ll have to follow the advice I gave elsewhere in the comments to insert more than one sheet at once.
Rose,
This is genius!
Thank you so much!
Excel didn’t recognise the formula when I pasted it in. (Yes I corrected the files name so they were correct.) Not sure why it didn’t work and I don’t know enough about Excel formulas to troubleshoot the problem.
Sorry, it could be a million and one things, so I have no idea how to help. Maybe a friend of yours can take a look?
Can you make a video and post on Youtube ?
Ur… Not really, it’d take a bit of time and I’m a bit busy. Anyone else would be welcome to do so, provided they link back to this blog/mention where they got the idea.
Hey – this is really smart! I’d developed a system of flagging changes so I could review them (the messy extra-column method), but I never thought of an approach like this for a full worksheet. This is an elegant and flexible approach, and I’m glad my search (for “excel compare documents”) took me here. Thank you for sharing!
You’re welcome! Glad to have helped!
I’ve got to admit, I HATE Excel! I really do. But I have to work with it, so it’s nice to find a post like this where things get explained properly. Thanks!
Thanks, Rose, for this creative use of formulas in Excel!