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…

  1. Open your corrected file.
  2. Save it as a copy, for example with “changes” in the name. This is to avoid losing your original file.
  3. Select the first worksheet in your file.
  4. If using an older version of Excel, you will see and need to click insert worksheets from a file…
  5. …select the original document you have edited (in this case the dodgy translation), then select all the sheets, then click okay.
  6. The original document will now be inserted, appended with “_2” on the end of each worksheet name. Your corrected worksheets will not be appended.
  7. 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.
  8. The ones appended with “_3” will be your comparison worksheets.
  9. 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.
  10. Now copy the formula in that cell, and paste it across the entire worksheet where there is currently text.
  11. 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.
  12. 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.
  13. 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.
  14. 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…