G6LP-3MS Excel Formatiing-Formulas-Functions

Lesson Plan-1 Computer Science MS Excel Formatting, Formulas, Functions Topic-Format and Formulas 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. Use 6 types of cell formatting options available in Excel. 2. Use formulas in excel for performing calculations. 3. Understand different types of operators like arithmetic, and relational. 4. Use compound formulas with multiple operators. Resources 1. video https://www.youtube.com/watch?v=tG8svgkvyh0(9.09) 2. Use the eContent to show the animated demos of the lesson. Session Conduction Engage: Discuss what formatting would they like to add to cell content. Once they answer., explain how Microsoft Excel has several features that permit users to customize the way their data is displayed. We can format the data in various ways, like setting font color, size, alignment, borders, etc. Excel also lets us format monetary values by adding currency symbols. Date formats can be applied to data values. We can also specify the number of decimal places that we want to use, whether we want to use a thousand separator, and how we want to display negative numbers. Concept introduction: All cell content uses the same formatting by default, which can make it difficult to read a workbook with a lot of information. Basic formatting can customize the look and feel of a workbook, allowing us to draw attention to specific sections and making content easier to view and understand. Excel formatting helps in structuring and standardizing data that is required in order to prepare data for analysis.

Excel enables users to perform simple calculations such as finding totals for a row or column of numbers. Formulas and functions can be useful in complex situations, including calculating mortgage payments, solving engineering or math problems, and creating financial models. Formulas and functions drive almost everything interesting and useful we will ever do in a spreadsheet Concept Demo/Explanation: Discuss formatting cells can draw attention to important data or more accurately display the contents at hand, for example, adding $ to cells that contain values pertaining to prices or configuring cells that represent dates to a standard display of xx/xx/xxxx, increase decimal or decrease decimal to show more or fewer digits after the decimal point, set the background color of all selected cells, customize the way that we would like our values to be aligned, both vertically or horizontally. Next ask, how fast they can add 100 numbers. We use calculators for faster calculations. After data is formatted in Excel, we need to use it for performing calculations. Formulas and functions in Excel enable us to perform calculations Discuss what are formulas and operators like arithmetic or relational. Show how to write formulas using arithmetic operators for addition, multiplication, subtraction, or division. Use a relational operator and create a formula to find out if the student’s age is greater than 18. Concept Practice: Show how to format the font of the cells or the table with the help of the styles and format tab present in the home tab. Show how to format Excel tables using the six tabs found in the Format Cells dialog box. Apply number formatting like adding a percentage symbol to a column, adding decimal places, and specifying how negative numbers are to be displayed. Show what happens when we select the wrapped text, shrink to fit, and merge cells options in the Alignment tab. Add border, two-color patterns, or shading as a background color. Show what happens when a locked option or hidden option is selected from the protection tab. The steps for MS Excel 2016 and 2019 are the same. Show how to find out the total marks obtained by a student using formulas. Write a formula to find out compound interest. Show what kind of errors we can get while performing calculations. Ask students to complete the Do-It-Yourself activities of the chapter. Optional Activity: Students can watch the video https://www.youtube.com/watch?v=tG8svgkvyh0 (9.09).

Practical Application: Complete the lab activity. 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 to https://www.simonsezit.com/article/basics-of-formulas-and- functions-in-microsoft-excel-2016/ find out more about the formulas in Excel 2016. Evaluation After completing the lesson solve the exercises given in the book.

Lesson Plan-2 Computer Science MS Excel Formatting, Formulas, Functions Topic: Functions 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. Edit or delete a formula used in a cell. 2. Get familiarized with function structure in Excel. 3. Use at least 10 built-in functions of Excel 2016 Resources 3. video https://www.youtube.com/watch?v=DksYxHyCQEc (4.55) 4. Use the eContent to show the animated demos of the lesson. Session Conduction Engage: Recap the previous session. Concept introduction: A function in Excel is a pre-set formula, that helps perform mathematical, statistical, and logical operations. When we want any function to use, all we have to do is enter an equal sign (=) in the cell, followed by the name of the function and the cell range it applies to. Excel includes many common functions that can be used to quickly find the sum, average, count, maximum value, and minimum value for a range of cells. Concept Demo/Explanation: Excel lets us edit our formulas and delete them if required. Edit a formula written in the previous class by selecting it. In order to work correctly, a function must be written a specific way, which is called syntax. The basic syntax for a function is the equals sign (=), the function name (SUM, for example), and one or more arguments inside (). Arguments contain the information we need to

calculate. Give example =SUM (A1:A9). Explain what we provided here as arguments to calculate the sum of the values in the cell range A1:A9. Explain some of the built-in functions. Create a workbook and add columns like names, state, country, and pin code. Enter some data. Join the columns using the Textjoin function. Use the switch function and find out if the day is a weekend or not. Discuss how we can carry out more than one comparison in our formula or test multiple conditions instead of just one by using AND, OR, and Not logical functions. Show how to use AND function in excel by finding if the value in the column is between 100 and 200. Concept Practice: Show how to insert a simple function from the insert function option Formula tab. Find out the total sum by adding the cell range as a reference in the function argument dialog box. Use the example in the book to demonstrate how to add logical functions in Excel 2016. Use IFS and check if the student has received more than 35 marks to declare him passed or failed. The steps for Excel 2016 and 2019 are the same. Ask students to complete the do-it-yourself activities. Students can find additional insights from the Amazing Fact and notes sections of the chapter. Optional Activity: Students can watch the video from the resource sections. Practical Application: Complete the lab activity. 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://www.myexcelonline.com/blog/top-excel-formulas-function- examples-to-get-better-at-microsoft-excel/ and discuss their findings with the teacher. Evaluation After completing the lesson solve the exercises given in the book.