Categories
How to manage file versions in Excel
Many nightmares have arisen because of confusion about the correct file versions of Excel documents. In the file itself, it is often difficult to see what the latest version is, and is the latest version even the right version? In this article we’ll share our best practices on how to manage file versions in Excel.
Apply a consistent naming system
Our first piece of advice is: Use a consistent and clear naming system. Do not trust the last modified date of the Excel file, nor should you put your trust in a folder structure that distinguishes old and new. The modification date is already reset if an old version is opened and saved accidentally. The folder structure is useless when colleagues send Excel files to each other per e-mail. That is why a consistent naming system is incredibly important.
Learn from software programmers
What is the best way to be clear and consistent with a naming system? Let’s take a look at how programmers of software (i.e. not Excel) do it. Often they use a three-point edition system with points in between, starting with 1.0.0. The first number goes up 1 with big changes, the second one with small changes and the third number is reserved for bug fixing (very slight changes). For very large spreadsheet projects, it is a good idea to use this system, for example “Budget report 2019 V3.1.2”. For smaller projects, it may be a bit over the top: 2 numbers suffice, and or maybe just one number is enough.
Version numbers
We believe the system with version numbers (“Exploitation 2017 V1.3”) works great, better than an edition system where, for example, the date is used as key (“Exploitation 2017 D20170412”), but this is also a workable option. If you choose to do it that way please remember to record the date as yyyymmdd, so the versions can be sorted properly in the explorer.
“Which version did you send me?”
Normative system
In an earlier article we wrote about the importance of documenting your work. Good version numbers make it a lot easier to document changes, just another bonus of good version naming practices!
The absolute NO GO
We strongly advise that you never, never use terms like ‘new version’, ‘concept’, or ‘final’. We have seen countless Excel files with imaginative names like “BudgetDepartment17 Final Concept Def Version2.XLSX”. This can’t possibly be the intention, and even if you think something is done, it may well be changed in the future. With a numbering system it’s easy to communicate these things without terms like ‘final’ and ‘current’ in the file name. Simply state in the documentation (or an e-mail to you coworkers) what the latest version is, and use this one.
“Did the board already make a decision based on that version?”
As a final note we want to make sure you understand that such systems need to be standardized. Only one or two people following this standard won’t really help in the long run. Make sure your company is consistent in its naming practices.
File versions and PerfectXL
In case your didn’t document your changes securely, or you are not sure whether or not your colleagues edited your file, PerfectXL Compare comes in. This Excel file compare tool will provide you with an overview of all the differences between two Excel files. We recommend you read the PerfectXL Compare walkthrough to see all the possibilities, as well as how easy it is to use.