Latest Stories

Collaborate on Spreadsheets

How to share Excel files with others on your network.

BY PAUL GOLDWATER AND LOIS S. MAHONEY

Related

TOPICS

Uncategorized Article

ould you like to be able to gather
spreadsheet data from people throughout your
organization, automatically producing detailed
reports or summaries that then can be shared with
colleagues? Or would you like a team of users to be
able to work on a spreadsheet report simultaneously,
sharing information and coordinating the results?

If so, Microsoft’s Excel has just the
thing for you—a built-in function called
Share Workbook . It
makes no difference whether the team
members are in offices down the hall or on
other continents. The only requirement:
They must be on a shared network. Follow
along and we’ll show you how to create a
workbook that as many as 256 users can
share at the same time.

Click on the Editing
tab and then place a check next
to Allow changes by more than
one user at the same time .
If others are using the file, their
names and their log-on times will appear
under Who has this workbook open
now. Note that as the
originator of the Share Workbook function, you
have the power to remove a user by
clicking on the name and then on the
Remove User button.
Next click on the Advanced
tab (see exhibit 2 ,
below).

Exhibit 1

This box offers several options. The first,
under Track changes , lets you
keep a history of all changes made to the workbook
for up to 10,000 days. Be sure to enter a large
enough number to cover the period you want because
Excel permanently erases the change history for
any days beyond that deadline, including discarded
changes.

The second option lets you fix a
common frequency for automatically seeing and
updating changes other users made—from as short as
5 minutes to as long as 1,440 minutes. If you
choose to automatically see other users’ changes,
then you have two more options: Save my
changes and see others’ changes (in
which case Excel will save your changes and will
incorporate those that other users made) or
Just see other users’ changes
(Excel will save others’ changes in your
workbook but not your changes). Excel won’t
incorporate your changes in the shared workbook
until you manually save (Ctrl+S) the workbook.

However, be aware that users are allowed to
create their own settings for the second option,
so we suggest your group agrees on a common
policy. If you let different users change the same
cells in your workbook, you are bound to run into
conflict.

The third option, under
Conflicting changes between
users , allows you to choose
between Ask me which changes win
or The changes being
saved win . In order to avoid
error, we suggest you choose Ask
me which changes win . With
this option, when two or more users update
the same cells and then save the workbook,
the Resolve Conflicts
dialog box will appear (see
exhibit 3 , below). Each user can
then choose to accept his or her changes
or the other user’s changes by clicking on
the appropriate button.

The fourth
option allows participants to set their
own personal view of the worksheet. Each
person using the shared workbook is
allowed to set his or her own printer or
filter settings. Thus, each person can
choose to have their information printed
in landscape and with grid lines without
affecting the printing options of any
other user. Similarly, each user can set
their own filter settings (a quick and
easy way to find a subset of data that
an individual person or department may
want to work with), and this setting
also will not affect what any other user
will see.

Exhibit 2

When
you’ve finished your selections, click on
OK , and the
Save As dialog box
appears to let you know you need to save
the workbook on a network drive where
others can gain access to it. Once you
save it, the bracketed word
[Shared] appears in the
file’s title bar (see exhibit 4 ,
below) whenever anyone opens the workbook.

Exhibit 4

Exhibit 3

If you decide you no longer want the workbook
to be shared, uncheck Allow changes by
more than one user at the same time on
the Editing tab of the
Share Workbook dialog box (
exhibit 1 ). If anyone happens to be using
the shared workbook when you deselect this option,
they automatically will lose their changes. In
addition, by turning off the shared workbook,
Excel automatically erases the history.

TRACK CHANGES
Revisions by any
participant can be marked and tracked. You also
can mark changes from a certain time and in a
certain area of a worksheet. Changes are color
coded by user, and when you rest the pointer over
a colored cell, the cell displays the name of the
author, the time of the change and the original
and changed value of the cell. To enable tracking
of changes, click on Tools ,
Track Changes and
Highlight Changes (see
exhibit 5 , below).

To set
the filters for the changes you want to track,
first clear the checks next to When
, Who and Where
. If you had selected a time period in
exhibit 2 for saving history, you now
will be able to see that history. Now click on
OK .

As participants
make future changes to the workbook, a
small colored triangle will appear in the
corner of the changed cell (see B11 in
exhibit 5 ). When a user moves the
cursor over this cell, a small flag will
indicate the change, who made it and when.

