Use Protected Save and Sharing Options

As well as (or instead) of protecting cell contents within a workbook, you can also use save options to require a password to be open or edit the file which is one of the subjects covered in this topic. Alternatively you can also save your Excel workbooks in a read-only (non-editable) format such as a PDF which is standard functionality in Excel 2007 onwards.

Whilst you should follow the advice of frequently saving files, this can sometimes lead to frustration when changes are saved which you later want to back out though the file has become overwritten. Whilst many corporate networks as well as later versions of Windows keeps version history (right click on a file and select ‘Previous Versions’), Excel does have an option to allow you to automatically create a backup of a file each time it is opened which you can then revert back to if needed.

This topic also looks at workbook sharing which allows multiple people to have the file open and make edits at the same time.This is a common requirement, particularly when Excel is being used for data collection – everyone updating the same file means there is only one version of the truth – so the feature is very useful. However there are some important limitations and considerations with the use of shared workbooks which it is important to bear in mind.

If you want to create a read-only, non-editable, easily printable copy of your data to be presented in a standardised format, then converting your Excel workbook into a PDF format is often a good option. Excel 2007 and 2010 have a new save feature which allows you to save part or all of your Excel workbooks as a PDF file which can then be viewed with a standard PDF reader such as Adobe™ Reader.

To save as a PDF, In Excel 2007 click on the Ribbon and then Save As -> PDF or XPS. (In Excel 2010 you click on File -> Save & Send -> Create PDF/XPS Document). Alternatively when saving your file as normal select ‘PDF’ as the ‘Save as Type’ option from the drop-down menu.

In the save dialog box that then appears (screenshot below) you can name the new PDF file and click on ‘Publish’ – this will save the current worksheet in PDF format (taking into account page breaks – shown by dotted lines – that apply on the sheet).

If you want to save a different range to this as a PDF then before saving, click on ‘Options’ and you will be presented with the below dialog box where you can change the range that will be saved as a PDF. For example select ‘Entire workbook’ to convert all sheets in the workbook into PDF format, or ‘Selection’ to publish only the cells currently highlighted. If you only want to publish some worksheets then ensure you have selected these (you can select multiple tabs by holding down Ctrl or Shift and then selecting the ones you want) before saving as PDF and then select this option.

If you have print areas set up on the sheets then these will be taken into account by default – if you’d rather print the whole region of every sheet despite the print area set then click on ‘Ignore Print Areas.’

The important thing to be aware of with this (and also when printing normally) is your page layout – you often need to explicitly set page layout options in the way you want or your data will print out in quite a strange format. The Page layout options are in the ‘Page Layout’ tab of the ribbon. Here you can set sheets to landscape or portrait and then ensure that the sheets print to no more than one page wide/tall by setting the ‘width’ or ‘height’ parameters to ‘1 page’. Where you want to apply these settings to multiple sheets (perhaps all of those you intend to print) then select multiple tabs (by holding down Ctrl or Shift) before applying page layout settings – any settings will then apply to all sheets selected.

Be careful of having multiple sheets selected when not intended – any changes you make to cells (e.g. typing into them, copying and pasting) will apply to all worksheets selected! Therefore only select multiple sheets when you are making changes across multiple sheets and consciously de-select to having only one active sheet afterwards.

Saving Excel files with a password is a useful but under-used feature of Excel, partly perhaps because the save with password options are somewhat hidden, and perhaps partly because people are nervous about password protecting the workbooks if the password is subsequently forgotten!

This is a valid concern but instead of having a password required to open a file you can instead allow it to be opened freely but require a password only to edit it – this will ensure that the original file remains intact and people are never overwriting the original ‘master’ file with their changes.

To save your file with password options, in the ‘Save As’ dialog box (shown below) click on ‘Tool’s and then ‘General Options’

You will then be presented with the dialog box below – if you wish to require users to enter a password in order to open the file then type a password in the ‘Password to open’ text box; if instead you want them to be able to open the file but force them to save it as a new file each time then type a password in the ‘Password to modify’ text box instead.

