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.

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.
Outlines
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)

You can view the change history either directly on the worksheets or in a separate History worksheet.
To activate the track changes in a workbook select (Tools > Track Changes > Highlight Changes).
Any changes made before switching this feature on will not be tracked.
Using this method though will allow the users to remove (or modify) the tracked changes.
If you want to prevent users from removing the tracked changes you must add a password before the workbook is shared.

Switching the track changes on

Before using track changes you must define the changes which you want to track.
Select the "Track Changes while editing" checkbox.
The default is to track "All" the changes made by "Everyone" although this dialog box is a little confusing.
Make sure you have the "When" checkbox ticked and "All" is selected.
Make sure you have the "Who" checkbox selected and "Everyone" is selected.

Highlight which changes - Defines exactly what changes to track: when, who and where.
Highlight changes on screen - Indicates any changes as soon as they are made with a cell border and a cell comment.
List changes on a new sheet - All the changes can be inserted on a separate worksheet which is inserted after the last worksheet in the workbook. This is disabled initially because there are no changes to display initially.

Remove the tick from the "Highlight changes on screen" checkbox.
Press OK to enable the tracked changes made from this point forward.
The change history will be kept for 30 days by default. This is to limit the size of the workbook.
The workbook will now be saved as a shared workbook.
You will notice that the title bar for the workbook now has [Shared] appended to the workbook name.

When

You can control which changes you want to highlight.

Since I last saved - Only the changes since the workbook was last saved;
All - Displays all the changes since the workbook was first saved as a shared workbook;
Not yet reviewed - Only the changes that you have not reviewed;
Since Date - Only the changes since a particular date. Click "since date" and then type in the earliest date you want to view the changes from. The date format must be day, month, year separated with either a dash or a forward slash. It is possible to include a time after the date by using a space to separate the two.

Who

Everyone -
Everyone but me - This allows you to track any changes made by other people.

Where

Allows you to choose a particular range of cells you want to track the changes for as opposed to tracking the whole workbook.
You can drag to select the cells directly while the dialog box is still open.
If a range of cells is specified then Excel will only track the changes within that range.
If this is left blank then the whole workbook will be reviewed.

Important

You can increase or decrease the number of days to keep.
If you are keeping 30 days of tracked changes and you open the workbook for the first time after 2 months you will be able to see all the changes in the last 2 months. When you save the workbook the history from 31 to 60 days will be permanently removed.
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.