Laboratory Three Windows Excel

Laboratory three: Excel

Laboratory Three–Windows Excel

I am sure that after last week’s training, you have learnt more about the use of Excel. This week covers more advancedfeatures of excel.

Objectives:

There are five objectives:

1)How to create a grade book to assign grade to each mark; (30 minutes)

2)How to create mail merge; (30 minutes)

3)How to create other features (15 minutes);

4)How to set protection so that others can use but cannot modify it; (15 minutes)

Procedure 1:how to create a grade book – 30 minutes

Sometimes, it is good to use Excel to determine the average scores. However, it is a little bit difficulty to determine grade. Below you see an image of the beginning of a gradebook. Several elements of this image will be discussed one at a time.

Step 1: type in the data, or use cut and paste the following:

Chemistry / Physics / Maths / Chinese / English / average
John Chan / 70 / 56 / 78 / 50 / 56 / 62
Charlie Man / 45 / 76 / 87 / 65 / 65 / 68
Joe Tsui / 37 / 45 / 34 / 34 / 23 / 35
Philip Ma / 89 / 45 / 65 / 64 / 63 / 65
Kenny Chu / 90 / 80 / 76 / 47 / 90 / 77
Rose Ip / 87 / 88 / 46 / 76 / 73 / 74
Maggie Liu / 30 / 15 / 54 / 58 / 73 / 46
Brain Li / 89 / 78 / 96 / 89 / 72 / 85
Ivan Shum / 78 / 94 / 67 / 75 / 56 / 74
Lili Ho / 34 / 84 / 34 / 67 / 55 / 55

Step 2: Now we will ask Excel to look at the numerical average in column I and compare it to a list which defines the grading scale, for the purpose of assigning a letter grade to the average. Room was left at the top of the gradebook for this purpose.

Step 3: Enter the grade as follows:

Step 4:Next we will write an equation which will look at a student's numerical average, look at a list of grades, and assign a letter grade to the student. This is done with a functioned named VLOOKUP. The equation must specify three elements:

The location of the numerical grade to be compared (H7 in thisexercise)
The name of the lookup table (grades)
The location of the letter grade in the lookup table (2)

Step 5: Define the lookup table as grades as follows:

Step 6: You have to Define Name under“insert”

Step 8: Use the formula as follows:

Step 9: Repeat all as follows:

Activity 1:

Change the C grade to 67

Activity 2:

Change the B grade to 76

Activity 3:

Add two more rows of data and play around the above.

One mark: ______

Procedure 2 – How to create a mail merge – 30 minutes

Two Microsoft Office applications will be required for this task. Excel will be used for the data and Word will be used to create the form letter. A form letter is one in which the body of the letter stays the same but specific information is inserted for/about several different individuals.

The first step will be to prepare the data source from which information will be inserted into the form letter.

Step 1.Open Microsoft Excel. Remember to leave your browser window open until this lesson is finished.

The excel worksheet will take the place of a database. A database has field names for each type of information entered. Common field names are First Name, Last Name, Telephone, and zip code. The field names you choose will be determined by the form letter you want to produce. For this activity we will use the following field names:

First name
Last name
Subject
Average grade

Step 2. Field names must be entered in the cells across row 1. Type "First name" in cell A1. Press the Tab key to move to cell B1 and type "Last name." Continue until each of the field names above has been entered. Do not worry if the cell is not wide enough. Your worksheet will probably look somewhat like the one you see below:

Step 3. Place your cursor over the "A" in the column heading and drag to the right unto you reach the "E" column heading. All five columns will be highlighted, and then enter the data as follows:

Step 4. Save the worksheet as mail. Remember where you saved it.

Step 5. Open Word. If a new blank document does not automatically open, go to the File menu and select New. Then open a new blank document. It is not necessary to begin typing the form letter. You may start the mail merge process before typing anything.

Step 6. Go to the Tools menu and select Mail Merge. The Mail Merge Helper dialog box opens. As you make selections in this box other selections will become available.

Step 7. Click on the Wizard button and choose Form Letter from the drop down dialog box.

Type in your document as follows:

Dear

As we approach the end of Semester I wanted to let you know how glad I am to have taught you. Your mark of your subject is .

Choi yiu kuen

Select the Excel file that you just created as follows:

Step 8: Now click the insert to insert the field name.

Step 9:Try to replace the field that I just typed using the “Insert Field”.

Step 10: Repeat the step, you will find that the system will display the following:

The fields you inserted will be displayed as above. Finally, you click the Edit individual letters, you will have all the letters.

Activity: Modify the letter to have more meaning and insert the field again.

Procedure 3 – How to use Excel for other features – Calendar (15 minutes)

Below is the Calendar for July 2003. I quote this an example illuminating that Excel is very powerful apart from calculation.

Activity

Based on the above, do the August 2003.

You could download the file:

Procedure 4– How to set protection on your sheet (15 minutes)

Download the excel file temperature from

Now play around this simple conversion. Select B2 and modify it. You will find that it is protected so that no one can modify. Now unprotect it by selecting.

Enter the password 10956

Modify the contents that you want and protect it again so that no one can modify.

Activity

Change one of the cell contents and protect it.

One mark ______

02/10/2018