IT Spark Class- 6 Flipbook

 When the game starts, the beetle will keep falling randomly from the top and frog should eat it. Falling of the beetle will go on infinitely until the game is over. So, add Control block forever to the script. Forever Block Forever block has logically 3 parts:  Moving the beetle 7 steps down: Use Motion block move with value 7 for steps.  Checking if the beetle touches the dark green colour of the frog.  Checking if the beetle touches the light green colour of If the beetle touches the frog  Add Sensing block touching color to the condition section of if and set the colour to the green colour of frog.  Add Motion block go to and set value of y to 160.  Add Operator block pick random to the value for x in go to block and set the values for random number range as - 200 to 200.  Since frog is successful in eating the beetle so score should be increased by 1. Add Data block change _ by. Select variable score from drop down list and set value for by as 1. If the beetle touches the ground  Add Sensing block touching color to the condition section of if and set the colour to the green colour of ground.  Add Motion block go to and set value of y to 160.  Add Operator block pick random to the value for x in go to block and set the values for random number range as -200 to 200. 50

 Since the beetle touches the frog life should decrease by 1. Add Data block change _ by. Select variable life from drop down list and set value for by to -1. Stop the game if all 5 lives expire  In the second if block, add another Control block if.  In the condition section of this newly added if, add Operator block =.  Before = add variable life from Data category and after = mention the value 0.  Once life is 0 the game should stop. Inside this if block, add Control block Stop and set its value to all from its drop down list. Stopping the game on space bar key press  Add Event block when ___ key pressed and select space from the dropdown list.  Add Control block Stop with value set to all. BEETLE The Complete Script FROG 51

Glossary : An object in the form of an image that constitutes and animation. Sprite Block : It is like an executable statement in the script. Script : A set of blocks arranged in a logical sequence for an animation. Event : An event is any action performed by user or program. Loop : Repeated execution of blocks in a script. Also called reiteration. Taking : Checking a condition and deciding the course of execution of Decision the statements in a script. Condition : An expression designed using comparison operator blocks. Variable : A named identifier for a value in a script or a storehouse for values. Quick Review  Sprites are arranged on the stage.Stage also plays the animation.  Sprites are the building blocks of an animation.  A Scratch script is a logical set of various blocks.  Blocks are arranged in various categories like Event,Motion,Control etc.  Event blocks are used to respond to various events.  Motion blocks are used in controlling the animation of the sprites on the stage.  Looks blocks help sprite say or think something or switch costume.  Control blocks provide tools for looping and decision making.  Sound blocks allow working with sounds and instruments.  Pen blocks are used to draw simple geometrical figures.  Operator blocks help in designing expressions for conditions to compare values.  Variables are used to store values during an animation. A. Choose the correct answer. b) games 1. Scratch is a visual tool to create: d) All of these a) demos c) animations 52

2. ____________ on stage together make your animation. a) blocks b) sprites c) scripts d) None of these 3. The script in Scratch is created by drag-dropping __________. a) blocks b) sprites c) backdrops d) None of these 4. An ______ is any action occurring at any point of time. a) animation b) script c) event d) reiteration 5. Another word for reiteration is ____________. a) while b) looping c) forever d) if-else 6. Blocks that help in decision making are __________ and ____________. a) if, then b) if, if-else c) else, then d) if, else B. Fill in the blanks. Looks, Library, Animation, Answer, Variable 1. Every ____________ in Scratch is treated as a separate project. 2. Sprites are arranged in various categories in Sprite ________. 3. Say and think blocks are found in _________ category. 4. A ____________ is the storehouse for a value at a time. 5. _________ block stores the value input by the user. C. Answer the following questions. 1. What is the difference between a block and sprite? ___________________________________________________________________ ___________________________________________________________________ ___________________________________________________________________ 2. How will you make a sprite move 100 steps. ___________________________________________________________________ 53

3. What is the basic difference between if-then and if-then-else block? ___________________________________________________________________ ___________________________________________________________________ 4. What is reiteration? What are the 2 types of loops provided by Scratch? ___________________________________________________________________ ___________________________________________________________________ 5. What is a variable? How will you create a variable in Scratch? ___________________________________________________________________ ___________________________________________________________________ 1. Add a sprite of your choice on the stage and create the script so that when the user clicks on it, make it play a sound. 2. Add two sprites on the stage which should move towards each other 10 steps at a time on down arrow key press and should move away 10 steps at a time on up arrow key press. 3. Add Star sprite on the stage. When user clicks on it the Star should blink (Show/ hide alternatively) and on space bar press it should stop blinking. 4. Accept two numbers from the user and display their product. 5. Using Pen, draw a Semi Circle and a Right angle triangle Teacher's Signature : __________________ Teacher's Remark : WEB https://en.wikipedia.org/wiki/Scratch_(programming_language) https://scratch.mit.edu/projects/31407152/ LINKS https://scratch.mit.edu/ Teacher’s Corner Dear teacher,please allow some tome to the students to exploreTips menu of Scratch. 54

5 MS Word: Mail Merge Dear Teacher, Yes Students, We want to send an It is possible to create invitation letter to our a letter with different friends. How can we addresses in MS Word. This feature is create a common letter called Mail Merge. with different addresses? Let's learn about it. We want to learn about it. Mail Merge Imagine that you have to send a letter to 50 people at different addresses. You have the letter drafted in MS Word. Can MS Word help you to generate desired number of copies of your letter with different addresses and names of the recipients? Answer is: Yes, the Mail Merge feature of MS Word helps you in doing so. Mail merge allows us to create documents which have most of the content similar with some varying data like recipient's Name, Address, Contact No., City, State, etc. How Mail Merge Works? Mail Merge works on the concept of following 3 components: Main Document: This document contains the common content that needs to be sent to multiple recipients. For example, a letter. Data Source: This is the document that contains all the varying data that needs to be inserted into (merged) the main document. For example, name address, city etc. This could be table in MS Word or a data set in a spreadsheet like MS Excel. Merged document: This is the final document, which contains the copies of the main document with varying data from the Data Source inserted at the desired locations in the main content. For example, one copy will contain the letter with recipient N K Sharma’s name, address and city while the other copy will have same letter with another recipient Ravi Kumar’s name, address and city of Ravi Kumar. Now, let us go through mail merging process, step-by-step. 55

