 |
|
|
Download the associated Workbook for this lesson
A shared Workbook is a Workbook that resides on a Network and allows more than one person to make changes to it at the same time. All users must be using Excel 97 onwards. There are some considerations that need to be made before sharing a Workbook as there are a lot of Excels features that will not work in a Shared Workbook. The list below is from the help file and should be read if considering sharing a Workbook.
Excel Help - Limitations of shared Workbooks
Some features of Microsoft Excel are not available when you use a shared workbook. If you need to use these features, do so before you share the workbook, or remove the workbook from shared use.
In a shared workbook, you cannot do the following:
Delete worksheets.
Merge cells. You can view cells that were merged before you shared the workbook.
Define or apply conditional formats. However, you can see the effects of conditional formats applied before you shared the workbook.
Set up or change data validation restrictions and messages. However, you can see the effects of restrictions and messages that were set up before you shared the workbook.
Insert or delete blocks of cells. You can insert or delete entire rows and columns.
Insert or change charts, pictures, objects, or hyperlinks.
Use the drawing tools.
Assign a password to protect individual worksheets or the entire workbook. Protection that you applied before sharing the workbook remains in effect after you share the workbook.
Change or remove passwords. Passwords that you assigned before sharing the workbook remain in effect after you share the workbook.
Save, view, or make changes to scenarios.
Group or outline data.
Insert automatic subtotals.
Create data tables.
Create PivotTables or change the layout of existing PivotTables.
Make changes to dialog boxes or menus.
Write, change, view, record, or assign macros. However, you can record operations in a shared workbook into a macro stored in another workbook that isn't shared. In a shared workbook, you can run macros that were created before you shared the workbook; although if you run a macro that includes an unavailable operation, the macro stops running when it reaches the unavailable operation
End of Help
So as you can see there are few features that are not available in a Shared Workbook. When all is said and done a Shared Workbook is best suited to a Workbook that requires other users to only input data into. The main purpose of Shared Workbooks is to distribute them to different users and collect their input. The Shared Workbooks can then be Merged into one. We will look at Merged Workbooks a little later.
There are two menu items under Track Changes. These are Highlight Changes and Accept or Reject Changes. The Accept or Reject Changes will only be available after Highlight Changes has been chosen and set and choosing Highlight Changes will share the Workbook. In other words we can only Highlight Changes in a Shared Workbook.
If we go to Tools>Track Changes>Highlight Changes we will see the Highlight Changes dialog box. The first thing that we need to do is check the Track changes while editing the checkbox. This will make the When, Who, Where and Highlight changes on screen all available. Notice that the List changes on a new sheet checkbox is not enabled. This will only be available if we have already saved the Workbook as a shared workbook.
When
This is where we can specify when Excel will display changes and has four choices
Since I Last Saved
All
Not Yet Reviewed
This option means you can highlight changes you have not yet reviewed. You review changes by selecting Accept or Reject Changes under Tools>Track Changes.
Since Date
If you select this you can type in any valid date. The default date will be the current date.
Who
There are two choices here they are :
Everyone
This is determined by the user name under Tools>Options-General/Username
Everyone but Me
Where
You can nominate a range of cells to highlighted changes on, or leave it blank and it will apply to all sheets and cells.
Selecting this option means each time a change is made to your Workbook, a cell comment will be inserted into the changed cell and will have: Username, Date, Time, Changed from and Changed to. A small comment flag (triangle) will be in the upper right of the cell and be a different colour to a normal cell comment.
Once we have selected our options we click OK, Excel will warn us that doing so will Save the Workbook and allow us to Continue or Cancel. When we opt to Continue our Workbook will become a Shared Workbook. From now on any changes that are made will be tracked. The type of changes that are tracked are as listed below:
Changes to cell contents, including moved and pasted contents
Inserted and deleted rows and columns
Remembering that we must first save our Workbook as a Shared Workbook, have some changes made and those changes saved, this option provides far more information of what changes have taken place. After you have saved the Workbook as Shared and selected this option, it means that you can then go to Tools>Track Changes>Accept or Reject Changes (after changes have been made) and have the following information automatically listed on a another Worksheet named History. This sheet will also, by default, have Excels AutoFilters turned on for easier viewing.
|
Action number |
This is the order in which the changes were made in numeric order |
|
Date |
|
|
Time |
|
|
Who |
User name. |
|
Change |
The type of change made eg; Cell change, row deletion etc. |
|
Sheet |
Name of the Worksheet that the change occurred on. |
|
Range |
Address of the change. |
|
New Value |
Value of the cell before it was changed. |
|
Old Value |
Value of the cell after it was changed. |
|
Action Type |
This will list whether the change was the "Result of rejected action", "Undo" or "Won" See Accept or Reject Changes below. |
|
Losing Action |
This relates to the Action Number if the change was the result of the Accept or Reject Changes dialog box. See Accept or Reject Changes below. |
The History Sheet will only be available until the Workbook is re-saved. Once the Workbook is re-saved the History sheet will be deleted. To display it again simply go to Tools>Track Changes>Highlight Changes and check the Highlight changes on screen option and click OK.
Once we have selected the options for Highlight Changes and there has been changes made and the Workbook has been saved, we can go to Tools>Track Changes>Accept or Reject Changes and see the Select Changes to Accept or Reject dialog box. It is in here that we determine which changes we want to Accept or Reject. The choices here are When, Who and Where. These option are basically the same as the When, Who and Where options under the Track Changes dialog box we used earlier when we set up Track Changes. To Accept or Reject Changes for the entire Workbook, leave the Where checkbox unchecked.
Once we have made our choices we click OK and Excel will display the Accept or Reject Changes dialog box. We can now move through all the changes made and either Accept or Reject them. Rejecting a change will result in the cell being returned to its changed from value. If there has been more than one change to a cell and the Workbook has been saved between changes, you will see a list of the changes made. You simply select the value you wish to return the cell value back to and click Accept. The Accept All and Reject All buttons will either reject or accept all changes, depending on the choice made. You must either Accept or Reject a change before you can move onto the next change.
Ok, now we have worked our way through Highlight Changes and Accept or Reject Changes we can move onto Sharing a Workbook.
Once you have decided to share a Workbook (based on the limitations listed earlier) you go to Tools>Share Workbook… This will display the Share Workbook dialog box. There are two page tabs on this dialog box, Editing and Advanced. To share the Workbook we must first check the Allow changes by more than one user at the same time checkbox. The Who has the workbook open now shows the names of all users who currently have the Workbook open and the date and time they opened it. You can select a name from here and click the Remove User button. Just be aware that if you remove a user before he/she has saved changes those changes will be lost.
One common problem that can happen with Shared Workbooks is that they are Shared before all the necessary steps have been taken. This is particularly true should you decide to Password Protect the Shared Workbook after it has already be Shared. So before sharing a Workbook it pays to ensure you have thought through all settings that will be needed. To Password Protect a Shared Workbook you need to remove the Workbook from Shared use, which can cause unforeseen problems.
It is also possible to Merge changes from a Workbook that is Shared, but there are certain conditions that must be met for this to be possible. The list below is from the Microsoft Excel help
Excel Help
Both workbooks must be copies of the same workbook.
Each copy must have a different file name.
The workbooks either must not have passwords or must both have the same password.
The workbook from which the copies were originally made must have been a shared workbook·
When the copies were made, the change history must have been turned on.
At the time that you merge the copies, the change history must have been maintained in both copies for at least as many days as have elapsed since the copies were originally made. For example, if the change history is being maintained in the workbooks for 30 days, and the copies were made 45 days ago, you can no longer merge the workbooks.
When you turn on the change history but aren't sure when you'll merge the workbooks, you can preserve the change history by setting a large number of days to maintain the change history, up to the maximum of 32,767 days.
If Microsoft Excel cannot merge the two workbooks, you can incorporate the information from one workbook into the other manually, by copying and pasting the information from one workbook to another.
End of Help
We will look at this after going through the rest of Shared Workbooks. The text below is a brief explanation of the features on the Advanced page tab of the Share Workbook dialog box
Once we have checked the Allow changes by more than one user at the same time checkbox we can set up the options under the Advanced page tab.
Keep change history for ........ days
In the box to the right of this simply enter the number of days you want the changed history retained for.
Don't keep change history
Switches off the change history. This means you cannot Merge changes from other copies of the same Workbook or review any changes.
When file is saved
All changes made by yourself, or by other users are updated each time you save the Workbook.
Automatically every ..... minutes
Will automatically show you changes that have been made and saved by other users at the interval you specify in the minutes box.
Save my changes and see others' changes
Works in conjunction with Automatically every ….minutes. Your changes are saved, while changes from other users are updated at the interval you set in the Automatically every ….minutes box
Just see other users' changes
Works in conjunction with Automatically every ….minutes. All others' changes are updated at the time interval specified in the Automatically every ….minutes, but your changes are not saved at the time of update.
Ask me which changes win
If checked and conflicting changes occur you will be displayed the Resolve Conflicts dialog box. In here you can review the conflicting change and decide which change wins.
The change being saved wins
If checked, any conflicting changes will be automatically replaced with your changes each time you save. The Resolve Conflicts dialog box will not appear.
Print Settings
Will save all your personal print settings, if you have specified any. Print settings include page breaks, print areas, any settings you make in page break preview, and any settings in the Page Setup dialog box (File menu).
Filter settings
Retains any settings you make using the Auto filter or Advanced filter.