A better way to track changes in Excel

Track changes in Word is one of the hottest features, but Microsoft has been struggling with providing an equivalent feature for Excel. The track changes functionality of shared workbooks has so many limitations that it was officially stamped as “legacy” and replaced by the new co-authoring functionality. While co-authoring is great (especially if you are coming from Google sheets), it seems to be a solution to a different problem. In this blog post, we’ll see why it is so much harder in Excel to track changes than it is in Word and show a better alternative than Microsoft’s co-authoring.

Table of Contents

1. Track changes in Microsoft Word
2. Why is tracking changes in Excel so much harder than in Word?
3. Microsoft’s (legacy) solution: Track changes in shared workbooks
4. A better way to track changes in Excel workbooks

Track changes in Microsoft Word

Word has a built-in tool to track changes which makes it a lot easier to collaborate on the same document. You can send your draft to your boss, she can do a few edits (with track changes turned on) and send it back to you. Now you can jump from one change to the other, accepting the changes that you like.

In track changes mode, Word will mark deleted words by crossing them and added words by underlining them in a different color for each author. Formatting changes are shown in the Reviewing Pane to the right. Look how easy that is:

So, what makes it so hard to apply the same technique to Microsoft Excel? The remainder of this post looks at the technical issues, why Microsoft’s solution failed and how the issue can be solved.

Why is tracking changes in Excel so much harder than in Word?

Issue 1: Excel has cells, and loads of them!

In Word, you can just scroll down from the top to the bottom of your document and you will be told by the vertical line on the side if it contains any changes. In Excel, you can have up to 17 billion cells per sheet and each of these cells can have up to 32,767 characters. That is as if every cell could contain a Word document of 15 pages! And finally, the number of sheets is only limited by the available memory of your machine.

This leaves a lot of room for changes and the concept of simply marking a row whenever something changed somewhere on that row is impossible.

Issue 2: Excel has relative formulas and row/column operations

Let’s assume we have the following cells:

         A            B             C
1 |     one     | =UPPER(A1) |            |

Inserting a column to the left gives us:

         A            B            C
1 |             |    one     | =UPPER(B1) |

This brings two challenges:

• First, it is evident that you can’t just apply cell-by-cell comparison here: Comparing cell A1 from before with cell A1 after the change would make you think that you deleted the content of cell A1.
• Second, the formula that moved from B1 to C1 changed - even though we didn’t touch it (it refers to B1 instead of A1 after the column insert)! Throw in formulas that cross-reference cells from different sheets and things get even more complex.

To track changes in Excel in a meaningful manner we need to account for row/column operations and relative formulas or we will end up with an endless list of noisy changes that nobody can make sense of.

Issue 3: Formulas, values and named ranges

Another complication is that you might be interested both in changes to values (where the formula stayed the same) and changes in formulas (where the value stayed potentially unchanged). Similar things happen when you use named ranges: when you change the referring cell reference, the formula won’t change and so needs to be tracked separately.

Issue 4: VBA

VBA is also used in Word, but it’s definitely a more critical component in Excel. Some Excel developers go as far as to do all the cell changes via VBA! Also, the VBA code you write in Excel tends to be much more tied to the cells: Often, you change your VBA code because of changes in the structure of your worksheets. When you track changes, it would be very useful to see these changes side-by-side so that you can make sense of the VBA code changes.

Microsoft’s (legacy) solution: Track changes in shared workbooks

Microsoft is offering a built-in solution in Excel to track changes but it has so many limitations that it is hidden away with the latest versions of Excel. Also, it is officially labeled “legacy” as it has been replaced by co-authoring (that was the idea at least). Have a look at my previous blog post about Collaboration on Excel Workbooks for details about how to use the legacy track changes in Excel and how it compares to co-authoring. Here just two screenshots for illustration:

Note that the cell tooltip only shows the latest change. However, every change you make after hitting the save button will be recorded as an individual change in the list version. For example, if you change A1 from a to b and then to c and only then hit the Save button, you will see a change from a to c. If, however, you hit the Save button after changing it to b and then again after changing it to c, you will have both changes recorded in a list view which will look something like this:

Excel track changes limitations

Microsoft’s solution works for some very basic workbooks but will fail for most real world workbooks for one of the following restrictions:

• Track changes only works on shared workbooks
• You can’t use track changes on a workbook that contains VBA or Excel Tables
• Creating/changing charts isn’t possible
• Deleting sheets doesn’t work
• Changing/deleting array formulas isn’t supported
• Inserting/deleting blocks of cells doesn’t work

For the full list of limitations, see the docs under Additional information > Unsupported features.

A better way to track changes in Excel workbooks

xltrail takes a different approach to track changes in Excel workbooks which is taken over from professional version control systems like Git (in fact, it also offers a direct integration with Git!).

Whenever you are done with an iteration of your changes, you simply drag and drop your workbook on the web based user interface, adding a message about what your changes are:

This will record a version history of who changed what, when and why:

From there you can not only download the historic file versions but you can also view the changes between any two versions to see what was changed by your co-workers across cells and VBA code (green means new or added while red means old or deleted):

If you need more context, you can always view the full sheet:

This way, you can easily stay up to date with what happened to your spreadsheets while others where working on them.