When you stop sharing a workbook, Microsoft Excel turns off change tracking and permanently erases all change history. When you reshare, changes are tracked from that point onward.
If you want to retain the full change history, don't remove the workbook from shared use. To work with features that are unavailable when the workbook is shared, work in a copy of the workbook, and stop sharing only the copy.
If you must stop sharing a workbook, you can copy or print out the change history.
- On the Tools menu, point to Track Changes, and then click Highlight Changes.
- In the When box, click All.
- Clear the Who and Where check boxes.
- Select the List changes on a new sheet check box, and then click OK.
- Do one or more of the following:
- To print the History worksheet, click Print
. - To copy the history to another workbook, select the cells you want to copy, click Copy
, switch to another workbook, click where you want the copy to go, and click Paste
.
Note When you copy or print the history, you may also want to save or print a separate copy of the shared workbook. If the workbook continues to change as you or others work on it, the history that you copied may no longer apply. For example, cell locations, including row numbers, in the copied history may no longer be current.
- To print the History worksheet, click Print
I turned on change tracking, but no changes are displayed.
Are the changes types that are tracked? Some types of changes aren't tracked, and as a result information about them can't be displayed.
Changes Microsoft Excel doesn't track
Change tracking records changes you make to cell contents, including moves and copies, and also row and column insertions and deletions. Microsoft Excel does not keep track of the following types of changes:
| Untracked change | Alternatives |
|---|---|
| Change sheet names | Changes aren't highlighted on the sheet tabs, but are tracked in the History worksheet. |
| Insert or delete worksheets | Changes to a worksheet after it's inserted are tracked, but the insertion itself is not tracked. |
| Format cells or data | None |
| Hide or unhide rows or columns | None |
| Add or change comments | None |
| Cells that change because a formula calculates a new value | To find cells that change due to recalculation, use the tools on the Auditing toolbar. |
| Unsaved changes | You can highlight changes as you make them, but changes appear in the History worksheet only after they're saved. |
Change tracking is available only in shared workbooks. As a result, features that are unavailable for use in shared workbooks also are not tracked as changes.
Features unavailable in shared workbooks
You can access the following features only if you stop sharing the workbook.
You cannot use shared workbooks on Web servers.
| Unavailable feature | Alternatives |
|---|---|
| Insert or delete blocks of cells | You can insert entire rows and columns. |
| Delete worksheets | None |
| Merge cells or split merged cells | None |
| Add or change conditional formats | Existing conditional formats continue to appear as cell values change, but you can't change these formats or redefine the conditions. |
| Add or change data validation | Cells continue to be validated when you type new values, but you can't change existing data validation settings. |
| Create or change charts or PivotChart reports | You can view existing charts and reports. |
| Insert or change pictures or other objects | You can view existing pictures and objects. |
| Insert or change hyperlinks | Existing hyperlinks continue to work. |
| Use drawing tools | You can view existing drawings and graphics. |
| Assign, change, or remove passwords | Existing passwords remain in effect. |
| Protect or unprotect worksheets or the workbook | Existing protection remains in effect. |
| Create, change, or view scenarios | None |
| Group or outline data | You can continue to use existing outlines. |
| Insert automatic subtotals | You can view existing subtotals. |
| Create data tables | You can view existing data tables. |
| Create or change PivotTable reports | You can view existing reports. |
| Write, record, change, view, or assign macros | You can run existing macros that don't access unavailable features. You can record shared workbook operations into a macro stored in another nonshared workbook. |
| Add or change Microsoft Excel 4 dialog sheets | None |
| Change or delete array formulas | Existing array formulas continue to calculate correctly. |
Have you made any tracked changes? When you turn on change tracking, Excel maintains information about changes from that point on. To view information about changes once they're being tracked, make some changes and then turn on change highlighting.
- On the Tools menu, point to Track Changes, and then click Highlight Changes.
- In the When box, click Since I last saved.
- Clear the Who and Where check boxes.
- Select the Highlight changes on screen check box, and then click OK.
To see the changes on the History worksheet, save the workbook, and then display the History worksheet.
- On the Tools menu, point to Track Changes, and then click Highlight Changes.
If the Track changes while editing check box is not selected, Microsoft Excel has not recorded any change history for the workbook.
- Select the When check box and click All.
- Clear the Who and Where boxes.
- Select the List changes on a new sheet check box.
- Click OK, and then click the filter arrows next to the column labels on the History worksheet to find the information you want.
Note Saving the workbook removes the History worksheet. To view the History worksheet after saving, redisplay it.
When I protect sharing, I can't set the number of change history days.
When you use the Protect and Share Workbook command and apply a password, Microsoft Excel does not erase the stored change history regardless of the number of days set to keep it.
If you don't specify a password, Excel uses the most recent settings for the change history, 30 days for a workbook that hasn't previously been saved with a different setting. You or other users can then increase but not decrease the number of days to keep the change history. Click Share Workbook on the Tools menu, click the Advanced tab, and then type a larger number in the days box.
I want to track changes without sharing my workbook.
If you do not want to make the workbook available to others, keep it on your local hard disk, You can also assign a password to the workbook file before you turn on change tracking, to prevent others from opening it.
Protect worksheet elements
Protect worksheet elements from all users
-
Switch to the worksheet you want to protect.
-
Unlock any cells you want users to be able to change: select each cell or range, click Cells on the Format menu, click the Protection tab, and then clear the Locked check box.
-
Hide any formulas that you don't want to be visible: select the cells with the formulas, click Cells on the Format menu, click the Protection tab, and then select the Hidden check box.
-
Unlock any graphic objects you want users to be able to change.
You don't need to unlock buttons or controls for users to be able to click and use them. You can unlock embedded charts, text boxes, and other objects created with the drawing tools that you want users to be able to modify. To see which elements on a worksheet are graphic objects, click Go To on the Edit menu, click Special, and then click Objects.
-
Hold down CTRL and click each object that you want to unlock.
-
On the Format menu, click the command for the object you selected: AutoShape, Object, Text Box, Picture, Control, or WordArt.
-
Click the Protection tab.
-
Clear the Locked check box, and if present, clear the Lock text check box.
-
-
On the Tools menu, point to Protection, and then click Protect Sheet.
-
Type a password for the sheet.
Note The password is optional; however, if you don't supply a password, any user will be able to unprotect the sheet and change the protected elements. Make sure you choose a password you can remember, because if you lose the password, you cannot gain access to the protected elements on the worksheet.
-
In the Allow all users of this worksheet to list, select the elements that you want users to be able to change.
-
Click OK, and if prompted retype the password.
Give specific users access to protected ranges
You must have Windows 2000 or later to give specific users access to ranges.
-
On the Tools menu, point to Protection, and then click Allow Users to Edit Ranges. (This command is available only when the worksheet is not protected.)
-
Click New.
-
In the Title box, type a title for the range you're granting access to.
-
In the Refers to cells box, type an equal sign (=), and then type a reference or select the range.
-
In the Range password box, type a password to access the range.
The password is optional; if you don't supply a password, any user will be able to edit the cells.
-
Click Permissions, and then click Add.
-
Locate and select the users to whom you want to grant access. If you want to select multiple users, hold down CTRL while you click the names.
-
Click OK twice, and if prompted retype the password.
-
Repeat the previous steps for each range for which you're granting access.
-
To retain a separate record of the ranges and users, select the Paste permissions information into a new workbook check box.
-
Protect the worksheet: On the Tools menu, point to Protection, click Protect Sheet, make sure the Protect worksheet and contents of locked cells check box is selected, type a password for the worksheet, click OK, and retype the password to confirm.
Note A sheet password is required to prevent other users from being able to edit your designated ranges. Make sure you choose a password you can remember, because if you lose the password, you cannot gain access to the protected elements on the worksheet.
Help protect workbook elements and files
-
On the Tools menu, point to Protection, and then click Protect Workbook.
-
Do one or more of the following:
-
To protect the structure of a workbook so that worksheets in the workbook can't be moved, deleted, hidden, unhidden, or renamed, and new worksheets can't be inserted, select the Structure check box.
-
To use windows of the same size and position each time the workbook is opened, select the Windows check box.
-
To prevent others from removing workbook protection, type a password, click OK, and then retype the password to confirm it.
-
-
If the workbook is already shared, and you want to assign a password to protect the sharing, unshare the workbook.
-
Have all other users save and close the shared workbook. If other users are editing, they will lose any unsaved work.
-
Unsharing the workbook deletes the change history. If you want to keep a copy of this information, print out the History worksheet or copy it to another workbook.
-
On the Tools menu, point to Track Changes, and then click Highlight Changes.
-
In the When box, click All.
-
Clear the Who and Where check boxes.
-
Select the List changes on a new sheet check box, and then click OK.
-
Do one or more of the following:
-
To print the History worksheet, click Print
. -
To copy the history to another workbook, select the cells you want to copy, click Copy
, switch to another workbook, click where you want the copy to go, and click Paste
.
Note You may also want to save or print the current version of the workbook, because this history might not apply to later versions. For example, cell locations, including row numbers, in the copied history may no longer be current.
-
-
-
On the Tools menu, click Share Workbook, and then click the Editing tab.
-
Make sure that you are the only person listed in the Who has this workbook open now box.
-
Clear the Allow changes by more than one user at the same time check box.
If this check box is not available, you must unprotect the workbook before clearing the check box.
-
When prompted about the effects on other users, click Yes.
-
-
Set other types of protection if you want: Give specific users access to ranges, protect worksheets, protect workbook elements, and set passwords for viewing and editing.
-
On the Tools menu, point to Protection, and then click Protect Shared Workbook or Protect and Share Workbook.
-
Select the Sharing with track changes check box.
-
If you want to require other users to supply a password to turn off the change history or remove the workbook from shared use, type the password in the Password box, and then retype the password when prompted.
-
If prompted, save the workbook.
Protect a workbook file from viewing or editing
-
On the File menu, click Save As.
-
On the Tools menu, click General Options.
-
Do either or both of the following:
-
If you want users to enter a password before they can view the workbook, type a password in the Password to open box, and then click OK.
-
If you want users to enter a password before they can save changes to the workbook, type a password in the Password to modify box, and then click OK.
-
-
When prompted, retype your passwords to confirm them.
-
Click Save.
-
If prompted, click Yes to replace the existing workbook.
Highlighting changes
I can see only recent changes.
Is change tracking turned off? On the Tools menu, click Share Workbook, click the Advanced tab, and make sure the Keep change history option is available and selected. If change tracking has been turned off, you can only see changes made during your current editing session, since the last time you saved the shared workbook.
Did you specify only recent changes? Make sure you're displaying the older change history:
- On the Tools menu, point to Track Changes, and then click Highlight Changes.
- In the When box, click the option you want:
Since I last saved shows only those changes made after the last time you saved the workbook.
Not yet reviewed shows only changes that you and other users haven't accepted or rejected and then saved.
Since date shows only changes after the date you specify. Click Since date, and then type the date in the When box.
All shows you changes regardless of their age.
Are you keeping enough history? History that's older than the number of days you set is deleted whenever a user closes the workbook. To keep the change history for a long period of time, you can set a large number of days, such as 1,000. To extend the number of days to keep history, click Share Workbook on the Tools menu, click the Advanced tab, and then type a larger number in the days box.
I don't see the changes I expect.
Turn on change tracking If you turn off change tracking or stop sharing a workbook, the change history is permanently deleted, and you can no longer view any changes.
How to turn on change tracking
- On the Tools menu, click Share Workbook, and then click the Editing tab.
- Select the Allow changes by more than one user at the same time check box.
- Click the Advanced tab.
- Under Track changes, click Keep change history for, and in the Days box, type the number of days of change history that you want to keep.
Be sure to enter a large-enough number of days because Microsoft Excel permanently erases any change history older than this number of days.
- Click OK, and if prompted to save the file, click OK.
Note Turning on change tracking also shares the workbook.
Make sure other users have saved When you save the workbook or receive an update, you receive only saved changes from other users, not changes that they haven't yet saved.
Check which change types Microsoft Excel tracks Excel doesn't track some types of changes, such as formatting changes and comments.
Changes Microsoft Excel doesn't track
Change tracking records changes you make to cell contents, including moves and copies, and also row and column insertions and deletions. Microsoft Excel does not keep track of the following types of changes:
| Untracked change | Alternatives |
|---|---|
| Change sheet names | Changes aren't highlighted on the sheet tabs, but are tracked in the History worksheet. |
| Insert or delete worksheets | Changes to a worksheet after it's inserted are tracked, but the insertion itself is not tracked. |
| Format cells or data | None |
| Hide or unhide rows or columns | None |
| Add or change comments | None |
| Cells that change because a formula calculates a new value | To find cells that change due to recalculation, use the tools on the Auditing toolbar. |
| Unsaved changes | You can highlight changes as you make them, but changes appear in the History worksheet only after they're saved. |
Change tracking is available only in shared workbooks. As a result, features that are unavailable for use in shared workbooks also are not tracked as changes.
Features unavailable in shared workbooks
You can access the following features only if you stop sharing the workbook.
You cannot use shared workbooks on Web servers.
| Unavailable feature | Alternatives |
|---|---|
| Insert or delete blocks of cells | You can insert entire rows and columns. |
| Delete worksheets | None |
| Merge cells or split merged cells | None |
| Add or change conditional formats | Existing conditional formats continue to appear as cell values change, but you can't change these formats or redefine the conditions. |
| Add or change data validation | Cells continue to be validated when you type new values, but you can't change existing data validation settings. |
| Create or change charts or PivotChart reports | You can view existing charts and reports. |
| Insert or change pictures or other objects | You can view existing pictures and objects. |
| Insert or change hyperlinks | Existing hyperlinks continue to work. |
| Use drawing tools | You can view existing drawings and graphics. |
| Assign, change, or remove passwords | Existing passwords remain in effect. |
| Protect or unprotect worksheets or the workbook | Existing protection remains in effect. |
| Create, change, or view scenarios | None |
| Group or outline data | You can continue to use existing outlines. |
| Insert automatic subtotals | You can view existing subtotals. |
| Create data tables | You can view existing data tables. |
| Create or change PivotTable reports | You can view existing reports. |
| Write, record, change, view, or assign macros | You can run existing macros that don't access unavailable features. You can record shared workbook operations into a macro stored in another nonshared workbook. |
| Add or change Microsoft Excel 4 dialog sheets | None |
| Change or delete array formulas | Existing array formulas continue to calculate correctly. |
Check your highlight settings These settings also affect what's included on the History worksheet. Point to Track Changes on the Tools menu, and click Highlight Changes. Make sure the Highlight changes on screen check box is selected, and check these settings:
- When box Click All, or clear the When check box, to view all available changes for all time periods. Since I last saved shows only those changes made after the last time you saved the workbook. Not yet reviewed shows only changes that you and other users haven't accepted or rejected and then saved. Since date shows only changes after the date you specify.
- Who box Everyone but me shows you changes made by all other users but does not highlight your own changes. Everyone, or clearing the Who check box, shows changes from all users.
- Where box Enter a range to view changes only for that range. To view changes for the whole workbook, clear the Where check box.
The Accept or Reject Changes command isn't available.
-
To make this command available, you must turn on change tracking.
- On the Tools menu, click Share Workbook, and then click the Editing tab.
- Select the Allow changes by more than one user at the same time check box.
- Click the Advanced tab.
- Under Track changes, click Keep change history for, and in the Days box, type the number of days of change history that you want to keep.
Be sure to enter a large-enough number of days because Microsoft Excel permanently erases any change history older than this number of days.
- Click OK, and if prompted to save the file, click OK.
Note Turning on change tracking also shares the workbook.
Changes you made before you turned on change tracking won't be available for review.
History worksheet
The checkbox for listing changes on a new sheet isn't available.
-
Turn on change tracking This option becomes available only after change tracking has been started.
How to turn on change tracking- On the Tools menu, click Share Workbook, and then click the Editing tab.
- Select the Allow changes by more than one user at the same time check box.
- Click the Advanced tab.
- Under Track changes, click Keep change history for, and in the Days box, type the number of days of change history that you want to keep.
Be sure to enter a large-enough number of days because Microsoft Excel permanently erases any change history older than this number of days.
- Click OK, and if prompted to save the file, click OK.
Note Turning on change tracking also shares the workbook.
Save some changes Once you are tracking changes, you must also make and save some changes to the workbook. The History worksheet lists only changes you've saved. You can highlight changes that you haven't yet saved, but these changes won't appear on the History worksheet until you save the workbook.
Microsoft Excel won't display the History worksheet.
Save some changes Once you turn on change tracking for a workbook, you or another user must also make and save some changes to the workbook. The History worksheet becomes available only after changes have been saved.
Check your highlight settings Your settings for the When, Who, and Where boxes determine what changes are listed on the History worksheet. If you aren't sure what type of changes have been saved, select all changes: point to Track Changes on the Tools menu, click Highlight Changes, clear all three of these check boxes, select the List changes on a new sheet check box, and then click OK.
Changes are missing from the History worksheet.
Save, then redisplay the History worksheet The History worksheet shows only your saved changes and changes other users saved before you last saved or received an update. If you receive an update after you display the History worksheet, the History worksheet is not updated until you redisplay it.
To see the most recent saved changes from other users, save the workbook, and then redisplay the History worksheet.
Highlight your unsaved changes You cannot view information about unsaved changes made by other users. You can highlight your own unsaved changes on the worksheet.
- On the Tools menu, point to Track Changes, and then click Highlight Changes.
- In the When box, click Since I last saved.
- Clear the Who and Where check boxes.
- Select the Highlight changes on screen check box, and then click OK.
I can't format or edit the History worksheet.
Copy the history to a new worksheet The History worksheet is available only for viewing, not formatting or editing.
- On the Tools menu, point to Track Changes, and then click Highlight Changes.
- In the When box, click All.
- Clear the Who and Where check boxes.
- Select the List changes on a new sheet check box, and then click OK.
- Select the cells you want to copy and click Copy
on the Standard
toolbar.
- Switch to another worksheet, click where you want the copy to go, and click Paste
.
When you copy the history, you may also want to save a separate copy of the shared workbook. If the workbook continues to change as you or others work on it, the history that you copied may no longer apply. For example, cell locations, including row numbers, recorded in the copied history may no longer be current.
The History worksheet disappears when I save.