Excel: Improvements to Version History

 

SAVE TO THE CLOUD

  Do you save your Excel workbooks on your local hard drive? On a company network? In OneDrive? In SharePoint Online? Back in the days when I spent a lot of time on the road, I would always save a workbook to my local hard drive so I could get to it even if I was on an airplane that didn’t have Wi-Fi.   Recent improvements to Excel like the Collaboration, Show Changes, and Version History features have made me change to saving my workbooks in my OneDrive folder. The folder is actually synchronized to my local hard drive, so I can still access the workbook even if an internet connection isn’t available.   The Version History pane is only available for workbooks that have been saved to the cloud. If you ask for Version History for a workbook saved on your local network or hard drive, Excel will ask you to save the workbook online to begin tracking version history.  

COLLABORATION DATA STREAM

  The Version History and Show Changes features became possible because of the improvements made to Excel to allow various people in the same company to work in the same workbook at the same time. Imagine you’re working on a workbook with many worksheets, tens of thousands of rows, and 20 columns. Five people from the accounting department are all working at the same time in the same file. When one person types a new number in cell A2, that change needs to appear almost instantly for the other four people editing the file. Rather than saving the entire workbook, Excel is tracking that a particular person changed a particular cell, at a particular date and time, with a particular new value or formula.   While this tiny data stream showing who, where, when, and new value was originally created to allow collaboration, it now also powers the new Version History feature. If your workbook contains 50MB of data, it wouldn’t be practical to save 10 versions of the 50MB workbook. Instead, Excel saves the most recent version and then the data stream showing all changes. By virtually reversing the changes from the data stream, Microsoft can rebuild past versions of the file on demand.  

BEST IN EXCEL ONLINE

  While an older, “Version 1” Version History is available in the desktop version of Excel, the new and improved Version History experience is only in Excel Online for now. Open your workbook in desktop Excel. Go to File, Account to note the sign-in credentials used. Then open your internet browser (such as Chrome, Safari, or Edge). In the URL address bar, type Excel.New. This shortcut will launch a new instance of Excel Online. Make sure that you’re signed in using the same credentials as in desktop Excel. Open the same workbook in the browser and then choose File, Info, Version History.

Initially, the Excel grid is hidden by a message asking you to “Select a Version From the Version History Pane.” The Version History pane shows the date, time, and the people who made changes in each version. When you click on an old version in the Version History pane, Excel shows a preview in the grid.   Figure 1 shows a version from June 2021. There were two people who made edits, and the ranges edited are outlined in different colors depending on who made the edits. Above the right side of the grid, a pair of arrows allows you to see each change. In this case, there were eight changes made to this version. Change #3 is the dark pink cell in S6.   When you click the right arrow icon (>) to move to change #4, the dark pink highlight moves to S7:S12 (Figure 2). One can reasonably assume that the new formula typed in S6 for change #3 was then copied down to S7:S12 for change #4. This is an impressive level of detail about changes made almost a year ago. When you find a prior version of interest, you can click the Save a Copy button above the grid to have Excel create a close replica of that workbook from the past date and time.  

In contrast, the “Restore” button will overwrite the current workbook with this historical workbook. This will essentially create a new latest version that undoes all of the changes made. But if you accidentally do hit Restore, the Version History should still offer the recent versions from before the Restore.