First, create a data source as a simple 1 table in MS Excel (or MS Word or MS Access) with proper file name. Now, follow these steps: 1. In MS Word document choose Mailings tab and click on Start Mail Merge button. A list appears. Click on Step by Step Mail Merge Wizard option. Note Mail Merge tool can be used in different ways. In this course, you are going to learn about Mail Merge tool using ‘Step-by-Step Mail Merge Wizard’ option. 2. This will bring Mail Merge pane at the right side of the Word document window. Here, you can specify type of document you want to create. For example, select Letters option and click Next: Starting document at the bottom of the pane. 3. Choose Use the current document option in Select starting document section. Click on Next: Select recipients at the bottom of the dialog box. 23 4 5 Select Data Source 4. In this step, you can choose different options to 6 import recipient list. For example, select Use an existing list. 5. Click on Browse to import desired recipients list. The Select Data Source dialog box appears. 6. Select the data source here and click on Open button. 56

Note 4. If you choose Type a new list option then you will get a link Create to type/create a new list. Click on it. 5. A New Address List dialog box appears. Here, enter the data in the required fields and click on OK button. 6. The Save Address List dialog box appears. Select the location to save the file and give proper name to the list and click on Save button. Press Tab to move between fields. Click on New Entry to 5 6 4 enter a new row of data. 7. The Mail Merge Recipients dialog 7 box appears. It shows the fields to be merged and data in the fields. Click on OK button. Insert Merge Fields 8. Put cursor on the main document where you want to merge a field. On Mailings tab on the ribbon, in Write and Insert Fields section, click on Insert Merge Field button, a list of fields from selected data source opens. Click on required field to be merged. Repeat this process till you merge all the fields. 8 57

View Merged Data 9. Click on Preview Result button in Preview Results section on Mailings tab. 9 To view next or previous data click on Next Record or Previous Record buttons. Previous Record Next Record First Record Last Record 10. On the Mailings tab, in Finish 10 section, click on Finish & Merge button and select Edit Individual Documents option. 11. The Merge to Printer dialog 11 box appears. Click on OK button. This action will merge all the data (on the separate pages) in a single document. Now, you can print the document as per your requirement. Print the letter 12. Now, you can take the printouts of your letter with the merged data. Click on File → Print. Select the required print option and click on Print button. Note By using Mail Merge, you can also create the following: • A set of labels or envelopes • A set of letters, e-mail messages, or faxes • A set of coupons with unique numbers 58

Do it Yourself Suppose, you are going to celebrate your birthday on 15 July. • Make a list of your friends (in MS Excel) that you want to invite. • Create an invitation card in MS Word, to invite your friends to attend your birthday party. • Merge the list of your friends in the document and take the printouts. Glossary Data Source : Like an address book that contains recipients’ details in tabular form. Merge Field : Data item to be merged with the document from a data source. Quick Review  Mail merge is a useful tool that is used to create a document for multiple recipients.  Using mail merge we can create various documents like letters with different addresses, labels, email messages, fax messages and numbered coupons etc. A. Choose the correct answer. 1. _____________________ is used to create a document for multiple recipients. a) Mail Merge b) E- mail c) Mail document d) None of these 2. _________ is obtained after merging fields from data source with main document. a) Merge data b) Merge field c) Merged document d) All of these 3. ______________ contains data to be merged with the main document. a) Data Source b) Mail document c) Final document d) Merged document 4. On Mailings tab, in Preview result group, ___________ button is used to view the first record. a) Previous Record b) Main Record c) Next Record d) First Record 59

B. Fill in the blanks. Letters, Previous Record, Merge Field, Data Source, Next Record, multiple 1. In Mail Merge, _______________________ is like an address book that contains the data to be merged with the main document. 2. In Mail Merge, _______________________ is the data item which is to be merged with the document from a data source. 3. On Mailing tab, in Preview Result group, click on _________________ and ___________________ buttons to view next or previous data. 4. Mail merge feature helps us to create a document for _________ recipients easily. 5. ________ is one of the document types that we can create using mail merge feature. C. Answer the following questions. 1. What is the use of Mail Merge tool in MS Word? ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ 2. Write a few words about the following terms (in context of Mail Merge): i) Main Document: _____________________________________________________ ____________________________________________________________________ ii) Data Source:_________________________________________________________ ____________________________________________________________________ iii) Merge Field: _________________________________________________________ ____________________________________________________________________ iv) Merge Document: ____________________________________________________ ____________________________________________________________________ 3. Write the steps to insert merge fields in the document. ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ 60

4. Write the steps to merge all the data (on separate pages) in a document (in context of Mail Merge). ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ 5. Write the steps to view merged data (in context of Mail Merge). ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ Your school is going to organize annual sports meet. Create an invitation letter (in MS Word) to invite all the schools of your zone/district to participate in this event. 1. Create a list of the schools (in MS Excel) that you want to invite. Note: The merge fields should be: <school_name> <address>. 2. Merge the list of the schools with address in the document and take the print outs. Teacher's Signature : __________________ Teacher's Remark : WEB http://www.gcflearnfree.org/word2013/31/print http://www.gcflearnfree.org/word2013/29 LINKS Teacher’s Corner Teachers should encourage students to practice the concept thoroughly in lab. 61

6 MS Excel: Formatting, Formulas and Functions Dear Teacher, Yes Students, Does MS Excel also have You can format the cells formatting tools? We and their contents in have also heard that Excel Excel. Besides, you can has many features for perform different types calculations. We want to of calculations in it. Let's learn about them. learn about them. You already know that MS Excel is a spreadsheet program. Like MS Word and MS PowerPoint, MS Excel also provides a wide range of formatting tools. In this chapter, you will learn about formatting the data and cells to make them more readable and attractive. You will also learn how to use the formulas and functions to perform calculations on the data in the cells. In a MS Excel worksheet, text can be formatted in two ways. Formatting Cells In MS Excel window, on Home tab, various formatting tools are available for formatting the text and alignment. You have already learnt about some of the tools in MS Word and MS PowerPoint. The steps to format the text (in a cell) using the tools available on Home tab are: 1. Select the cell where you want to format the text. 2. Click on required tool available in Font or Alignment group. Let's have a look on the tools available in Font and Alignment groups. Change Change Change Set vertical Set text Wrap long text font type font size font colour alignment orientation into multiple lines Make the text Apply border Fill colour in Set horizontal Combine and center the bold, italics and to the selected the selected alignment contents of selected cells underlined cell(s) cell(s) in a new larger cell 62

