ICT Grade 6 - Advanced Spreadsheet Functions (PDF)

UNIT 5 Spreadsheet: Using advanced features AIM: To use advanced features of a Spreadsheet software. LEARNING OBJECTIVES: By the end of this unit, pupils should be able to: Use auto-filter to view selected data. Use functions that perform maximum, minimum and count. Modify page setu...

UNIT 5 Spreadsheet: Using advanced features AIM: To use advanced features of a Spreadsheet software. LEARNING OBJECTIVES: By the end of this unit, pupils should be able to: Use auto-filter to view selected data. Use functions that perform maximum, minimum and count. Modify page setup. Print a worksheet. 80  UNIT 5 FILTERING DATA We use the Sort & Filter tool to arrange data in ascending and descending order in Grade 5. Spreadsheet software uses the Filter tool to display only some data in a worksheet and find information. For example, fil ers can be used to display boys only in a class where there are girls also. ACTIVITY 1 Activity 1: Filtering Data Step 1: Open Excel. Step 2: Type the following data as shown below: Step 3: Select cells A2 to E8. 81  UNIT 5 FILTERING DATA ACTIVITY 1 Step 4: On the Home ribbon, click on Sort & Filter. Step 5: On the drop down menu, select In the table, each header now has a “ “ next to it. Step 6: Click on next to Gender. Step 7: Click on (Select All) to uncheck. The tick disappears. 82  UNIT 5 FILTERING DATA ACTIVITY 1 Step 8: Click on Boy. A tick appears next to it. Step 9: Click on OK. The result is as follows: Notice that next to Gender has changed to. Step 10: Using steps 6 to 9 above, display marks for Girls only. Step 11: Save the file as “Marks” on the desktop. 83  UNIT 5 REMOVING FILTERS ACTIVITY 2 Activity 2: Removing Filters Step 1: On the Home ribbon, click on Sort & Filter. Step 2: On the drop down menu, select. All items are displayed again without filters. Step 3: Save your work. 84  UNIT 5 MAX, MIN AND COUNT FUNCTIONS We used functions in Grade 5 to calculate sum and average. We will use more functions to find maximum and minimum. Follow the activities to learn about the MAX and MIN functions. ACTIVITY 3 Activity 3: Calculating the highest and lowest price using MAX and MIN functions Step 1: Open the Marks workbook on the Desktop. Step 2: In cell A10, enter the following: Highest marks (English). Step 3: In cell B10, type: =MAX( Step 4: Select cells C3 to C8. 85  UNIT 5 MAX, MIN AND COUNT FUNCTIONS ACTIVITY 3 Step 5: Close the bracket =MAX(C3:C8) Step 6: Press Enter. The result is displayed: Step 7: In cell A11, enter the following: Lowest marks (English). Step 8: In cell B11, type: =MIN( Step 9: Select cells C3 to C8. Step 10: Close the bracket =MIN(C3:C8) Step 11: Press Enter. 86  UNIT 5 MAX, MIN AND COUNT FUNCTIONS ACTIVITY 3 The result is displayed. Step 12: Use steps 1 to 6 to calculate the highest marks (French) in cell A12. Step 13: Use steps 7 to 11 to calculate the lowest marks (French) in cell A13. Step 14: Save your work. 87  UNIT 5 THE COUNT FUNCTION ACTIVITY 4 Activity 4: Calculate the number of students using the COUNT function Step 1: Open Marks file. Step 2: In cell A13, enter the following: Number of students. Step 3: In cell B13, type: =COUNT( Step 4: Select cells C3 to C8. Step 5: The following will appear: Step 6: Close the bracket =COUNT(C3:C8) Step 7: Press Enter. The number of pupils is 6. Step 8: Save your work. 88  UNIT 5 MODIFYING PAGE SETUP (METHOD 1) ACTIVITY 5 Activity 5: Modifying Page Setup (Method 1) Step 1: Open the Marks workbook on the Desktop. Step 2: Click the Page Layout tab. Step 3: Click on Page Setup arrow. Step 4: The Page Setup Dialog box opens. 89  UNIT 5 MODIFYING PAGE SETUP (METHOD 1) ACTIVITY 5 The Page Setup Dialog box opens. 5 6 7 8 Step 5: Select the Page Orientation (Portrait). Step 6: Select the appropriate percentage to fit a worksheet onto one page for printing. Step 7: Select the appropriate paper size for printing. Choose A4 for paper size. Step 8: Click ok. 90  UNIT 5 MODIFYING PAGE SETUP (METHOD 2) ACTIVITY 6 Activity 6: Modifying Page Setup (Method 2) Step 1: Using the Marks workbook, click the file tab. Step 2: Click Print. Step 3: Click Page Setup. 91  UNIT 5 MODIFYING PAGE SETUP (METHOD 2) ACTIVITY 6 Step 4: The Page Setup Dialog box opens. 5 6 7 8 Step 5: Select the Page Orientation (Portrait). Step 6: Select the appropriate percentage to fit a worksheet onto one page for printing. Step 7: Select the appropriate paper size for printing. Choose A4 for paper size. Step 8: Click OK. 92  UNIT 5 PRINTING A WORKSHEET You can print data, graph and charts in spreadsheet software. Follow the steps to learn how to print in spreadsheet software. ACTIVITY 7 Activity 7: Printing a worksheet Step 1: Open the Marks workbook on the Desktop. Step 2: Click the file tab. Step 3: Click Print. 93  UNIT 5 PRINTING A WORKSHEET ACTIVITY 7 The Print Preview appears as shown below: 94  UNIT 5 PRINTING A WORKSHEET ACTIVITY 7 Step 4: Input number of copies. Step 5: Click on Print. 95  UNIT 5 PRINTING A WORKSHEET ACTIVITY 7 NOTE TO TEACHERS: This activity can be done in small groups to give a demonstration of how to print using the printer attached to the computer. 96  UNIT 5 PRINTING ONLY A PART OF THE WORKSHEET ACTIVITY 8 Activity 8: Printing only a part of the worksheet Step 1: Open the Marks workbook on the Desktop. Step 2: Select cells A1 to E13. Step 3: Click the Page Layout tab. Step 4: Click Print Area. Step 5: Click on Set Print Area. Step 6: Click the file tab. 97  UNIT 5 PRINTING ONLY A PART OF THE WORKSHEET ACTIVITY 8 The Print Preview appears as shown below: Step 7: Click Print. 98  UNIT 5 REMOVING PRINT AREA ACTIVITY 9 Activity 9: Removing Print Area Step 1: Click the Page Layout tab. Step 2: Click Print Area. Step 3: Click Clear Print Area. 99  UNIT 5 EXTRA CHALLENGE 1. Open Excel. 2. Select blank workbook. 3. Enter the following data: 4. Adjust the width of Column A to fit the d ta. 100  UNIT 5 EXTRA CHALLENGE 5. Center aligns the text. 6. Save your work using the file name Total Sales. 7. Calculate Total Sales for the 4 customers in cells E2, E3, E4, and E5. Total sales= January sales + February sales + March sales 8. Calculate the Average Sales in cells B7, C7 and D7 for each month. 9. Calculate the Highest Sales in cells B8, C8 and D8 for each month. 10. Calculate the Lowest Sales in cells B9, C9 and D9 for each month. 11. Calculate the number of customers using the COUNT formulae. 12. Change the Page orientation to Landscape and adjust the scaling to 85%. 13. Set Print Area for cells A1 to E10. 14. Save your work. 15. Print the file with the help of our teacher. 101