Sowmik Chowdhury
In many cases, we may need to track the cell changes in Microsoft Excel. We can track cell changes using an Excel formula. This is a very convenient and faster way to track cell changes. This article demonstrates a step-by-step guide of how to use a formula to track cell changes in excel.
How to Track Cell Changes with Excel Formula: 3 Steps
You might need to track the last changed cell in a very large worksheet on many occasions. Moreover, some other person might need to know the cell which was changed most recently. So, to track the last cell worked on, you can use the Excel CELL function. Now, I will show you a simple example of how to do so.
Let’s assume, you have a dataset of people with their Work Hours and Daily Pay. At this point, you want to change one data and keep track of this change. Follow the steps below to do so.
✤ Step 01: Create a Formula to Track the Cell Address
- First, select a cell to put the last Cell Address. In this case, it is cell G4.
- Then, insert the following formula.
=CELL("address")In this formula, address is the info_type argument of the CELL function.
✤ Step 02: Insert a formula to Track the Cell Content
- Now, select the cell to track your last content change. In this case, it is cell G5.
- Next, insert the following formula.
=CELL("contents")Here, the contents is the info_type argument for the CELL function.
✤ Step 03: Create a Formula to Track the File Address
- At this point, select the cell to show your File Address. In this case, it is cell G8.
- Finally, insert the following formula in the cell.
=CELL("filename")Here, the filename is the info_type argument for the CELL function.
💡 Explanation of the Output:
After applying all those formulas, change the Work Hours of Adam from 10 to 12 which is in cell C5. Now, take a look at the output and you can see the following changes.
- First, cell G4 shows $C$5 which was the last cell you changed.
- Second, cell G5 shows 12 because it is the last content.
- Third, cell G6 does not change because the file address remains the same.
Read More: How to Check Edit History in Excel Online
How to Track Changes in Formula Cells Applying VBA Code in Excel
Suppose, you have a dataset where changing one value results in changing many random cells. Now, you want to track which of the cells changes for the change of one or more values by changing the cell color. Also, after doing so you want to remove the cell color.
In the following dataset, due to a change of any one of the hourly pay, many cell values change. Now, you want to track which of the cells change, when you change the 1st Class Hourly Pay. Moreover, you want the changed cell colors to be yellow. At this point, follow the steps below to do so.
Steps:
- First, insert two shapes for a Reset button and a Track Change button like the screenshot below.
- Next, press ALT + F11 to open the VBA window.
- Then, Right-Click on Sheet 4 or the sheet you are working on.
- After that, select Insert > Module.
- Consequently, copy the following code and insert it into the blank space.
'This code will delete conditional formatting for all cells
Sub DeleteFormat()
Cells.FormatConditions.Delete
End Sub
'This Code will Track the Cell Changes
Sub TrackCellChange()
For Each sheetw In ActiveWorkbook.Worksheets
sheetw.Cells.FormatConditions.Delete
Err.Clear
On Error Resume Next
x = sheetw.Cells.SpecialCells(xlCellTypeFormulas, 23).Count
If Err.Number = 0 Then
For Each cell In sheetw.Cells.SpecialCells(xlCellTypeFormulas, 23)
cell.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
Formula1:="=" & cell.Value
cell.FormatConditions(cell.FormatConditions.Count).SetFirstPriority
With cell.FormatConditions(1).Interior
.Color = 2552550
.TintAndShade = 0
End With
cell.FormatConditions(1).StopIfTrue = False
Next cell
End If
Next sheetw
End Sub
In this code, the sub DeleteFormat () will create a Macro to delete all the conditional formatting in the workbook. Then, the sub TrackCellChange () will create another Macro to change the colors of the cells which changes their values. Moreover, we used the For loop to go through all the cells in the worksheet. Also, here the number 2552550 indicates the color Yellow using the RGB function.
- Now, save the code and go back to your excel file.
- Then, Right-Click on the shape Reset and select Assign Macro.
- Consequently, from the Assign Macro box select DeleteFormat macro.
- Next, click OK.
- Similarly, assign the macro TrackCellChange to the shape Track Change.
- After that, click on the Track Change button.
- Now, change your 1st Class Hourly Pay. In this case, we changed it from $20 to $25.
- Eventually, the cells C8, C11, and C14 change color to Yellow as those cells change their values.
- At this point, if you want to clear the color of those cells, click on the Reset button and you will have the output as follows.
Read More: How to See Who Made Changes in Excel
Things to Remember
- Using the CELL function, you’ll be able to keep track of the last changed cell only.
- Furthermore, after using the CELL function, you can use the Excel Watch Window from the Formulas tab to monitor the cell changes by simply adding cells G4, G5, and G6 to the Watch Window.
- When you download the practice workbook, you might find the results as a #N/A error like the screenshot below because your file location will be different. Simply, click on the formula cells and press ENTER Â to solve this problem.
Download Practice Workbook
You can download the practice workbook from the link below.
Conclusion
Last but not the least, I hope you found what you were looking for from this article. If you have any queries, please leave a comment below.