Formatting Cells using the Format Cell Dialog box The Format Cells dialog box is a “one-stop” formatting source. From within that one dialog box, you can find the options for fonts, cell borders & shading, alignment within the cells, text direction, etc. The steps to format the text (in a cell) using the Format Cells dialog box are: 1. Select the cell(s) where you want to format the text. 2. On Home tab, click on the dialog box launcher in Font or Alignment group. Or, right-click on the selected cell(s) and select Format Cells option. The Format Cells dialog box appears. Now, click on the required tab and choose the appropriate command. A. Number tab 1 3 2 4 I. Formatting the Numbers 1. Click on Number tab. 2. Select Number in the Category section. 3. On the right side, there is an option to set the decimal places (which means how you want to display the numbers in terms of decimal places) and set commas as separators (according to the place value). 4. In Negative numbers section, select the format in which you want negative numbers to be displayed. An example is given below. Here decimal places is set as ‘2’. Before setting the decimal values After setting the decimal values 63

II. Formatting the date and time Similarly, the date and time can also be formatted. Select the cell(s) and select Date and Time options (one by one) in Category section. Now, set the required format and click on OK button. Formatting date Formatting time B. Alignment tab Alignment tab is used to position text and numbers in the cells, change the orientation and specify text control in cells. 1. Click on Alignment tab. 2. Now, click on the required option (for alignment and orientation). • Text alignment (2i): To align the text horizontally, click on Horizontal list box and choose the required option or to align the text vertically, click on Vertical list box and choose the required option. 1 2i 2ii 2iii 3 Horizontal alignment Vertical alignment • Orientation (2ii): Degree of orientation can be set through the Orientation section. Text and date Setting orientation Text and date at at 0° orientation to 45° 45° orientation 64

• Text control (2iii): There are three options (in the form of check boxes): Wrap text- it wraps the text in the selected cell, Shrink to fit- it decreases the font size of the text in a cell until all the contents of the cell can be displayed and Merge cells- it combines two or more selected cells into a single cell Click on the required check boxes to select. 3. Click on OK button. Wrap text Merge cells Shrink to fit text C. Font tab Font tab is used to set the typeface (for 1 example, Times New Roman), along with 2i 2ii 2iii its attributes (point size, font style, underlining, color, and effects). 1. Click on Font tab. 2iv 2. Now, click on the required option. • Font (2i): It allows you to choose a 2v font (typeface), by clicking a name 2vi in the list. 2vii 3 • Font style (2ii): It allows you to set the font style i.e. Regular, Italic, Bold and Bold Italic. • Size (2iii): It allows you to set the font size. • Underline (2iv): It allows you to set an underline (of different types). • Color (2v): It allows you to choose the font colour. • Normal text (2vi): You can select this check box to set the font, font style, size and effects to the Normal style. • Effects (2vii): There are three check boxes. Strikethrough- to draw a line through selected text or numbers, Superscript- to format the selected text or numbers as superscript and Subscript- to format the text or numbers as subscript. Click on the required check boxes to select. 3. Click on OK button. 65

D. Border tab 2i 1 2iii Border tab is used to put a border in different styles around a single cell or a range of cells. 1. Click on Border tab. 2. Now, click on the required option. Note 2ii 3 If you have selected only single cell, then some buttons on the Border tab will be unavailable (dimmed). This is because these settings are only applicable when you apply borders to a range of cells. • Style (2i): Select any one of the line style from the list. • Color (2ii): Click on the drop-down arrow and select the desired colour. • Presets and Border (2iii): Click on the buttons (as per your requirement) under Presets or Border. This displays a line with your settings in the sample region. Note 1. If you want to remove a specific border, click the button for that border a second time. 2. If you want to change the line colour or style, click the style or colour that you want, and then click the button for the border again. 3. Click on OK button. Adding border to a range of cells E. Fill tab Fill tab is used to set the background colour of the selected cells. You can also use the Pattern list to apply two-color patterns or shading for the background of the cell. 1. Click on Fill tab. 2. Now, click on the required option. 66

• Background Color (2i): Select background colour from the colour grid. • Pattern Color and Pattern Style (2ii): Select colour from Pattern Color drop- down list and pattern style from Pattern Style drop- down list. • Fill Effects (2iii): Click on Fill Effects button to apply two-color patterns or shading for the background of the cell(s). • More Colors (2iv): Click on More Colors button to get more colour options. • Sample (2v): It shows the sample of the selected colour and pattern. 3. Click on OK button. 1 2i 2ii 2iii 2iv 2v 3 F. Protection tab 2i 1 2ii Protection tab provides two options to protect your worksheet data and formulas: Locked and 3 Hidden. Click on Review tab → Change group → Protect Sheet 1. Click on Protection tab. button and follow the instructions to complete the steps to lock the worksheet for various purposes. 2. Now, click on the required option. (Note: Take help of your teacher for detail). • Locked (2i): By default, this is turned on. When this option is turned on, following can’t be done:  Typing the data in an empty cell.  Moving the cell.  Resizing the cell.  Deleting the cell or its contents. 67

• Hidden (2ii): By default, all cells in a worksheet have the Hidden option turned off. If you turn on this option (and the worksheet is protected) the formula in a cell does not appear in the formula bar. However, you do see the results of the formula in the cell. 3. Click on OK button. Do it Yourself Open MS Excel and take a new workbook. Now, enter data and format the data cells as per the guidelines given below: 1. Create a time-table of your class (use cells from A1 to J8). 2. Select the cells from A1 to J1, merge them and give a heading ‘TIME TABLE’. 3. Resize the columns width (according to the maximum data width in the cells). 4. Select the data cells and set the font type ‘Calibri’ and font size ‘14’. 5. Select the cells (from A1 to j8) and give a colourful pattern background. 6. Select the cells from B2 to J2 and apply center (vertical) alignment. 7. Select the data cells and give colourful line border. 8. Select the cells range F2 to F8 and merge them. 9. Save the file with the file name ‘yourname_class_formatting’. See the figure for reference. Formula in MS Excel Mathematical formulas and functions are main features of MS Excel. A formula is an equation that performs a calculation. Like a calculator, Excel can execute formulas for addition, subtraction, multiplication and division. Before proceeding further, let's learn about some basic mathematical operators that are used to write a formula. Arithmetic operators To perform basic mathematical operations, arithmetic operators are used. For example, + (plus sign) for addition, – (Minus sign) for subtraction, * (asterisk sign) for multiplication, / (forward slash) for division, % (Percentage sign) for percentage and (^) carat for exponents. 68

