More Excel (no formulae or functions) Exercises



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 1

Exercise 2: Comments and Paste Special 1

Exercise 3: Worksheets 2

Exercise 4: Linked documents 3

Exercise 5: Linked range 4

Exercise 6: Security 5

Exercise 1: Comments

Open the Club.xls workbook.

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 unpaid to 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 folder and close it.

Exercise 2: Comments and Paste Special

1. Open the Exercise comments and paste special.xls workbook.

4. Add the comment Salary increase to cell A5.

5. Copy the comment in cell A5, and using Paste Special, paste it into the comment in cell N5.

6. Add the comment Total cost of holiday in Barbados to cell J9.

7. Use Paste Special 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.

8. Recreate the comment in cell J9.

9. Set cell J10 to 0.

10. Display all the comments on the worksheet.

11. Use the icons on the Reviewing toolbar to move to the holiday comment box.

12. Click the Hide Comment icon (on the Reviewing toolbar) to hide the holiday comment.

13. Edit the comment in the Dec salary comment box, adding the words performance related.

14. Click the Hide All Comments button, then close the Reviewing toolbar.

15. Copy the formats from cell C4 to cells D4:O4.

16. Put the double line border back on the right of cell O4.

17. Save the workbook as Exercise 2A.xls in the R:\training.dir\excel\more-excel folder and 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 worksheets Jan, Feb, Mar, First Quarter

18. Group the 4 worksheets together by holding down the shift key and clicking each worksheet tab.

19. Enter the labels from the diagram to the right:

20. Now ungroup the sheets by clicking on Jan worksheet tab.

21. 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.

22. 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.

23. On the Mar worksheet enter the Sales data:

77 86 99 67

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

24. On the First Quarter worksheet replace the label Month with the label Totals.

Calculate the total of all 3 months apples in cell B2:

type = in cell B2 in worksheet First Quarter to 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.

25. Copy the formula in First Quarter worksheet cell B2 into cells B3:B5

26. Save the file as Exercise 3A.xls in the R:\training.dir\excel\more-excel folder and close it.

Exercise 4: Linked documents

1. In a blank workbook, key in a number in cell A1.

27. Save the workbook as Linking1.xls and leave it open.

28. Create a new workbook and save it as Linking2.xls.

29. Display the two workbooks side by side using the Arrange option on the Window menu. Use the Vertical option.

30. In the Linking2.xls workbook, click in cell C1. Create a link by typing = and then double-clicking cell A1 in the Linking1.xls workbook.

31. Press Enter to complete the formula, which should read =[Linking1.xls]Sheet1!$A$1.

32. In Linking1, change the number in cell A1. Cell C1 changes automatically because of the link.

33. In the Linking1 workbook, key in a list of six numbers, starting in A1, down the column.

34. Use the AutoSum function to total them.

35. In cell C1 in Linking2, key in the label Total to overwrite the previous link.

36. The worksheets should still be side by side, if not, select Arrange from the Window menu as before, and select Vertical.

37. Click in cell C2 in Linking2. Type = to start a formula and then double-click on A7 in Linking1.

38. Press Enter to complete the formula. The link is then created and should be =[linking1.xls]Sheet1!$A$7

39. Copy the contents of cell A3 in Linking1 to the Clipboard.

40. Click in cell C4 in Linking2. Using the Paste Special option from the Edit menu, select Paste Link to create a link.

41. Change the number in cell A3. The numbers in cell C2 and C4 in Linking2 should change automatically.

42. 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 Linked Range.

43. Select the range A1:A7 in Linking1. Copy it to the clipboard.

44. Click in cell A11 in Linking2 and use Paste Link (the Paste Special option on the Edit menu) to create the link.

45. Click outside the selection to cancel it.

46. Clear the formula from A7 in Linking1 and the link in cell C2 in Linking2.

47. 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.

48. The figures in Linking1 are now totalled in cell C2 in Linking2.

49. Save the workbook Linking1.

50. Save and close the workbook Linking2.

51. Change any of the six numbers in column A in Linking1. Remember the changes, and then save and close the workbook.

52. Open the workbook Linking2, checking the message box that is displayed, and clicking on the Update option.

53. List the supporting workbooks by selecting Links from the Edit menu.

54. Close the Edit Links dialog box, and then save and close the workbook.

Exercise 6: Security

1. Open the workbook Exercise security.xls in Read-only mode. (The document is password protected for write access.)

55. Save the file as Task security.xls. You will now be able to edit the file and save your changes.

56. The worksheet has columns A to N, but only A and N are visible at present. Unhide columns B:M.

57. Check to see which rows are missing and unhide them.

58. Hide the workbook.

59. Unhide the workbook.

60. Why is it not possible to hide the worksheet? Write your answer here:

61. 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.)

62. Add sheet protection (from the Tools menu).

63. Add password security. (Remember: passwords are case sensitive.)

64. Save the workbook as Read-only giving it a Password to modify of security. Name the workbook Protected.xls.

65. Close the workbook.

66. Open the workbook Protected.xls. Key in the password when prompted and click OK.

67. Click No in the following message box so that you can edit the workbook.

68. Try changing the content of B4.

69. Change the figure in cell M6 to 350.

70. Remove the protection from the workbook. (UseUnprotect on theTools|Protection menu.)

71. Try changing the content of B4 again. (Excel should now allow you to make changes. Do not make any changes.)

72. Remove the Read-Only condition and password as follows:

a) Open the Save As dialog box,

b) select General Options from the Tools menu,

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

d) click OK.

73. Save the workbook, replacing the protected version, and close it.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download