G6LP-1Working With Data in MS Excel

Lesson Plan - 1 Computer Science Working With Data in MS Excel Topic-Cell References and Sorting Class: Period: Mode: Classroom/Lab Teacher: ____________________________________________________________________________________ Learning Support Assistant: ____________________________________________________________ S.M.A.R.T. Learning Objectives By the end of this session, students will be able to: 1. Understand what is cell address and cell reference. 2. Use three types of cells references 3. Sort numeric, text, date, and time data in ascending and descending orders. Resources 1. video https://www.youtube.com/watch?v=qx4L11ijOuM&t=163s (0.00 to 6.25) 2. Use the eContent to show the animated demos of the lesson. Session Conduction Engage: Ask students how they are addressed by their friends and family members. Just like their name is used for addressing them. We can call an Excel cell using cell reference. A cell reference or cell address is a combination of a column letter and a row number that identifies a cell on a worksheet. Each cell can be located or identified by its cell reference or address, e.g., B5. Concept introduction: In Excel, a cell reference (example A1) is a combination of column name (i.e., A) followed by a row number (i.e., 1). While writing formulas, the user can refer to cells/ range of cells using cell reference. For example, suppose we have the following data in cell A1 = 10 and cell A2 = 5. Let’s say we type the formula as =A1+A2. Then Excel will calculate the sum of values present in those cells which are 10+5 = 15. When a cell reference refers to more than one cell, it is called range. E.g., A1:A8 indicates the first 8 cells in column A.

Sorting is a feature in MS Excel that helps us to organize data. We can sort a text column in alphabetical order (A-Z or Z-A). We can sort a numerical column from largest to smallest or smallest to largest. We can also sort a date and time column from oldest to newest or newest to oldest Concept Demo/Explanation: Discuss the three types of cell references. Relative Cell References are the most widely used type of cell reference in formulas. Relative cell references are basic cell references that adjust and change when copied or when using AutoFill. Situations arise in which the cell reference must remain the same when copied or when using AutoFill. A dollar symbol, when added in front of the row and column number, makes it absolute i.e., stops the row and column number from changing when copied to other cells. Mixed references are of two types i) Absolute row and Relative column ii) Absolute column and Relative row. Use the example of the book to show how references work. Use sheet references to copy the cell references of one worksheet into another. Explain what is sorting using real-life situations where sorting is used. For e.g., names are called alphabetically or roll number-wise, the name appears alphabetically in a telephone directory. Ask students where else they see data sorting based on alphabets or numbers. Concept Practice: Create a worksheet with item names, the number of items sold, and unit price. To calculate the total sales for each item, we need to multiply the price of each item by its quantity of that. Write the formula for the first item then copy the formula to the rest of the cell using relative reference. Show how to use absolute reference for finding the commission amount a salesperson will get by fixing the commission percentage as 20% multiplied by the total sale they have done. Create a multiplication table from 1 to 5 like the following using mixed references. 12345 112345 2 2 4 6 8 10 3 3 6 9 12 15 4 4 8 12 16 20 5 5 10 15 20 25

Use text and numeric columns of a worksheet and show how to sort data using the sort and filter option from the Home tab of Excel 2016. Take a date column and show how sort and filter options changes newest to oldest and vice versa. Optional Activity: Students can watch the video from the resources section. Practical Application: Complete the do-it-yourself and lab activities. Home Assignments 1)Revise the topic covered. 2) Practice the interactive exercises in Edusoft Smart App. 3) Solve any additional exercises on the playground.edusoft.co.in. Guided Assignment Students can visit https://edu.gcfglobal.org/en/excel2016/relative-and-absolute-cell- references/1/ to find out more examples of cell references in Excel 2016. Evaluation After completing the lesson solve the exercises given in the book.

Lesson Plan - 1 Computer Science Working With Data in MS Excel Topic: Filter, Style, Conditional Formatting, Pivot Tables Class: Period: Mode: Classroom/Lab Teacher: ____________________________________________________________________________________ Learning Support Assistant: ____________________________________________________________ S.M.A.R.T. Learning Objectives By the end of this session, students will be able to: 1. Create a custom sort in excel. 2. Filter data in excel. 3. Apply pre-designed table styles. 4. Highlight a set of data using conditional formatting. 5. Create a pivot table to calculate, summarize, and analyze data. Resources 1. video https://www.youtube.com/watch?v=qx4L11ijOuM&t=163s (6.25 onwards). 2. video https://www.youtube.com/watch?v=nR5TVVEq0Zg (2.52). 3. Use the eContent to show the animated demos of the lesson. Session Conduction Engage: Recap the previous session. Ask students if they have to sort by weekdays (Mon to Sun) or month-wise (Jan- Dec) in Excel is that possible. Sometimes, we need to sort by an irregular set of terms, and when that happens, we need to create a custom sort in Excel. Concept introduction: Sorting is easy in Excel. We just click the option and Excel does the rest. However ascending and descending sorts, won’t always be adequate. For instance, what if we want to sort by the days of the week: Monday, Tuesday, Wednesday, and so on? For such scenarios, Excel includes a custom sort feature. We can create a unique sort order, such as the days of the week. The filter function in Excel is used to filter a range of data based on the criteria that we specify. When data is filtered, only rows that meet the filter criteria will display and other rows will be hidden.

A PivotTable is an interactive way to quickly summarize large amounts of data. PivotTables are used to analyze numerical data in detail and answer unanticipated questions about our data. Concept Demo/Explanation: A custom sort is the one we define. For instance, we might need to sort T-shirts by small, medium, large and extra-large. Or sort temperatures by cold, warm and hot. Or regions by north, south, east, and west. Create a column with weekdays. Select the column and choose Custom Sort from the sort and filter options of the Home tab. In the resulting dialog, From the Order dropdown, choose Custom Sort, which will open a new dialog bog select a list from the customs list or add a new list by adding items in the list entries. Once the custom list is selected, select the order and the data will be sorted. Explain the benefits of filtering. With filtered data, we can then copy, format, print, etc., our data, without having to sort or move it first. We can use filtering to find students who scored more than 80 or find the sales detail of a particular item. We select the column or multiple columns where we want to apply the filter. Show when we choose the filter command from the Home tab small arrows appear on the columns We have created our worksheet with information like student marks detail or item sold details. Now we need to find details like who scored the highest and lowest marks, which item was sold the most, etc. We can find out these pieces of information easily using a pivot table. Pivot tables allow us to extract the significance from a large, detailed data set. Concept Practice: Refer to the steps of books to filter data. Style an excel table using the Styles option on Format as Table. Show how to make a cell appear bold only when the value of the cell is greater than 80. Highlight particular text values with yellow colour using conditional formatting. Highlight duplicate values. Enter some values in a column like 10,30,40,30,50,60,50 and then apply data scales as conditional formatting. Create a pivot table for any of the worksheets. The steps for MS Excel 2016 and 2019 are the same. Optional Activity: Students can watch the video from the resource sections. Practical Application: Complete the do-it-yourself and lab activities. Home Assignments 1)Revise the topic covered. 2) Practice the interactive exercises in Edusoft Smart App. 3) Solve any additional exercises on the playground.edusoft.co.in.

Guided Assignment Students can visit https://support.microsoft.com/en-us/office/highlight-patterns-and- trends-with-conditional-formatting-eea152f5-2a7d-4c1a-a2da-c5f893adb621 and https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet- data-a9a84538-bfe9-40a9-a8e9-f99134456576 and discuss their findings with the teacher. Evaluation After completing the lesson solve the exercises given in the book.