Note Example, order of calculation Precedence of basic mathematical operators: The equation: =6 + 5 - 4 * 3 / 1. ( ) Parenthesis (evaluate items in parenthesis) 2, will be calculated in the 2. % Percentage following order: 3. ^ Exponent 4. *, / Multiplication, division 1. Multiplication: 6+5-12/2 5. +, - Addition, subtraction 6. =, <>, <=, >= Comparison operators 2. Division: 6+5-6 The operators that are equal in precedence are performed left to right. 3. Addition: 11-6 4. Subtraction: 5 Answer: 5 Do it Yourself 1. Take help of your teacher or Internet and try to know the precedence of other operators in Excel formulas. 2. Find the output of the following equations in MS Excel and write the answer in the spaces given below: i) 9–3+5*7 = _____ ii) (9–5)/(2+7) = _____ iii) 5^3*5–6 = _____ iv) 3*4-18/4 = _____ v) (8+9)*2 = _____ vi) 7–7/3 = _____ Comparison/Relational Operators To compare two values, comparison operators are used. The result is always a logical value i.e. either True or False. For example, = (Equal to), > (Greater than), < (Less than), >= (Greater than or Equal to), <= Less than or Equal to) and <> (Not Equal to). Writing simple formula  Every formula must begin with an equal sign ‘=’.  There must be an operator(s) to operate on the data.  The data, which can be text, number, date, time or a cell address. 69

Let's see some examples of writing simple formulas: A. 1. Select the cell where the answer will appear. 2. Type the equal sign = with the formula that you want Excel to calculate and press Enter key. For example, =5 + 5. B. 1. Enter data in a worksheet in 2 columns J, K, L, M and N (as shown in the figure). 3 2. Click in the cell O10 below the Note ‘Total’ heading and type the formula. Specify the cell address While creating a formula using cell with the arithmetic operator. reference, you can use mouse to select the cell to insert it’s address in the formula, For example, =L10+M10+N10 . instead of typing. In this example, type ‘=’, click on L10 cell, 3. Press Enter key. You will get the type ‘+’, click on M10 cell, type ‘+’, click answer in the cell. on N10 to create formula to get addition of the values of these cells. 4. To calculate the total marks of other students, place the cursor on 46 the bottom-right corner of the formula cell. The cursor changes into a solid plus sign ‘+’. 5. Click and drag the mouse till the end of the list. 6. Release the mouse button. Amazing Fact To perform a calculation using a cell address (in an equation) to represent the value in a cell, is called cell reference. You will learn more about cell reference in the next chapter. C. 1. Enter a value in A1 cell. For example, 12. 2. Click in B1 cell and type the formula =A1*5. 3. Press Enter key. Using Compound Formula In a compound formula, more than one operator are used. Let's learn about it with the help of an example (with mouse click). 1. Enter values in the worksheet (see the figure given on next page). 70

2. Click in B4 cell and type ‘=’, click on B1 cell, type ‘*’, click on B2 cell, type ‘*’, click on B3 cell (with mouse click in the cells, their respective cell address are displayed in the formula), type ‘/’ and 100. Press Enter key. 3. The calculated result is displayed in the B4 cell. Do it Yourself Open MS Excel and create a worksheet as per the guidelines given below: 1. Create four headings in the first row and enter the respected data in first three columns. See the figure for reference. 2. Click in D2 cell to calculate the total value of the item and enter formula = B2*C2 and press Enter key. 3. Copy the same formula in the cells D3 to D6 ↓ (using mouse/drag and drop). 4. Save the file with the file name ‘yourname_class_formula’. Amazing Fact If the value in any of the cells is changed, whose address is used in the formula, the result in the corresponding cell changes automatically. Note Error Results You may get a few errors while performing calculations. Let's see some of the error results with their reason. 1. ##### : The column is not wide enough to display the value. 2. #NAME? : The text is not recognized in a formula. 3. #VALUE! : The formula has the wrong type of argument. 4. #DIV/0! : The formula tries to divide a number by 0 or an empty cell. 5. #REF! : The formula refers to a cell that is not valid. 6. #N/A : Data is not available. Edit a Formula 1. Click on cell (that contains the formula) you want to edit. 2. Insert the cursor in formula bar and edit the formula as desired. You can also double- click the cell to view and edit the formula directly from the cell. 3. When finished, press Enter key or click on Enter   command. 71

Delete a Formula When you delete a formula, the resulting values of the formula is also deleted. However, you can instead remove only the formula and leave the resulting value of the formula displayed in the cell. To delete formulas along with their resulting values, do the following: 1. Select the cell or range of cells that contains the formula. 2. Press Delete key on keyboard. To delete formulas without removing their resulting values, do the following: 1. Select the cell or range of cells that contains the formula. 2. On Home tab, in Clipboard group, click on Copy option. 3. Click on the drop-down arrow on Paste and select Paste Values option. Functions in Excel Functions are Predefined Formulas that perform calculations by using specified values called arguments in a particular order or structure. When you need to do calculations in a larger range of cells, to enter formulas repeatedly becomes difficult and time consuming. Functions can be used to perform simple or complex calculations. Structure of a Function: A function begins with an equal sign ‘=’, followed by the function name, an opening parenthesis ‘(’, the arguments for the functions separated by commas and a closing parenthesis ‘)’. Note Ÿ An argument in a function is the value that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Ÿ Common arguments that are used within functions include numbers, text, cell references, logical values such as TRUE or FALSE, error values such as #N/A and names. Arguments can also be constants, formulae or other functions. The argument, you designate, must produce a valid value for the argument. Ÿ An argument tool tip with the syntax and arguments appears as you type the function. For example, click in a cell and type ‘= SUM(’ and the tool tip will appear. Tool tips appear only for built-in functions. Built-in Functions There are lots of functions available in Excel. These functions fall under different categories such as Text, Mathematical, Trigonometry, Financial, Date and time, etc. 72

