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.

Whatever.

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.

Voila.

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:

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?

10?

100? 

1,000?

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.