Microsoft Excel training notes - SchoolNet SA



Microsoft Advanced Excel training notes

These notes cover the following skills:

• Revision of Foundation and intermediate Level skills

• Using absolute cell referencing

• Applying conditional formatting

• Using nested IF statements

• Using the LOOKUP function and combo boxes

These notes will allow you build on the skills developed in the Excel foundation and intermediate training notes. The exercise will focus on the development of a grade book which has relevance for all teachers. The advanced training is an opportunity for you to apply skills relevant to your own school/classroom. Excel is an very powerful data analysis software package and what you can achieve is limited by your own needs in the classroom and to some extent your imagination!. Good luck

1. Before you begin to work through this section, you should already be able to open MS Excel and set up worksheet as shown below (fig. 1). Make sure to format each column to the appropriate category e.g. name should be formatted as “Text”, telephone numbers should be formatted as “Text” to allow you enter a zero before each number, the percentage column as “Percentage” etc. The spreadsheet you are developing below will allow the teacher to keep track of two tests (A and B) and automatically calculate the percentage and the grade (Distinction, Merit, Pass or Fail).

Figure 1

Three things you need to format once you have entered the data as above:

a. Set the title of the grade book to bold and font size 14 (Arial).

b. Merge cells D6 to F6 and label as “Test A” and colour cell green

c. Merge cells G6 to I6 and label as “Test B” and colour cell orange.

d. Draw border as shown in the screenshot (click on Format and then Border and Shading)

2. Now enter the marks for both test A and text B which each student achieved as shown in the screen shot below.

Figure 2

3. To calculate the percentage, you need to divide each raw mark by the total marks for each text and work out the percentage. In the previous training sessions, you learnt how to enter formulas in a cell and replicate that to other cells in a column. This time we will use absolute referencing in which the exact address of a cell, regardless of the position of the cell that contains the formula is maintained. An absolute cell reference takes the form $A$1. You will soon understand the advantage of using absolute references.

Create another table as shown below which will hold the total marks for test A and Test B (Fig. 3).

Figure 3

4. Now you have to set up the formulas in both the percentage columns to automatically calculate the total percentage.

In cells E8 enter the following formula = D8/$B$17 (make sure that you format the column E to percentage category. Set the number of decimal places to 0). Notice the use of dollar sign in the formula which denotes an absolute reference to cell B17. Now replicate the formula to cover the range E8-E14. Repeat to calculate the percentage for Test B (column I). At the end of this step, you should have a worksheet which looks like fig 4.

[pic]

Figure 4

5. Now you want to automatically work out what the grade each student has obtained based on the percentage. First, you need to set up another table with the grade boundaries for each test as shown in Fig. 5. Both test A and B have different grade boundaries which will determine whether a student achieves a final grade of distinction, merit, pass or fail.

Figure 5

6. In both the foundation and intermediate training, you used the IF function. The IF function tests the value in a cell and does one thing if the test is true and another if the test is false. We will test each percentage against the grade boundary and working out the grade. For example, for test A, if the percentage is greater than 70%, then the student gets a distinction. Unlike the basic IF function which tests two conditions; we have to test each condition against the three grade boundaries. Hence for this you will learn how to use what is referred to as a nested IF statement.

This is the logic. If E8 > B19, grade is distinction; if E8 > B20, grade is merit; IF E8 > B21, grade is pass; if E8 < B21, grade is fail. To construct a formulas, you will use a nested IF statement and absolute referencing.

Enter the following formula in cell F8 =IF(E8>$B$19,"Distinction",IF(E8>$B$20,"Merit",IF(E8>$B$21,"Pass","Fail")))

Once entered, you worksheet should worksheet should look like the one below (Fig. 6)

Now see if you work out what the formula you need to enter in cell I8 to calculate the grades for Test B.

Figure 6

7. The use of colour coding in marks book is a good visual aid to see the performance of the whole class and give a snapshot impression of progress made. You can use conditional formatting to make cells stand out if they meet certain criteria. For example, you may want to colour all distinction grades as green and all fail grades as red.

To achieve this, highlight all the cells to which you want to apply conditional formatting in one block by dragging across cells F8 to F14 as shown below (Fig. 7).

[pic]

Figure 7

