Recording the history of a cell’s value

Here’s the situation.

You’ve got a spreadsheet where you’d like to track the changes to some cell values over time.

Maybe they’re stock prices, or exchange rates or input parameters to a model.


But when you change the value tomorrow, you want a record of what that value was last week.

The Curse of Excel’s Track Changes Feature

The first option you have is to use Excel’s built-in Track Changes feature.

Sounds like this’ll be a short post as we’ve already found a solution!

Not so fast, cowboy.

I’ve got good news and bad news.

Let’s start with the good, shall we?

Excel certainly does have cell-level auditing. This records cell changes in a cell comment.

It you rummage through Excel’s ribbon you probably won’t find it. Not because this feature has been removed; it’s just been hidden.

This is because the Track Changes feature depends on Excel’s Shared Workbook functionality.

If you weren’t aware, Microsoft is now discouraging Shared Workbooks in favour of Excel’s newer co-authoring functionality.

If Track Changes is suitable for you (and I’ll tell you why it isn’t), you can add it back to the ribbon like this:

1. From the File menu, select Options.

2. The down the left-side, select Customize Ribbon.

3. Then from Choose Commands from select All Commands (no point doing things by halves, ay?)

4. Scroll down the list of command until you can find Track Changes.

5. You can add it to the Review tab (or any other tab for that matter) but you’ll need to add it to a new group.


To track the cells changes, try this:

1.From the Review tab click the Track Changes button and choose Highlight Changes.

2.Check (tick) the option for Track Changes while editing.

3.Click in the Where box and then select (highlight) the cells you want to track (audit).

4.Click OK.

Give it a go. I’ll wait here for you 😊

When you change a value, Excel will enter a special comment (not the standard comment with a red triangle at the top-right corner of the cell) showing you the last value.

Pretty nifty, huh?

Okay, now the party’s over.

Now let’s have a heart-to-heart about the bad news.

I think this approach is sub-standard for two main reasons (let me know if you think of any others).

Shared Workbooks Suck

Sorry, I couldn't think of a more appropriate adjective.

I confess, I hate shared workbooks in Excel. In 20 years, they’ve given me nothing but headaches.

The Track Changes feature is based on Shared Workbooks, hence Microsoft is trying to <cough> ‘encourage’ you to use co-authoring instead.

Note also that while a workbook is shared you can’t view (yet alone edit) any VBA code in the workbook. Dang!

Audit's One-Shot Wonder

For me, the biggest negative is that Track Changes only records the last change.

There’s no history.

I can’t see what a value was 2 weeks ago, or the fact that Fred changed a value last Thursday (you get the idea).

Tracking Cell Changes with VBA
Here’s an alternative which provides the same functionality with the following characteristics:

Tracking Cell Changes with VBA

Here’s an alternative which provides the same functionality with the following characteristics:

You can log cell changes in each cell’s respective comment

You can keep an ongoing audit trail (yeah, baby)

You do not - repeat, do not - need to share the workbook

You can still access and edit any VBA code in the workbook.

Houston, I think we’re on to a winner here.

Let’s say you had a spreadsheet that looked like this:

Every time you changed a sales number, you wanted the value recorded in a comment with the cell that changed. Note that the sales figures are in cells C3:C14.

Now we need some VBA code to add a comment to a cell and audit trail for us.

Here’s one example:

Let’s see what it’s doing . . .

(1) First the routine accepts the cell you want to audit (as a range object we’ll call Target).

(2) We don’t want to add an audit comment to any old cell, so we’ll use the Intersect method to confirm the Target cell is in the range of cells we want to track.

You can change the Range to whatever’s relevant to you.

(3) Here we build up the details we want to record. Add what you like: user name, time, wind direction. Whatever.

(4) If the Target cell doesn’t currently have a comment, add one with our details. Simples!

(5) If the Target cell does already have a comment, add the new details at the beginning of the comment (so the most recent change is at the top).

(6) What the heck, let’s change the height and width of the comment as well, so the audit trail is easier to read.

Now we need a way to trigger this code.

Worksheet events to the rescue.

In the Visual Basic Editor (VBE) double-click the sheet you want to track.

(1) From the list of Objects, select Worksheet.

(2) From the list of Events, select Change.

(3) In the code stub, call our new routine and pass along the cell which changed, called Target here.

Now go and change some of the cell values in the range we’ve designated.

You should see an audit trail something like this:

Making the Audit Trail More Flexible

There’s one minor hiccup with our AuditComment function.

Can you guess what it is?

The range we want to assess is hard-coded into the VBA function.

This makes it inflexible.

