Problem solve Get help with specific problems with your technologies, process and projects.

Sharing Excel files safely

Sharing files is a very common practice, but you may be doing it in a non-secure way. This tip will explain how to safely share an Excel 2007 workbook and how to keep it safe, too.

A lot of people don't realize it, but Microsoft built Office 2003 and Office 2007 with collaboration in mind. Both versions allow you to share an Excel workbook with other users so multiple users can make modifications to a spreadsheet. In this article, I will show you how to share an Excel 2007 workbook. More importantly, I will discuss how you can keep a shared workbook secure.

Before you even begin sharing a workbook, the first thing you need to do is protect the spreadsheet you've created. It is important to lock the cells so that other users are not able to modify them.

You can protect a range of cells by selecting them, right clicking on them and selecting the Format Cells command from the resulting shortcut menu. When you do, Windows will display the Custom Lists properties sheet. Go to the Protection tab and either lock or unlock the protected cells. Keep in mind that locking a cell has no effect until you protect the sheet.

Securing Excel

To protect a worksheet, click on the Review menu and then click the Protect Sheet icon. When you do, Windows will display the Protect Sheet dialog box.

Verify that the Protect Worksheet and Contents of Locked Cells check box is selected. Then enter a password to use in order to unprotect the sheet. Keep in mind that this password is worksheet specific.

Before you click OK, read through the other protection options on this dialog box. You can control what users will and will not be allowed to do to the spreadsheet just by selecting a check box. By default, users are allowed to select both locked and unlocked cells in a protected spreadsheet, but that's about it. It is up to you to decide whether you want to allow your users to format or create or delete cells, rows or columns. You can also authorize or forbid various other types of edits.

Now that you have implemented some basic protection, it's time to share the workbook. To do so, go to Excel's Review menu and click the Share Workbook icon. Excel will display the Share Workbook dialog box. To share the workbook, select the Allow Changes by More Than One User at a Time check box, found on the Editing tab, as shown in Figure A.


Figure A
To share the workbook, select the Allow Changes by More Than One User at a Time check box.

Before you click the OK key, go to the Advanced tab, shown in Figure B. As you can see in the figure, the Advanced tab asks you if you want to track changes to the file. If security is a concern to you, then I highly recommend tracking changes to the file. By doing so, you create an audit trail that allows you to see exactly what modifications were made by whom.


Figure B
The Advanced tab contains settings related to tracking document changes.

The next section is the Update Changes section. By default, updates occur when the file is saved, but you can configure changes to be updated on a periodic basis.

The fact that multiple users can work on the document simultaneously means that there is a potential for conflicts to occur. Two users could potentially save contradictory changes at the same time. As such, Excel makes you decide what should happen when contradictory changes occur. By default, Excel will ask you which change should be kept, but you have the option of configuring Excel so that the most recently saved change is authoritative. From a security perspective, I recommend going with the default setting because then you can decide for yourself which change to keep and which to discard.

The last section shown in Figure B is the Include in Personal View section. By default, each user is allowed to maintain his own separate printer settings and filtered view settings. This isn't usually a threat to security since a user cannot change another user's print settings or filter settings. Therefore, I recommend going with the default settings in this section.

Click OK, and the worksheet will be shared. Keep in mind, though, that you have only granted document-level sharing. It is still up to you to place the shared worksheet into a network location where it will be accessible to all of the necessary users. I strongly recommend setting some NTFS permissions so that only the intended users are allowed to access the document.

About the author:  Brien M. Posey, MCSE, is a Microsoft Most Valuable Professional for his work with Windows 2000 Server and IIS. He has served as CIO for a nationwide chain of hospitals and was once in charge of IT security for Fort Knox. As a freelance technical writer, he has written for Microsoft, TechTarget, CNET, ZDNet, MSD2D, Relevant Technologies and other technology companies. You can visit his personal Web site at www.brienposey.com.

This was last published in February 2007

Dig Deeper on Microsoft Office Suite

PRO+

Content

Find more PRO+ content and other member only offers, here.

Join the conversation

4 comments

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

But protect unprotect command show run-time error 1004 while in macro and not worked in shared workbook. Have you any solution for this?
Cancel
Sharing safely is the correct way.
Cancel
This is an older article and users should be cautious when password protecting documents in older versions of Excel. Some versions make you take extra steps to encrypt and they are more susceptible to brute force attacks. The advice on protecting cells and setting up an audit trail seems very useful to this day as users have been slow to adopt Microsoft's cloud-based tools thus far.
Cancel
Um.. what's SharePoint is for then?
Cancel

-ADS BY GOOGLE

SearchVirtualDesktop

SearchWindowsServer

SearchExchange

Close