| Sample Pages from Excel™ 5.0 for Windows Self-Paced Training Materials |
 |
| Sample Pages from Excel™ 5.0 for Windows Self-Paced Training Materials |
|
This page contains excerpts from the Excel training manual and is indicative of the style and content of the full training kit. Note that 25 point Arial sans serif font is used exclusively in the Large Print notes, as research indicates that this type of font is generally easiest for a vision impaired person to read (your browser may present this page in a different font).
The following text is taken from Lesson 1 of the Excel training manual and is an example of our "describe then practise" style of teaching. This excerpt covers the various aspects of Saving and Closing Workbooks, and includes the Practical Exercises associated with this topic.
SAVING AND CLOSING WORKBOOKS
Saving a Workbook
Workbooks can be saved either on the hard drive, that is C
drive, or to a floppy disk. To save a workbook you would select
File from the menu bar by pressing Alt and F, and choose either
Save by pressing S, or Save As by pressing A.
Saving a Workbook for the First Time
A workbook can be saved for the first time by selecting File
from the menu bar and then choosing Save As. This will open the
Save As dialogue box. You can then specify the drive to which you
wish to save the workbook, and also give the workbook a name.
Excel automatically saves all the files with a .XLS extension
unless you specify otherwise.
NOTE: Once a drive is specified, this remains the default drive for all future workbooks created in the current session.
Practical Exercise: Saving a Workbook for the First Time
1. From the menu bar select File by pressing Alt and F, and then choose Save As by pressing A.
2. Select Drive by pressing Alt and V.
3. Indicate the drive by typing "a".
4. Return to the File Name text box by pressing Alt and N.
5. Type "first" and press Enter. The workbook will be saved as ‘first.xls’. As you have selected ‘a’ as your drive, the workbook will be saved to your floppy disk in the ‘a’ drive.
6. Leave the workbook open for the next practical exercise.
End of Practical Exercise
Saving a Previously Saved Workbook
Once you have saved a workbook, you will need to save it
again in order to save new data. Selecting File from the menu bar
and choosing Save will update the active workbook. This process
is a ‘quick-save’ and no dialogue box is opened. When
entering data into your workbook it is a good idea to frequently
save your work. You can also press Control and S to quick-save
your work.
Practical Exercise: Saving a Previously Saved Workbook
1. Ensure that the workbook ‘first.xls’ is open.
2. Type "Chemistry" and press Enter.
3. Type "Art" and press Enter.
4. From the menu bar select File and choose Save. No dialogue box will open. The workbook will now be automatically saved while retaining its present name.
5. Leave the workbook open for the next practical exercise.
End of Practical Exercise
Saving a Workbook With a Different Name
If changes have been made to your workbook, and you wish to
save it with a different name, you would select File from the
menu bar and choose Save As. This will open the Save As dialogue
box. The original name will appear in the File Name text box. By
typing in a new name for the workbook, the original name will be
replaced with the new one. On pressing Enter the workbook will be
saved and the new name will be displayed on the title bar. You
will then have two workbooks, the original, and the newly saved
workbook.
Practical Exercise: Saving a Workbook With a Different Name
1. Ensure that the workbook ‘first.xls’ is open.
2. From the menu bar select File and choose Save As. The Save As dialogue box opens.
3. In the File Name text box type "second" and then press Enter. The workbook name on the title bar will now have changed from ‘first.xls’ to ‘second.xls’. The workbook ‘first.xls’ is closed, leaving ‘second.xls’ open.
4. Leave the workbook open for the next practical exercise.
End of Practical Exercise
Saving on a Different Drive
As well as being able to save a workbook under a different
name, you can also select on which drive you will save it. When
the Save As dialogue box opens, you can also choose the drive by
pressing Alt and V and typing the letter of the drive you
require. Pressing Enter will accept the drive change.
Practical Exercise: Saving on a Different Drive.
1. Ensure that the workbook ‘second.xls’ is open.
2. From the menu bar select File and choose Save As.
3. Select Drive by pressing Alt and V.
4. Type "c" to indicate the C drive and then press Enter. Tab to OK and press Enter. This will close the dialogue box and return you to the workbook.
5. Leave the workbook open for the next practical exercise.
End of Practical Exercise
Closing a Workbook
To close a workbook you would select File from the menu bar,
and then choose Close. This does not exit Microsoft Excel, it
simply closes the active workbook.
If the workbook being closed has not been saved a dialogue box appears displaying: "Save changes in ‘Book#’?", together with the command buttons: Yes, No, Cancel and Help. You would then select the command button required.
If ‘Yes’ is selected, the Save As dialogue box opens. You must then enter the filename of your workbook and press Enter.
If the workbook being closed has been saved previously, but recent changes have not been saved, a dialogue box appears displaying: "Save changes in ‘(name of workbook)’?", together with the command buttons: Yes, No, Cancel and Help. You would then select the command button required.
If ‘Yes’ is selected, the workbook is saved under its original name.
If ‘No’ is selected, the workbook is saved without any changes being saved.
Having closed the workbook, a blank screen will appear. This is not a workbook screen. The only options on the menu bar are File and Help.
Practical Exercise: Closing a Workbook
1. Ensure that the workbook ‘second.xls’ is open.
2. From the menu bar select File and choose Close by pressing C. This will close your workbook, and a blank screen will appear.
End of Practical Exercise
Return to the top of this page
The following text is taken from Lesson 11 of the Excel training manual and is an example of our "describe then practise" style of teaching. This excerpt covers the description of Vlookup Tables and then Practical Exercises in using these within a spreadsheet. Note that this text is taken from one of the later lessons in the Excel Manual and that concepts not described in depth have already been dealt with in earlier lessons.
VLOOKUP TABLES
A lookup table allows you to make entries into an active worksheet from a table containing additional information which is located elsewhere on the worksheet. When information is entered into a worksheet from a lookup table, a link is established and any changes made in the table will be reflected in the worksheet. An example would be where you are given the percentage marks for students and wish to calculate their grades based on certain criteria. You would create a lookup table with percentage scores and corresponding grades.
A lookup table can be horizontal or vertical. In this lesson we will discuss vertical lookup tables. When using a lookup table you are comparing values in your sheet with values in the lookup table. The values to be compared must be in the first column of the lookup table and must be in ascending order. The results of the comparison are taken from the other columns in the lookup table. In the example given above the values to be compared would be the percentage marks. The next column could be the corresponding grades and another column could be a comment. A lookup table can have two or more columns. When entering the formula you must specify the column number which contains the required results.
You would create the lookup table in a space on your existing worksheet. Once the lookup table has been created you would select it and name it. Naming the table makes it easier to enter the formula.
In the cell where you want the result entered you would type an equals sign followed by ‘vlookup’ and then an open bracket. Next you would type the cell address of the data which is being compared with the data in the lookup table followed by a comma. Then you would type the name of the Table followed by another comma, and then the number of the column from which the results will be returned, followed by a closed bracket. The formula syntax would be as follows: =VLOOKUP(B4,Grades,2). Do not leave any spaces in the formula.
NOTE: If the lookup value is less then the smallest number in the table then a #N/A error is returned. You can prevent this happening by always including zero at the start of the table.
Practical Exercise: Creating and Using a VLOOKUP Table
1. Open the workbook ‘vlookup.xls’.
2. Select the worksheet ‘English’. This sheet shows the percentage results for English students. Create a VLOOKUP table to calculate the corresponding grades using the following steps:
a) Go to cell A17 and type "Percentage". This is the first column of the table and will contain the values to be compared.
b) Go to cell B17 and type "Grade". This is the second column of the table and will contain the values returned by the comparison.
c) Go to cell A18 and type "0%".
Press Enter and then type "35%".
Press Enter and type "50%".
Press Enter and type "65%".
Press Enter and type "75%". Press Enter.
d) Go to cell B18 and type "E".
Press Enter and then type "D".
Press Enter and type "C".
Press Enter and type "B".
Press Enter and type "A". Press Enter.
e) Select the range of cells A17:B22 and name it "Grades". This is the name of the VLOOKUP table.
3. Go to cell C2 and insert the formula to calculate the grades using the following steps:
a) Type an equals sign and then type "vlookup".
b) Type an open bracket and then type "B2". B2 is the cell address of the first value you want to compare with the VLOOKUP table.
c) Type a comma and then type the name of the VLOOKUP table, which in this case is "Grades".
d) Type a comma and then type "2". This is the number of the column which contains the results.
e) Type a closed bracket and then press Enter.
4. Joy Adamson’s result was 57% which has been returned as a C grade. Review the VLOOKUP table and notice that results between 50% and 64% are returned as a C grade.
5. Copy the formula into the range C3:C14 to fill in the grades for the remaining students.
6. Centre the student grades in the column.
7. Go to cell A25 and type your name.
8. Print one copy of the worksheet.
9. Save and close the workbook.
End of Practical Exercise
You can format VLOOKUP tables using any of the methods used in a worksheet. It is a good idea to create a border round a VLOOKUP table so that it stands out from the main work area of the active worksheet.
Practical Exercise: Creating and Using a VLOOKUP Table
1. Open the workbook ‘vlookup.xls’.
2. Select the worksheet ‘Social Studies’. Create a VLOOKUP table to show grades and comments using the following steps:
a) Go to cell A17 and type "Percentage". This is the first column of the table and will contain the values to be compared.
b) Go to cell B17 and type "Grade". This is the second column of the table.
c) Go to cell C17 and type "Comment". This is the third column of the table.
d) Go to cell A18 and type "0%".
Press Enter and then type "30%".
Press Enter and type "40%".
Press Enter and type "50%".
Press Enter and type "60%".
Press Enter and type "70%".
Press Enter and type "80%".
Press Enter and type "90%". Press Enter.
e) Go to cell B18 and type "E".
Press Enter and then type "D".
Press Enter and type "D+".
Press Enter and type "C".
Press Enter and type "C+".
Press Enter and type "B".
Press Enter and type "B+".
Press Enter and type "A". Press Enter.
f) Go to cell C18 and type "Fail".
Press Enter and then type "Fail".
Press Enter and type "Re-sit".
Press Enter and type "Average".
Press Enter and type "Well done".
Press Enter and type "Good".
Press Enter and type "Very Good".
Press Enter and type "Excellent". Press Enter.
g) Select the range of cells A17:C25 and name it "Results". This is the name of the VLOOKUP table.
h) Create a border around the VLOOKUP table. Adjust the width of column C to accommodate the longest entry.
3. Go to cell C2 and insert the formula to calculate the grades using the following steps:
a) Type an equals sign and then type "vlookup".
b) Type an open bracket and then type "B2". B2 is the cell address of the first value you want to compare with the VLOOKUP table.
c) Type a comma and then type the name of the VLOOKUP table, which in this case is "Results".
d) Type a comma and then type "2". This is the number of the column which contains the grades.
e) Type a closed bracket and then press Enter.
f) Copy the formula to the range C3:C14 to complete the grades for the other students.
4. Go to cell D2 and type the formula to insert the comments using the following steps:
a) Type an equals sign and then type "vlookup".
b) Type an open bracket and then type "B2".
c) Type a comma and then type the name of the VLOOKUP table, which in this case is "Results".
d) Type a comma and then type "3". This is the number of the column which contains the comments.
e) Type a closed bracket and then press Enter.
f) Copy the formula to the range D3:D14 to enter the comments for the other students.
5. Centre the student grades in the column.
6. Go to cell A30 and type your name.
7. Print one copy of the worksheet.
8. Save and close the workbook.
End of Practical Exercise
Return to the top of this page
The following text is taken from Review Exercise 20 of Lesson 6 of the Excel training manual. This Exercise is one of the end-of-lesson Reviews and covers Copying and Moving Worksheets, Adjusting Column Width, and Adjusting Row Height. Bear in mind that a full lesson on these concepts has already preceded this exercise in the full training manual, and that it is a final practical review exercise. All files used in the Exercise are included on the kit diskette.
REVIEW EXERCISE 20
In this exercise you will retrieve a previously created workbook. By using shortcut keys you will copy the data from one worksheet to a new workbook. You will add columns, insert new data, create and copy an absolute formula, complete the required calculations, select and format non-adjacent cells and print one copy of the worksheet.
1. Open the workbooks ‘Lesson6’ and 'Lesson4' and select the worksheet ‘Commission’.
2. Copy the 'Commission' worksheet to the workbook 'Lesson6'.
3. In the new worksheet go to cell G3 and type "Bonus".
4. Go to cell H3 and type "10%". This is the percentage bonus which each salesman will receive based on his total Sales Commission.
5. Go to cell G4 and insert a blank row.
6. Go to G5, apply the bold format and type a new heading "BONUS".
7. Go to cell H5, apply the bold format and type a new heading "Monthly Totals". Wrap the text to fit the cell.
8. Go to cell G6 and insert an Absolute Reference formula to calculate the amount of bonus for salesman ‘Bell’.
9. In cell H6 insert a formula to add the bonus to the total commission achieved by salesman ‘Bell’.
10. Copy the formula in cell G6 to the cell range G6:G12.
11. Copy the formula in cell H6 to the cell range H6:H12.
12. In cell H14 enter the formula to calculate the new ‘Monthly Totals’.
13. Select the cell range G6:H14 and format the cells as currency with two decimal places.
14. Select the non-adjacent cells G5:H5 and G14:H14. Apply a thick line border to the top and bottom of these cells and also apply a light shading.
15. Cancel the selection.
16. In cell A21 type your name and "Ex 20 Bonus".
17. Rename the worksheet "Bonus".
18. Centre the worksheet horizontally and vertically and then print one copy.
19. Save and close both workbooks.
End of Review Exercise
Return to the top of this page
The following Quiz is taken from Lesson 9 of the Excel training manual and covers Protection Commands, Sorting Data and Linking Worksheets. A full lesson on these concepts has preceded this Quiz in the complete training manual, and this is a formal test of the student's knowledge. The final Course Exam included in the full kit is a more thorough test of a student's practical knowledge of Excel.
QUIZ FOR LESSON 9
This quiz contains 10 questions, both multiple-choice questions and practical exercises. The multiple-choice questions are worth one mark and the practical exercises are marked as indicated.
For multiple-choice questions choose the option which best answers the question in each case.
Answer by typing a b c or d, or True or False after the word Answer.
Complete practical exercises as instructed in the question.
Please type your name here:
1. The Protection command is located within which menu bar option?
a. Tools.
b. Data.
c. Edit.
d. Format.
Answer:
2. Which of the following can be performed when a workbook is protected?
a. Delete worksheets.
b. Rename worksheets.
c. Edit worksheets.
d. Move worksheets.
Answer:
3. How would you leave cells unlocked in a protected worksheet?
a. Select Insert, choose Cells and then choose Unlock cells.
b. Select Format, choose Sheet and then choose Hide.
c. Select Tools, choose Protection and then deactivate the Locked check box.
d. Select Format, choose Cells and then the Protection tab, and deactivate the Locked check box.
Answer:
4. With the highlight on the column label how would you display the list of unique items in the column to be sorted with AutoFilter?
a. Press Control and Enter.
b. Press Control and the down direction arrow key.
c. Press Alt and the down direction arrow key.
d. Press Alt and Enter.
Answer:
5. AutoFilter can only be used if the columns in a worksheet have labels. True or False?
Answer:
6. When using AutoFilter, the rows which do not contain the item you have chosen to display are:
a. deleted.
b. hidden.
c. relocated.
d. moved.
Answer:
7. To exit the AutoFilter function you would select Data and choose:
a. Sort and then Show All.
b. AutoFilter and then Show All.
c. Filter and then Undo AutoFilter.
d. Filter and then AutoFilter.
Answer:
8. To create a link between data in worksheets you would position the highlight in the cell which is to be linked and then type:
a. an equals sign, switch to the source worksheet, then select the cell to be linked and then press Enter.
b. an equals sign and the cell address which is being linked.
c. the name of the worksheet containing the cell address to which you are linking.
d. Type the cell address to which you are linking.
Answer:
9. When a source document is updated it is necessary to update the linked cells. True or False?
Answer:
10. Switch to Microsoft Excel and open the workbook ‘test.xls’. Select the worksheet ‘Quiz 9’.
(2 marks)
Total marks: 11
THIS IS THE END OF QUIZ 9.