Let’s have a look at some functions Sum : This is the most popular function. The SUM function takes all the values in each of the specified cells and totals their values. Syntax: =SUM (value1, value2, etc.) // =SUM(range of cells) Example =SUM (15, 25, 65) Output=105 Average: This function is used to find the average of the specified data. Syntax: =AVERAGE (value1, value2, etc.) Example =AVERAGE (10, 20, 30) Output=20 Product : This function is used to find the product of passed values. Syntax: =PRODUCT (value1, value2, etc.) Example =PRODUCT (10, 25, 2) Output=500 Max : This function is used to find the largest value from the range of values. Syntax: =MAX (value1, value2, etc.) Example =MAX (15, 25, 65) Output= 65 Min : This function is used to find the lowest value from the range of values. Syntax: =MIN (value1, value2, etc.) Example =MIN (15, 25, 65) Output=15 Round : This function is used to round the decimal values from the specified digits. Syntax: =ROUND (decimal value, no. of digits) Example =ROUND (1525.24876, 2) Output= 1525.25 Sqrt : This function is used to find the square root of passed number. Syntax: =SQRT (value to find square root) Example =SQRT (15625) Output= 125 Power : This function is used to find power value of passed values such as base and exponent. Syntax: =POWER (decimal value, round-number) Example =POWER (15, 4) Output= 50625 Lower : This function is used to convert the text into lower case (Small letter). Syntax: =LOWER (“Text”) Example=LOWER (“Edusoft IT Solutions”) Output= edusoft it solutions Upper : This function is used to convert the text into upper case (capital letter). Syntax: =UPPER (“Text”) Example =UPPER (“Edusoft IT Solutions Pvt. Ltd.”) Output= EDUSOFT IT SOLUTIONS PVT. LTD. Today : This function is used to display the current date in the selected cell. The syntax is: =Today () Output= 12-10-18 73

Using Simple Function Let's learn about using a simple function with help of an example. Let's take SUM() function. The steps to use a function are: Shortcut 1. Click in the cell where you want to display To display Insert Function dialog the sum of the numbers. For example, F4. box: Shift+F3 2. Click on Formula tab. 23 3 1 4 5 6 3. In Function Library group, click on Insert Function tool. The Insert Function dialog box appears. 4. Select SUM function and click on OK button. The Function Arguments dialog box appears. 5. Type the argument in Number1 text box i.e. the range of the cells to find their sum. For example, C4:E4. 6. Click on OK button. The result appears in the selected cell. To calculate the total of the marks of other students, place the cursor on the bottom- right corner of the formula cell. The cursor changes into a solid plus sign ‘+’. Now, click and drag the mouse till the end of the list and release the mouse button. 74

Do it Yourself Open MS Excel and create a worksheet as per the guidelines given below: 1. Create a worksheet as shown here. 2. Click in the cell G4. On the Formula tab, click on Insert Function tool. 3. Select Average in the dialog box and click on OK button. 4. In the Function Arguments dialog box, input the range C4:E4 in Number1 text box. 5. Drag and copy the formula in other cells. 6. Save the file with the file name ‘yourname_formula_av’. Glossary Formatting : This is a process to make the data and cells more readable and attractive. Formula : A formula is an equation that performs a calculation. Function : A function is a predefined formula that performs calculations by using specified values called arguments in a particular order or structure. Quick Review  The Format Cells dialog box is a “one-stop” formatting source.  Alignment refers to the position of data in a cell in Excel.  Arithmetic operators are used to perform basic mathematical operations.  A formula and function begins with an equal sign ‘=’.  An argument in a function is the value that a function uses to perform operations or calculations. 75

A. Choose the correct answer. 1. In a ________________ formula, more than one operators are used. a) Compound b) Relational c) Arithmetic d) None of these 2. ______________ error is displayed when the text is not recognized in a formula. a) #Value! b) #Name? c) #N/A d) All of these 3. To display the current date in the selected cell, ___________ function is used. a) Date( ) b) Today( ) c) Time( ) d) All of these 4. To find the lowest value from the range of values, ___________ function is used. a) Below( ) b) Lower( ) c) Small( ) d) Min( ) 5. To get the sum of three values in a cell, ________ function is used. a) Sum( ) b) Total( ) c) Plus( ) d) None of these B. Fill in the blanks. #N/A, =, Relational, Average( ), Format Cells 1. The ______________ dialog box is a ‘one-stop’ formatting source for the cells in a worksheet. 2. To compare two values in Excel, _____________ operators are used. 3. A formula and a function in Excel begins with an ____________ sign. 4. __________________ function is used to find the average of the specified data. 5. ____________ error is displayed when the data is not available in the cell. C. Tick () the correct statement and cross () out the wrong one. 1. Fill tab of Format Cells dialog box is used to set the background colour of the selected cells. 76

2. To protect a worksheet, Protection tab of Format Cells dialog box provides the options. 3. ‘<’ operator is used to find the greater value out of two. 4. Format Cells dialog box contains different tabs e.g. Number, Alignment, Font, etc. 5. ‘Round( )’ is an example of built-in function in Excel. D. Answer the following questions. 1. Name any five formatting tools available on Home tab in Excel. ________________________________________________________________________ ________________________________________________________________________ 2. Write the steps to format the numbers in a cell in a worksheet. ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ 3. What are the uses of Alignment and Font tabs in Format Cells dialog box in a worksheet? ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ 4. Write the steps to apply a colourful border to a cell in a worksheet. ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ 5. What is a formula in Excel? ________________________________________________________________________ ________________________________________________________________________ 6. Name the operators that are used for arithmetic calculations in Excel. ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ 77

7. What is a function in Excel? ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ 1. Make a list of the students in your class with their birthdays and addresses. Add border and pattern. 2. i) Create a worksheet based on the last T-20 cricket world cup match tally. See the following figure for reference: ii) Add record of at least 8 teams. iii) In the cell H1 add a heading: Win%age. iv) Add border and background colour to the headings. v) Click on cell H1 and write a formula to calculate the winning percentage of each team. [Hint- Won/Match Played x 100 (give cell address instead of headings in the formula)] vi) Copy the formula to other cells. vii) Save the file with the file name ‘yourname_class_la’. 3. Create a marks sheet of 10 students of your class. Enter their roll no., name, subjects and calculate the total marks and percentage. Add border and pattern. Teacher's Signature : __________________ Teacher's Remark : WEB http://www.dummies.com/how-to/content/how-to-find-the-excel-2013- formatting-toolbar-butt.html LINKS http://www.gcflearnfree.org/excel2013/13 http://www.excel-pratique.com/en/course/excel_comparison_operators.php http://www.excel-easy.com/examples/compound-interest.html Teacher’s Corner Teacher should explain the significance of functions using real life, simple examples. 78

