PROTECTING SHEETS AND LINKING IN EXCEL

Excel worksheets may represent many hours of work, and yet it can all be wiped out by an unknowing user. Locking worksheets to prevent changes can prevent this loss. There may also be times when you want users to be able to access parts of a worksheet, but not all. Both of these can be accomplished by protecting the worksheet.
PROTECTING THE WORKSHEET:
1.	Click on the sheet tab of the worksheet to be protected. This makes the sheet the active sheet.
2.	Click on Tools, Protection, Protect sheet. There are 3 options to be selected: contents, objects, and scenarios. Clicking contents prevents changes to cells on worksheets or to data and other items in charts, and to prevent viewing of hidden rows, columns, and formulas. Clicking Objects prevents changes to graphic objects on worksheets or charts,. Scenarios prevents changes to the definitions of scenarios on a worksheet. You can also add a password so that users can not remove the protection. If you choose to do so, enter the password, then enter it again at the confirmation box. If you forget your password, you will not be able to open the worksheet. While protection is on, many items on the toolbars are unavailable.
3.	Click on OK.
REMOVING THE PROTECTION:
1.	Click on Tools, Protection, Unprotect Sheet. If you entered a password when you applied the protection, you will be prompted for that password.
REMOVING CELLS FROM THE PROTECTION If you would like users to be able to access certain cells, but not all, you must remove those cells from the protection BEFORE the protection is turned on.
1.	Select the cells you DON'T want protected.
2.	Click on Format, Cells, Protection. Click on the Locked box to deselect it.
3.	Protect the worksheet following the instructions above. All cells of the worksheet EXCEPT those that you unlocked will be protected. Users will be able to enter and edit information in those unlocked cells. Helpful tip: Shade the unlocked cells for easy recognition.
PROTECTING ENTIRE WORKBOOKS You can also protect the entire workbook, including all sheets and the window size.
1.	Click on Tools, Protection, Workbook. Select Structure to protect the structure of a workbook so that worksheets in the workbook can't be moved, deleted, hidden, unhidden, or renamed and new worksheets can't be inserted. Select Windows to use windows of the same size and position each time the workbook is opened.
2.	Enter a password if desired.
3.	Click on OK. Cells can be omitted from the Workbook protection by using the Format, Cells, Protection option listed above.
Other options: If you do not want users to be able to open a workbook at all or to open it as a read-only file, click on options under File, Save as, and set a password.
COPYING, EMBEDDING AND LINKING Copying, embedding and linking all provide means to get data from one location to another. All three methods use a source (the original data) and a target (where the data ends up), however, there is a difference in their uses. Copying is a one-shot deal: to get a copy of the information from A to B, such as from one worksheet to another or from Excel to Word. Copying should be used if you expect no changes in the data. Embedding and Linking are available only in programs that support object linking and embedding (OLE). All of the programs in Microsoft Office support OLE. In Embedding, the data (a chart, portion of a worksheet, etc.) is copied from the source to the target. Additionally, the target retains the original application program aspects, making editing easy. For instance, if you embed an Excel chart into a Word document, you can double-click on the chart in Word and you will be returned to the chart in Excel, where you can make changes. Linking allows changes from a source to be reflected in linked (target) cells. For instance, if you have a worksheet detailing the amount spent on office supplies as the source, the target cell could reflect only the grand total spent on office supplies. As the details change, the grand total will also change - a sort of domino effect. This keeps the user from having to make changes in several places. In linking and embedding, the source and the target do not have to open simultaneously. The source file may be changed over the course of time, and when the target file is finally opened, the user will get a message asking about updating the links. This handout will cover only linking between Excel worksheets, and save Embedding and Linking between applications for another workshop.
LINKING CELLS IN EXCEL
1.	Click on the target cell. Key in an equal sign =.
2.	Change to the source cell.
3.	Press the enter key. Your active cell pointer will return to the target cell and sheet path or filepath will appear. For instance, if your source cell was on Sheet 1 in cell A4, you will see the following: =Sheet1!A4. If your source cell was in a completely different workbook named Orders, you would see: =[Orders.xls]Sheet1!$A$2; where Orders is the name of the source workbook, sheet 1 is the source worksheet and cell A2 is the source cell. Notice that the workbook name is enclosed in [ ]'s, the sheet name is followed by an exclamation point, and the $'s indicate that the cell reference is absolute. Could you key in the source reference yourself? Sure, but watch out for typos. The reference will have to be keyed exactly the same as the workbook and sheet names for the reference to work. Additionally, the source will have to have been saved prior to creating the link if it is in a workbook different from the target location. Changing to the source cell may involve changing worksheets or workbooks. To change from one sheet to another in the same workbook, click on the sheet tab. To change from one workbook to another, where both workbooks are open, click on Window on the menu bar and click on the name of the source workbook.
EXAMPLE:
1.	Create this worksheet on Sheet1 of a new workbook. The formula is cell B6 is =SUM(B2:B5), C6 is =SUM(C2:C5); and D6 is =SUM(D2:D5).
2.	Click on Sheet2 in the same workbook and create this worksheet:
3.	We're going to link cell A2 on sheet 2 to cell B6 on sheet 1. Click on in Cell A2 of Sheet2, key in an =.
4.	Change to Sheet1. Click on cell B6. The marquee will appear. Press enter. Your link should appear in cell Sheet2!A2. Test the link. Change any of the numbers in cells B2:B5 on Sheet1. Be sure to press the enter key after you key in the new number. The total in cell Sheet1!B6 should change as well as the total in cell Sheet2!A2.
5.	To complete the remainder of the links for Sheet2!B2 and B3, you could repeat the process above. You can also:
6.	Click on Sheet2!A2. Select A2:C2.
7.	Click on Edit, Fill, Right. The remaining links should be pasted into place. Always double-check that the correct cells have been linked. When you link between different workbooks, the cell references will be absolute. Edit, Fill Right will work, and will save you a lot of time, but you may need to edit each linked cell to correct the cell address. For instance, if our Sheet1 above was actually in a different workbook named Sales, and Sheet2 was in a workbook named Total Sales, when you used Edit, Fill, Right, the links would be like this: Notice that all 3 cells are linked to the same B6 cell. Edit the formulas: and the links will now work correctly.