Often good practices prevent a significant number of problems from occurring. Other times, its those same good practices that are the actual source of the problems. How so? Let me explain…
Let’s say you are preparing to embark on the journey of preparing a spreadsheet to report the stellar annual performance of your department to upper management. It will contain data from a few different sources, have a few pivot tables, and some charts that will eventually be copied into a PowerPoint presentation. It is an important report, and it is going to be distributed to a wide and critical audience. For this reason, you follow the well established best practices of Excel to ensure you stay organized and produce a report that will allow you to adjust in a moment’s notice. For example, you ensure you have no hard-coded values in formulas – all values that can change are stored in their own clearly-labelled cells. You create a separate sheet in your workbook to document assumptions, data sources, methodologies, and changes made. Data from each source is placed on its own separate sheet and is clearly labelled. And as you work you make sure you save your file often, so often in fact that you develop a habit of pressing Ctrl+S after every significant change you make to the file. Somewhere throughout the process one of following common situations occurs:
- You need to make a significant design decision. (i.e., create one large data table merging all your data from 3 source sheets, or apply modifications to each of the 3 data sheets separately) These kinds of changes are often critical and difficult to reverse without a lot of effort and time. You make your choice and carry on, only to discover after several hours that the other choice was the better option and you need to reverse your decision.
- A colleague introduces a new data source or requirement that is going to require you to rework the design or methodology. This requires rework and usually ends up forcing you to take a few steps back to the point where the change can be introduced and worked into the model.
- You get to a point where you discover that the method you are using isn’t going to allow you to develop one of the types of reports that is required. This situation often forces you to undo a lot of the work you have already done to get back to where you can change course and take a different approach.
- You decide you want to ‘clean up’ your growing file, and delete all the sheets you had some temporary information on because they are no longer needed. Unfortunately after removing the sheets and saving the file you discover you forgot to copy a few things from one of the deleted sheets. Now you can’t undo the removal of the sheets by closing and not saving the file, because your good habit of saving often caused to you save the file as soon as the sheets were deleted.
- And the list goes on…
Ironically, the common enemy here is your otherwise good habit of saving your file often. Pressing Ctrl+S, or saving your file by any method, makes changes irreversible. After saving you no longer have the option to undo and back up 5 or 10 steps. Even undo has a limited number of steps it allows when it is available, so at best it only provides minimal protection, mostly from against accidental clicks or keystrokes. One of the most overlooked and underused best practices employed by all seasoned professionals is version control. Versioning your file does a lot to mitigate the risks of needing to roll back your work by multiple steps, several hours, or a few days. The most common form of versioning involves navigating to the file menu, clicking SaveAs, and adding _v1, _v2, _v3, and so on to the end of the file name. All of that saving as new versions, done repeatedly, is a nuisance and consumes time. Is it really worth it? Absolutely. 100%. Every time. Yes.
Consider the following scenario:
You have been working on a spreadsheet all morning, and your boss asks you to include one more type of summary. You think to yourself, “I wish you had told me that an hour ago before I did x, y, and z… now I have to undo everything from the last hour, and make sure I don’t introduce any errors in the process of reversing course.” In this situation, you are not just losing one hour. You are losing one hour plus however much time it takes for you to work yourself back to wherever you were an hour ago. Sometimes this adds 15 minutes, sometimes a couple hours. However, if you had saved new versions of your file upon any significant change to your file, you would only be losing the one hour. You would simply find the saved version you need to start from again, then open it to get started on the necessary revisions. The few minutes spent saving your file as new versions will have potentially saved you hours of rework and an untold amount of stress, frustration, and strained relationships with colleagues.
This has been a simple example of why versioning your files is important to improving your performance and maintaining your emotional well being. However, the good news is there are several ways to reduce the time and effort required to create new versions. As mentioned above, navigating to the File menu then selecting SaveAs isn’t convenient or efficient. For this reason, many people end up using one of the following techniques to manage version control:
- Quick Access Toolbar: Add a SaveAs button to their Quick Access Toolbar. Personally, this is the FIRST change I make when setting up a new installation of Excel. A bonus is that this requires no advanced skills.
- F12 key: Press F12, the keyboard shortcut for SaveAs. This option is not well known and isn’t popular for a couple reasons. Most other programs use Ctrl+Shift+S as the shortcut for SaveAs, so it makes it difficult to remember F12 is Excel’s SaveAs shortcut. Many keyboards, especially laptops and wireless keyboards, require you to toggle the Function key on/off to alternate between using the Fx keys, or their other shared shortcut assignments. This makes using F12 for the SaveAs shortcut an irritant for many users.
- Personal Macro Library: Write a VBA macro in a personal macro library to assign the keyboard shortcut Ctrl+Shift+S to Excel for SaveAs, to match most other programs and applications. This requires some basic VBA abilities, but still requires manual inputs for updating the file name.
- Advanced Macro: Write a VBA macro to save the file and auto-increment the version number. This type of macro can be saved within the file being versioned or within a personal macro library. This requires more advanced VBA skills.
- Excel Add–ins: Download an Add-in that has a utility for SaveAs or versioning. There are quite a few good Add-ins available that vary in features and price. The nice thing about Add-ins is they have usually been developed by experienced developers and require no programming knowledge to use. This is the simplest way to get advanced functions or utilities that deliver maximum performance and convenience. The importance of versioning is why I have built a “Save New Version” utility into my own commercially distributed Add-in (Azura Speed Tools) and often include this utility for free in systems that I develop for my clients. It saves the current file as a new version, and auto-increments the version number of the file regardless if the version number is at the start, end, or somewhere in the middle of the filename. I have found that providing access to fast and easy-to-use utilities right in Excel’s ribbon menu has helped me persuade some of my reluctant-to-version or refuse-to-version clients into versioning their files like professionals. If you are interested in using this approach, please consider downloading a free trial of my Add-in to test it out.
- Software: Use a file management system that supports version control (SharePoint, or similar). This is a very robust solution, but involves additional costs for licensing, system hosting, administration, and management.
Regardless of your skill levels or preferred method for versioning, by far the most straight-forward is to add a SaveAs button to the Quick Access Toolbar in order to make it easy to find and encourage you to use it. It is free, can be set up with only a few mouse clicks, requires no special skills, and makes versioning relatively painless compared to not having access to a previous version when something doesn’t go as planned. If you are more motivated, explore some of the other options listed above.
The overall message is to treat yourself well by versioning your files. Inevitably your future self will thank you for it.