Next click on Format and Conditional Formatting. Choose equal to from the drop-down list and set the value to Distinction as shown on Fig. 8

[pic]

Figure 8

Click on Format and click on the pattern tab to set the Cell shading to green. (Fig. 9).

Figure 9

To set a second conditional format, repeat, click in Add and choose equal to from the drop-down list and set the value to Fail (Fig. 10)

[pic]

Figure 10

Repeat the cell shading to red and your conditional formatting box should appear as shown in Fig. 11.

[pic]

Figure 11

Once you click OK, the worksheet should now appear as shown below (Fig. 12)

[pic]

Figure 12

Now set the conditional formatting for Test B as done for Test A. Your final worksheet should appear as below (Fig. 13)

[pic]

Figure 13

8. Lets revisit and see the advantage of absolute referencing and setting up a separate table for grade boundaries. Lets say that you as a teacher decided to change the grades boundaries for both Text A and B. It’s simple as all you have to do is change the values in the Grades boundaries table. All the rest is automatically generated. Change the grades boundaries and to the values as below and you should get a worksheet similar to the one shown below (Fig. 14). Notice the change in colour of grades as defined by conditional formatting.

[pic]

Figure 14

9. Now its time to do something really exciting with the grade book you have created. You want to set up another worksheet and create a user friendly interface to retrieve information from the grade book you have just created. First, click on the sheet 2 of your Excel Workbook and enter data as shown below (fig. 15)

Figure 15

10. The next step is to create a Combo box to select a name of a student whose details you want to bring up in the page. For this, click on View and Forms (fig. 16)

Figure 16

Select a Combo box from the Forms toolbar and draw a Combo box (fig. 17).

Figure 17

Right click on the Combo box and click on Format and Control tab to see a window as shown in fig. 18

[pic]

Figure 18

In the Input Range Box enter the range of the table (on sheet 1) which contains all the names. However, note that you are entering the range in worksheet 2 but linking to the table on worksheet 1 hence the notation as below.

Sheet1!$B$8:$I$14 (Sheet1! Identifies the link and $B$8:$I$14 is the range with absolute referencing).

Next, choose a cell link which will be used to provide a link between the worksheet 1 and information to be retrieved into the form created in worksheet 2.

In cell link, enter $E$4 and click on OK (fig 19)

[pic]

Figure 19

Once this is completed, you should be able to click on the Combo box and see the list of all the names of the students. Notice that when a name is selected, a cell link value appears in cells E4 which corresponds to the student number (the left most column of the table).

Figure 20

11. Once a name has been selected from the drop down list, you want the grades for Test A, Test B and telephone details to automatically appear. To do this, you will use an Excel lookup function called VLOOKUP. This function is used to retrieve information stored in a table and when the desired value is stored elsewhere on the workbook.

The VLOOKUP has the following arguments:

VLOOKUP (lookup_value, table_array, col_index_num)

lookup_value: The value to be found in a column which in this example is in cell E4

table_array: The table of information in which the data is looked up.

col_index_num: the numeric position of the column that is being searched.

Enter the VLOOKUP function in cell C6 as follows: =VLOOKUP(E4,Sheet1!A8:I14,6).

Enter the VLOOKUP function in cell C8 as follows: =VLOOKUP(E4,Sheet1!A8:I14,9)

Enter the VLOOKUP function in cell C10 as follows: =VLOOKUP(E4,Sheet1!A8:I14,3)

When you choose Fazal as the student whose details you want brought up, the following information should appear as shown below (fig.21)

Figure 21

12. Formatting the final screen. Insert a colour for the complete worksheet by selecting a part of worksheet and choosing a colour (fig. 22)

Figure 22

Next you want to make the cell link value in E4 invisible. You can do this by making it the same colour as the background.

13. Add an image to the page to enhance the design by clicking on Insert and picture from file (fig. 23)

Figure 23

This concludes the Advanced Level training module for MS Excel. MS Excel provides teachers with a powerful tool for managing budgets, registers and other records (such as test results), and also enables both teachers and students to process and present data in a fast and convenient way, whether in Mathematics or in subjects such as Social Sciences, where large amounts of data (such as census information, trade figures or climate statistics) need to be analysed. It also allows one to manage and retrieve data in a very efficient manner.

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

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

Google Online Preview   Download