An Excel history log tracks changes made to a workbook, allowing users to view a record of edits, revisions, and modifications over time. This serves as an audit trail that promotes transparency and accountability in data analysis and reporting. With a robust history log, users can revert spreadsheets to previous versions, see who made what changes and when, and easily resolve editing conflicts or errors.
Excel provides several native tools to log changes, including the Edit History, which shows cell-level edits, and Change History logging for tracking metadata like revisions by user. Third-party add-ins can also enhance history functions. Overall, change tracking is an essential feature for collaborators, auditors, and anyone wanting to preserve data integrity in Excel workbooks.
This article provides an overview of the history logging capabilities native to Excel, as well as complementary solutions, to help users optimize change tracking and unlock the full benefits of version control.
Viewing Edit History in Excel
Excel allows users to view the edit history of a workbook, which tracks all of the edits and changes made to it over time. There are a couple ways to access the edit history in Excel:
To view the history for the entire workbook, go to the File tab and select Info > Versions. This will display the Versions pane on the right side, showing a list of all save points and edits made to the workbook. You can click on any entry in the list to scroll through and visually see how the workbook looked at that point in time.
To view history for just a specific worksheet, right click on the sheet tab and choose View Edit History. This displays a sidebar with information on changes, additions, deletions for that sheet. You can toggle on and off which types of changes to display.
The edit history shows the user name and timestamp of edits, making it easy to see who made what changes and when. This allows collaborators to track the evolution of a workbook and revert back if needed. Older versions are stored for the lifetime of the workbook by default.
Some key benefits of the edit history feature are:
- Track changes over time as multiple users edit the workbook
- Restore workbook or sheets to previous versions if needed
- See inserted, deleted, and formatted cells at a glance
Overall, viewing edit history gives visibility into how a workbook has evolved. This helps maintain data integrity and provides an audit trail for collaborative editing ([1][1]).
[1] https://rutube.ru/video/08e07e5bc2756a1cc0efb073f2d0cda3/
Undo History
Excel’s multi-level undo feature allows users to reverse recent edits made to a spreadsheet. By default, Excel stores up to 100 undo actions that can be accessed via the Undo button on the Quick Access Toolbar or with the keyboard shortcut Ctrl + Z. Users can step backwards sequentially through edits to revert the spreadsheet to a previous state.
The undo history persists even after saving or closing a file, so changes can still be undone after reopening Excel. However, certain actions like running macros may clear the undo history. Excel 2010 introduced multiple levels of undo, expanding the ability to undo multiple sequential actions in one step. Overall, the extensive undo capabilities provide a useful safety net for reversing unwanted changes.
Change History Logging
Excel offers a built-in way to track the changes, edits, and deletions made to a workbook over time. The Change History Logging feature records each change as it occurs, including information about the user who made the change and the date/time it happened.
To enable Change History Logging in Excel:
- Go to the Review tab.
- Click on Track Changes.
- Select Highlight Changes.
Once enabled, Excel will start logging any additions, edits or deletions made. To view the change history:
- Go to Review tab.
- Click on Track Changes.
- Select Highlight Changes.
- Click the down arrow next to Highlight Changes and select List Changes on a New Sheet.
This will create a new sheet listing all the changes, with details like cell coordinates, time stamp, user name and type of change. The change history log provides an audit trail of modifications over time.
Printing Edit History
Excel provides the ability to print a log of all the changes that have been made to a spreadsheet, which can be useful for record keeping purposes. To print the edit history in Excel:
1. Go to the Review tab and click Track Changes > Highlight Changes.
2. In the Highlight Changes dialog box, select the List Changes on a New Sheet option.
3. Click OK. This will generate a new worksheet listing all the changes.
4. To print this change history worksheet, go to File > Print and select the worksheet in the print dialog box.
The printed change history log will contain details like the cell edited, time of edit, contents before and after the edit, and the username of the person who made the change (Microsoft).
This provides an easy way to print a record of all edits made to an Excel spreadsheet, which can be useful for auditing and recordkeeping purposes.
History in Co-Authoring
With Excel’s co-authoring capabilities, multiple people can edit a shared workbook at the same time. This enables collaboration, but can also make tracking changes difficult. Luckily, Excel provides some tools to view edit history when co-authoring.
When working on a shared workbook, Excel automatically logs changes, additions, and deletions made by each co-author. To view this change history, enable ‘Track Changes’ under the Review tab. This will show edits by each author in a separate color. You can also right-click the workbook in OneDrive and view the version history to see incremental saves by each co-author.
Additionally, if you enable ‘Keep change history’ under Track Changes, Excel will log a specified number of days of change history on a separate worksheet. This provides a comprehensive audit trail of all changes made to the workbook.
So while co-authoring can get messy, Excel provides the ability to track edits and view a complete history log of all changes – enabling true collaborative work while maintaining accountability.
Sources:
[Track changes in a Shared Workbook](https://support.microsoft.com/en-us/office/track-changes-in-a-shared-workbook-22aea671-cac7-4fa3-845d-eeb23725bd15)
[Tracking the “edit history” from Co-authors on online excel](https://answers.microsoft.com/en-us/msoffice/forum/all/tracking-the-edit-history-from-co-authors-on/bb8e62e2-e072-4014-8a9a-4fc139605d39)
AutoRecover History
Excel’s AutoRecover feature automatically saves versions of your workbooks while you work on them. These AutoRecover files allow you to restore or recover unsaved changes if your original file becomes corrupted or closes unexpectedly before you save it.
By default, AutoRecover saves a snapshot of your Excel workbook every 10 minutes while you’re working on it. These snapshots are stored in a temporary folder location on your computer or OneDrive. The exact folder location varies between Windows and Mac operating systems.
When you properly close an Excel workbook without saving changes, the AutoRecover versions are deleted. However, if Excel crashes or closes unexpectedly, the latest AutoRecover file remains available as a recovery option. When you reopen Excel, a Document Recovery pane may automatically appear, prompting you to restore the AutoRecover version.
You can also manually access AutoRecover files to restore unsaved changes. In Excel 2016 or later, go to File > Options > Save and check the “Save AutoRecover information every” box. Here you can adjust how often snapshots are captured.
Although not a full version history, AutoRecover provides a way to retrieve unsaved changes if you forget to save or encounter document corruption issues. It serves as a temporary back up of your work.
Tracking Changes
Excel has a built-in Track Changes feature that allows users to track edit history, similar to the tracking changes functionality found in Microsoft Word. With Track Changes turned on in Excel, additions, deletions, and formatting changes made to the worksheet are visibly marked and a separate History worksheet is generated showing details of all changes made.
To enable Track Changes in Excel, go to the Review tab and click “Track Changes” then “Highlight Changes”. This will turn on change tracking and begin logging any edits made from that point forward. Any cell modifications will be marked with a color indicator in the cell and along the scrollbar.
The History worksheet stores information such as the old and new values, the date/time of change, and the user who made the edit. One can view a complete audit trail of changes by opening this sheet. However, one limitation is that the History sheet does not persist after closing Excel and will need to be enabled again next time the file is opened [1].
While not automated like Google Sheets version history, Track Changes provides a way to manually monitor cell changes in Excel. It can be useful for collaborating on spreadsheets when there is a need to review edits made by different users.
Third-Party History Add-ins
While Excel provides basic edit history functionality, some users may need more advanced history logging and tracking. Fortunately, Excel allows third-party developers to create add-ins that can enhance Excel’s built-in features. There are several add-ins available that provide more robust history tracking in Excel:
Configuring the Excel Add-in for Historian – This add-in integrates with OSIsoft’s Historian system to provide detailed audit trails for any changes made in an Excel workbook. It tracks edits by user, timestamps all changes, and allows rolling back changes.
Excel add-ins overview – Microsoft provides an overview of developing custom add-ins that extend Excel’s functionality. Developers can leverage the robust Excel JavaScript API to build add-ins that track history in innovative ways.
These are just a couple examples of the many third-party add-ins available to provide more advanced change tracking, version control, and edit history functionality within Excel. The add-in ecosystem allows power users to customize Excel’s history logging to suit their specific needs.
Conclusion
Excel offers several built-in ways to view edit history within a workbook, as well as through co-authoring and third-party add-ins. The Undo History lets you view and undo recent changes, while Change History Logging tracks all changes made, allowing you to print an audit trail. AutoRecover saves versions as you work, so you can revert to an older draft if needed. When collaborating on a shared workbook, you can see an overview of edits made by others. Finally, third-party add-ins provide additional change tracking and history capabilities beyond what comes standard in Excel.
In summary, while Excel does not have a singular complete history log, it provides robust history and versioning capabilities through Undo History, Change Logging, AutoRecover, and co-authoring features. With the help of third-party add-ins, you can expand Excel’s built-in history tracking to suit your needs. By leveraging these tools, you can monitor changes, revert to earlier versions, and improve accountability when editing workbooks.
