All Resources

How to Set Up Version Control for Excel

One of the features that is much loved in Microsoft Word is the ability to track changes in a document from version to version (sometimes referred to as a “blackline” since changes are underlined). This facility to track changes and versions of documents over time is an invaluable tool when reviewing and editing documents. It allows users to figure out what incremental changes have occurred since the last version of the file.

Newer online versions of spreadsheet tools (such as Office 365 or Google Sheets) have version tracking that can be very helpful. But older versions of Excel lack this feature and for the millions of us that are working with older Excel files that are constantly being updated or changed, we need some different techniques to determine changes between versions.

Before spreadsheets with version control became available, the most common way to track versions was to append the file name with the date of the version. In our example, we are comparing two models for changes:

  • Precision Forecast Model – 08-20-2018.xls
  • Precision Forecast Model – 09-04-2018.xls

The method or “hack” that we will use is an old technique to compare models quickly and involves three simple keyboard shortcuts:

  1. Control + Tab – to jump between open files
  2. Alt + W + Q – to set the zoom magnification for the view of the spreadsheet
  3. Page Up/Page Down

The first step is to open the two files in Excel (close any other files) and use Alt + W + Q to set the magnification to the same level on both files (in this case, 130%). Second step is to position the cursor in the same cell on both the files (in this case, cell A1).

Once we are set up we can use Control and Tab to toggle between the files quickly. This rapid change allows us to spot differences quickly and zero in on changes. Once we have identified anything of note, we hit Page Down for both files and repeat the process. Since the files are at the same magnification the two worksheet views should stay aligned allowing you to quickly compare the two files.