What if there was another range we wanted to audit?

Sure, we could that that range to our code as well.

But what if we wanted to have an audit trail for 20 ranges? 30 ranges?

Here’s another approach. Suppose we add another worksheet with a table like this:

This table has just two columns. These ranges we want to audit (Cell Address) and the worksheets those ranges exist in (Worksheet Name).

For convenience (and flexibility), I’ve named this range ‘CellsToAudit’.

The VBA code then needs to be tweaked so it loops through the CellsToAudit range and checks if the Target is one of the ranges in the list.
Here’s the updated code.

I’ve only highlighted the parts that have changed:

Let’s see what the differences are . . .

(1) First, we grab the list of worksheets and ranges to audit and pass them to an array.

(2) Now we check each worksheet & cell address combination in the list by looping through the array.

(3) The Intersect method only works in a single sheet. So before using Intersect, you’ll check to confirm the Target cell is in the same worksheet you want to audit.

You can do this by checking the Target’s (cell’s) Parent and comparing it to the name of the worksheet in the list.

(4) Finally, you use the Intersect method just as before. But instead of the range being hard-coded, you pass the cell address from your list.

In theory, you could add as many sheets and ranges as you wanted to audit.

This makes the code far more flexible. Whenever you want to change which cells should be audited, you only need to update the list – no code changes are required making this solution “table driven”.

Improving the Audit Trail

The code’s good but certainly not perfect.

What could you do to improve on this?

A couple of things come to mind.

The first is a limit. How many audit entries do you want per cell?




One improvement may be to restrict the number of audit entries per cell either by a maximum number or perhaps by date (only keep entries for 12 months).

The other is error handling (I’m usually a big stickler for this).
If the “Cell Address” being tested isn’t a valid range, the code will crash. So, it’s worth adding some defensive coding as well some standard error logging.

What else would you change? Let me know in the comments below.

Download the code

Kostas was kind enough to ask in the comments about getting the workbook with the code in it.

You can download the workbook with the sample code by clicking the Download Code button below.


