Track Changes

If you work in a team and several of you are responsible for maintaining a workbook it can be useful to know who made what changes.
Track changes allows you to track not just who, but when a change was actually made.
Even if you are the only person that uses a workbook it is even possible to track your own changes.
You can either track the changes from the last time the workbook was saved or from a particular date.
You can view the change history either directly on the worksheets or in a separate History worksheet.

Shared Workbooks

Tracked changes are closely linked with shared workbooks.
Switching on track changes in a workbook will automatically put your workbook into shared mode just as if you had selected (Tools > Share Workbook).
Therefore any features that are not available in a shared workbook are also not available in a workbook that has track changes enabled.
If you want to track the changes without sharing a workbook turn on track changes and save the resulting workbook to a personal folder rather than a network folder.
There are some limitations as to what you can do when a workbook is shared.

What you CANNOT do in a Shared Workbook

The following things cannot be changed once a workbook is shared.
Cells & Ranges - inserting, deleting or merging
Charts - inserting or formatting
Conditional Formats - define or apply any new formats but you can see the effects of formatting applied before the workbook was shared
Data Tables - create or modify
Data Validation - change any restrictions or messages but you can see the effects of validation applied before the workbook was shared
Drawings and Shapes - use any drawing tools - most of these buttons are disabled - check
Graphics and other Objects - inserting or changing
Hyperlinks - no creating or changing
Macros - write, change, view, record or assign macros but you can run macros and record a macros into other nonshared workbooks.
Pivot Tables or Pivot Table Reports - cannot change the layout of existing pivot tables
Protection - change or remove any passwords
Rows & Columns - hide or unhide
Scenarios - save, view or make changes
Subtotals - automatic subtotals cannot be inserted
Worksheets - inserting, deleting
Toolbars & Menus - customisations

What you CAN do in a Shared Workbook

In a shared workbook, each user can update values, add rows and columns sort data; however he or she cannot change the cell formatting or create formulas in the list ??
The following operations can be performed when a workbook is being shared
1) Entering and Deleting Text and Numbers
2) Formatting (no tracked changes on screen)
3) Formulas (any edited cells that have changed due to a recalculation are not tracked)
4) Copy, Paste and Move data by dragging with the mouse. Cannot insert blocks of cells or merge cells.
5) Comments (no tracked changes on screen)
6) Rows and Columns - insert entire
7) Worksheets - renaming (no tracked changes on screen, only History worksheet)


Comments are short descriptive bits of text that can be attached to individual cells.
You can only attach one comment per cell although you can make the comments as long as you like.
Adding comments to your worksheets is extremely useful when you need to document complex formulas.

When you edit a comment the Reviewing toolbar will appear automatically.
You can quickly move between the comments on a worksheet by using the Next and Previous buttons.
Using the Reviewing toolbar can make working with comments slightly easier.


If you don't want to share the workbook with anyone else but just want to track your own changes then make sure you save the workbook in a personal folder and not on a shared drive. Alternatively you could password protect the workbook.
Tracked Changes is based on user identification so if two people are sharing the same PC it may not be possible to distinguish between the two users.
The quickest way to ensure that all the changes are tracked for a workbook is to untick the when, who and where checkboxes.
You can turn the track changes off by selecting (Tools > Track Changes > Highlight Changes) and clearing the "Track Changes while editing" checkbox. A confirmation dialog box will be displayed.
Track Changes is very closely linked to Shared Workbooks.

© 2018 Better Solutions Limited. All Rights Reserved. © 2018 Better Solutions Limited TopNext