|
91- Protecting a Worksheet
1)
Select the worksheet that you want to protect.
2)
To unlock any cells or ranges that you want other
users to be able to change, do the following:
·
Select each cell or range that you want to unlock.
·
On the Home tab, in the Cells group, click Format,
and then click Format Cells.
·
On the Protection tab, clear the Locked check box, and then click OK.
3)
To hide any formulas that you don’t want to be
visible, do the following:
·
In the worksheet, select the cells that contain the
formulas that you want to hide.
·
On the Home tab, in the Cells group,
click Format, and then click Format Cells.
·
On the Protection tab, select the Hidden check box, and then click OK.
4)
To unlock any graphic objects (such as pictures, clip
art, shapes, or Smart Art graphics) that you want users to be able to change,
do the following:
·
Hold down CTRL and click each graphic object that you
want to unlock.
5)
This displays the Picture Tools or Drawing
Tools, adding the Format tab.
6)
You can also use the Go To command to quickly
select all of the graphic objects in a worksheet. On the Home tab, in
the Editing group, click Find & Select, and then click Go
To. Click Special, and then click Objects.
·
On the Format tab, in the Size group,
click the Dialog Box Launcher
·
On the Properties tab, clear the Locked check box, and if present, clear the Lock text check box.
7)
You don’t need to unlock buttons or controls for users
to be able to click and use them. You can unlock embedded charts, text boxes,
and other objects created with the drawing tools that you want users to be able
to modify.
8)
On the Review tab, in the Changes group,
click Protect Sheet.
9)
In the Allow all users of this worksheet to list, select the elements that you want users to be able to change.
In the Password to unprotect sheet box, type a password for the
sheet, click OK, and then retype the password to confirm it.
Protect workbook
elements:
1.
On the Review tab, in the Changes group,
click Protect Workbook.
2.
Under Protect workbook for, do any of the
following:
·
To protect the structure of a workbook, select the Structure check box.
·
To keep workbook windows in the same size and position each time the
workbook is opened, select the Windows check box.
3.
To prevent other users from removing workbook
protection, in the Password (optional) box, type a password, click OK,
and then retype the password to confirm it.
92- Sharing Worksheets
Share a
workbook:
1)
Create a new workbook and enter any data that you want
to provide, or open an existing workbook that you want to make available for
multiuser editing.
2)
On the Review tab, in the Changes group,
click Share Workbook.
3)
On the Editing tab, select the Allow changes
by more than one user at the same time. This also allows workbook merging check box.
4)
On the Advanced tab, select the options that
you want to use for tracking and updating changes, and then click OK.
5)
Do one of the following:
·
If this is a new workbook, type a name in the File
name box.
·
If this is an existing workbook, click OK to
save the workbook, click Microsoft Office Button
6)
In the Save in box, select a network location
that is accessible to the intended users, and then click Save.
7)
If the workbook contains links to other workbooks or
documents, verify the links and update any links that are broken, and then
click Save
After you open a shared workbook, you can enter and
change data as you do in a regular workbook.
Open the shared workbook.
1)
Click Microsoft Office Button
2)
In the Look in box, locate the network location where the shared
workbook is stored, and then click the shared workbook.
3)
Click Open.
4)
Click the Microsoft Office Button
5)
In the Popular category, under Personalize your copy of Office,
in the User Name box, enter the user name that you want to use to
identify your work in the shared workbook, and then click OK.
6)
Enter and edit data on the worksheets as usual.
Before you stop sharing the workbook, make sure that
all other users have completed their work. Any unsaved changes will be lost.
Because the change history. will also be deleted, you may want to start by
printing the History worksheet. or by copying it to another workbook.
To keep a copy of the change history information, do
the following:
1)
On the Review tab, in the Changes group,
click Track Changes, and then click Highlight Changes.
2)
In the When list, select All.
3)
Clear the Who and Where check boxes.
4)
Select the List changes on a new sheet check
box, and then click OK.
5)
Do one or both of the following:
§
To print the History worksheet, click Microsoft
Office Button
§
To copy the history to another workbook, select the
cells that you want to copy, click Copy
6)
You may also want to save or print the current version
of the workbook, because this history data might not apply to later versions of
the workbook. For example, cell locations, including row numbers, in the copied
history may no longer be current.
Ø
In the shared workbook, on the Review tab, in the Changes group, click Share Workbook.
Ø
On the Editing tab, make sure that you are the only person listed
in the Who has this workbook open now list.
Ø
Clear the Allow changes by more than one user at the same time. This
also allows workbook merging check box.
If this check
box is not available, you must first unprotect the workbook. To remove shared
workbook protection, do the following:
1)
Click OK to close the Share Workbook dialog box.
2)
On the Review tab, in the Changes group,
click Unprotect Shared Workbook.
3)
If you are prompted, enter the password,
and then click OK.
4)
On the Review tab, in the Changes group,
click Share Workbook.
5)
On the Editing tab, clear the Allow changes
by more than one user at the same time. This also allows workbook merging check box.
Ø
When you are prompted about the effects on other
users, click Yes.
93- Track Changes
Are you looking for a way to track changes made to an Excel
spreadsheet. In an earlier post, I wrote how you can protect an Excel
workbook using a password, but there are many cases where you have to distribute a file and allow
others to edit it also.
In these types of cases, it’s handy to be able to track any
changes made to the original Excel spreadsheet. Tracking changes in Excel is
fairly straight-forward and here’s how you do it.
First, open Excel and click on the Review ribbon. At the far right, you should see an option called “Track
Changes”.
Click the button and choose Hightlight Changes.
You’ll get a dialog box where you now need to check off the option “Track
change while editing. This also shares your workbook”.
You have several options here for When, Who, and Where. For When, All means every change will be highlighted. You also have the
option of highlighting changes from the last time you saved the document, since
a specific date, or changes that have not yet been reviewed.
If you select Who, you can choose to track changes made by
anyone or changes made by everyone except you. The Where filter allows you to
track changes only for a specific portion of the spreadsheet. Just click the
button at the right and select the range of cells you want to keep track of.
Finally, you can un-check the Highlight changes on
screen option if you do not want others to know you are tracking
changes. By default, once you start tracking and this option is selected, any
changed cell will show a small arrow at the top left to indicate it was
changed.
If you deselect the Highlight changes on screen option, that
little blue arrow won’t show up. Also, if you click on a changed cell (with
Highlight changes on screen turned on), you’ll get a little popup window
showing what the value was changed from and to and at what time.
So what you can do, for example, is hide the changes on the
screen by not checking the box, then send out your file to everyone who has to
make changes to it, and when you get it back, just go to Track Changes and
Highlight Changes and recheck the box!
After this, you’ll need to either approve or reject the
changes. So even though someone has made a change, it does not necessarily have
to be permanent. You can review all the changes and pick and choose which ones
you want to keep or want to discard.
Simply click on Track Changes and choose Accept/Reject Changes. Select the options to choose which changes you
want to accept or reject. So whatever options you selected when turning on
change tracking the first time, choose those again.
Click OK and Excel will begin to show you each change that
was made and give you the option to Accept or Reject.
After you have finished reviewing all of the changes you can
click on Track Changes again and Highlight Changes and you’ll notice that the
box for “List changes on a new sheet” is now enabled. For some
reason this option is not enabled the first time you turn on change tracking in
Excel.
Check the box and click OK and now all of the changes that
were performed will be shown in a new Excel sheet:
That’s it! Now you can track any changes made to your Excel
spreadsheet easily by using this built in feature.
Back To Top Back To Excel Index Page (Open Exercise file)
|
