How to track changes in excel

Answer

The recommended way is to use Show Changes together with co-authoring on files stored in OneDrive or SharePoint; this shows who changed what, where, and when for workbooks edited with apps that support co-authoring.

Which tracking methods exist, and when should I use each?

1. Show Changes + Co-authoring — Use for cloud-stored workbooks and real-time collaboration; best for most current Microsoft 365 users.
2. Legacy Track Changes (Highlight Changes / Shared Workbook) — Use only when you must work with older offline workflows or older Excel versions. This legacy feature is deprecated and limited.
3. Version History — Use to restore earlier saved versions and view broader change snapshots when the file is saved to OneDrive/SharePoint.
4. Spreadsheet Compare / Compare and Merge (Windows only) — Use to compare two saved workbook files (best for forensic comparisons on Windows).
5. Custom VBA audit log — Use for offline workbooks when you need a tailored, cell-level audit trail and are comfortable with macros.

How to use Show Changes (Microsoft 365 and Excel for the web)

To view recent edits with the modern method, follow these steps:

1. Store the workbook in OneDrive or SharePoint.
2. Open the workbook in Excel for Microsoft 365 (desktop) or Excel for the web.
3. On the Review tab, select Show Changes.
4. Use the Show Changes pane to see who made edits, what changed, the cell location, and the time of change.
5. Click a change entry to jump to the changed cell.

Notes:

• Everyone who edits should use an app that supports co-authoring for the most complete history.
• Some brief gaps in the timeline can occur when edits come from unsupported clients.

How to use Legacy Track Changes (Highlight Changes / Shared Workbook)

To enable the legacy Track Changes feature when you must use it, follow the appropriate path below.

Windows (Excel desktop — legacy Track Changes)

1. Open the workbook (best to use an .xlsx or .xlsm saved on a network share for older workflows).
2. On the Review tab, search for Track Changes → Highlight Changes.
3. Check Track changes while editing. This also shares your workbook.
4. Choose the scope: when, who, and where to track.
5. Save the workbook; changes will be highlighted, and a list of changes will be available for review and acceptance.
   Notes: You can add the legacy command to the Ribbon via File → Options → Customize Ribbon → All Commands → Track Changes (Legacy) if it is not visible.

Mac (Excel desktop for macOS — legacy Track Changes)

1. Open the workbook.
2. From the menu, choose Tools → Track Changes → Highlight Changes.
3. Enable Track changes while editing and pick your filters (who, when, where).
4. Save the workbook.
   Notes: The legacy Track Changes workflow behaves like the Windows legacy tool and has the same limitations.

Important limitations of the legacy method

1.      Legacy shared workbook tracking has many restrictions (limited features, compatibility issues).

2.      Microsoft recommends co-authoring and Show Changes for current collaboration needs.

How to use Version History to review or restore earlier states

1.      Open the workbook stored in OneDrive or SharePoint.

2.      Click the file name at the top of the Excel window or use File → Info → Version History.

3.      Browse previous versions, open the version to inspect it, and restore if needed.
Notes: Version History shows saved snapshots rather than every discrete edit, so combine this with Show Changes for fine detail.

How to compare two saved workbooks (Windows only)

1. Save the original workbook and the edited workbook as separate files.
2. Use Spreadsheet Compare (part of Microsoft Office tools) or Compare and Merge Workbooks to view cell-by-cell differences.
3. Review added/removed rows, formula changes, and value edits.
   Notes: Spreadsheet Compare is available on Windows with some Office installations and is not present on macOS.

When should I use VBA to track changes?

Use a VBA audit trail when:

1. Workbooks stay offline and you require a persistent, custom log.
2. The built-in tools are unavailable or insufficient.
   Steps overview:
3. Add a Workbook or Worksheet event macro (for example, Worksheet_Change).
4. Write code to record username, timestamp, cell address, old value, and new value to a hidden sheet or external file.
5. Protect the audit sheet to reduce tampering.
   Notes: Macros must be enabled and the workbook saved as a macro-enabled file (.xlsm). Use this method only when you understand macro security and distribution implications.

Best practices for reliable change tracking

1. Choose cloud storage for the workbook to get the most complete change history and easy version restoration.
2. Use Show Changes for everyday collaboration because it is the supported, modern tool.
3. Avoid using legacy shared workbook mode for new projects because Microsoft deprecated many of its features.
4. Keep a named version history or dated backup when preparing major edits.
5. Document the tracking method you choose so collaborators know how to view and respond to changes.

Troubleshooting common problems

1. Track Changes or Show Changes not visible — Add the legacy command to the Ribbon or confirm you are using an Excel client that supports Show Changes.
2. Incomplete change history — Confirm all editors are using apps that support co-authoring and that the file is saved in OneDrive or SharePoint.
3. Need cell-level old values offline — Use VBA to log old values, or save periodic snapshots to compare later.

Summary guidance

1. Use Show Changes + co-authoring for modern, cloud-based collaboration and the clearest edit history. Use Version History to restore previous workbook states.
2. Use Legacy Track Changes only when constrained to older workflows; plan to migrate away from it. 

Yes · 0 No · 0 Add Comment · 0

Add ·

Report