Important : For Track
Changes to be effective, you
must save your workbook with these
settings on your network drive before
you allow other users access.

LIMITING USERS
Not only
can Share Workbook
determine who sees and edits a
spreadsheet, it also can selectively
hide specific worksheets. Protection for
a shared workbook is applied in
layers—and only by the person initially
creating the worksheet or someone who
has the person’s password: First you
protect the worksheet, then the
workbook, and last you designate the
capability for sharing and change
tracking. All these actions must be
taken before users open the workbook for
sharing.

Now click on
Tools , General Options
, opening the Save Options
dialog box ( exhibit 8 ) where
you can enter a password.

Then click on
OK , evoking a
Confirm Password dialog
box, which asks you to reenter your
password and click on OK
. If you select two passwords,
you must confirm twice. Finally, click on
Save in the
Save As dialog box. The
same password can be used to open and
modify a spreadsheet.

If you don’t
want users to see specific worksheets in
the shared workbook, click on the
worksheet you want to hide and then on
Format ,
Sheet and
Hide . Repeat these
steps for each worksheet you want to
hide.

Exhibit 8

If you check Always create backup
, Excel will automatically create a
backup of the spreadsheet exactly as it appeared
when opened, naming it “Backup of XXX.” This
backup is saved when you save the spreadsheet with
new changes incorporated.

If you check the
Read-only recommended box,
Excel will not automatically override the password
to modify the worksheet. When a user enters the
correct password to modify, a second user form
comes up asking how the user wants to open the
spreadsheet, as read-only or
modify . If no password is used
but the read-only box is checked, the user can
open up the spreadsheet, view it and make changes
but is not allowed to save it under the original
name.

After you hide worksheets, you must
protect the workbook to keep other users from
redisplaying the hidden worksheets. To do that,
click on Tools ,
Protection and Protect
Workbook , bringing up the
Protect Workbook dialog box
(see exhibit 9 , at right). Check the
Structure box, enter your
password and click on OK . When a
Confirm Password dialog box
appears, reenter your password.

Exhibit 9

Exhibit 10

The simplest way to restrict participants’
ability to edit worksheets is to limit them to
their own worksheets—that is, have a separate
worksheet for each person or group. To allow only
authorized users to change a specific worksheet,
you first need to go back and unprotect the
workbook. Then click on Tools, Protection
and Allow Users to Edit Ranges
, evoking the dialog box by that name
(see exhibit 10 ).

Now click on
New , bringing up the
New Range dialog box (see
exhibit 11 ).

Exhibit 11

In the
Refers to cells box,
type a range of cells the user will be
allowed to change. Then enter the
password in Range password
and click on OK
, and you’ll be asked to
confirm the password, after which you’ll
be taken back to the Allow Users
to Edit Range ( exhibit
10 ). Click on Protect
Sheet , evoking the
Protect Sheet box
(see exhibit 12 ).

Exhibit 12

Enter your password, click on OK
and you’ll be asked to reenter your
password. When the authorized users want to change
the information in the allowable cells, they
simply double-click on those cells, bringing up
the Unlock Range box (see
exhibit 13 ), and enter the password they
were given.

Because shared history is lost
if a workbook is unshared, you may want to
restrict the ability to unshare the workbook. To
do this, click on Tools, Protection,
Protect and Share Workbook , bringing
up the Protect Shared Workbook
dialog box (see exhibit 14 ,
below). Check Sharing with track changes
and enter your password.

Exhibit 13

As you
can see, Share Workbook
is a very powerful and useful
program. However, don’t be put off by
its seeming complexity. Yes, there are a
lot of steps to setting up the workbook,
but after you go through the exercise
once, you’ll immediately see that they
are clearly defined and quite intuitive.

In today’s multiple-enterprise
environments, a fast and efficient way
to collaborate is a must, and the
Share Workbook
feature is a perfect tool for
that.

Exhibit 14

PAUL GOLDWATER, PhD, CMA, is an associate
professor at the University of Central Florida’s
School of Accounting, Orlando. His e-mail address
is
paul.goldwater@bus.ucf.edu . LOIS S.
MAHONEY, CPA, PhD, CMA, is an assistant professor
at the university. Her e-mail address is lois.mahoney@bus.ucf.edu
.

The results of the 2016 presidential election are likely to have a big impact on federal tax policy in the coming years. Eddie Adkins, CPA, a partner in the Washington National Tax Office at Grant Thornton, discusses what parts of the ACA might survive the repeal of most of the law.