Excel Audit Log Template: How to Build One

How-to basics · Updated November 22, 2026

Yes, you can create an Excel audit log template that tracks who changed what and when, using built-in features like Track Changes, cell comments, and a simple VBA macro that writes changes to a hidden sheet. The most reliable method is a lightweight macro that records the worksheet name, cell address, old value, new value, user name, and timestamp to a dedicated “Log” sheet each time a cell is edited. Below is a full guide to building one, plus alternatives and common pitfalls.

Why You Need an Audit Log in Excel

Excel does not automatically keep a detailed history of every edit. The Undo stack is cleared when you save and close, and the built-in “Track Changes” feature (available in Excel 2016 and earlier, and still present in Excel 365 under Review > Track Changes > Highlight Changes) only works on shared workbooks and has limitations—it doesn’t capture changes made by VBA macros, and it can be turned off. An audit log is essential when multiple people edit a workbook, when you need to comply with regulatory requirements (like SOX or HIPAA), or when you simply want to know who accidentally overwrote a formula.

Option 1: Use Excel’s Built-in Track Changes

If you need a quick, no-code solution, you can enable Track Changes. In Excel for Microsoft 365 on Windows, go to Review > Track Changes > Highlight Changes. Check “Track changes while editing” and choose “When” and “Who”. Changes are highlighted with a blue border, and you can view them on a History sheet by selecting “List changes on a new sheet”. However, this feature is deprecated—Microsoft no longer recommends it, and it is not available in Excel for the web. It also stops working if the workbook is not shared. For a permanent, more robust log, a VBA macro is better.

Option 2: Build a Custom Audit Log with VBA

This is the most flexible approach. The macro below runs automatically whenever a cell is changed on a specific sheet (or all sheets) and writes the details to a “Log” sheet. Here’s how to set it up:

  1. Press Alt+F11 to open the VBA editor (on Mac, Fn+Option+F11).
  2. In the Project Explorer, double-click the worksheet you want to monitor (e.g., “Sheet1”).
  3. Paste the following code into the code window:
Private Sub Worksheet_Change(ByVal Target As Range)
    ' Declare variables
    Dim LogSheet As Worksheet
    Dim LastRow As Long
    Dim OldValue As Variant
    Dim NewValue As Variant
    Dim Cell As Range

    ' Exit if more than one cell changed at once (e.g., paste)
    If Target.Cells.Count > 1 Then Exit Sub

    ' Set reference to the log sheet (create it if missing)
    On Error Resume Next
    Set LogSheet = ThisWorkbook.Sheets("AuditLog")
    On Error GoTo 0
    If LogSheet Is Nothing Then
        Set LogSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        LogSheet.Name = "AuditLog"
        LogSheet.Range("A1:F1") = Array("Timestamp", "User", "Sheet", "Cell", "Old Value", "New Value")
    End If

    ' Get old value from the Undo buffer (may not work if undo is disabled)
    OldValue = ""
    On Error Resume Next
    OldValue = Application.CommandBars("Standard").Controls("Undo").List(1)
    On Error GoTo 0
    ' The above often returns a description, so fallback to a blank
    If InStr(1, OldValue, "Undo") > 0 Then OldValue = ""

    ' For simplicity, we cannot reliably get the old value in VBA.
    ' Instead, we log the new value and a note that old value is unavailable.
    ' To capture old values, you would need to store a copy of the sheet before changes.
    OldValue = "(not captured)"

    ' Loop through each changed cell (though we exited if more than one)
    For Each Cell In Target
        NewValue = Cell.Value
        LastRow = LogSheet.Cells(LogSheet.Rows.Count, "A").End(xlUp).Row + 1
        LogSheet.Cells(LastRow, 1) = Now
        LogSheet.Cells(LastRow, 2) = Application.UserName
        LogSheet.Cells(LastRow, 3) = Cell.Parent.Name
        LogSheet.Cells(LastRow, 4) = Cell.Address
        LogSheet.Cells(LastRow, 5) = OldValue
        LogSheet.Cells(LastRow, 6) = NewValue
    Next Cell
End Sub

Important: Before running any macro, save a copy of your workbook. Macros cannot be undone with Ctrl+Z. Also, this code does not capture the old value because VBA’s Undo buffer is not reliable; a more advanced solution would store a backup copy of the workbook or use the BeforeSave event. For most audit purposes, logging the new value and timestamp is sufficient.

How to Capture Old Values Reliably

