MS Excel 2010 Track Changes

Posted on 2016-09-16
Last Modified: 2016-09-19
Hi all,

I am looking into the use of Track Changes across a number of Project Tracking workbooks.

Initially, the built in Track Changes feature seems to do exactly what we want; ie keep a history of changes made to the workbook; who, when and what.

I have come across some issues:

Shared Workbook - Enabling track Changes makes it a Shared Workbook. I have read (and cited) on numerous occasions that Shared Workbooks are notoriously unreliable. Has this improved with more recent versions of Excel? The workbooks involved will probably only be accessed by one person at a time so strictly speaking the shared feature is not required.

VBA editing - When in Shared mode the macros within the workbook can be used but cannot be edited. In order for the Administrator of the files (currently me!!) to go in and amend the macros from time to time, eg adding features or fixing issues, I have to switch off the Shared mode but this then deletes the History.

Too much detail - One of the macros in each of these workbooks allows the user to add rows to the table of data, using the macro ensures that all of the relevant formulas are copied into the new rows. The History sheet created by the Track Changes shows a line for the Insert of rows and then shows the detail of each row being populated, 20 rows of detail per row inserted. If the user inserts 10 rows which wouldn't be unrealistic that would be 200 rows of change in the History and then rows related to the user populating any of the 10 rows inserted.

Question 1:  can any of these be avoided or do I have to just put up with them?
Question2: I notice in the Track Changes wizard that you can ask for changes to a specific range. Can it be set that only a specific range is monitored? I guess I can get round this by Protecting areas that won't need changing. I have read elsewhere but don't recall where Protecting the sheet can be done in such a way that Macros can still be run even if they affect protected cells. How do I do this? I can get round this at the minute by starting the routine with ActiveSheet.Unprotect and finishing with ActiveSheet.Protect and the relevant parameters; I haven't applied a password as yet but might need to in future.

As always, thanks for your help.

Rob H
Question by:Rob Henson
LVL 14

Expert Comment

Comment Utility
Look at using git for excel... I think there are some apps...?
LVL 18
Comment Utility
Excel workbooks should not be shared. Better to use Access or another database to store information and each has their own copy of workbook with linked information. For tracking changes, you can use the Change event in Excel and write information to whatever is the back-end database.
LVL 31

Author Comment

by:Rob Henson
Comment Utility
Dr Tribos - there is a feature within Excel itself; looking for advise from people that have used it, successfully or otherwise.

Crystal - I don't think the workbooks would end up being shared in the sense of multiple people using the file at the same time. Each project only has one Project Manager so should only need one person at a time. In addition to the PM the file would be accessed by Finance Admin which is me so I would hopefully recognise when the file is already open with the PM, I can use the Shared Workbook pop-up to check when I need to do Admin work to the file.

The files are saved on a shared network and it gets backed up every day so if the Shared feature does muck things up there will be something to return to.

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

LVL 18

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points
Comment Utility
in that case, you will be able too see the old values and who made the change like comments, review what is different, and accept or reject them. It is not as sophisticated as tracking changes in Word but will let you see what has been done.
LVL 17

Accepted Solution

Karen Falandays earned 250 total points
Comment Utility
Hi Rob, there are only a few ways that shared workbook can be used successfully. One is if there is a separate tab for each user, and no two users will ever edit the same sheet at the same time.

The other option is for you to turn on Shared workbooks and send a copy of the workbook to another user. The features will then be visible and when the workbook is sent back to you, you can merge the changes.

Otherwise, it is strongly advised against. As you mentioned, the features that will NOT work when workbook sharing is enabled are many: you cannot have graphs, macros, pivot tables, data tables, conditional formats, merged cells, hyperlinks, data validation, scenarios, outlines or subtotals. That is giving up a lot of powerful functionality, IMHO

LVL 31

Author Comment

by:Rob Henson
Comment Utility
@kfalandays, thanks for your advice. As for features mentioned that will not work when shared:

  • Graphs - don't have any in these workbooks so can't test;
  • Macros - I can still use the macros but cannot edit them in VBE or any buttons assigned to them, as mentioned in question;
  • Pivot Tables - I do have a couple of these in each file and they don't work. This is probably going to be the stumbling block as I do need to use these;
  • Tables - I did have one in each but I can convert to range and use dynamic names instead;
  • Conditional Formats - I do have these and they still work;
  • Merged Cells - I don't have any of these in these particular workbooks, I try and avoid them anyway if I can;
  • Hyperlinks - I do have this and they still work when workbook is shared;
  • Data Validation - I do have this and it still works;
  • Scenarios - don't have any of these;
  • Outlines - I do have this and they still work when workbook is shared;
  • Subtotals - I don't have any of these but tested and they still work.
As mentioned, the stumbling block is going to be the use of Pivot Tables, this is still required and I don't know of another way of getting the same with the same simplicity. This on its own means I won't be able to use the Track Changes feature so will need to look for another way.

Rob H
LVL 31

Author Closing Comment

by:Rob Henson
Comment Utility
Chose kfalandays comment as it points out that Pivot Tables are not available in Shared Workbooks. In this situation I need the use of those so cannot use the Track Changes feature.

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now