Selecting the ‘Always create backup’ text box will create a backup copy of your file each time it is opened, in the same folder location as the file, with a different file extension. This is potentially useful for ensuring that if any unintended changes are made to the workbook and saved, an earlier version can be recovered. This option can be selected either with or without password protection and is a very useful feature, not least because it doesn’t create multiple backups (only the last saved version will be the back-up).

Clicking on ‘Read-only recommended’ will prompt the user each time the file is opened to ask if they want to open it as a read-only version.

Sharing a workbook allows multiple people to have it open and edit it simultaneously so is useful for situations where different people are feeding data into a spreadsheet over the same time period.

To enable sharing, click on ‘Share Workbook’ under the review tab of the ribbon and then in the dialog box that appears (screen shot below) tick the box to allow changes by more than one user at the same time. The workbook will then be saved as a shared workbook – denoted by the filename being appended by [Shared]’ in the title bar.

The sharing workbook functionality is so powerful because it allows you to see and accept/reject all the changes that other people have made at any point in time. By default the change history is kept for 30 days, and people are required to save the file in order to be able to then see the latest set of changes that have been made to it. The ‘Advanced’ tab of the Share Workbook dialog box allows you to change some of the settings. For example you can increase or decrease the length of time change history is kept for (although bear in mind this impacts on the file size) and you can determine whether you want changes to be visible to users at given time intervals rather than only when they save the file.

To see a list of changes that have been made to a Shared workbook at any point on time click on ‘Track Changes’ under the Review tab and then ‘Highlight changes’. This brings up the below dialog box under you can specify which changes you want to see – e.g. only those since the last review (or since a certain date), those made by a specific user and/or those made within a particular range of cells within the workbook. I find it most useful to list the changes on a new sheet rather than highlighting them on the screen – this gives you a list of values that were changed, who changed them and when they were changed.

If you then wanted to reverse any of the changes, you can go through an approval/rejection process for all the changes made. To do this, click on ‘Accept/Reject Changes’ under the ‘Track Changes’ drop-down in the Review tab. You will then be presented with a similar dialog box to the ‘Highlight Changes’ one to be more specific about which changes you want to go through.

For those set of changes, you will then be able to accept or reject each one. Where there is a conflict (e.g. the same cell has been input into by more than one user) you will have the opportunity to select which value you want to enforce.

Note that by default, in unprotected workbooks, everyone who can access the workbook is able to view and accept/reject anyone else’s changes. In practice this should probably be restricted to a subset of people (or perhaps just one person), which can be enforced if required by using the ‘Protect and Share workbook’ feature instead. This saves the workbook as a shared file (so multiple people can edit it at the same time) and enforces the tracking of changes made to the worksheet. As the owner of a protected shared workbook you then have specific rights such as removing users from the shared workbook and resolving conflicting changes.

Whilst all this functionality probably sounds quite amazing, the reality of it is that it far from being the slickest solution for people to collaborate or feed into a document for a number of reasons including:

• A lot of functionality in Excel available on a normal workbook becomes unavailable when the workbook is shared. Some of these features cannot be changed but can be used provided they have already been set up before the workbook is shared (if you want to use these features in a shared workbook then un-share the workbook first by un-ticking the box in the Share Workbook dialog box). Examples of restricted features include:
• Data Validation
• Tables
• Conditional Formatting
• Pivot Tables
• Managing the change history is onerous – there is no work flow or ability to specify approvers (for this you need to use something like Microsoft SharePoint). Whilst it is useful to be able to see changes that have been made, I doubt the approval/rejection functionality is widely used.
• Unlike database or server applications (such as Microsoft Access, SQL server and SharePoint), Excel isn’t really designed for concurrent use. As a result shared workbooks often crash or become corrupted and can become increasingly difficult to maintain over time.

In summary, the shared workbook functionality should only really be used as a temporary solution for data collection (rather than reporting workbooks which are more likely to require use of some of the restricted features), where people need to input data at the same time and you don’t require sophisticated tracking/approval mechanisms. As a longer term and more robust solution, if you any of the more mainstream applications/technologies available to you then you should strongly consider using those instead.