Excel 2007: Collaboration Made Easy
When I think of the new Excel 2007 I think of Carlyle and his quest for it – a short film promoting the new Microsoft Office.
It’s only a couple of minutes long but really funny. See The Quest and four other short films here under The Films tab.
What I like most about this film is that I can relate to Carlyle. I’ve been in his shoes, not in a big conference room in front of my superiors, but I have been in those desperate situations where the one piece of information that I need right now is nowhere to be found. I’m sure you’ve had the same feeling too. Like all the crucial, indispensible information and data has deserted you on purpose.
The new Microsoft Office, and especially the new Excel 2007, has made some major improvements in this area. It’s easier to organize, analyze and find your information when you need it.
Plus there’s so much more you can do! We’ve filled a whole training course with it – over 12 hours of nothing but Excel 2007!
Today I’ll focus on just a few things that you can do in Excel that deal with collaboration:
- How to share an Excel workbook with others
- How to restrict access in a shared workbook
- How to track changes in a shared workbook
- How to remove a workbook from shared use
How to Share an Excel Workbook
A shared workbook allows multiple users on a network to view and make changes to the workbook at the same time. Every time a change is made and saved it is seen by the other users who have access to the workbook. This is very useful for any collaborative projects where multiple users need access to the same information and want to be up-to-date on the progress of the project.
Before you start sharing your workbook, note that not all features will work in a shared workbook. If you want to add any of the items below you should do so before you start sharing your workbook, and remember that you cannot change these features while the workbook is being shared:
- conditional formats
- data validation
- data tables
- pivot table reports
- merged cells
1. To start sharing a workbook, click on the Review tab, and locate the icon that says Share Workbook under Changes:
2. In the Share Workbook dialogue box that comes up, on the Editing tab select Allow changes by more than one user at the same time. This also allows workbook merging.
3. On the Advanced tab, select the options that you want for tracking and updating changes, conflicting changes between users, and personal view. Then click OK:
4. If this is a new workbook, type in the name of the file in the box that comes up and select a network location that is accessible to the users you want to give access to and click Save.
If you have already named this workbook, click on the Microsoft Office Button, click Save As, and select a network location that is accessible to the users you want to give access to and click Save.
At this point all users who have access to the network location that you specified have full access to the shared workbook that you just created. If you want to create a workbook with restricted access, keep reading!
How to Restrict Access in a Shared Workbook
1. If you want to create a shared workbook that only select users can access click on the Review tab, and under Changes click on Protect Shared Workbook:
2. In the dialogue box select Sharing with track changes and if you want enter a password, and click OK:
3. If you entered a password you will be asked to repeat it, and your worksheet is now shared!
If you want to change any of the Advanced options for sharing your workbook, click on Share Workbook on the Review tab and make any necessary changes. Notice that some options have been grayed out and cannot be changed in a workbook that has restricted access:
How to Track Changes in a Shared Workbook
1. Once your Excel workbook is being shared you can track all changed being made to it by others. To track all changes, click on the Review tab and select Track Changes:
2. You will have two options: Highlight Changes and Accept/Reject Changes. When you click on the first option you will get to customize how changes will be tracked, when, where in the worksheet and by whom:
3. When you select the Accept/Reject Changes option you can specify which changes you want to be able to review before you accept or reject them:
How to Remove a Workbook from Shared Use
When your team is finished with a project and you want to stop sharing a workbook you should first make sure that all changes have been added to the workbook.
For your records you might want to save a copy of the history worksheet, which is actually a separate workbook that lists all changes that were made to the shared workbook. This information might come in handy since it not only lists the changes, but also the names of the persons who made them, when and where the changes were made, which data was deleted, and how you resolved any conflicts.
1. To save a copy of the history worksheet, click on the Review tab, click Track Changes, and then click Highlight Changes:
2. In the dialogue box select All in the When list and clear the Who and Where check boxes. Select the last box List changes on a new sheet, and click OK:
3. When the history worksheet with all changes comes up either copy and save the worksheet or print it out.
4. Now you’re ready to stop sharing the workbook. Start by clicking on Share Workbook, located in the Review tab, just like you did when you were setting up the workbook to start sharing:
5. In the dialogue box that comes up, on the Editing tab make sure that you’re the only person listed under the Who has this workbook open now list.
Uncheck the box that says: Allow changes by more than one user at the same time. This also allows workbook merging. Click OK and then Yes to the warning about removing the workbook from shared use:
And that’s it, you’re done! Your workbook is no longer being shared.
Microsoft Excel 2007 Training – Available Now!
Take advantage of everything the new Excel has to offer with Train Signal’s Microsoft Excel 2007 Training – Available Now!
Our complete Excel 2007 training – beginner to advanced – offers 12+ hours of instructor led video, instructor’s notes, and plenty of Excel exercises to put your new knowledge into practice.
Some of the topics in the training include:
- Formulas and Calculations
- Charts, Templates, and Pivot Tables
- Basic and Advanced Formatting
- Multiple Workbooks and Collaboration
- Using Macros and more!
Our Excel 2007 training also covers the Using Microsoft Office Excel 2007 Certification Exam (77-602 MCAS). So take advantage of all the new features and see how much more you can accomplish with your data.