The Show Changes feature will let you see each change for any cell, including who made the change and whether the user typed a new value or typed a formula. Microsoft introduced this feature because worksheet protection doesn’t yet work in the online version of Excel (which we’ll refer to as Excel Online). And any collaboration scenario requires the workbook to work with Excel Online. Since the proactive Protect Worksheet isn’t working, it was easier to introduce the reactive Show Changes feature. Although Show Changes was created for the people using Excel Online, it will also be useful to people using the desktop version of Excel (Desktop Excel).
Don’t confuse the new Show Changes with the ancient Track Changes feature. The Track Changes feature was nearly useless, as you could no longer insert rows or columns in a workbook once Track Changes was turned on. Show Changes does a great job of showing the history for each cell, even if those cells are moved around due to cutting and pasting or the insertion of rows or columns.
LOGGING CHANGES IN DESKTOP EXCEL
Nothing in Desktop Excel reveals that changes are being tracked. Yet anyone with an up-to-date Microsoft 365 subscription can start logging changes by saving their workbook to the cloud. As soon as a workbook is saved to OneDrive or SharePoint Online, Show Changes will be turned on and start tracking the changes of that workbook. Changes are logged whether you have AutoSave on or off—though, if you’re working with AutoSave turned off, the changes aren’t logged until you click Save.
The Show Changes feature keeps a 60-day history of all changes, starting from the day that the workbook is saved to the cloud. Yet there are important distinctions of what’s logged.
For example, say that cells B4:C10 contain numbers. If you type new values into B4:C10, that data entry will be logged as changes. But there are also formulas in cells D4:G10 that perform calculations based on the data typed into B4:C10. As you typed numbers in B4:C10, the formulas in D4:G10 were constantly updating with new results. None of those changes as the result of a formula will get logged.
If you type a new formula in G4, that change to the formula will be logged. If you then copy G4 and paste to G5:G10, the formula change to the region is logged.
When you sort the sheet, the action of sorting gets logged, along with all of the cell movement as a result of that sort.
Formatting changes won’t be logged. Any changes made as the result of a VBA macro in Desktop Excel or as the result of a TypeScript macro in Excel Online won’t be logged.
There’s also a limitation: If someone deletes a row or a column, the changes for that column are lost.
SHOWING CHANGES IN EXCEL ONLINE
Since Show Changes was intended as a feature for Excel Online, you can only view the changes in Excel Online. The Excel team at Microsoft is essentially using Excel Online as a beta test for new Excel features. With most Excel Online users being novices working on tiny worksheets, it’s a great place to test new features. Once a feature is stable in Excel Online, the team will introduce the feature to Desktop Excel.
Like many accounting and finance professionals, you probably spend many hours each week working in Desktop Excel and have never opened Excel Online. The Show Changes feature was exciting enough to drag me into Excel Online for the first time.
First, in Desktop Excel, go to File, Account to see your account name. Then open an internet browser and navigate to office.live.com/start/excel.aspx. Sign in with the same account used in Desktop Excel. Look for My Recent, and open the workbook that you saved from Desktop Excel.
To reveal all changes, go to the Review tab in Excel Online and choose Show Changes (see Figure 1). To show changes for a single cell or a region, select those cell(s), right‑click, and choose Show Changes. The Show Changes panel appears on the right. It shows a complete history of every change to each cell (Figure 2).
There will be times when you want to forward the workbook to someone and don’t want to send the history. Either do a Save As to save the workbook under a different name, or download the workbook to your local hard drive. Either of these actions will clear the change log. After all, you may not want the recipient to see all of the budget changes made along the way to the final result.
August 2021