7 MS Excel: Working with Data Dear Teacher, Yes Students, Now, we are familiar In this chapter, we with MS Excel. We will learn about some want to learn about advanced features like other features in Excel. cell referencing, sorting and filtering. In previous chapter, you have learnt about basics of formatting, formulas and functions in Excel. Let's learn more about cell reference in formulas and functions and some advanced features. Cell Reference Cell reference is also known as cell address. It identifies the location of a cell in a group of cells in the worksheet. These cell addresses are used in formulas and functions. Types of Cell Reference Cell References are of three types: 1. Relative Cell Reference 2. Absolute Cell Reference 3. Mixed Cell Reference Cell containing function 1. Relative cell reference to calculate sum of values of cells A1 to A5. Relative cell reference is a default cell Figure 1A reference. If you move or copy a formula to another cell, the cells referred to in the formula will change accordingly. Excel adjusts the cell reference relativity to the new cell, in which the formula is pasted (figure 1A and figure 1B). In figure 1A, SUM function is used in cell A6 to get the sum of the values from A1 to A5 i.e. ‘=SUM (A1:A5)’. In figure 1B, function of A6 cell is copied to cell B6 to get the sum of values from B1 to B5. The cell references is changed to B1: B5 i.e., ‘=SUM (B1:B5)’. 79

2. Absolute cell reference In absolute cell reference if you move or copy the formula to another cell, the cells referred to in the formula will not change. Excel does not Cell B6, where adjust the absolute cell reference. To make a function is copied. reference absolute, add a ‘$’ (dollar) sign in front of both, the columns letter and the row numbers Figure 1B (see figure 2A and figure 2B). In figure 2A, formula in cell C4 is ‘=B4*$A$4’ and after copying the formula in cell C5 it is ‘=B5*$A$4’. In cell C5 formula, reference of cell A4 is not changed. Cell containing the formula to Formula is copied calculate the amount after discount. to cell C5. Figure 2A Figure 2B 3. Mixed cell reference Mixed cell reference consists of both relative and absolute cell reference. Excel adjusts the relative part of the reference while the absolute part is not adjusted. In a mixed cell reference, only the row or only the column remains fixed in a formula. To make a mixed reference, add a dollar ($) sign in front of only the column name or the row number. See figure 3A and 3B. Formula is copied to cell C4. Cell containing the formula to calculate the amount after discount of 0.15. Figure 3A Figure 3B Cell reference of another worksheet Cell reference of one worksheet can also be used in another worksheet. It can be done in two ways: 80

A. Using Copy-Paste: Select and copy the formula/ function of a cell in a worksheet and paste it in another worksheet. B. Using Sheet Reference: In this method, sheet number, exclamation mark and cell address are used. For example, on sheet1 you have a table of the marks of 1st term of class 6 students. On sheet2 you have marks of 2nd term. To get the grand total of 1st and 2nd term marks on sheet2, click on G19 cell on sheet2 and type the formula i.e. =F19+Sheet1!O10 and press Enter key. Do it Yourself You must have applied basic mathematical formulas on numbers. Do you know, y o u c a n perform addition on text data also? ‘&’ operator (symbol) is used for addition of text values. Let’s do it with the help of the following activity: 1. In a worksheet, type text values in the cells A1 and B1. e.g., “Edu” in A1 and “soft” in B1. 2. Click on the cell C1 and type the formula =A1&B1 and press Enter key. 3. Observe the output and write here- _______________________ Data Sorting Sorting is used to arrange the data in ascending or descending order. Sorting data can be done for alphabets, numbers, date and time, cell color, font color or icon set. Sort operations can be done for both columns and rows. The steps to sort the data are: 1. Select the required data that you want to sort. 2. On Home tab, in Editing group, click on Sort and Filter. 3. Select the required option. The data is sorted accordingly. 2 Note 3 When you select the entire range of data/table for sorting, 1 the options in the Sort & Filter menu is displayed according to Data is sorted according the type of data in first column to the option selected in of the range. The data is sorted Sort & Filter menu. according to the values in first column of the data/table. 81

Excel displays the options in Sort & Filter menu as per the type of data, selected in the worksheet. To sort the data according to a particular column in the table, do the following: Sorting according to the Text data 1. Select the required range of cells. 2. On Home tab, in Editing group, click on Sort and Filter. 3. Click on Sort A to Z or Sort Z to A option. 4. The Sort Warning dialog box appears. Select Expand the selection option and click on Sort button. The data (in the table/range) is sorted according to the selected column. Sort according to the Numeric data 1. Select the range of cells containing the data. 2. On Home tab, in Editing group, click on Sort and Filter. 3. Click on Sort Smallest to Largest or Largest to Smallest option. 4. The Sort Warning dialog box appears. Select Expand the selection option and click on Sort button. The data (in the table/range) is sorted according to the selected column). 82

Using Custom Sort The steps to use Custom Sort option are: 1. Click on any cell in the table/range. For example, A19. 2. On Home tab, in Editing group, 2 click on Sort and Filter. 3 45 3. Click on Custom Sort option. 1 7 4. The entire database is selected and the Sort dialog box appears. 5. In the Sort dialog box, default, My data has headers checkbox is selected. 6. Select the desired values in Custom, Sort On and Order fields. 7. Click on OK button. 6 The database is sorted accordingly. Do it Yourself Open MS Excel and create a worksheet as per the guidelines given below: 1. Create a table that contains the detail of the marks of five subjects, their total and percentage. See the figure for reference. 2. Select and merge the first row and give a title. 3. Apply a border to the table. 4. Sort the data basis of the Percentage column. 5. Save the file with file name 'yourname_ac_sorting'. Filtering Data The filter tool separates the data that matches the given criteria. Filtering data displays the data in the rows that meet the criteria and hides the rows that do not meet the criteria. Filtering a database allows to manipulate records as a group, separate from other database. Like sorting, filtering data can be done on text, numbers, date and time, cell colour, font colour and cell icon, selection, top or bottom numbers and above or below numbers. 83

The steps to filter the data are: 1 2 3 1. C l i c k o n a n y c e l l i n t h e 5 4 table/range. For example, A19. 6 7 2. On Home tab, in Editing group, click on the Sort and Filter. 3. Click on Filter option. 4. Small arrows are added to each filed name to filter data. 5. Click on drop-down arrow of A18 i.e. ‘Roll No' field and select the desired values in Number Filters section (by clicking on the check boxes). (Note: To display the complete data, click on Select All). 6. Click on OK button. 7. The list gets filtered and is displayed. Note To remove filter, on Home tab, in Editing group, click on Sort & Filter and select the Filter option in the menu. Do it Yourself Open the file that you have created in the previous activity and do the following. 1. Click on any cell in the Computer column in the table/range and apply filter on the table. 2. Display the data where marks in the English subject falls between 51 and 100. 3. Display the data where Percentage is greater than 80. 4. Save the changes. Format Table Using Tables Styles You can apply pre-defined table styles to a table in the worksheet. The steps to format the table using table styles are: 1. Select the table (data range). 84

