The Microsoft program Excel makes storing and processing data a piece of cake. But the more entries are added to a table, the greater the chance that some values are duplicated in the process. Excel offers a function for removing duplicates to solve this problem with ease. This allows you to delete redundant double entries with just a few clicks.
When you edit Excel workbooks in collaboration with others, it’s important to track the changes made and approve or reject corrections. For this purpose, Excel offers a ‘Track Changes’ function, often referred to as the ‘tracking mode’. This function allows you to highlight changes visually, to log them in a tabular format, and to undo them later if necessary.
Other spreadsheet programs provide similar features to track changes!
Tracking changes in Excel is closely linked to the approval of workbooks. Both functions are often used in conjunction. In newer Office versions, which are tailored to clouds, the function to approve workbooks is no longer provided because they are designed for collaborative working from the get-go.
- Excel: turning on Track Changes
- Detect processed cells
- Accepting or declining changes
- Turning off track changes
- Limitations to Excel’s track changes
- Requirements for tracking in Excel
- Adding track changes to your Excel menu bar
Your very own .uk domain name!
Short, sharp, .uk. Claim your new domain name now!
Excel: turning on Track Changes
You have two options to track changes in Excel. We’ll look at both of them here.
Open ‘Review’ from the main menu bar. Select the option ‘Track Changes’ from the dropdown followed by ‘Highlight Changes…’.
In the following dialogue box you can define your tracking settings.
A simple option is to select: ‘Track changes while editing. This also shares your workbook.’ and ‘Highlight changes on screen’. Now the changes are tracked in Excel and highlighted by a comment next to the cell. The workbook is automatically released. With this default setting, all other settings remain unchanged.
The table below shows additional settings and their function:
|Check box/optional field ‘When:’||All: The default setting. All changes are tracked.Since last save: Only changes since the last save are tracked.Not yet reviewed: Only unverified changes are tracked.Since ...: Enter the date to begin tracking changes.|
|Check box/optional field ‘Who:’||Everyone: The default setting. Changes by all editors are tracked.Everyone but me: Only other editors' changes are tracked, not yours.(Own name): Only your changes are tracked.|
|Check box/optional field ‘Where:’||Select an area where you want changes to be tracked.|
|Check box/optional field ‘Highlight changes on a new sheet’||A new spreadsheet ‘Change History’ is created in which every change is logged. This setting can only be selected when some changes have already been logged. Only changes already made will be copied to the new worksheet.|
Sharing a workbook
Alternatively, you can go to the ‘Review’ tab and select ‘Share Workbook’ from the ‘Changes’ section.
A dialogue box appears in which you must tick the box ‘Allow changes by more than one user at the same time’ below ‘Editing’. Then you can adjust the settings in the ‘Advanced’ tab.
The following table provides an overview of ‘Advanced’ settings.
|Optional field ‘Keep change history for: … days’||A history of changes is kept for 30 days by default. You can change the time setting here and store changes for longer.|
|Optional field ‘Don’t keep change history’||Tracking changes will be deactivated.|
|Optional field ‘Update changes when file is saved’||Changes are updated once the file is saved, and only then are they visible to other users of the file.|
|Optional field ‘Update changes automatically every…minutes’||Here you can set a time interval for updating the changes. After each update, the changes are visible to other users.|
|Optional field ‘Save my changes and see others’ changes’||Your own changes are given priority over those of others, but you can still view others’ changes.|
|Optional field ‘Just see others changes’||Changes made by others will be visible as soon as they are updated.|
|Optional field ‘Conflicting changes between users – ask me which changes win’||A ‘Resolve Conflicts’ dialogue box appears when multiple users have made conflicting changes.|
|Optional field ‘Conflicting changes between users – the changes being saved win’||Your changes are automatically given priority over other’s changes.|
|‘Include in personal view’ – checkbox ‘Print settings’||Each user can define their own print settings.|
|‘Include in personal view’ – checkbox ‘Filter settings’||Each user can define their own filter settings.|
Detect processed cells
How changes are displayed depends on your settings after you activate tracking mode in Excel.
If you select the ‘Highlight changes on screen’ option from the settings, the column and row labels turn dark red as soon as a cell has been changed in the column or row. In addition, changed cells automatically are tagged with a comment, recognisable by a coloured frame and a small coloured triangle in the upper-left corner of the cell. A different colour is assigned to each user. If you move the mouse cursor over the changed cell, the comment will be displayed. You can also see who changed the cell and when, and what changes were made.
Worksheet ‘Change History’
Once you select the option ‘List changes on a new sheet’, a new worksheet called ‘Change History’ will launch in your workbook. This provides an overview of all changes in tabular form. In it you will also see, among other things, cases of conflict, for example, if two users want to change the same cell.
Only saved changes are included in the list. The scope of the included changes also depends on your settings.
If you uncheck the checkbox ‘Log changes on a new sheet’ in the settings, the ‘Change History’ worksheet will be removed.
In addition to tracking changes in Excel you can use the Excel function CELL to check which cell was changed last. Just enter the following formula into a cell:
Now you can view the address of the cell that was last modified. But beware that when you don’t specify a reference, the information is always output for the cell that was changed last.
HiDrive Cloud Storage with IONOS!
Based in Germany, HiDrive secures your data in the cloud so you can easily access it from any device!
Accepting or declining changes
If you want to accept or reject tracked adjustments in an Excel sheet, you can use the ‘Accept/Reject Changes’ button. You can find this option under the ‘Track Changes’ menu item, located in the ‘Changes’ section of the ‘Review’ tab. Initially, a message will appear informing you that you need to save the worksheet – confirm the message by clicking ‘OK’. In the following dialogue box you can specify which changes you want to review. Here, you can filter by time, user, and range, similar to the settings in Excel for tracking changes. By default, all changes that have not yet been checked are checked.
A dialogue box will guide you through the individual changes. You can choose to accept or reject the changes individually or all at once. If you reject a change, the corresponding cell is returned to the state it was in before the change. If a cell has been modified several times, you can check the changes individually and accept or reject them.
Following the review, the changed cells are highlighted, even if you have accepted or rejected the change. Highlighting disappears when you turn off track changes in Excel.
Turning off track changes
If you no longer want Excel to track changes, proceed as follows:
- Select the ‘Review’ tab from the main menu.
- Select ‘Track Changes’ from the ‘Changes’ section.
- Click on ‘Highlight Changes’.
- Deactivate the checkbox that reads ‘Track changes while editing…’.
When you turn off track changes, the change history is deleted. However, you can save the history separately beforehand. To do this, you can ‘Log changes in a new worksheet’ as described above and copy the ‘Change History’ worksheet to another Excel workbook.
After deactivating track changes, joint editing of the workbook is no longer possible. A warning message will pop up. If another user saves their version of the workbook after deactivating track changes, previously saved versions will be overwritten.
Limitations to Excel’s track changes
When tracking changes is enabled, the restrictions for shared workbooks in Excel apply. This means that certain editing options are not possible, e.g., conditional formatting, joining cells, including hyperlinks, using Excel macros and a handful of additional features (see list below). If you want to use an Excel function but it is greyed out, you should check to see whether the title bar of your Excel window says ‘[Shared]’ beside the file name – this may explain why certain functions are not available.
Examples of unsupported Excel functions when track changes in Excel is active include:
- Creating and pasting tables
- Pasting or deleting cell blocks
- Adding or changing conditional formatting
- Deleting worksheets
- Adding or changing of data validation
- Connecting cells or breaking up connected cells
- Creating or changing diagrams or PivotChart reports
- Sorting or filtering by format
- Inserting or changing images or other objects
- The use of drawing tools
Also note that tracking changes doesn’t work the same way as a typical undo function. You cannot reject changes in the reverse order in which they were made. Each change is rejected individually.
Not all changes are logged. Content changes to cells are tracked, but changes such as formatting, showing, or hiding rows/columns, or simply recalculating are not tracked.
Requirements for tracking in Excel
Excel’s Track Changes only works in shared workbooks (enabling change tracking automatically shares the workbook, see ‘Settings’ above). In newer versions of Excel like Excel 365, the concept of ‘shared workbooks’ has been replaced by ‘shared document creation’, where several users can edit the workbook at the same time and immediately see the edits made by others – but this also means that tracking mode cannot be used anymore. This is true, for example, if you have saved the Excel workbook in a shared space such as OneDrive or SharePoint. You can then only track the changes made by other editors directly, but not afterwards.
With Microsoft 365 by IONOS you’ve got the perfect toolkit at hand for collaborative working in the cloud!
Adding track changes to your Excel menu bar
If the buttons shown to enable tracking changes are not present in your Excel menu (e.g., as is the case with Excel 365), you can add them back to the menu in the following way:
- Go to File > Options > Customise Ribbon.
- Select ‘Review’ from the main tabs on the right.
- Click on ‘New Group’ so that a new group is created under ‘Review’.
- Click on the ‘Rename’ option and give the group the name ‘Changes’.
- Select the ‘All Commands’ entry in the left window under ‘Select Commands’.
- Scroll down the list to ‘Shared Workbook (Legacy)’ (you may have an entry called ‘Share Workbook ...’). Select this entry and click ‘Add’.
- Scroll down the list to ‘Track changes (Legacy)’. Select this item and click ‘Add’ as well.
- Confirm the process with ‘OK’.