How to keep track of changes in a MS Excel spreadsheet

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:

   1: Dim AuditRecord As Range
   2: ' This is our change history ...
   3: Set AuditRecord = Worksheets("ChangeHistory").Range("A1:B65000")
   4: r = 0
   5: ' Now find the end of the Change History to start appending to ...
   6: Do
   7:    r = r + 1
   8: Loop Until IsEmpty(AuditRecord.Cells(r, 1))
   9: ' For each cell modified ...
  10: For Each c In Target
  11:   Value = c.Value
  12:   Row = c.Row
  13:   ' ... update Change History with value and time stamp of modification
  14:   AuditRecord.Cells(r, 1) = Worksheets("Products").Cells(Row, 1)
  15:   AuditRecord.Cells(r, 2) = Value
  16:   AuditRecord.Cells(r, 3).NumberFormat = "dd mm yyyy hh:mm:ss"
  17:   AuditRecord.Cells(r, 3).Value = Now
  18:   r = r + 1
  19: Next

Note:

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

  1. We copy the price for product B to C and D
  2. 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:

Advertisements

11 Responses to “How to keep track of changes in a MS Excel spreadsheet”

  1. Simon Ericson Says:

    Really like this – thank you for posting it. I presume it is also possible to track what they have changed the figure from. Is it also possible to ID who made the change to a shared workbook and force them to give a reason for the change?

  2. amagard Says:

    Hi Simon,
    getting the old value might be a bit tricky, since the change event for a worksheet is fired once the value has been changed.
    Logging the user should be easy by using the Application.UserName property, as for instance described here.
    Forcing input of a reason for a change might be possible as well by launching some input dialog from the handler of the worksheet change event.

    • Simon Ericson Says:

      …..so the first event would be the cells in the defined range changing from blank – so able to capture there?

      The reason for this somewhat overzealous approach is that I have a set of auditing guidelines that say an Excel spreadsheet is not an appropriate way to capture income as cells can be changed – so they want paper capture as changes are (more) obvious. …no,really…!!

      Really appreciate your response.

      • amagard Says:

        Right, if you start with blank cells initially all changes ever made would show up in your change log.
        Regarding auditability I am not sure whether coming up with a change log like I described it would satisfy your audit requirements since it is easy to manually overwrite (manipulate) the change log itself.

  3. Simon Ericson Says:

    Point well made, but I think if the sheet is locked and/or hidden, that will be safeguard enough for my purposes. One cannot completely defend against a determined attack, but 80-20 rule applies.

  4. shereen Says:

    I need a help .. the above coding only apply for 2 col data .. what if I need to track for more than 2 col data ? can you pls help

  5. amagard Says:

    Hi shereen,
    in order to monitor more columns you need the following changes:
    . you also need to obtain the Column property of c ( from target )
    . use that when fetching the value; line 14: .Cells(Row, Column) instead of .Cells(Row, 1)
    . have an additional column in your change history so that you can record what column changed. Assuming the column name in row 1 you would get the value for that with .Cells(1,Column)

  6. How to keep track of changes in multiple columns in a MS Excel spreadsheet | Axel's Travelog Says:

    […] a question to my blog article “How to keep track of changes in a MS Excel spreadsheet” I have been asked whether the code I showed there could be changed to monitor multiple columns […]

  7. Macy Says:

    You rescue me from a headache today. Thanks


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: