Excel Version Control with Git

This is a guest post from reader Björn Stiel.

Let’s Git Excel Under Version Control!

Git has become one of the most popular and trusted version control systems. In fact, so popular that even Microsoft moved their Windows codebase to Git. Why is Git so popular? Well, Git’s branching model is simple and works (branches provide isolated environments for changes without interfering with production-quality code on master and other branches). Also, Git is very flexible. It can be extended and customized in a myriad of imaginable (and unimagibale) ways, which is the basis for any successful ecosystem.

So what about version-controlling Excel workbooks then? If Git works great for code and is customizable, what does it actually take to make it work for Excel workbooks, too? Let’s get started with what happens when we put an Excel workbook file under Git version-control (I’m assuming some basic Git knowledge here, so if you are an absolute Git noob, have a look the excellent Atlassian Git tutorial, or, if you are are very short on time, check out the simple Git guide.

Let’s hit the ground running and get started with an example repository that contains a simple Excel workbook named Book1.xlsb. Clone the repo…

…and have a look at Book1’s version history (aka the file’s commit log):

So, you might wonder what changes were actually made in the latest commit “Added new VBA Module”? That is, you want to see the changes (the diff) between commits 04b45b99c883e5d184a20cfd73e4556ef8d06bfd and 429ee1ff383b8c706aa69c6a87f3a2c50fa1bcd1:

Well, that isn’t very helpful, is it? The problem is that Git does not understand Excel workbook files; to Git, an Excel workbook is just any binary file and therefore a black box.

Fortunately, thanks to Git’s extensibility and modularity, we can configure it to use another application to “diff” Excel file formats. Question is: Which application can diff Excel workbooks? Microsoft’s Spreadsheet Compare (which is part of Office Professional Plus and Office 365) is one option but there is quite a bit of technical DIY plumbing involved. If you are interested in the details, you can find them here.

In order to make Git Excel-ready almost out of the box, I created git-xltrail, a free, open-source Git extension. git-xltrail comes with a custom Excel workbook differ that understands the VBA code inside your workbook (handling spreadsheets is on the roadmap). And git-xltrail also takes care of the correct Git configuration so that all the Excel oddities (such as temporary files) are handled.

To get started, download and run the latest installer version. This installs the Git command-line extension, the workbook differ, and configures a few environment variables (more details can be found here). As soon as the installation is complete, open a new command line window:

Run git xltrail install once to make git-xltrail work across all your (existing and future) repositories (the fire-and-forget solution). Alternatively, git-xltrail can be installed on a per-repository basis. In the repository’s root folder, run:

When installing git-xltrail in local mode, git-xltrail creates .gitattributes (or modifies in case it already exists); make sure .gitattributes is tracked as part of your repository.

With git-xltrail installed (either globally or locally in the example repository), revisit the example repository’s
commit history and compare the latest two versions. This time, you actually get a meaningful diff:

Much more helpful this time round. You can try it yourself by editing the VBA inside your working copy’s Book1.xlsb (and/or the text file README.md in the repository to see how it works when you edit Excel- and non-Excel files in the same commit) and compare your working copy versus your working version:

With this under your toolbelt, you can cross-check expected versus actual code changes and use branching so that you don’t mess up your production workbooks. In short, you can write better VBA code. What’s next for git-xltrail? We are planning to support merging (and to make our diffs support not only VBA but also worksheets). If you would like to see a feature, you are very welcome to open an issue or contribute to the project.

Documentation is available:

7 thoughts on “Excel Version Control with Git

  1. Looks good.

    For those of us not familiar with git, can you recommend a good introductory tutorial to the procedures, and the jargon?

  2. I don’t see any connection between using GIT and writing better VBA code; the example code proves the opposite.

    It feels like returning to the sixties: using DOS commands, before the GUI in Windows was introduced. Weird.

    To use Office as a GUI for Python see: http://www.snb-vba.eu/VBA_Python_en.html

  3. snb – Thanks for the link to your Python from Excel page.

    I agree with your comments in some respects (such as sticking with VBA for the things it is good at, and Python for things where it has some advantage), but I have to differ on the advice not to use Python with UDFs.

    I have used xlwings (and previously ExcelPython) for a large number of UDFs, some of them quite complex, and I find they work just fine. Being able to link directly to the Numpy and Scipy libraries from Excel offers huge advantages over pure VBA, and doing this through UDFs suits the way I work.

  4. Instead of UDF’s that are being recalculated ad nauseam I’d prefer a button to recalculate when the user has finished inputting.
    Access to Numpy or Scipy is not any different at all.

  5. To compare the VBA code in 2 workbooks this code suffices:

    Sub M_snb()
    For Each it In ThisWorkbook.VBProject.VBComponents
    c00 = c00 & it.codemodule.Lines(1, it.codemodule.countoflines)

    For Each it In Workbooks(2).VBProject.VBComponents
    c01 = c01 & it.codemodule.Lines(1, it.codemodule.countoflines)

    MsgBox "Identical VBA-code ? : " & Format(StrComp(c00, c01) = 0, "yes/no")
    End Sub

  6. So is this only for tracking changes in VBA code or does it also track changes in cell values/formulas?

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.