Did you know that you can keep track of changes made in a MS Excel spreadsheet, thus basically create an audit trail of changes made to a particular range in a particular worksheet ?
Here is how to do it. I have created a little sample spreadsheet listing some products with a price in a worksheet called “Products”. In a change history I want to see what product price was changed to what value when.
In order to do this I first create an extra worksheet named “ChangeHistory”. I define three columns: “Product”, “Price”, “Timestamp”.
Now I write some Visual Basic code for the change event of my “Products” worksheet. The easiest way to invoke the code editor properly is to do a right-click on the tab of my worksheet “Products”, then select “View Code”. In the appearing code editor I select “Worksheet” in the left drop down and “Change” in the right drop down. This takes me to a sub routine called “Worksheet_Change” into which I type in the following code:
- Target is the range of changed cells as an input parameter to this sub routine,
- Line 14 needs to be modified for a different worksheet name; here I grab the value from column 1 of my changed range as a label ( here: product ) of the item changed.
And here is how it works. Suppose we have the following initial list of products:
Now we make the following changes:
- We copy the price for product B to C and D
- We change price for product G to $ 4.100.
Thus we end up with this list:
If we check out our change history it reflects nicely what has been changed to what new value when: