UCL

Education & information support division

information systems

Excel 2003

More Excel
(no formulae or functions)

Exercises

Document No. IS-019 v4

Contents

Exercise 1: Comments

Exercise 2: Comments and Paste Special

Exercise 3: Worksheets

Exercise 4: Linked documents

Exercise 5: Linked range

Exercise 6: Security

Exercise1: Comments

Open the Club.xlsworkbook.

  1. Add the comment Resigned with effect from June 2007 to cell A6.
  1. Add the comment If total due is less than total paid then flag as unpaidto cell G3.
  2. What do the notes say in cells A11 and A22?
  3. Save the file as Exercise 1A.xls in the R:\training.dir\excel\more-excel folderand close it.

Exercise 2: Comments and Paste Special

  1. Open the Exercise comments and paste special.xls workbook.
  1. Add the comment Salary increaseto cell A5.
  2. Copy the comment in cell A5, and using PasteSpecial, paste it into the comment in cell N5.
  3. Add the comment Total cost of holiday in Barbados to cell J9.
  4. Use PasteSpecial to add the amount in cell J10 to the figure in cell J9. Click OK. (580 has been added to the amount in cell J9.)

Note that Add feature in Paste Special has removed the Comment from cell J9.

  1. Recreate the comment in cell J9.
  2. Set cell J10 to 0.
  3. Display all the comments on the worksheet.
  4. Use the icons on the Reviewing toolbar to move to the holiday comment box.
  5. Click the HideComment icon (on the Reviewing toolbar) to hide the holiday comment.
  6. Edit the comment in the Dec salary comment box, adding the words performance related.
  7. Click the Hide All Comments button, then close the Reviewing toolbar.
  8. Copy the formats from cell C4 to cells D4:O4.
  9. Put the double line border back on the right of cell O4.
  10. Save the workbook as Exercise 2A.xlsin the R:\training.dir\excel\more-excel folderand close it.

Exercise 3: Worksheets

In this task, you will use multiple sheets within a single workbook to store some fruity information.

  1. Open a new workbook. Add another worksheet in addition to the 3 already there. Rename these worksheetsJan, Feb, Mar, First Quarter
  1. Group the 4 worksheets together by holding down the shift key and clicking each worksheet tab.
  2. Enter the labels from the diagram to the right:
  3. Now ungroup the sheets by clicking on Janworksheet tab.
  4. On the Jan worksheet enter the Sales data:

98 56 76 45

in the range B2:B5 and replace the label Month with the label Jan.

  1. On the Feb worksheet enter the Sales data:

88 76 87 85

in the range B2:B5 and replace the label Month with the label Feb.

  1. On the Mar worksheet enter the Sales data:

77 86 99 67

in the range B2:B5and replace the label Month with the label Mar.

  1. On the First Quarter worksheet replace the label Month with the label Totals.
    Calculate the total of all 3 months applesin cell B2:
    type=in cell B2 in worksheet First Quarterto tell excel you are entering a formula
    click on cell B2 in worksheet Jan
    and type+
    click on cell B2 in worksheet Feb
    and type+
    click on cell B2 in worksheet Mar
    and press enter.
  2. Copy the formula in First Quarter worksheet cell B2into cells B3:B5
  3. Save the file as Exercise 3A.xlsin the R:\training.dir\excel\more-excelfolder and close it.

Exercise 4: Linked documents

  1. In a blank workbook, key in a number in cell A1.
  1. Save the workbook as Linking1.xls and leave it open.
  2. Create a new workbook and save it as Linking2.xls.
  3. Display the two workbooks side by side using the Arrange option on the Window menu. Use the Vertical option.
  4. In the Linking2.xlsworkbook, click in cell C1. Create a link by typing = and then double-clicking cell A1 in the Linking1.xlsworkbook.
  5. Press Enter to complete the formula, which should read =[Linking1.xls]Sheet1!$A$1.
  6. In Linking1, change the number in cell A1. Cell C1 changes automatically because of the link.
  7. In the Linking1workbook, key in a list of six numbers, starting in A1, down the column.
  8. Use the AutoSum function to total them.
  9. In cell C1 in Linking2, key in the label Total to overwrite the previous link.
  10. The worksheets should still be side by side, if not, select Arrange from the Window menu as before, and select Vertical.
  11. Click in cell C2 in Linking2. Type = to start a formula and then double-click on A7 in Linking1.
  12. Press Enter to complete the formula. The link is then created and should be =[linking1.xls]Sheet1!$A$7
  13. Copy the contents of cell A3inLinking1 to the Clipboard.
  14. Click in cell C4 in Linking2.Using the Paste Special option from the Edit menu, select Paste Link to create a link.
  15. Change the number in cell A3. The numbers in cell C2 and C4 in Linking2 should change automatically.
  16. Leave both workbooks open for the next exercise.

Exercise 5: Linked range

  1. Continue from Exercise 4 with Linking2.xls and Linking1.xls both open side-by-side (Window|Arrange|Vertical).
  2. In cell A10 in Linking2, enter the label LinkedRange.
  1. Select the range A1:A7 in Linking1. Copy it to the clipboard.
  2. Click in cell A11 in Linking2 and use Paste Link (the Paste Special option on the Edit menu) to create the link.
  3. Click outside the selection to cancel it.
  4. Clear the formula from A7 in Linking1 and the link in cell C2 in Linking2.
  5. Click into C2 in Linking2 and type =SUM( and then select the range A1:A6 in Linking1. Close the bracket ) and press Enter to finish the formula.
  6. The figures in Linking1 are now totalled in cell C2 in Linking2.
  7. Save the workbookLinking1.
  8. Save and close the workbookLinking2.
  9. Change any of the six numbers in column A in Linking1. Remember the changes, and then save and close the workbook.
  10. Open the workbookLinking2,checking the message box that is displayed, and clicking on the Update option.
  11. List the supporting workbooks by selecting Links from the Edit menu.
  12. Close the EditLinksdialog box, and then save and close the workbook.

Exercise 6: Security

  1. Open the workbookExercise security.xls in Read-only mode. (The document is password protected for write access.)
  1. Save the file as Task security.xls. You will now be able to edit the file and save your changes.
  2. Theworksheet has columns A to N, but only A and N are visible at present. Unhide columns B:M.
  3. Check to see which rows are missing and unhide them.
  4. Hide the workbook.
  5. Unhide the workbook.
  6. Why is it not possible to hide the worksheet? Write your answer here: ......
  7. Lock the following ranges (i.e. unlock all the other cells):
    B4:M4, B10:M10, B14:M14, N1:N14.

(Tip: unlock all the cells, and then lock the cells listed. To lock or unlock, select Cell from the Format menu, and then click the Protection tab.)

  1. Add sheet protection (from the Tools menu).
  2. Add passwordsecurity. (Remember: passwords are case sensitive.)
  3. Save the workbook as Read-only giving it aPassword to modify of security. Name the workbookProtected.xls.
  4. Close the workbook.
  5. Open the workbookProtected.xls. Key in the password when prompted and click OK.
  6. Click No in the following message box so that you can edit the workbook.
  7. Try changing the content of B4.
  8. Change the figure in cell M6 to 350.
  9. Remove the protection from the workbook. (UseUnprotect on theTools|Protection menu.)
  10. Try changing the content of B4 again. (Excel should now allow you to make changes. Do not make any changes.)
  11. Remove the Read-Only condition and password as follows:

a)Open the Save Asdialog box,

b)select General Options from the Tools menu,

c)clear the password and read-only recommended boxes,

d)click OK.

  1. Savetheworkbook, replacing the protected version, and closeit.

UCL Information Systems1 More Excel (no formulae or functions) Exercises