How to remember or save previous value of a changed cell in Excel?
When you edit a cell in Excel, the prior value is overwritten instantly and is no longer visible unless you undo. If you need to preserve that earlier value—for comparison, auditing, or rollback—you can either capture it automatically with a short VBA routine that writes the previous value to a helper column before each change, or review changes using Microsoft 365 tools (Show Changes and Version History) when the file is stored in OneDrive/SharePoint. This tutorial explains both approaches so you can choose the one that fits your workflow and governance needs.
➤ Microsoft 365 — Review previous and updated cell values with Show Changes and Version History
Save previous cell value with VBA code in Excel
Suppose you have a table as shown in the screenshot below. When any cell in column C is edited, you may want to capture its previous value in the corresponding cell of column G, or add the old value as a cell comment automatically for reference and review in future. This approach is particularly helpful when you wish to keep a running log or refer back to old values during ongoing data entry or quality checks.
Key scenario: For worksheets where you frequently review or reconcile changes, such as inventories, logs, or financial tracking, keeping previous values visible can save time and minimize accidental data loss.
To get started, open the worksheet where you want to record previous values. Then, follow these steps:
1. Right-click the sheet tab and choose "View Code" from the shortcut menu. This will open the Visual Basic for Applications (VBA) editor. See screenshot:
2. In the "Microsoft Visual Basic for Applications" window, paste the following VBA code into the Code window. This macro will help save the previous value of cells in the target column to another column.
Before running this script, ensure you adjust the target column references in the code. In these examples, "C:C" refers to the column being tracked for changes and number "7" corresponds to column G, where previous values are stored. You may customize these references according to your layout.
VBA code: Save previous cell value into another column cell
Dim xRg As Range
Dim xChangeRg As Range
Dim xDependRg As Range
Dim xDic As New Dictionary
Private Sub Worksheet_Change(ByVal Target As Range)
Dim I As Long
Dim xCell As Range
Dim xDCell As Range
Dim xHeader As String
Dim xCommText As String
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False
xHeader = "Previous value :"
x = xDic.Keys
For I = 0 To UBound(xDic.Keys)
Set xCell = Range(xDic.Keys(I))
Set xDCell = Cells(xCell.Row, 7)
xDCell.Value = ""
xDCell.Value = xDic.Items(I)
Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim I, J As Long
Dim xRgArea As Range
On Error GoTo Label1
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Set xDependRg = Target.Dependents
If xDependRg Is Nothing Then GoTo Label1
If Not xDependRg Is Nothing Then
Set xDependRg = Intersect(xDependRg, Range("C:C"))
End If
Label1:
Set xRg = Intersect(Target, Range("C:C"))
If (Not xRg Is Nothing) And (Not xDependRg Is Nothing) Then
Set xChangeRg = Union(xRg, xDependRg)
ElseIf (xRg Is Nothing) And (Not xDependRg Is Nothing) Then
Set xChangeRg = xDependRg
ElseIf (Not xRg Is Nothing) And (xDependRg Is Nothing) Then
Set xChangeRg = xRg
Else
Application.EnableEvents = True
Exit Sub
End If
xDic.RemoveAll
For I = 1 To xChangeRg.Areas.Count
Set xRgArea = xChangeRg.Areas(I)
For J = 1 To xRgArea.Count
xDic.Add xRgArea(J).Address, xRgArea(J).Formula
Next
Next
Set xChangeRg = Nothing
Set xRg = Nothing
Set xDependRg = Nothing
Application.EnableEvents = True
End SubFor situations where it is preferable to store the previous value as a cell comment—useful for quick viewing and annotation—the following VBA code accomplishes this automatically whenever a cell in the target column is changed:
VBA code: Save previous cell value in the comment
Dim xRg As Range
Dim xChangeRg As Range
Dim xDependRg As Range
Dim xDic As New Dictionary
Private Sub Worksheet_Change(ByVal Target As Range)
Dim I As Long
Dim xCell As Range
Dim xHeader As String
Dim xCommText As String
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False
xHeader = "Previous value :"
For I = 0 To UBound(xDic.Keys)
Set xCell = Range(xDic.Keys(I))
If Not xCell.Comment Is Nothing Then xCell.Comment.Delete
With xCell
.AddComment
.Comment.Visible = False
.Comment.Text xHeader & vbCrLf & xDic.Items(I)
End With
Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim I, J As Long
Dim xRgArea As Range
On Error GoTo Label1
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Set xDependRg = Target.Dependents
If xDependRg Is Nothing Then GoTo Label1
If Not xDependRg Is Nothing Then
Set xDependRg = Intersect(xDependRg, Range("C:C"))
End If
Label1:
Set xRg = Intersect(Target, Range("C:C"))
If (Not xRg Is Nothing) And (Not xDependRg Is Nothing) Then
Set xChangeRg = Union(xRg, xDependRg)
ElseIf (xRg Is Nothing) And (Not xDependRg Is Nothing) Then
Set xChangeRg = xDependRg
ElseIf (Not xRg Is Nothing) And (xDependRg Is Nothing) Then
Set xChangeRg = xRg
Else
Application.EnableEvents = True
Exit Sub
End If
xDic.RemoveAll
For I = 1 To xChangeRg.Areas.Count
Set xRgArea = xChangeRg.Areas(I)
For J = 1 To xRgArea.Count
xDic.Add xRgArea(J).Address, xRgArea(J).Text
Next
Next
Set xChangeRg = Nothing
Set xRg = Nothing
Set xDependRg = Nothing
Application.EnableEvents = True
End SubParameter notes:
- Number7 in
Cells(xCell.Row,7)refers to column G. Change this value if you want to save the previous value in another column. Range("C:C")defines which column's changes will be tracked. Adjust as needed, such as "D:D" for column D.
3. To enable dictionary support for the script, go to "Tools" > "References..." in the VBA editor. When the "References – VBAProject" dialog appears, check "Microsoft Scripting Runtime" and click "OK" as shown:
4. Press "Alt" + "Q" to close the VBA editor and return to your worksheet.
From this point forward, whenever a value in column C is modified, Excel will automatically copy its previous value into column G or add it to the cell's comment. This allows you to conveniently track and validate changes, ensuring the historical data is accessible for review, auditing, or error correction.
Example output for saving previous cell values in other cells:
Example output for saving previous cell values in comments:
Tips & troubleshooting:
- If the VBA macros do not work as expected, verify macro security settings under "File" > "Options" > "Trust Center" > "Macro Settings" and set to "Enable all macros".
- Review your column references if values are not being logged in the intended location.
- If errors persist, ensure the "Microsoft Scripting Runtime" library is properly enabled as above.
Microsoft 365 — Review previous and updated cell values with Show Changes and Version History
In Microsoft 365, you can audit edits (who changed what and when, including before/after values) without legacy Track Changes. Use Show Changes for cell-level details and Version History for full file snapshots and restores.
Requirements
- Workbook saved to OneDrive or SharePoint.
- Signed in with a Microsoft 365 account; AutoSave turned On.
- Modern format (.xlsx); not using legacy Shared Workbook (Legacy).
Show Changes (cell-level audit)
1. Open the cloud-saved workbook and go to Review ▸ Show Changes to open the pane.
2. (Optional) Select a sheet or range first to scope the results.
3. Browse entries to see editor, timestamp, and old → new values; click an entry to jump to the cell.
Version History (compare and restore)
1. Go to File ▸ Info ▸ Version History.
2. Open a prior version to compare; copy cells out or click Restore to roll back the whole file.
- Show Changes is greyed out? Save to OneDrive/SharePoint, turn on AutoSave, convert from Compatibility Mode (File ▸ Info ▸ Convert), and ensure Shared Workbook (Legacy) is off. Try Excel for the web if desktop remains unavailable.
- Security/locks: Workbook/worksheet protection, encryption, or IRM can disable Show Changes.
- Local files: Full history is not kept for purely local files; use Version History via the cloud for best results.
- Need values written into cells? Show Changes and Version History are audit tools; to write the “previous value” into a helper column on each edit, use a small
Worksheet_ChangeVBA logger.
Pros
- Automatic audit of who/when with before/after values; no macros required.
- Cross-platform (Windows, Mac, web) when stored in OneDrive/SharePoint.
Cons
- Requires cloud storage and AutoSave; limited detail for some transient edits.
- Does not populate previous values into cells; use VBA if that is required for business logic.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in