Microsoft Office 2016: The Complete Guide (2015)

HOW TO COLLABORATE IN EXCEL 2016 BY USING A SHARED WORKBOOK

When a workbook is shared, it can be viewed by multiple users; however, they cannot make any simultaneous changes to the file. Changes can be made instead to one copy or several copies of the workbook after which the changes are merged into the file that was shared by you.

This is how the process works. The workbook is shared. As part of the sharing action, the workbook is saved. The shared workbook is then copied by someone else who enters additional data to their copy. The copy data is merged back into the original.

Others can easily access a workbook, make a copy, and make changes when it is shared. Putting a workbook on a network site or on a OneDrive folder is a great way to share a workbook.

Sharing a workbook

To begin sharing you would open the workbook to be shared, whether it is one that was already created or a new one that was just created. You would then go to the Changes group on the Review tab and click on “Share Workbook”. The “Share Workbook” dialog box is found on the Editing Tab. Choose “Allow changes by more than one user at the same time”. The workbook merging check box is also allowed. Choose your option to track and update changes by going on the “Advanced tab”. Then click OK. Put in a network location in the “Address bar” in the “Save As” dialog box. If it is a new workbook you would save it by typing the name in the “File name box” and click ‘Save”. If it is an existing workbook, you would just click Save. You would then send an email to all the people who will be sharing the workbook. Included in your message should be the file location. Ask the users to make a copy of the workbook for their personal use and keep all the copies with the original in the folder.

How to Merge Changes

Before starting to merge changes you need to add a command to your Quick Access toolbar. This is located in the upper-left corner of the Excel screen. Below is a guide as to how to add a command. When your coworkers with whom the data was shared have finished adding their data to their copies of the workbook, you can proceed in merging your changes with theirs.

The following steps outline how to add the command:

There is a down arrow on the right hand side of the Quick Access toolbar, click on this arrow and then click on More Commands.

In the Choose commands from list in the Excel Options dialog box, select All Commands.

By scrolling down the list, choose the Compare and Merge Workbooks then click Add.

Close the Excel Options dialog box by clicking OK.

The command for Compare and Merge Workbooks is on the Quick Access toolbar.

The following guide you on How to Merge Changes

First you would open the copy of the shared workbook into which changes are to be merged.

Next, you would Click Compare and Merge Workbooks on the Quick Access Toolbar

You might be prompted to save the workbook. Please save.

Click on the copy of the workbook which contains the changes that you desire to merge, and then click in the OK Select Files to Merge into Current Workbook dialog box.

Here are some shortcut TIPS:

If you hold down the CTRL or SHIFT key, click on the file names, and then click OK, you can merge the workbook with multiple copies of the shared workbook simultaneously.

If a message appears that says that there are no new changes to merge, just click OK.

Resolving conflicting changes in a shared workbook

When two users attempt to save changes to the same cell a conflict takes place. Only one of the changes made in that cell can be kept. The Resolve Conflicts dialog box is displayed by Excel when the second user saves the workbook. The following steps guide you on how to resolve conflicting changes when they occur:

When the Resolve Conflicts dialog box appears, read the information about the changes and the resulting conflicting changes that were made by the other user.

If you want to progress to the next conflicting change after you have decided to either keep your change or the other user’s change you click on Accept Mine or Accept Other, respectively. To keep all your changes, click on Accept All Mine. To keep all of the user’s changes, click Accept All Others.

There is another method to having your changes override all other changes without the Resolve Conflicts dialog box being displayed. Just do the following:

In the Changes group on the Review tab, click Share Workbook

Under Conflicting changes between users on the Advanced tab, click The changes being saved win, then click OK.

You can view how others had resolved conflicts in the past by doing the following

In the Changes group on the Review tab, click Track Changes and then choose Highlight Changes.

Select All from the When list.

Clear the Who and Where check boxes

Choose the List changes on a new sheet check box and then OK

Scroll to the right on the History worksheet to view the Action Type and Losing Action columns

Previous conflicting changes that were kept have the Action Type Won value. The rows with information about conflicting changes that had not been kept, including any data that was deleted, are identified by the row numbers in the Losing Action column. Here is tip on how to save all your changes in a copy of the workbook. In the Resolve Conflicts dialog box, click on Cancel and then save a copy of the file by giving it a new name.

