How to Use Formula to Track Cell Changes in Excel (with Easy Steps)

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.


Download Practice Workbook

You can download the practice workbook from the link below.


3 Steps with Excel Formula Using CELL Function to Track Cell Changes in Excel

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.

excel formula for tracking cell changes

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.

Using CELL Function to Track Cell Changes in Excel

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.

Using CELL Function to Track Cell Changes in Excel

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.

Using CELL Function to Track Cell Changes in Excel

💡 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.

Using CELL Function to Track Cell Changes in Excel


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.

Applying VBA Code to Track Changes in Formula Cells in Excel

Steps:

  • First, insert two shapes for a Reset button and a Track Change button like the screenshot below.

Applying VBA Code to Track Changes in Formula Cells in Excel

  • 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.

Applying VBA Code to Track Changes in Formula Cells in Excel

  • 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

Applying VBA Code to Track Changes in Formula Cells in Excel

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.

Applying VBA Code

  • Consequently, from the Assign Macro box select DeleteFormat macro.
  • Next, click OK.

Applying VBA Code

  • Similarly, assign the macro TrackCellChange to the shape Track Change.
  • After that, click on the Track Change button.

Applying VBA Code

  • 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.

Applying VBA Code

  • 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.

Applying VBA Code


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.

Things to Remember


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. Also, if you want to read more articles like this, you can visit our website ExcelDemy.

Sowmik Chowdhury
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo