Tracking changes in Excel, such as identifying the last person who edited a specific row, can be a crucial part of maintaining data integrity, especially in collaborative environments. Unfortunately, Excel doesn’t natively provide an easy way to automatically record the user ID of the person who last edited a row. However, there are ways to set up your spreadsheet to track this information with a combination of techniques. This article will explore how to use a combination of Excel features and external tools to achieve this goal.
Understanding the Need for Tracking Edits
In a multi-user environment, particularly when several individuals collaborate on the same Excel file, it becomes essential to know who last modified certain data. This ensures accountability and traceability, which is important in environments like project management, finance, and any other fields where data integrity is critical. While Excel itself doesn’t offer a built-in feature to track the user ID of the last person who edited a row, it’s still possible to set up a system that can do this.
The Limitation of Excel's Native Features
Excel has some built-in features like Track Changes or the Comments/Notes feature, but these features have limitations. The "Track Changes" feature in Excel can track who made edits and what was changed, but it doesn’t allow you to store the specific user ID directly in a cell, nor does it provide this information in a way that you can easily use in formulas or automated workflows. Additionally, Track Changes only works when the file is shared, and it can become cumbersome for users working with non-shared versions of a file.
Creating a System to Automatically Record the Last Editor's User ID
While Excel doesn't natively support this feature, there are a few different methods to implement a system for tracking who edited the row. The most common methods involve using Excel VBA (Visual Basic for Applications) or third-party tools, such as Power Automate, which can integrate with Excel Online or SharePoint. Let’s explore how to approach this with Excel and VBA.
Method 1: Using VBA to Track Edits
VBA (Visual Basic for Applications) is a powerful programming language built into Excel that allows you to automate tasks and extend Excel’s functionality. To track the last editor, you can create a VBA macro that automatically populates a column with the user’s name or ID every time a change is made to a row.
Here’s how to do it:

1. Open the Excel workbook where you want to track the last editor.
2. Press Alt + F11 to open the VBA editor.
3. In the VBA editor, find the sheet where you want to track the edits in the "Project Explorer" on the left side. If you don’t see the Project Explorer, press Ctrl + R.
4. Double-click on the sheet name to open the code window.
5. Paste the following VBA code into the window:
   Private Sub Worksheet_Change(ByVal Target As Range)
   ' Specify the column where you want to store the user ID, for example, column D
   Dim UserColumn As Range
   Set UserColumn = Me.Range("D:D") ' If the edited cell is in the UserColumn, exit the subroutine
   If Not Intersect(Target, UserColumn) Is Nothing Then Exit Sub ' Store the username in the UserColumn
   Target.Offset(0, 1).Value = Application.UserName
   End Sub
6. Save your workbook as a macro-enabled workbook (.xlsm).
7. Now, every time a cell is edited on that sheet, the VBA code will record the user’s name in the adjacent column (in this case, column D).
   How It Works:
   • The Worksheet_Change event is triggered every time a change is made to any cell in the worksheet.
   • The code checks if the edited cell is in the target column (in this case, column D where we want the user ID to be stored).
   • If it isn’t in the specified column, it will store the username (using the Application.UserName property) in the next column, allowing you to track who made the change.
   Limitations of Using VBA
   While this VBA solution works well for tracking edits in a basic Excel workbook, there are some limitations to be aware of:
   • The Application.UserName property captures the name of the user logged into Excel. This might not be unique in certain environments, especially in shared workbooks or with generic user profiles.
   • VBA macros don’t work in Excel Online or mobile versions of Excel, so this solution is only applicable to desktop versions.
   • If a user opens a workbook on a different machine, the user ID may not match expectations.
   • Excel needs to be set up with macros enabled for the VBA script to work, which can pose challenges for users who may have macro settings disabled due to security concerns.
   Method 2: Using Power Automate (For Excel Online or SharePoint)
   If you're working with Excel Online, OneDrive, or SharePoint, you can take advantage of Power Automate, a cloud-based service that allows you to create automated workflows between applications and services. Power Automate can be configured to track changes in a workbook and log the last user to edit a particular row.
   To do this, you would create a flow that triggers whenever a row is modified, and the flow could add the username (or email) of the editor to a designated column. While Power Automate provides more flexibility than VBA, it does require the workbook to be saved in OneDrive or SharePoint and may require users to be logged in with their Microsoft 365 accounts.