Editing a Shared workbook

Editing a shared workbook is not a difficult task. It is done in the same way that you would change or enter data in a workbook from your computer.

Opening a shared workbook

To open a shared workbook, just click on File, next click Open, and then double-click on your shared workbook.

You can go straight to the network folder where your file is to start Excel and open the file by double-clicking it. If this is the first time that you are opening the workbook, then you would click on the Options button on the File tab. You can enter the name you wish to give the file in order to identify your work in the shared workbook. Just go to the User Name box in the General category under Personalize your copy of Microsoft Office, type in the name you choose to give the file and then click OK.

How to edit the workbook?

To edit a workbook, the data is entered and edited on the worksheets as normal. Some elements, however, cannot be added or changed. Examples are hyperlinks and merged cells. You can add any filter and print settings that you want to personally use.

Each user’s settings are saved individually so you are able to use the filter or print settings that were added by the workbook’s owner.

How to use original filter and print settings

In the Changes group on the Review tab, click Share Workbook.

Under Include in personal view, click the Advanced tab. Next clear the Print settings or Filter settings check box, then click OK.

To see any changes made by other users that were saved since the last time you saved or to save your changes to the workbook, click on the Save icon on the Quick Access toolbar, or use the shortcut method by pressing Ctrl+S.

Resolve the conflicts by clicking the Resolve Conflicts dialog box if it appears.

You can find help to do that by going to the “Resolving conflicting changes in a shared workbook” section found in this same article.

On the Editing tab of the Share Workbook dialog box, you are able to see which other users have the workbook opened.

You are able to occasionally get automatic updates of other users’ changes whether or not you save. This is done under Update changes on the Advanced tab of the Shared Workbook dialog box.

How to remove a user from a shared workbook?

If the need arises, you are able to remove a user from a shared workbook.

However, before proceeding to remove or disconnect a user, you need to ensure that they are finished using the workbook. Removing any user that is still active will cause any unsaved work to be lost.

To begin removing a user just visit the Changes group and go to the Review tab, then click Share Workbook.

Next you go to the Who has this workbook open now list on the Editing tab to review the names of users.

Just select the name of the user you want to remove and click on Remove User.

Even though this disconnects the user from the shared workbook, it does not stop that user from being able to edit the shared workbook again.

The user’s personal view settings can be deleted. Just do the following:

In the Workbook Views group, click View, then Custom Views.

From the Views list, choose the view that you want to remove and click Delete.

How to copy the change history information?

To begin copying change history information, do the following:

In the Changes group on the Review tab, click Track Changes, and then click Highlight Changes.

Next select All from the When list

Clear the check boxes for Who and Where

Choose the List changes on a new sheet check box then click OK.

You can then do either one or both of the following:

Make a copy of the history workbook. You do this by selecting the cells you want to be copied, then press Ctrl+C, go to another workbook and click the destination for the data you copied and press Ctrl+V.

The next option is to print the History worksheet.

You might want to save or print current version of the workbook as well since this history data might not be applicable to newer versions of the workbook. Cell locations inclusive of numbers in the copied history may no longer be up to date or current.

How to stop sharing the workbook

To stop sharing a workbook you follow the subsequent procedures as outlined:

Go to the shared workbook then go to the Changes group found on the Review tab and click on Share Workbook.

Next step is to ensure that you are the only person listed in the list of Who has this workbook open now. This is found on the Editing tab.

For removal of additional users, see the “How to remove a user from a shared workbook” section found earlier in the article.

Clear the Allow changes by more than one user at the same time check box. This also allows the workbook merging check box.

The checkbox might not be available so ensure that you unprotect the workbook. Here is how you would remove shared workbook protection:

Close the Share Workbook dialog box by clicking OK.

In the Changes group on the Review tab, click Unprotected Shared Workbook.

If prompted, enter password and then click OK.

In the Changes group on the Review tab, click Allow changes by more than one user at the same time. This will also allow workbook merging check box.

You will be prompted about the effects on other users. Click Yes.

NEW FEATURES FOR WINDOWS IN EXCEL 2016

The upgraded version of Excel 2016 not only retains all the previous features and functionality that you were familiar with but now has additional features and improvements as well as the best new features that Office 2016 possesses.