Excel online training course

excel lesson 16, Sharing and Reviewing workbooks

Sharing Workbooks

We can share workbooks with other users in a network and simultaneously make changes to them.

There are a few things you need to take into account when you share a workbook.
The changes that are mentioned below cannot be altered or inserted anymore:

# merged cells
# conditional formatting
# graphs
# images
# objects
# hyperlinks
# summaries
# subtotals
# data tables
# workbook and worksheet security
# macros
# data validation
# pivot table reports

Thats why you need to do these things before you will share the workbook.

To share a workbook we first need to open our workbook.
Then we click on "Tools" and "Share Workbook" in the menubar.
This will open the "Share Workbook" window.

Choose the "Editing" tab and check the box "Allow changes by more than one user at the same time".
Then we choose the "Advanced" tab to set the extra options.

In the top section of the "Advanced" tab we set how long we want to save the changes.
In the second section we set when we want to save our changes.
In the third section we will set what must happen with conflicts through changes by multiple users.
And in the last section we can check the filter box to show this in the "Standard View".
Click on "Ok" one you are satified with the options and click on "Ok" when you are asked to save the changes.
Once all of this is done, other users can start making changes to this workbook.

When two users make changes in the same cell, Excel will show us the "Resolve Conflicts" window.
In this window we can see the information of all the changes and the conflict.
To keep your changes, click on "Accept Mine", or to keep the other users changes you click on "Accept Others".
You can also choose for "Accept All Others" or "Accept All Mine" to keep all changes.
This will of course only happen when we checked the box with "Ask me which changes win" in the "Advanced" tab of the "Share Workbook" window.

When we want to stop sharing our workbook with others we click on "Tools" and "Share Workbook" in the menubar.
In the window that opens you check the box that says "Allow changes by more than one user" again and then click on "Ok".

Track changes

To mark changes we click on "Tools", "Track Changes" and "Highlight Changes" in the menubar.

In the window that opens we can select the following options:

Track changes while editing: this must allways be checked when we want to track our changes.
When: we can make some choices in here which dont need alot explenation.
Who: another list to choose from which is pretty obvious.
Where: here we can enter a range that we wish to check for changes.
In the second section of the window we have the choice to display the changes on our screen or/and in a new worksheet.
When you are done here click on "Ok".

Accept changes

After looking at the changes we can choose if we want to accept them or not.
To do this we click on "Tools", "Track Changes" and "Accept or Reject Changes" in the menubar.
In the window that opens we filter the changes by clicking on the corresponding check boxes to limit our list.
Once you made you selection, click on "Ok".

In the window that opens we can see what has been changed, by who and when.
Click on "Accept" or "Accept All" to accept the changes or click on "Reject" or "Reject All" to reject them.
When mulitple changes have been made to one cell, we first need to select which we want to keep.

Reviewing toolbar

We use the reviewing toolbar to provide cells with comments or look at changes in a shared workbook.

A short explenation about the buttons in the reviewing toolbar:

new comment   remove comment
change comment   hide written comments
previous comment   remove written comments
next comment   create outlook task
hide comment   update file
hide all comments   send to mail recipient

Adding comments

When we enter formulas or make changes in a cell, it can be usefull to add some comments about it so others who are also working with our workbook can read it.
For example:
We could add a comment to a cell from which the function of a formula is explained.
More than once we want to give comments to changes we have made to a cell so we can explain the purpose of it to other users.
Cel comments are small textframes which are added to a cell.
When someone moves his cursor of the cell a small popup window will appear with our comment.
Cells where a comment is added have a small red mark in the topright corner.

We can easily add a comment to a cell by clicking on the "New Comment" button excell_small_81.jpg (1.449 bytes).
Or by right clicking in the cell and choose "Insert Comment" from the dropdown menu.

In the textarea that will open the comment will be displayed together with the name of the person who made it.
When we are done entering our comment we click somewhere in the worksheet close the comment field.
A red mark is now added to the cell.

Manage comments

We can edit our comment by clicking on the "Edit" button excell_small_83.jpg (1.382 bytes).
This button is only visible when the cell allready has a comment.
Or we right click on the cell and choose for "Edit Comment" from the dropdown.
To remove a comment, we select the cell and click the "Delete Comment" button excell_small_82.jpg (1.505 bytes).
Or we right click the cell and choose for "Delete Comment" in the dropdown menu.

Show comments

By moving the cursor over the cell we can view the comment.
But if we have multiple comments in different cells it can be usefull to read these comments one by one.
We can do this by using the "Previous" excell_small_85.jpg (1.434 bytes) and "Next" excell_small_87.jpg (1.463 bytes) buttons.
When you click on the "Show All Comments" button excell_small_91.jpg (1.212 bytes) all comments in our worksheet will be displayed, when you click on this button again the comments will once again become hidden.

When comments overlap each other, we can move them by moving the cursor over the edge of the comment untill this changes into a cross with arrows icon, by clicking and dragging to any direction you can move the comment.

Print comments

We have the choice to print our comments at the end of our worksheet or inside the worksheet.
Click on "File" and "Page Setup" in the menubar.
In the "Comments" field of "Print" section we can choose between "None", "At end of sheet" or "As displayed on sheet"

Click on "Ok" when you are done.

Send Spreadsheets

When we want to mail a shared workbook or a copy to someone, we first need to open the workbook.
Then we click on "File", "Send To" and "Mail Recipient (for review)" in the menubar.
This will send the worksheet to the recipient so he can edit and/or view it.
When he is done making his changes he can mail it back to us.
When we choose for "Mail Recipient (as attachment)" this will be send as a copy of the worksheet attached in the email and thus cannot be used for "Sharing Workbooks".

When we have choosen for "Mail Recipient (for review)" the recipient can click on the button from the reviewing toolbar to send a copy back to the sender.

When the sender receives the altered worksheet back he will be asked if he does or doesnt accept the changes.
When all changes between all users have been made you click on the button "End Review".

Comparing and Merging workbooks

When we received all copies with different adjustments from different users, we can merge all these workbooks into one workbook.

There are a few requirements these files need to have before we can do this.

1. all workbooks need to be copies from the original.
2. they all need to have a different name but need to be in the same folder.
3. they are not allowed to have passwords.
4. they all need to have "Track Changes" enabled because if you will merge the files they need to know the history of it.

Please note that when you are merging mulitple workbooks, it will replace the changes in the last workbook you add even if there are conflicts.

To merge workbooks please make sure they are all in the same folder first.
then we open the workbook where we want the merge to go.
We click on "Tools" and "Compare and Merge Workbooks" in the menubar.
We will save the worksheet if this is asked.
This will open the "Select Files to Merge into Current Workbook" window.
Here we select all the copies of the different workbooks we wish to merge.
And finally click on "Ok".