2. On Home tab, in Styles group, click on Format as Table and select the required table style. 2 1 3 4 3. The Format As table dialog box appears. Click on OK button. 4. Required design is applied to the table. Small arrows are added to each filed name to filter data. Do it Yourself Open the file that you have created in the previous activity and do the following. 1. Select the table and remove the filter. 2. Now, select the table again (except the table header). 3. On Home tab, in Styles group, click on Format as Table and apply a table style. 4. Save the changes. See the figure for reference. 85

Conditional Formatting on Data Conditional formatting allows you to apply formats to a cell or range of cells (change the font colour, styling etc.) depending on the value of the cell or the value of a formula. Although Excel provides various built-in formatting rules, you can also add your own formula to a conditional formatting. For example, you can have a cell 1 appear bold only when the value 2 of the cell is greater than 80. Let’s learn it with the help of the following example, 1. Select the cells. 2. On Home tab, in Styles group, click on Conditional Formatting and select the required option. Calculating Subtotals Using Subtotal tool, you can organize data in groups. To subtotal a data list, you first sort the list on the field for which you want the subtotals. The steps to use a Subtotal function are: 1. Select the rows or columns in the table, that you want to group. 2. On Data tab, in Outline group, click on Subtotal. The Subtotal dialog box appears. 2 1 3 4 5 6 7 86

3. Select Month in At each change in: drop-down list. 4. To find the total of month-wise collection, select Sum in Use function: drop-down list. 5. Click on Total Collection in Add subtotal to: section. 6. Replace current subtotals check box can be deselected by clicking, if you want to overwrite the existing subtotal. Summary below data check box is already selected. 7. Click on OK button. - The subtotal is displayed. Note To remove all the subtotals in the worksheet, click on Data tab → Subtotal. In the Subtotal dialog box, click on Remove All button →OK button. Analysing Data with Pivot Table PivotTable helps to make the data more manageable by summarizing it and allowing you to manipulate it in different ways. The steps to create a PivotTable are: 1. Select the data (range). 2. On Insert tab, in Tables group, click on PivotTable. The Create PivotTable dialog box appears. 23 14 5 3. In Table/Range text box, the table range/name is displayed, that has been selected for the pivot table. Note: If the complete table is not selected, you can select the table manually. 4. New Worksheet option is selected by default. 87

5. Click on OK button. Note 6. The PivotTable layout appears in the worksheet (with a To rename the title in PivotTable Fields pane). cell A3, select the cell and type ‘Stu Name’. 7. Select the fields in the PivotTable Fields pane to add the report. In this example, we've selected the check boxes next to the Roll No, Stu Name, Total, Percentage. 8. The selected fields appear in the worksheet. 67 Do it Yourself Open a worksheet in Excel and do the following. 1. Create a table that contains the detail of the weekly earning of each month. 2. Select the data and find the subtotal of monthly earning. See the figure for reference. Using ‘IF’ Logical Function ‘IF’ is a logical function and it can be used for decision making in the worksheet. Conditions are passed to it as arguments and results will be returned according to the criteria of the condition. Syntax: =IF (condition, action if true, action if false) • Condition is the expression which is to be evaluated. 88

• Action if true is the value that is returned if the condition is true. • Action if false is the value that is returned if the condition is false. For example, In a game if a person gets more than 80 points then he will get a ‘Tab’, otherwise he will get a ‘Smart phone’. =IF (B2>80,“TAB”,“SMART PHONE”) You can also use nested ‘IF' condition. Syntax: =IF (condition, action if true, IF condition, action if true, action if false)) For example, to get the grade based on total marks obtained by the students in a class. =IF(C2>90,\"A\",IF(C2>80,\"B\",IF(C2>70,\"C+\",\"C\"))) Glossary Cell Reference : It identifies the location of a cell in a group of cells in the worksheet. Sorting is used to arrange the data in ascending or descending order. Sorting : It separates the data that matches the given criteria. PivotTable helps to make the data more manageable by summarizing it. Filtering : PivotTable : Quick Review  In relative cell reference, if you move or copy a formula to another cell, the cells referred to in the formula will change accordingly.  In absolute cell reference, if you move or copy the formula to another cell, the cells referred to in the formula will not change.  Mixed cell reference consists of both relative and absolute cell reference.  Sorting data can be done for alphabets, numbers, date and time, cell color, font color or icon set.  Conditional formatting allows you to apply formats to a cell or range of cells.  Using Subtotal function, you can organize data in groups. 89

A. Choose the correct answer. 1. In _____________ cell referencing, if you copy formula to another cell, the cells referred to in the formula will change accordingly. a) Relative b) Mixed c) Absolute d) None of these 2. In ____________ cell referencing, if you move the formula to another cell, the cells referred to in the formula will not change. a) Mixed b) Relative c) Absolute d) None of these 3. In ____________ cell referencing, if you copy the formula to another cell, the relative part of the reference is adjusted/changed while the fixed part is not adjusted. a) Absolute b) Relative c) Mixed d) None of these 4. On Home tab, in Styles group, the _____________ tool is used to apply the table style to a table on Excel worksheet. a) Sub-total b) Format as Table c) Total d) None of these 5. ____________ allows you to apply formats to a cell or range of cells, depending on the values of the cell/formula. a) Conditional Formatting b) Pivot Table c) Chart d) None of these B. Fill in the blanks. Filter, Pivot Table, Relative cell reference, Sub-Total, Sorting 1. ______________________________________ is a default cell reference in Excel. 2. _____________ option is used to arrange the data in ascending or descending order. 3. The __________________ tool separates the data that matches the given criteria. 4. _____________________________tool is used to organize data in groups. 5. _____________ helps you to make the data more manageable by summarizing it. 90

C. Answer the following questions. 1. What is cell referencing? ________________________________________________________________________ ________________________________________________________________________ 2. What are the types of cell referencing? ________________________________________________________________________ ________________________________________________________________________ 3. What is data sorting in MS Excel? ________________________________________________________________________ ________________________________________________________________________ 4. What is the use of Filter tool is MS Excel? ________________________________________________________________________ ________________________________________________________________________ 5. What is the use of ‘IF' function? ________________________________________________________________________ ________________________________________________________________________ D. Mark () on the correct statement and cross () out the wrong one. 1. The cell addresses are used in formulas and functions. 2. Sort operations can be done for both columns and rows. 3. Filtering data tool does not hide the rows that do not meet the filter criteria. 4. A table in a worksheet cannot be formatted using any pre-defined table styles in Excel. 5. The following ‘IF' function statement is correct: IF (condition, action if false, action if true) 1. Create a worksheet with the data, as shown in the figure: GST is common to all the items. Enter a formula in E2 cell to calculate the amount. Copy the same formula to calculate the amount of other items. (Use Fill handle to copy the formula). 91