Marcus has been an Excel & VBA developer for over 20 years working in the trenches in Investment Banks and Assets Managers, previously in Melbourne and now in London.

  • Michael says:

    Haha, love this article, so funny and helpful!

  • Kostas Kydonis says:

    Hi Marcus,

    This code is triggered when the values are changed by a formula? In the comment do you store the previous value or all the previous values?

    Can I have the spreadsheet with the code?


    • Marcus says:

      Hey Kostas,

      Right now, the code is only triggered when you manually change a value in one of the cells.

      But that’s a great question; although there is a little more work to trigger code when the value of a formula changes.

      I’ll make this the topic of the next blog post 🙂

      Also, in the Comment, the code will keep adding values each time the value of the cell changes. So, yes, it stores all the previous values.

      It is possible to modify the code so it saves just the last 10 values, for example.

      I’ve added a ‘Download Code’ at the end of the blog post so you can get the sample code


      • Kostas Kydonis says:

        Hi Marcus,

        That seems great, looking forward to the next post, will stay close!

        Thanks for the code, it´s very useful :).


      • ghellam says:

        Looking forward to see it triggered by a formula , great work

        • Marcus says:

          Hey ghellam,

          Thanks for the nudge. I confess work has been a bit hectic and I’d completely forgotten about it.

          I’ll take another look at this soon. Feel free to prod me again if you don’t see it soon.


          • ghellam says:

            Thank you

          • Scott says:

            Hi Marcus,

            just wondered if you’d managed to find time to look into the audit log triggered by a formula yet?

            I also wanted to ask whether it’s possible to NOT log the initial cell entry & only log the subsequent changes?

            Thanks, Scott

          • Marcus says:

            Hey Scott,

            Check my response yo Mikhail’s comment (who had the same question).

            All the best,

        • Marcus says:

          Hey ghellam,

          Check my response to Mikhail’s comment (who had the same question.

          All the best,

      • Mikhail says:

        Hello, Marcus!
        Thanks for the article! Great job!
        Was it possible to convert the code to work with values are changed by a formula? It would be very helpful

        • Marcus says:

          Hey Mikhail,
          The handy thing about the Worksheet_Change event, is it tells you exactly which cell changed via its ‘Target As Range’ parameter.

          In contrast, the Worksheet_Calculate event does not tell you which cells recalculated.

          You *can*, however use the Worksheet_Calculate event to record cell updates based on formula value (result) changes like this:

          Private Sub Worksheet_Calculate()
          ‘ Which cell(s) recalculated?

          Dim Target As Range
          Set Target = Range(“C7”)

          If Not Intersect(Target, Range(“C2:C8”)) Is Nothing Then
          Debug.Print Target.Address, Target.Value
          End If

          End Sub

          That is, you would want to maintain a list of cells to check each time the worksheet calculated. You would need to go one step further and compare the current cell value against the prior cells value (perhaps stored in the cell’s comment) to determine if the result had actually changed before updating the audit log.

          Hope this helps 🙂


  • Ian says:


    Thanks for the great article! I am on a hunt to figure out how to code these audits in a cell column instead of the comments section. In other words with each new entry I want an “insert cell” function with a stamp of the previous value. The column could theoretically grow to infinity – but it will be limited by the length of time each sheet will be utilized. Do you have any idea how to accomplish this?

    • Marcus says:

      Hey Ian,

      Sorry for the late reply.

      Pasting the change to a cell comment is really just a convenience – hover you mouse cursor over the cell and you get an immediate snapshot of recent changes.
      Meanwhile, there’s nothing stopping you from saving cell changes anywhere you require, to another worksheet as you suggest, to a text file or even to a database.

      To insert the previous value to a sequential cell your code should find the first blank cell in the column. Here’s one example of a function which would do that:

      Sub InsertValue(ByVal CellValue As Variant)
      Dim Target As Range

      Set Target = Worksheets(“Audit”).Range(“A1048576”)
      Set Target = Target.End(xlUp).Offset(1, 0)

      Target.Value = CellValue
      End Sub

      When the cell change gets trigger it would call this function and pass the changed value.

      The code the sets a range variable to start at the bottom of Column A (that is Range(“A1048576”)).

      It then says, from here move the the last (End) cell moving Up and then drop back down (Offset) one cell.

      You can do this with one line of code but I split it to help explain it.

      The code then enters the changed cell’s value in the Target cell.

      Alternatively, rather than passing the changed cell’s vale, you could pass a reference to the cell itself.

      That way you could record more details in your audit log such as the cell reference and the sheet name the changed cell lives in.

      Happy Exceling,

  • Tyler says:

    Hi Marcus,

    I’ve downloaded the code and followed your guide but every time I try to apply the macro to my own spreadsheet there is a pop up saying ‘Argument Not Optional’. Any chance you can help please?

    Thanks, Tyler.

    • Marcus says:

      Hey Tyler,
      Thanks for dropping by. Sorry to hear the code’s not playing nicely.

      I’ve downloaded the sample code and run it on my PC without any errors, so we’ll need to figure out what’s different between my PC and yours.

      What specific line of code is this message appearing for?
      Does the message appear when you compile the code or just when you change a value in one of the cells?
      Also, what version of Excel are you running. Windows or Mac?

      All the best,

      • Tyler says:

        Hi Marcus,

        No specific line of the code is being highlighted by the error message, and it appears when I am trying to run the code after compiling it.

        I’m currently using Excel 2016 on Windows.

        Thanks, Tyler.

      • Tyler says:

        Hi Marcus,

        This message does not highlight any specific line of the code, and it appears when I try to run the code after compiling it.

        I am currently using Excel 2016 on Windows.

        Thanks, Tyler.

  • Cody says:

    Hi Marcus,

    I applied your code to my protected spreadsheet but I keep getting the error “The cell you’re trying to change is on a protected sheet.” I made sure the cells that I am adding comments to are unlocked and when I hit “debug”, it highlights the part of your code that says “Target.AddComment AuditNote”. Any help would be appreciated!


  • Tiva says:

    I get an runtime error 5 if I add or remove row from my table. Can you please let me know how to solve this?

    • Marcus says:

      Hey Tiva,
      In the example workbook you can download, there is a tab called ‘Audit Cells’. This lists which tabs and cells should be ‘audited’.

      You will receive Error 5 when delete a row *and* the cell below it already has a cell comment.

      This is because the VBA code will either try to add a new comment, or edit an existing comment.

      All the best,

  • Charles DeSadier says:

    Hello Marcus,

    I love this code and have been using it to help us track price changes on products that we order. Though I have a couple questions. I tried pulling the code that you made more flexible into my spread sheet and added a separate sheet to look for the cell ranges add comments to but couldn’t get it to work. I didn’t see where you had your named range “CellstoAudit”. Might be missing something. But I did get you orignal code to work perfectly for my needs. Though is it got me wondering if you could then have a code to look at the comments and highlight a cell based on the an entry into the comment box. For me I would like to have it highlight a cell if the last entry hasn’t changed in a 60-90 day time period. This would then let me know quickly if a product hasn’t been ordered based on the price not being changed in that time frame.

  • >