Setting your sharing options
After you turn on file sharing for a workbook, Excel creates a Change History log that records all the changes made by different individuals to the same workbook file. You can then use the Change History log to review the various changes made to a shared workbook and to determine which changes to retain in the event that conflicting changes are made to the same cells. You can also use this log when merging changes from different copies of the same workbook into a single file.
By default, Excel maintains the Change History log for a period of 30 days from the date that you first share the workbook. If you wish, you can change the length of time that Excel maintains the Change History log or even, in rare circumstances, elect to not keep the log. To make changes to the length of time that Excel maintains the Change History log and make other changes to the sharing settings, select the Advanced tab of the Share Workbook dialog box.
The Advanced tab is divided into the following sections, each with its own options for not only changing how long the Change History log is maintained but also when and how updates are handled:
- Track Changes: Enables you to modify how long Excel keeps the Change History log. Just enter a new value in the Keep Change History For text box or select a new value with the spin buttons. Select the Don't Keep Change History option button if you don't need the Change History log.
- Update Changes: Determines when changes made by different users are saved. By default, Excel saves changes when the file is saved. To
have the program save changes at a set interval, select the Automatically Every option button and then enter the number of minutes for the save
interval in the Minutes text box or select it with the spin buttons.
When automatically saving changes at a set time interval, by default Excel saves only your changes while showing you changes made to the workbook by others. To have the program display the changes made to the file by others when the save interval is reached and not save your changes, select the Just See Other Users' Changes option button. - Conflicting Changes between Users: Determines how changes made to the same cells of a shared workbook by different users are treated. By default, Excel asks you which user's changes to accept and which to deny. If you want Excel to accept the changes made by any user at the time she or he saves the workbook, select The Changes Being Saved Win option button.
- Include in Personal View: Determines which of your personal settings are saved when you save the workbook. By default, Excel saves both your personal print settings (including such things as page breaks, changes to the print area, and changes to the printing settings in the Page Setup dialog box) and the filtering settings you select with the Data → Filter command. Clear the Print Settings and/or Filter Settings check boxes if you don't want these settings saved as part of the shared workbook.
Turning on change tracking
The other way to share a workbook on your network is simply by turning on change tracking. When you do this, Excel tracks all the changes you make to the shared workbook's cells by highlighting the cells and adding comments that summarize the type of change you make. Whenever you enable change tracking, Excel automatically turns on file sharing and creates a Change History log for the workbook in which the changes are recorded.
To share a workbook by turning on change tracking, follow these steps:
- Open the workbook for which you want to track changes.
This assumes that you've already saved the workbook in a folder on your network that's available to all the potential users. - Choose Tools → Track Changes → Highlight Changes to open the Highlight Changes dialog box.
- Select the Track Changes While Editing check box.
By default, Excel selects the When combo box and chooses the All option from its pop-up menu to have all changes made to the workbook tracked. Excel also selects the Who combo box and chooses Everyone from the pop-up menu, meaning it automatically tracks the changes made by anybody who opens and edits the workbook (including you). - If you don't want to track all the changes in the workbook, select one of the following options
from the When pop-up menu:
- Since Last Saved: Select this option to track the changes only from the time you last saved the workbook.
- Not Yet Reviewed: Select this option to track all the changes that you've not yet reviewed (and decided whether or not to accept).
Most often, you want to select this option so that you can use the Tools → Track Changes → Accept or Reject Changes command to review each user's changes and decide whether or not to keep them. - Since Date: Select this option to track changes from a particular date. Excel then inserts the current date into the When combo box, which you can then edit as necessary.
- If you want to exempt yourself from change tracking, select Everyone but Me from the Who pop-up menu. Or, if you want to restrict change
tracking to a particular user, select the user's name on the Who pop-up menu.
Change tracking automatically traces changes made to any cells in any sheet in the workbook. - If you want to restrict change tracking to a particular cell range or cell selection in the workbook, select the Where check box and then select the cell range or nonadjacent cell selection in the workbook.
- If you don't want changes displayed in the cells on-screen, clear the Highlight Changes on Screen check box.
Note that after you finish saving the workbook as a shared file, you can return to the Highlight Changes dialog box and then select its List Changes on a New Sheet check box to have all your changes listed on a new worksheet added to the workbook.
Note too, that if you select this check box when the Highlight Changes on Screen check box is selected, Excel marks the changes in their cells and lists them on a new sheet. If you clear the Highlight Changes on Screen check box while the List Changes on a New Sheet check box is selected, Excel just lists the changes on a new worksheet without marking them in the cells of the worksheet. - Click OK to close the Highlight Changes dialog box.
After Excel closes the dialog box, an alert dialog box appears, telling you that Excel will now save the workbook and asking you if you want to continue. - Click OK in the alert dialog box to save the workbook with the change tracking and filesharing settings.
After you turn on change tracking in a shared workbook (indicated by [Shared] appended to the filename on the Excel program title bar), the program highlights all the following changes in the workbook:
- Changes made to the cell contents, including moving and copying the contents to new cells in the worksheet
- The deletion of the cell contents
- The insertion of new rows, columns, or cells in a worksheet
When change tracking is turned on in a workbook, the program does not highlight any of the following changes:
- Formatting changes made to the cells
- Hidden or unhidden rows and columns in the worksheet
- Renamed sheet tabs in the workbook
- The insertion or deletion of worksheets in the workbook
- Comments added to the cells
- Changes to cell values resulting from recalculating their formulas or in cells whose values depend directly or indirectly upon the results of these formulas
To highlight changes you make to the shared workbook, Excel draws a thin line (in another color - usually blue) around the borders of the cell, places a triangle of the same color in the cell's upper-left corner, and changes the color of the cell's column letter and row number to red. When you position the thick white cross pointer on a highlighted cell, Excel displays a comment indicating the change made to the cell, along with the date and time it was made and who it was made by.
When you turn on change tracking, you turn on file sharing, and while file sharing is in effect, you can't make certain kinds of editing changes. For a complete list of these unavailable changes, see the section "Editing changes not available to a shared workbook," earlier in this tutorial.