lab 5: microsoft excel tutorial - knowledge directory

CMPF124 Microsoft Excel Tutorial

Prepared by Nazrita Ibrahim@2005, updated by Rozita Ismail@2009 UNITEN 1

Lab 5: Microsoft Excel Tutorial Excel Worksheet Microsoft Excel works as account ledger. An Excel Workbook (1) could have multiple Worksheets (2). A cell in Excel is referred by its Column and Row naming style (displayed in the name box). A heavy border around a cell indicates that it is an active cell (3). Any entry made at this point is made into the active cell, and any commands that are executed affect the contents of the active cell. The active cell can be changed by clicking a different cell, or using the arrow keys. An active cell reference is displayed on the name box (4).

Quick Access Toolbar

Office Button

Ribbon

Name Box

Title Bar

Formula Bar

Worksheet

Name Box

Cell

selector

Row

header

Active

Sheet

Formula bar

Column

header



Lesson 1: Basic 1. Open file ExcePractice.xls (http://metalab.uniten.edu.my/~rozita/cmpf124/lab). To rename a

worksheet, Right click at Sheet 1, choose Rename. Type Exercise 1

2. Now, you need to add two more columns. To do this, right click at column D, choose Insert.

You have a new column inserted. Write “Midterm Percentage” as the table heading. Repeat the step to insert a new column just next to Final, and name it as “Final Percentage”.

http://metalab.uniten.edu.my/~rozita/cmpf124/lab



3. Now, you need to calculate the average, the lowest and the highest score for the lab, midterm

and final. Write the word “Average” at cell A16, the word “Highest” at cell A17, and the word “Lowest” at cell A18.



4. Now, before you can do any of the calculations, you should change the cell format

accordingly (in this case, number). To do this, highlight cell B4 -> G18, right click and choose Format Cells.

5. A Format Cell dialog box appears. From the Category list, select Number. Set the

Decimal places to 2. Click the OK button.



6. Now, you should have all your numbers within the selected area to be in 2 decimals format.



Lesson 2: Using formula 1. Now that you have all the facts and figure ready, it is time for you to do some calculation. 2. The first few things that you want to calculate is the average, lowest and highest mark for

each assessment. 3. To calculate the average for lab marks, click on cell B16. Next, click on the Insert

Function icon. 4. An Insert Function dialog box appears. From the category list, select Statistical.

Select AVERAGE from the Select a function: list. Click the OK button



5. Next, a Function Arguments dialog box would appear. Under the AVERAGE section, make sure that text field Number 1 is highlighted.



6. Now, using your mouse, select cells B4 – B12 (we want to calculate the average for lab marks remember!!). Click the OK button. You will now have the average automatically calculated on cell B16.



7. Now, repeat the same steps to find the lowest and highest marks for lab. Your results should similar to the following:



8. Now, you should find out the average, highest and lowest mark for midterm and final as well. There are two ways of doing this, either you repeat the similar steps, or you just copy paste the cell. When you copy a cell that contains a formula, you are actually copying the formula, not the value. When you paste a formula, Excel will work out the corresponding cell to come out with the new result. Now, highlight cell B16 (source range), right click, and select Copy. Next, select cell C16 (destination range), right click, select Paste. An average is automatically calculated for C16. Now click on cell B16 again, observe that the formula is=AVERAGE (B4:B12). Next, click on cell C16. Observe that the formula this time is =AVERAGE (C4:C12). Excel automatically adjusts the corresponding cells whenever a formula is copied and paste (this is called relative reference).

9. Now, find the highest and lowest marks for Midterm, as well as the average, highest and

lowest marks for Final. Your result should be like this:



10. Next, you want to calculate the equivalent percentage for midterm. The weight age for

midterm is 30% of the total percentage. Take Ahmad score for a while. The formula to calculate the equivalent percentage for Ahmad’s midterm would be: C4 * $K$4 (C4 is the Ahmad midterm mark, while $K$4 refers to cell K4 which contain the weight age). The dollar sign ($) here refers to absolute reference, meaning, we would want to refer to THIS PARTICULAR cell only, even when we copy paste the formula.

11. Therefore, to calculate the midterm percentage for Ahmad, select cell D4 (destination cell). At

the formula bar, write =C4*$K$4, then press ENTER. The equivalent percentage is automatically calculated. (all function/calculation in Excel starts with the sign = ).



12. Now, copy cell D4, and paste to cells D5 – D12.Observe how the midterm percentage for

each student is automatically calculated. 13. If you select cell D4, you will see the formula is C4*$K$4, meaning that Excel will take

Ahmad’s midterm mark (C4), and multiply it with the midterm weightage (cell K4). Now if you select cell D6, you will see the formula is C6*$k$4, meaning that Excel will take Chong’s midterm mark (C6) and multiply it with the midterm weightage (cell K4). Observe that eventhough the midterm mark for each student would come from different cell, the weightage for midterm will come from the same cell (K4). That is why you use the dollar sign ($) to indicate absolute reference (meaning you want to refer to THIS PARTICULAR cell in every formula).



14. Now, using the same steps, calculate the equivalent final percentage for each student. You result should look similar to the following:

15. Now, we want to calculate the total mark for each student. Take Ahmad for example. The

formula to calculate the total marks for Ahmad would be B4+D4+F4. Your task is to calculate the total marks for each student. The result should look similar to the following:



16. Now, you want to add a grade lookup table. Please enter the following data into cells J8 –

K12.



17. Next, you want to determine the grade for each student. In this exercise, you are going to

learn how to use VLOOKUP function. To use VLOOKUP function, your grade table must be sorted in ascending order. Observe that those who get between 0 – 44 will get F; those who get between 45 – 54 will get E and so on.

18. Let’s start with Ahmad. Select cell H4. Click on the Insert Function icon. 19. An Insert Function dialog box appears. Select Lookup and Reference from the category

list. Select VLOOKUP from the function list. Click the OK button. 20. A Function Arguments dialog box will appear.

a. For the Lookup_value field, select cell G4. This is the cell where the value we want to look up (compare). b. For the Table_array field, highlight grade table you created just now, i.e. J8 – K13. Add dollar sign to make it as absolute reference. This is the table where the Excel will refer when assigning lookup value. c. For Col_index_num field, type in 2. This value 2 refers to the second column, where the matching value will be returned. d. Click the OK button.



21. You will see that Ahmad has just scored an A. Copy and paste the formula to determine

each student’s grade. The result should look similar to the following:



22. Now, you are going to learn how to do Conditional Formatting. Highlight cell H4 – H12.

Click on the Conditional Formatting in the Home tabs -> New Rule.

23. A Conditional Formatting dialog box will appear. In the Select a Rule Type, choose

format only cells that contain; another dialog box will appear. Make sure you fill each field with the following values, and click the Format button. Set the font color to red, then click the OK button.



24. You will see that grade F has changed color to red.

25. Now, you will learn how to count how many students in each letter grade. To do this, first

you need to insert the following values into cell J16 – J21.



26. Select cell K16. Click on the Insert Function icon. An Insert Function dialog box will appear. Choose Statistical from the category list. Choose COUNTIF from the function list. Click the OK button.

27. A Function Arguments dialog box appears. For Range text field, select H4 – H12 (i.e. the

grades for each student). For Criteria text field, select cell J16 (i.e. A). Here, what you are doing is you want to count “How many A’s within the grade obtain by this class”. Click the OK button. You will see the value 2 appear in cell K16 indicating there are two A’s within the selected range.



28. Repeat the same steps to count the number of B, C, D, E and F within the class range.

The result should look similar to the following:

29. Now, you could add border to the tables. To do this, select the area, then right click,

choose Format Cell.



30. A Format Cells dialog box appears. From the Border tab, make sure you select both the Outline and Inside button. You should also select a pattern from the Line Style. Click the OK button.

31. You could also apply background color to a cell. To do this, select the cell, right click and

choose the Format Cells option. 32. A Format Cells dialog box appears. From the Fill tab, select the colour of you

choice. Click the OK button.



33. Now, you can view the result in ExcelPractice – stage1.pdf



Lesson 3: Working with Chart 1. Click the Insert Tab at the top of the Excel screen. When you do you’ll see that one of the

Groups in the Insert Tab/Ribbon is Charts.

2. Under the Data Range tab, highlight the Data range text field. Using your mouse,

highlight cell J16 – K21. 3. For our first chart, we’ll use a Column Chart. Move your cursor over Column and an image

like the one on the right will appear. 4. Click the Column button and you’ll see an image like the one on the left. As you can see

there all kinds of Column Charts. We’ll begin with a simple two dimension 2-D Column chart. You’ll be able to change this later, if you desire – to another Column Chart of one of the other selections.

5. Click on the 2-D Column chart indicated by the arrow on the left.



As soon as you click, because you highlighted your data, an image similar to the one below will appear. Don’t worry that the Chart is covering your data – we’ll take care of that in a minute. Notice that when we highlighted cells J16 to K21 this created the X-Axis labels (A, B, etc.).

6. Now, let’s move our chart to a page of its own – so it will be easier to work with and not

cover our data. Click anywhere on your chart and you will notice that a new Tab appears at the top of your Excel screen – Chart Tools. Click the Chart tools Tab and the Chart Tools Tab/Ribbon will appear like the image below.

Notice, on the right end of the Chart Tools Tab/Ribbon is a Move Chart Location button. Click the Move Chart Location button.

When you click the Move Chart Location button a Move Chart menu screen will appear (like the image below).



7. Click on the small circle to the left of New Sheet and change Chart 1 to Class Result – as

indicated above. Then click the OK button. 8. Look at the bottom left of your Excel screen. You will see a new Tab – Class Result

Chart! Your data is on Sheet 1. We’ll rename it when we have finished working with our chart.

9. You should be on your Class Result Chart Tab. If not, click this tab. Your chart should now

fill the Excel screen. Click in one of the outside corners of your chart. To add chart title, legend, etc: Chart Tools -> Layout



Experiment this: Now try change the lab mark for Julita from 10 to 15, press Enter. Observe how the change is also reflected in the graph and the number of students per grade table. You can experiment this by changing the mark (mark ok.. not the percentage) for any student that you like. What is the conclusion that you can make from this observation?

lab 5: microsoft excel tutorial - knowledge directory

Documents