Track Updates, Duplicates, and Timestamps in Excel
Question details
Track changes and duplicate entries in a shared Excel workbook, including the last modification time for each data row.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Users paste and edit shared table data, check for duplicates, and add comments where needed.
- Observed behavior
- A formula alone cannot reliably store the time of a manual edit because formulas recalculate rather than preserve event history.
Identify the columns that define a duplicate and decide which column will hold each row's timestamp. The VBA method requires desktop Excel, a macro-enabled workbook, and permission to run trusted macros.
Use an Excel Table and VBA to timestamp changed rows
Use this method to flag duplicates and record the latest edit time for each changed table row in desktop Excel.
The worksheet event writes a fixed timestamp when a user changes table data. It does not record a full edit history, editor identity, comment changes, formatting changes, or formula recalculations.
The timestamp uses the computer's local clock. Edits made in Excel for the web do not run this VBA event, so this method is not a complete audit trail for a shared workbook.
Select the data and choose Insert > Table. On the Table Design tab, set the table name to Data, then add a column with the header Updated.
Select the data cells in the column to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. For duplicates based on multiple columns, use a COUNTIFS formula that matches the relevant columns instead.
Open the VBA editor with Alt+F11, find the worksheet containing the Data table, and double-click it. Paste this code into that worksheet's code window: Private Sub Worksheet_Change(ByVal Target As Range) Dim tbl As ListObject Dim changed As Range Dim cell As Range Dim updatedCol As Long On Error GoTo CleanUp Set tbl = Me.ListObjects("Data") If tbl.DataBodyRange Is Nothing Then Exit Sub updatedCol = tbl.ListColumns("Updated").Index Set changed = Intersect(Target, tbl.DataBodyRange) If changed Is Nothing Then Exit Sub Application.EnableEvents = False For Each cell In changed.Cells If cell.Column <> tbl.ListColumns("Updated").Range.Column Then Me.Cells(cell.Row, tbl.ListColumns("Updated").Range.Column).Value = Now End If Next cell CleanUp: Application.EnableEvents = True End Sub
Choose File > Save As and save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Reopen it in desktop Excel, enable macros only if you trust the file, and edit or paste into table data cells to check that Updated changes for each affected row.
Select a cell and choose Review > New Comment to discuss an entry. Comments are separate from the timestamp and are not captured by the VBA event.

Review and organize spreadsheet data with WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format support for an easier move between suites. It does not fix Excel's VBA tracking or guarantee that Excel macros will behave identically.

Frequently Asked Questions
Why can't I use a formula to store the last edit time?
Functions such as NOW() recalculate rather than permanently recording an edit event. A Worksheet_Change macro writes the current time as a value when table data changes.
Will the timestamp update when someone edits in Excel for the web?
No. Excel for the web does not run the VBA worksheet event. Use a separate audit or version-history process if edits from all clients must be tracked.
How can I check duplicates using more than one column?
Add a helper column to the table and use COUNTIFS with the columns that define a match. For example, count rows where both an ID and a date match the current row, then flag counts greater than one.
Does the macro record who changed a cell?
No. The example writes only the latest time for each affected row. Use an audit process that records editor identity and change history if you need to know who made each edit.
Why is my Updated column not changing?
Check that the workbook is saved as .xlsm, macros are enabled in trusted desktop Excel, the code is in the correct worksheet's VBA window, and the table and timestamp column are named Data and Updated.