2. Create a worksheet to enter the marks obtained by the students in your class and to find their status: Pass/Fail. Enter data of at least 10 students. See the figure for reference. In cell F3 use ‘IF’ function to find the status of the students based on the marks entered in column E. Drag ‘IF’ function of cell F3 to cell F12. 3. Create a worksheet as shown in the figure. Now, create a PivotTable with the fields: Month and Collection. Teacher's Signature : __________________ Teacher's Remark : WEB http://www.gcflearnfree.org/excel2013/15 http://spreadsheets.about.com/od/excelfunctions/ss/if_function_sbs.htm LINKS Teacher’s Corner Teacher is advised to provide some more examples on calculations through formulas and functions in Excel. 92

8 MS Excel: Presenting Data in Charts Dear Teacher, Yes Students, We have heard that We can create a we can create a variety of charts in variety of charts in Excel. Besides, we can Excel. We want to format, move and learn about them. resize them. Let’s learn about them. A chart is a pictorial representation of the data i.e. it allows us to illustrate the data graphically. A chart displays series of numeric data in a graphical format by using elements such as columns (in a column chart) or lines (in a line chart). You can create different types of graphs easily from the data in the spreadsheet. Charts once created can easily be changed in terms of the chart text, colour, size, position, etc. A chart has many elements. Some of these elements are displayed by default, others can be added as needed. You can change the display of the chart elements by moving them to other locations in the chart, resizing them, or by changing the format. You can also remove chart elements that you do not want to display. Types of Chart Column Chart There are different types of charts in Excel. Let’s have a look at some of them: Column Chart This chart displays the data in the form of vertical columns. It typically displays categories along the horizontal axis and values along the vertical axis. It allows to compare a single category of data between two or more items. 93

Line Chart In this type of chart, data is plotted in a form of line with connecting dots. It displays categories along the horizontal axis and values along the vertical axis. It shows continuous data/data trends over a period of time. Line Chart Pie Chart In this type of chart, data is displayed in the form of circles that shows the size of items in one data series, proportional to the sum of the items. Pie Chart Bar Chart In this type of chart, data is displayed in the form of horizontal bars. It displays categories along the vertical axis and values along the horizontal axis. Bar Chart Area Chart In this type of chart, data is displayed in the form of an area. It is used to show the magnitude of change over time. Area Chart Scatter Chart XY(Scatter) Chart A Scatter chart is used to show the correlations between two sets of values. A scatter chart has two value axes: horizontal axis (x) and vertical axis (y). It combines x and y values into single data points and shows them in irregular intervals, or clusters. 94

Some other types of charts available in Excel are Stock chart, Surface chart, Radar chart and Combo chart. Components of a Chart The various components of a chart are, 1. The chart area is the entire chart and all its elements. 2. X-axis or category axis is the horizontal axis of a chart. It shows the names. 3. Y-axis or value axis is the vertical axis of a chart. It shows the values/data series. 4. Axis titles are labels/titles of X-axis and Y-axis. 5. The plot area is the area of the chart bounded by the axis. 6. Data series are the 5 81 representation of data in 6 the form of bars and other 4 elements. 3 4 2 7. The legend identifies the 7 patterns or colours that are assigned to the data series or categories in the chart. 8. Chart title is the title of the chart. Creating a Chart Components of a Chart The steps to create a chart are: 1. Select the (cell) range you want to chart (including column titles). For example, A1:E6. 2. On Insert tab, in Charts 2 group, click on desired chart type. 3 1 3. Select the chart sub-type from the drop-down list. For example, Column chart and select the Clustered Columns. Note Hover the mouse over the options to know their names. 95

4. The selected chart appears on the worksheet. 4 Two new tabs, named as Design and Format, appear under Charts Tools category. These tabs can be used to change the design and format the chart. Let’s learn about them in the coming sections. Moving and Resizing the Chart A chart, created on the worksheet, can be moved and resized. To move a chart on the worksheet, 1. Point mouse cursor anywhere on the chart area, it converts into moving handle. 2. Click and drag the chart to new location. Resizing a chart Moving a chart To resize a chart on the worksheet, Put mouse cursor on the corner of the chart and drag it diagonally. Note To create a chart on separate sheet instantly, 1. Select the cell on the worksheet. 2. Pres F11 key. The chart of the selected cells will be displayed on a separate sheet. 96

Do it Yourself Open MS Excel, create a worksheet as shown here: 1. Select the cells and create a Pie chart. 2. Save the file with file name ‘yourname_class_chart'. See the figure for reference. Changing Chart Type The steps to change the chart type are: 1. Select the chart on the worksheet. The Chart Tools (having Design and Format tabs) appear on Ribbon. 2. Click on Design tab. 3. In Type group, click on Change Chart Type button. The Change Chart Type dialog box appears. 2 34 15 4. In All Charts tab, select the chart type in left pane and its sub-type in right pane. 5. Click on OK button. 97

Changing Chart Style The steps to change the chart style are: 1. Select the chart on the worksheet. The Chart Tools (having Design and Format tabs) appear on Ribbon. 2. Click on Design tab. 3. Click on the drop-down button of Chart Styles and select the required style from the menu. 2 3 1 Do it Yourself Open file created in the previous activity and do the following: 1. Select the chart on the worksheet. 2. Change the chart type and set it to Line chart (select Stacked Line chart in sub-type). 3. Save the changes. See the figure for reference. Note To rename the chart title: Click on the chart title, remove the existing title using Delete/Backspace key and type the new title. 98

OR 1. Put mouse cursor on the chart title and right-clck on it. Select Edit Text option. 2. The typing cursor starts blinking at the starting of the existing text. 3. Remove the text using Delete key and type the new title. Shortcut Note To take the printout of the chart, To take the printout of the chart, select the chart and press Ctrl + P. Select the chart and click on File → Print option. Formatting the Chart A Chart can be formatted to change its appearance. A chart can be formatted by changing its font, text, colour, border style and other properties. The steps to format the chart are: 1. Point the mouse and right-click on Chart Area (to format the chart area) or Plot Area (to format the plot area) and select the Format Chart Area/Format Plot Area option. For example, click on Format Chart Area. 2. The Format Chart Area pane appears on the right of the worksheet. Note: You can click on plot area of the chart to activate the Format Plot Area pane. 3. There are two categories: Chart Options and Text Options. By default, Chart Options is selected. 32 1 99