Excel 2007: Share Workbooks

Shared workbooks can save you tons of time by allowing multiple people to edit and view the same workbook, at the same time, as long as they all have access to the save network folder.

Sharing Workbooks

With a shared workbook, multiple users can view and edit your workbook simultaneously. And, in the event that two users edit the same cell at the same time, when you save your changes, you will be prompted with a dialog box that allows you to choose which changes you want to keep.

However, with a shared workbook, you may notice some reduced functionality. For instance, you won’t be able to insert or delete cells, pictures or other objects. Additionally, you won’t be able to change protection or password settings, use Automatic Subtotals, or create data tables or lists.

Keeping this in mind, it’s a good idea to set up all of the advanced formatting, tables, lists, etc… that you want BEFORE you designate the Excel 2007 file as a “shared” file.

Sharing a Workbook

     
  1. Set up your workbook with the structure and formatting you need
     
  2.  
  3.  Click the Share Workbook command in the Changes group on the Review tab
     

Figure 15. Share Workbook - Editing Tab Dialog Box

    3.       Check Allow changes by more than one user at the same time. This also allows workbook merging on the Editing tab

    4.       Switch to the Advanced tab

 

Figure 16. Share Workbook - Advanced Tab Dialog Box

 

    5.       Set your Sharing options

    6.       Click OK

 

Figure 17. Sharing Save Dialog Box

    7.       Click OK

Protecting Shared Workbooks

Once you share a workbook, all users that have access to the location in which the file is saved can open the file and make changes.

To protect a shared workbook, you can set a password on the shared workbook that needs to be entered to allow only those with the password to access the shared file.

     
  1. Make sure the workbook you are protecting is NOT currently shared
     
  2.  
  3.  Click the Protect and Share Workbook command in the Changes group on the Review tab
     

Figure 18. Protect Shared Workbook Dialog Box

    3.       Check Sharing with track changes (if desired)

    4.       Enter a Password (if desired)

    5.       Click OK

    6.       Enter your password again

    7.       Click OK

    8.       Click OK

 

Figure 19. Confirm Password Dialog Box

Stop Sharing/Remove Users Options

Occasionally, you may need to perform maintenance on a shared file or you may just need to stop sharing the workbook, altogether.

Keep in mind that when you stop sharing the workbook, you lose the workbook history. However, you can print the History worksheet before you stop sharing so you always have a record of edits that were made while the workbook was in share mode. And, before you stop sharing the workbook, make sure that all other users have saved their work.

Printing the History Worksheet

     
  1. Click the Track Changes drop-down arrow in the Changes group on the Review tab
     
  2.  
  3. Choose Highlight Changes
     

Figure 20. Highlight Changes Dialog Box

    3.       Uncheck all boxes under Highlight which changes (to print everything)

    4.       Check List changes on a new sheet

    5.       Click OK

    6.       Print the new worksheet that is created labeled “History”

Removing a User from a Workbook

     
  1. Click the Share Workbook command in the Changes group on the Review tab
     
  2.  
  3. From the Editing tab, you will see a list of the users who currently have the workbook open
     
  4.  
  5. Select the name of the user you want to disconnect
     
  6.  
  7. Click Remove User
     

Stop sharing a workbook

     
  1. Click the Share Workbook command in the Changes group on the Review tab
     
  2.  
  3. Uncheck Allow changes by more than one user at the same time. This also allows workbook merging on the Editing tab
     
  4.  
  5. Click OK