The limitation above is real. To capture the old value, you can use the Worksheet_Change event in combination with a hidden “shadow” sheet that stores the previous values. Alternatively, you can use the Workbook_SheetChange event and store a copy of the entire sheet in a hidden sheet before any change. A simpler approach is to use Excel’s “Track Changes” for old values, but as noted, it’s deprecated. For a production-grade audit log, consider using a database or a third-party add-in. For Excel-only, you can use the BeforeDoubleClick or BeforeRightClick events to log the current value before an edit, but that’s not foolproof.

Choosing the Right Approach for Your Needs

Consider the following factors when deciding:

  • Number of users: If more than one person edits simultaneously, you need a shared workbook or a multi-user environment. The VBA macro works on a single file, but if multiple users open the same file on a network, only one can edit at a time unless you use co-authoring (which does not support VBA events).
  • Compliance requirements: If you need to prove data integrity, a VBA log with timestamps and usernames is often enough, but you may also need to protect the log sheet with a password and set the workbook to “Read-only recommended”.
  • Ease of use: For non-technical users, Track Changes is easier, but it’s not permanent. A macro requires enabling macros and trusting the source.
  • Excel version: Track Changes is not available in Excel for the web or in Excel 365 for Mac (it’s still present in the Windows desktop version). VBA macros work in Excel 365 for Windows and Mac, but not in the web version.

Common Problems and How to Solve Them

Here are typical issues you might encounter:

  • Macro doesn’t run: Ensure macros are enabled (File > Options > Trust Center > Macro Settings). Also, the code must be placed in the correct worksheet module, not a standard module.
  • Log sheet gets deleted: Protect the log sheet with a password (Review > Protect Sheet) and hide it (Format > Hide Sheet).
  • Changes made by VBA macros are not logged: The Worksheet_Change event does not fire when a macro changes a cell. You need to add logging calls within those macros or use the Workbook_SheetChange event, which also doesn’t fire for programmatic changes. To log macro changes, you must explicitly call a logging subroutine from within the macro.
  • Performance slows down: If the log sheet grows large, performance may suffer. Consider archiving old logs or using a separate workbook for the log.
  • Undo history is lost: Once you run a macro, the Undo stack is cleared. Save a backup before running any macro that modifies data.

Alternatives to a Custom Excel Audit Log

If VBA is not for you, consider these options:

  • Excel Online (Microsoft 365) Version History: If you store your workbook in OneDrive or SharePoint, you can use Version History (File > Info > Version History) to see previous versions, but it doesn’t show per-cell changes by user.
  • Google Sheets: Google Sheets has a built-in “Version history” that shows who changed what and when, down to the cell level. This is often easier than Excel’s native tools.
  • Third-party add-ins: Tools like Ablebits or Kutools offer audit trail features, but they are paid.
  • Database-backed logs: For enterprise use, connect Excel to a SQL database and use triggers to log changes.

Costs and Safety Considerations

Building your own VBA audit log is free, but it requires basic coding knowledge. The main cost is time and the risk of errors. Safety-wise, always keep a backup of your workbook before implementing any macro. Also, be aware that VBA macros can be a security risk if you open workbooks from untrusted sources—only enable macros for files you trust. If you need to share the workbook with others, you must ensure they also enable macros, otherwise the log won’t record their changes. For sensitive data, consider password-protecting the log sheet and the VBA project (Tools > VBAProject Properties > Protection).

Frequently Asked Questions

Can Excel track changes without VBA? Yes, the built-in Track Changes feature (Review > Track Changes) can highlight changes and list them on a history sheet, but it only works on shared workbooks and is not available in Excel for the web. It also doesn’t capture changes made by VBA macros.

Why doesn’t my Excel audit log capture old values? The Worksheet_Change event fires after the change is made, so you cannot directly access the old value. To capture it, you need to store a copy of the cell’s value before the change, such as in a hidden sheet or by using the BeforeSave event. A common workaround is to use the Undo buffer, but it’s unreliable.

Can I use an Excel audit log template for Google Sheets? No, Google Sheets has its own built-in version history that tracks changes automatically. You can also use Google Apps Script to create a custom log, but the Excel VBA code will not run in Google Sheets.

Does Excel’s Track Changes work in Excel 365? Yes, it is still available in the Windows desktop version of Excel 365, but Microsoft has deprecated it and recommends using co-authoring with Version History instead. It is not available in Excel for the web or on Mac.

How do I protect my audit log from being edited? You can protect the log sheet with a password (Review > Protect Sheet) and hide it (Format > Hide Sheet). To prevent users from unprotecting it, you can also protect the workbook structure (Review > Protect Workbook).

Can I log changes to multiple sheets at once? Yes, you can place the Worksheet_Change event code in each sheet’s module, or use the Workbook_SheetChange event in the ThisWorkbook module. The latter captures changes on all sheets, but it still requires the code to be placed in the ThisWorkbook module.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.