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. Right click, and 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 a German to English IT and technology translator? 😉

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…

About the Author:

Rose Newell is a British-born, Berlin-based copywriter and translator specialising in high end and high tech. Rose works exclusively with direct clients, mostly located in Germany, Switzerland, and Austria. This blog is a labour of love for colleagues, not a sales funnel for paid membership groups, webinars, seminars, courses or coaching services. As one of those who has consistently spoken out against instagurus, readers can trust this blog will never be monetised. Truly successful translators have no need for the pittance generated by such activities.

43 Comments

  1. Lisa 01/12/2015 at 3:43 pm - Reply

    I just used this and it worked great! Thank you!!!!!

  2. Josua 29/10/2015 at 10:14 am - Reply

    If you have Excel 2013, you can compare two files with “Spreadsheet Compare 2013” which is included in Office 2013.

  3. Jack Repenning 28/09/2015 at 11:34 pm - Reply

    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&”‘”)

    • Jack Repenning 28/09/2015 at 11:37 pm - Reply

      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.

  4. Kim LAursen (DK) 30/04/2015 at 5:18 am - Reply

    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

    • Rose Newell 27/05/2015 at 11:34 am - Reply

      That works in newer versions of Excel. The version of Excel I was using when dealing with this problem was an older one.

    • Ami 25/09/2015 at 7:28 pm - Reply

      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?

  5. Payam 27/08/2014 at 1:07 am - Reply

    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.

    • Kartik Shetty 12/02/2015 at 3:08 pm - Reply

      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

      • heather cruse 26/03/2015 at 2:01 pm - Reply

        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

  6. Sathya Rajagopalan 19/06/2014 at 12:46 pm - Reply

    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.

  7. john 07/05/2014 at 5:44 pm - Reply

    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))

  8. […] 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 […]

  9. Fabio Hirata 01/04/2014 at 7:01 pm - Reply

    Hey Rose, thanks for this tip, worked like a charm with Kat’s formula and some adjustments.

    Cheers!

  10. Kat 15/01/2014 at 6:08 am - Reply

    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.

  11. Mike 13/01/2014 at 8:42 pm - Reply

    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.

  12. none 13/12/2013 at 8:48 pm - Reply

    try a comma (,) instead of a semi colon in the formula. IF(x=1,”1″,”2″)

  13. James B 13/12/2013 at 6:47 pm - Reply

    Thanks for this article, Rose! The SourceForge Add-In for Excel 2010 worked great for me.

  14. Shay 04/12/2013 at 9:59 pm - Reply

    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.

  15. Bob 24/11/2013 at 6:52 pm - Reply

    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.

  16. Alexis 08/11/2013 at 10:19 pm - Reply

    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?

    • Rose Newell 09/11/2013 at 12:09 am - Reply

      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.

  17. Scott 09/10/2013 at 11:09 am - Reply

    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.

    • Rose Newell 09/10/2013 at 11:12 pm - Reply

      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.

    • Drew 26/03/2014 at 8:27 pm - Reply

      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).

    • Ozziemark 17/04/2014 at 4:29 am - Reply

      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

  18. Smalls 18/09/2013 at 9:56 pm - Reply

    Awesome! Thanks for the info. I’ll go through the other comments too.

  19. Adam N 18/09/2013 at 9:42 am - Reply

    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?

    • Rose Newell 18/09/2013 at 10:35 am - Reply

      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.

  20. vijeesh 09/09/2013 at 3:27 am - Reply

    Nice one!…was very helpful

  21. roger 23/08/2013 at 4:49 pm - Reply

    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

    • Rose Newell 18/09/2013 at 10:37 am - Reply

      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.

  22. Smalls 21/08/2013 at 6:27 pm - Reply

    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?

    • Rose Newell 18/09/2013 at 10:37 am - Reply

      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.

  23. Robert 24/07/2013 at 11:08 pm - Reply

    Rose,

    This is genius!

    Thank you so much!

  24. Jackie C 02/07/2013 at 9:17 am - Reply

    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.

    • Rose Newell 03/07/2013 at 3:34 pm - Reply

      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?

  25. Anand 28/06/2013 at 5:18 am - Reply

    Can you make a video and post on Youtube ?

    • Rose Newell 03/07/2013 at 3:36 pm - Reply

      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.

  26. Vic 02/03/2013 at 2:59 pm - Reply

    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!

  27. EP 30/11/2012 at 9:18 pm - Reply

    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!

  28. José carlos G. Ribeiro 16/11/2012 at 7:54 pm - Reply

    Thanks, Rose, for this creative use of formulas in Excel!

Leave A Comment