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

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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)
Formula1:="=" & cell.Value
cell.FormatConditions(cell.FormatConditions.Count).SetFirstPriority
With cell.FormatConditions(1).Interior
.Color = 2552550
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: Excel VBA: Track If a Cell Value Changes

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

## Related Articles

<< Go Back to Track Changes | Share Workbook | Workbook in Excel | Learn Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sowmik Chowdhury

Sowmik Chowdhuri, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a crucial Excel & VBA Content Developer at ExcelDemy. His profound passion for research and innovation seamlessly aligns with his unwavering dedication to Excel. In this role, Sowmik not only skillfully addresses challenging issues but also demonstrates enthusiasm and expertise in gracefully navigating intricate situations, highlighting his steadfast commitment to consistently deliver content of exceptional quality and value. Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF