Code 402 Class 9 MS Office Base

2. Setting Shape Outline Colour: It sets the outline colour of the selected shape. 3. Setting Shape Effects: It adds a visual effect to the selected shape like shadow, glow, reflection or 3-D rotation. 4. Adding text on the shape: You can add text on the shape. Exercise 1. Write three paragraphs about your school in MS Word and do the following: a) Insert a picture of your school's building in the document. b) Rotate and Resize the picture. c) Click on the picture and try Crop option in Format tab. Save the file. 2. Open the document saved in the previous session and do the following: a) Insert a picture of your school's building in the document. b) Set its Layout Option to Through. 150

c) Set/move the picture to the right side of the document. Save the file. 3. Collect the pictures of Diwali/Holi/Eid/Christmas celebrations at your home and make a collage in MS Word. 4. Make a colourful greeting card/poster of New Year in MS Word. 5. Create a document of a size of a visiting card. Insert a small logo (any image) and details like name, designation and address. 6. Make a colourful greeting card/poster of New Year in MS Word using Shapes. 7. Create an invitation latter for teachers to a Play students going to present on teachers' day. Use various shapes in decorating the card. Session-14 Create a Table Tables are used to represent a given data in the form of rows and columns. It is very useful in organizing and representing the data in an effective way. For example, if a shopkeeper wants to maintain the detailed list of the dresses available in his shop, a table will be a better option. A sample table is given below. A row is the horizontal series of cells in a table. A column is the vertical series of cells. The intersection of a row and a column makes a rectangular box called a cell. Inserting a Table There are three ways by which a table can be inserted in MS Word. Place the typing cursor (│) in the document where you want to insert the table and do the following: Method 1: Using the grid option 1. On the Insert tab, in Tables group, click on the Table button. A drop-down menu appears. 2. Hover the mouse over the grid to select the range and click on the last box of the selected range. Or, click and drag the mouse pointer to select the range (number of columns and rows). For example, 4x3 (4 columns and 3 rows). The table gets inserted in the document. Using the Grid option, you can insert a table of maximum 10 columns and 8 rows. Method 2: Using Insert Table dialog box 1. On the Insert tab, in Tables group, click on the Table button. 2. Click on the Insert Table option from the drop-down list. The Insert Table dialog box appears. 151

3. Insert the number of columns and rows in Insert Table dialog box. 4. Click on the OK button. Method 3: Drawing a Table 1. On the Insert tab, in Tables group, click on Table button. 2. Click on the Draw Table option from the drop-down list. The cursor changes into pencil tool. 3. Click and drag the cursor diagonally to draw the boundary of the table. 4. Now, draw the columns and rows inside the boundary one by one. When a table is drawn or selected, a set of Table Tools, containing two tabs Design and Layout, appear on the Ribbon. As the name suggests, these tools contain the predefined sets of table designs and layouts. Use of these tabs is given in the next session. Exercise Open MS Word and create a table as per the guidelines given below: 1. Insert a table of 10 columns and 7 rows. 2. Enter the text as given in the figure. 3. Save the file with name yourname_Timetable. Format a Table Merging Cells Merging cells means combining two or more cells into one cell. The steps to merge the cells are: 1. Select the cells that you want to merge. 2. Click on the Layout tab. 3. In the Merge group, click on the Merge Cells button. Splitting Cells Dividing a single cell into multiple cells is called cell splitting. The steps to split a cell into two or more cells are: 1. Click in the cell that you want to split. 152

2. Click on the Layout tab. 3. In the Merge group, click on the Split Cells button. The Split Cells dialog box appears. 4. Enter the number of columns and rows into which you want to split the selected cell. 5. Click on OK button. Cell Alignment Cell alignment is basically the text alignment in a cell of a table. As you know, that alignment refers to setting the direction of the text. In a table, there are nine options of cell alignment. The steps to apply cell alignment on the table cells are: 1. Select the cell(s) on which you want to change the cell alignment. 2. Click on the Layout tab. 3. In the Alignment group, click on the required alignment. Table Designs MS Word provides a set of predefined table designs that includes borders, colours, cell alignments, etc. The steps to apply a design on a table are: 1. Click on a cell in the table. 2. Click on the Design tab. 3. Click on the drop-down arrow of Table Styles. A list of table design appears. 4. Hover the mouse on the designs to select the design. 5. Click on the desired design. Adding Rows and Columns in a Table Once a table is created, additional rows and columns can be added. Place the cursor (in a cell) where (after/before or left/right) you want to add a row or column. For example, in previous topic a table is created, having two columns in it. Same table is being used here for the reference. 153

Adding rows The steps to add a row above or below the current row are: 1. Click in the cell (e.g. cell containing 'Mono Act'). 2. Click on the Layout tab. 3. In Rows & Columns group, click on the Insert Above to insert a new row above the current row. Or, click on the Insert Below to insert a new row below the current row. Adding columns The steps to add a column to left or right of the current column are: 1. Click in the cell (e.g. cell containing 'Mono Act'). 2. Click on the Layout tab. 3. In Rows & Columns group, click on the Insert Left to insert new column on the left of the current column. Or, click on the Insert Right to insert new column on the right of the current column. New rows and columns can also be added by context-menu (by right- clicking) on the cell. Deleting Rows, Columns and Table After creating a table, unwanted rows and columns can be deleted. The steps to delete a row or column are: 1. Click in any cell (of the row or column that you want to delete). 2. Click on the Layout tab. 3. In Rows & Columns group, click on Delete button. A drop-down list appears. 4. Click on the Delete Columns option to delete the entire column, or, click on the Delete Rows option to Copying and Moving Table: A delete the entire row. table can be selected and then copied or moved just like plain or, click on the Delete Table option to delete entire text anywhere in the document. table. Exercise A. Open MS Word and open the document yourname_Timetable (you created in the previous activity). Edit the table as per the guidelines given below: 1. Click in any cell in the second column and add a column to the left. 2. In first cell of new column, type '0 Period'. 154

3. Click in any cell in the last column and add a column to the right. 4. In first cell of new column, type 'Extra Class'. 5. Click in any cell in the first row and add a row above it. 6. Save the changes. See the figure for reference. B. Open MS Word and open the document yourname_Timetable (you created in the previous activity). Edit the table as per the guidelines given below: 1. Select all the cells of the first row and merge them. 2. Type 'Time-table' in the (merged) cell (of the first row). Set 'Align Center' cell alignment in the first row. 3. In the 7th column, select the second cell to last cell and merge them. 4. Type 'Lunch Time' in the (merged) cell (of 7th column). 5. Click in any cell in the table and apply a table design. 6. Save the changes. C. Create a table containing the details of your friends. 1. Table should have columns like: Name, Address, E-mail and Mobile_no. 2. Apply a design to the table. 3. Save the file with file name 'yourname_friends' in D: drive. 4. On the table created above, perform the following modifications: a. Insert a column City after the column Address and enter data for this column. b. Insert a column SNo before the column Name and fill it with Serial Numbers. c. Assume that the first person in the table has two addresses in different cities soinsert a blank row below the row of the first person and fill in his Address and City. d. Merge the cell containing Serial number 1 with the blank cell below it. For the same person, merge Name with the blank cell below it, E-mail with the blank cellbelow it and Mobile Number with the blank cell below it. After doing steps c) and d), the sample data should look like this: 155

Session-15 Printing a Document Print Preview Print Preview enables you to see what the document will look like when it is printed. You can Print Preview a document by selecting File tab → Print option. Print Preview appears. Based on how you want the final printout to look, you can adjust the Margins, Orientation etc. Controlling Printing Print a Document Documents created in MS Word, need to be printed at times for various purposes like reporting, reading, proofreading, record keeping etc. A document saved on the computer is called a Soft Copy and its printed copy is called Hard Copy. Open an existing document. Click File >Print Print dialog box appears. It shows various options and settings. Let us understand them. Print Range: It allows you to determine what part of the document (or the whole document) you need to Print All Pages: Prints entire document Current Page: Prints currently active (on which the cursor is currently placed) page. Selection: Prints only the selected text. Pages: Prints pages with specified page numbers. Continuous pages can be specified as a range of page numbers with hyphen. For example, 3-9 will print all pages from page number 3 to 9. Specific, noncontinuous pages are mentioned as comma separated list. For example, 1, 5, 9 will print page numbers 1, 5 and 9 only. Copies: Specifies how many copies of a document you need to print. Properties: It provides additional options for more advanced control on printing the document. (This will be discussed later in advanced levels of MS Word. Click Print to print the document. 156

Exercise Open a multi-page document and do the following: 1. Print the whole document. 2. Print current page. 3. Print selected text. 4. Print continuous and non- continuous pages. Session-16 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. First, create a data source as a simple table in MS Excel (or MS Word or MS Access) with proper file name. Now, follow these steps: 1. In MS Word document choose 1 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. 157

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. 4 23 5 Select Data Source 4. In this step, you can choose different options to 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 6 Open button. 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 4 enter a new row of data. 5 158

7. The Mail Merge Recipients dialog 6 box appears. It shows the fields to be merged and data in the fields. Click on 7 OK button. 9 Insert Merge Fields 159 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 View Merged Data 9. Click on Preview Result button in Preview Results section on Mailings tab.

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 11 button and select Edit Individual Documents option. 11. The Merge to Printer dialog 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 Exercise 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. 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. 160

Assessment 1. What do you understand by text editing in MS Word? 2. Write the names of the main components of MS Word interface (window). 3. How will you open an existing document in MS Word? 4. What is the advantage of Find and Replace tool? 5. Name any five Font families (types). 6. How is Spelling and Grammar checking tool useful? 7. Why do we need to present some content with Bullets & Numbering? Give an example. 8. Describe various text alignments in brief. 9. What is the advantage of headers and footers? How will you insert page numbers in a document? 10. Describe various text wrapping options related to images in a document. 11. Describe various print options available for printing a document. 12. Describe the use of the term – main document, data source and merged document. Common Keyboard Shortcuts Keyboard Shortcut Function Ctrl+X Cut Ctrl+C Copy Ctrl+V Paste Ctrl+Z Undo Ctrl+Y Redo Ctrl+S Save Ctrl+P Print Home Beginning of line End End of line Ctrl+Home Go to start of document Ctrl+End Go to end of document Right Arrow Right one character Left Arrow Left one character Ctrl+Right Arrow Right one word Ctrl+Left Arrow Left one word Up Arrow Up one line Down Arrow Down one line Ctrl+Up Arrow Up one paragraph Ctrl+Down Arrow Down one paragraph 161

F4 Repeat last command Ctrl+A Select Whole Document Ctrl+B Bold Ctrl+I Italics Ctrl+U Underline Ctrl+Shift+L Bulleted list Ctrl+L Align left Ctrl+R Align right Ctrl+E Align centre Ctrl+[ Shrink font size Ctrl+] Grow font size TEST YOUR KNOWLEDGE 1. WYSIWYG stands for: a. What you share is what you gain b. What you share is what you get c. What you see is what you gain d. None of these 2. WYSIWIG refers to the fact that: a. Print out will be exactly as document is seen on screen. b. Print out will be exactly like the document scanned. c. Scanned document will look exactly similar on screen also. d. All of these 3. Which group of Home tab has options to apply bullets in MS Word document? a. Font b. Paragraph c. Style d. Layout 4. Below a mis-spelt word a ______ wavy line appears in MS Word. a. Red b. Green c. Pink d. Black 5. ___________ tab allows spell check and grammar in the document. a. Home b. Design c. References d. Review 6. Find and Replace dialog box is displayed by pressing: a. Ctrl + F b. Ctrl + R c. Ctrl + H d. Ctrl + X 162

7. For setting the font size 13 in MS Word document, you need to: a. Open font drop-down and select size 13. b. Click on Increase or Grow Font button. c. Click on Decrease or Shrink Font button. d. Type 13 in the Font drop-down textbox and press Enter key. 8. Pressing Ctrl+E aligns the selected text in ___________. a. Left b. Center c. Right d. None of these 9. Which view is best suited to review the document in MS Word? a. Review mode view b. Outline view c. Draft view d. Web layout view 10. Ravi pressed Ctrl+Home in his MS Word document. Then he gave print command with Print Range as Current Page, which page will be printed? a. Last page b. First page c. All pages d. Selected text only 11. Using _________ tab, cells of an MS Word table can be merged or split. a. Layout b. Design c. Format d.None of these 12. In an MS Word table, a cell can be split into: a. Two or more columns b. Two or more rows c. Both a) and b) d. Cells cannot be split 13. 13. _____________ contains the details to be merged with main document in mail merge. a. Main document b. Merged document c. Data source d. Merged fields 14. 14. In Pages option while printing, if we give 3-9, then it includes which pages? a. Page 3 and 9 b. Page 6 c. Page 3 to 9 d. None of these Watch www.eduitspl.com le&arn www.youtube.com/edusoftknowledgeverse 163

Unit Electronic Spreadsheet 4 Session-1 Introduction to Spreadsheets So far, we have learnt that computers help us in different ways. One of the core powers of computers is their ability to do calculations at a remarkable speed. The software that deal with numbers ad calculations make good use of this ability of computers. One such category of software is spreadsheets. Spreadsheet software or electronic spreadsheets help us in maintaining numerical figures and perform calculations easily and efficiently. Financial institutions like banks, businesses of all size use electronic spreadsheets on computers for simple to complex calculations. Before the advent of computers spreadsheets were in the form of large piece of paper used for calculations by the financial institutions. Hence the name Spread + sheet. The detailed information about expenses and finance was shown in the form of figures and charts over these huge spreadsheets. An electronic spreadsheet is a powerful and flexible tool to deal with numbers, financial figures, calculations, formulas and other related calculations. Electronics spreadsheets are fast, flexible and easy to maintain. They provide several in-built features that assist in easy and efficient calculations as well as reusing the formulas, functions and formatting. Some popular electronics spreadsheet applications are Excel, OPEN Office Calc, Google Sheets on Google Drive, Apple Numbers, Gnumeric etc. Some common features of spreadsheets are as follows: ¤ Maintaining and keeping track of financial and mathematical figures and records over the time. ¤ Analysis of data through various tools like graphs, pivot tables and goal seeking methods. ¤ Generating various reports useful for management in making decisions (Management Information System – MIS) ¤ Applying formulas to perform automatic calculations across changing figures. ¤ Managing inventory in various business areas and keeping track of stocks. ¤ Making financial decisions, predicting trends and setting targets. Getting started with Spreadsheet: This entire unit will use MS Excel 2013 as the reference for explaining spreadsheets however, all the spreadsheet applications have most of the features common. 164

Starting MS Excel Note: The steps to start MS Excel are: 1. You can also click on the shortcut key of the MS-Excel icon which may present on the desktop or pinned up on the taskbar. Start button > All Programs > Microsoft Office 2013 2. You can type 'excel' in the text box and press Enter key. > Excel 2013. Components of MS Excel Window Microsoft Excel consists of the following components: Quick Access Toolbar: It contains commands that are frequently used like Save, Undo, Redo, Print, etc. Title Bar: It displays the name of the workbook and three control buttons - minimize, maximize and close. Ribbon: It contains two parts: eight tabs - File, Home, Insert, Page Layout, Formulas, Data, Review and View. Each tab contains commands in different groups. Cell Name Box: It displays the name of the active cell or cell reference. The column letter and row number corresponding to the active cell is displayed in the cell name box. Cell Reference: Cell reference is the column letter and the row number that identifies a single cell. For example, A1 is the cell reference of the selected cell in the worksheet. A cell reference can be thought of as a cell name. The address of the first cell in a worksheet is A1 and the last cell address is XFD1048576. 165

Cell Range: Cell range is a group of contiguous cells (cells adjacent to each other). A cell range name consists of the cell address of the first cell, a colon and the last cell address. For example, B2:C4. The smallest range is a single cell and the largest range is an entire worksheet. Active Cell: The selected cell is called the active cell or current cell. It is displayed with bold border that differentiates it from the rest of the cells. The highlighted box is called Cell Pointer. To select a cell, you have to just click on it (Tab or Arrow keys on the keyboard can also be used to select the cells on the worksheet). To enter the data in a cell you first need to select it. Formula Bar: It displays the data and formulas that you entered in an active cell. It can also be used for entering or editing data, labels and formulas. Rows: These are horizontal lines of cells, labeled as numbers 1, 2, 3, 4, 5 and so on. Columns: These are vertical lines of cells, labeled using letters A, B, C, D and so on. Sheet Tab: It indicates the name of the worksheet and you can switch between the different sheets by clicking on it. Status Bar: It displays information about current selection (that includes total number(count) of cells (in the selection) that contains the data, average of the numeric values in the cells and sum of the numeric data in the cells), view buttons, zoom slider, etc. Scroll Bars: Horizontal and vertical scroll bars are found on the extreme right hand side and bottom of the window respectively. They are used to scroll horizontally and vertically along the Excel sheet respectively. Exercise Identify the key components of spreadsheet and Excel interface. Session-2 Open, Save and Close Spreadsheet Opening an Existing Workbook To open an existing worksheet, the steps are as below: 1. Click on Start button. 2. Click on All Programs. 3. Click on Microsoft Office 2013. 4. Click on Excel 2013. 5. Click on Open Other Workbooks. 6. You can select the workbook from Recent Workbooks list or click on Computer and Browse to locate and open the workbook. If Excel is already opened, then: Shortcut 1. Click on File tab. To open a new blank workbook: Ctrl + N. 2. Click on Open option. 3. The Open pane appears on the right of the window. If the required file is listed in Recent Work books section, click on it. 166

Or, click on Computer. 4. Click on Browse button. The Open dialog box appears. 5. Select the location where the required file is saved. 6. Select the file. 7. Click on Open button. Entering Data in the Worksheet Let's start entering data in a worksheet. The steps to enter the data in a worksheet are: 1. Click on cell D1 and type 'World Ranking'. 2. Click on cell A2 and type 'Team'. Press right arrow key and type 'Cricket' in cell B2. Likewise type 'Football' in cell C2 and 'Tennis' in cell D2. 3. Click on cell A3 and type 'India' and press Enter key. The cell pointer shifts to cell A4. 4. Type 'Bhutan' in cell A4. Similarly, enter some fictitious data in other cells. Note: By default, numbers are right aligned while text is left aligned in a cell. Saving a Workbook In Excel, a workbook is saved with the extension .xlsx. In Open Office, the file will get saved with the extension .ods. Let us save our worksheet created above by the name World Ranking.xlsx The steps to save the workbook are: 1. Click on File tab. 2. Click on Save As option. The Save As pane appears on the right of the window. 3. To save the file in local (memory) drive, click on Computer (if not selected) and then on Browse button. The Save As dialog box appears. 4. Select the location to save the file. 167

5. Enter the file name World Ranking in File name text box. Shortcut 6. Click on Save button. The workbook/file is saved. To save a workbook: Ctrl + S Closing a Workbook You have completed the work and saved the workbook. Now, close the workbook (not the Excel window). The steps to close a workbook are: Shortcut 1. Click on File tab. 2. Click on Close. To open a workbook: Ctrl + W Exercise You generally visit the market with your parents to purchase the household goods. Open MS Excel and do the following: 1. Make a list of any ten household goods with their price (per unit). 2. Click on C1 cell and type 'Price List of Household Goods'. 3. Save the workbook with name 'Household Goods'. 4. Finally, close the workbook. 5. Open it again and try adding some more data, save and close. Session-3 Enter Data in Spreadsheet Spreadsheets enable us to work with different kinds of data for organizing, calculating and presenting information. We will start by entering data in a spreadsheet. Relevant Knowledge We have already seen how data is entered in the worksheet. Open the workbook World Ranking created in the previous session. Editing the Data The data entered in the cells in a worksheet can be edited later. The steps to edit the data in a cell are: Double- click in a cell to edit the data in it. Or, Click in the cell and press F2 (function key). Now, edit the data. Or, Select the cell and edit the data in the formula bar. 168

A horizontal range from A vertical range from cell B3 to cell E3. (B3:E3) cell G3 to cell G6. (G3:G6) Cell Range A range from cell B8 to cell E10. (B8:E10) A set of adjacent cells is called a range. See the following figures. Cell ranges are denoted by the shorthand that includes Starting cell address of the range, Ending cell address of the range and a : (colon) in between them. This shorthand is used in the formula to perform calculations on a set of values in a range. A range can be identified by a name also. To do so: 1. Select the desired cell range then click on Define Name option in Define Names group under Formulas tab. 2. In the New Name dialog box, type the name you want to give to the range. 3. Select Scope as Workbook (range name is available in all the sheets) or select a specific sheet and click OK. Exercise A. Open MS Excel and do the following: 1. You generally visit the market with your parents to purchase the household goods. 2. Now, make a list of any ten household goods with their price (per unit). 3. Click on C1 cell and type 'Price List of Household Goods'. 4. Save the workbook with name 'Household Goods'. B. Open MS Excel and create a workbook according to the instructions given below: 1. Use cells from A1 to J7 and create a time- table of your class. 2. Save the file with file name 'yourname_excel' in D: drive. 169

Session-4 Formulas Different Types of Data In a spreadsheet, we deal with different types of data. When we say – different types of data – then it has two aspects: 1. The data which are the figures about any event or entity such as numbers, dates and text. For example, Marks_Obtained (numbers), Date_of_Birth (dates), Address (text). The data about facts can be in different formats such as Text can be in different cases upper, lower, proper etc.), Numbers can be with decimal places, Dates can be as short date, long date, medium date etc. 2. The data entered in the cell which refer to the column headings or row labels, formula and function. We shall learn about data formatting in coming sections. 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. 170

Note Example, order of calculation Precedence of basic mathematical operators: 1. ( ) Parenthesis (evaluate items in parenthesis) The equation: =6 + 5 - 4 * 3 / 2, will 2. % Percentage be calculated in the 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 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. 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 columns J, K, L, M and N (as shown in the figure). 2. Click in the cell O10 below the ‘Total’ heading and type the formula. Specify the cell address with the arithmetic operator. For example, =L10+M10+N10 . 2 3. Press Enter key. You will get the answer 3 in the cell. 4. To calculate the total marks of other students, place the cursor on 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. 4 171

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. Note While creating a formula using cell reference, you can use mouse to select the cell to insert it’s address in the formula, instead of typing. In this example, type ‘=’, click on L10 cell, type ‘+’, click on M10 cell, type ‘+’, click on N10 to create formula to get addition of the values of these cells. 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). 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. 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. 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. 172

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. Session-5 Functions 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 argument calculations. The type of argument a function uses is specific to the function. 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. 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. 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 173

Count : Count function returns the number of numeric and date values it is passed Max : Min : or in a cell range. (CountA counts the number of all types of values including text.) Round : Sqrt : Syntax: =COUNT (value1, value2, etc.) Example =COUNT (10, 25, 2) Output=3 Power : This function is used to find the largest value from the range of values. Lower : Upper : Syntax: =MAX (value1, value2, etc.) Today : Example =MAX (15, 25, 65) Output= 65 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 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 This function is used to find the square root of passed number. Syntax: =SQRT (value to find square root) Example =SQRT (15625) Output= 125 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 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 This function is used to convert the text into upper case (capital letter). Syntax: =UPPER (“Text”) Example =UPPER (“ABC Pvt. Ltd.”) Output= ABC PVT. LTD. This function is used to display the current date in the selected cell. The syntax is: =Today () Output= 12-10-22 Using Simple Function 2 3 Let's learn about using a simple function with help of an example. Let's take SUM() function. 1 The steps to use a function are: 1. Click in the cell where you want to display the sum of the numbers. For example, F4. 2. Click on Formula tab. 3. In Function Library group, click on Insert Function tool. The Insert Function dialog box appears. Shortcut 4 To display Insert Function dialog box: Shift+F3 174

C4:D4 5 6 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. Exercise 1. Create four headings in the first row and enter the respected data in first three columns. See the figure for reference. 2. Create a worksheet as shown here. Session-6 Format Data – Labels, Date, Time, Number In spreadsheets, data is usually in the form of text (or labels), numbers and dates. We can apply various formats on the data depending on the requirement. For example, monitory values may have 2 decimal places, dates may be displayed in various patterns, large amounts of numbers can be displayed as scientific notations, currencies can be displayed with currency symbol, time can be displayed in various formats, 175

fractions, accounting values can also be displayed in various formats. To apply the desired data format, first select the required cells and then, in the Number group under Home tab, click the format drop-down. Select the desired format from it or click on More number formats option. In the Format Cells dialog box, select the required category and type of the format, Finally, click on OK button. Use Currency Symbols All over the world there are different currencies in all the countries. Each currency is represented by a unique symbol. Excel allows inserting suitable currency symbol along with the financial or monitory figures. To do so, go to Home tab and in Number section, click on Accounting Number Format button and select the desired format. 176

You can also click on More Accounting Formats… option to bring up Format dialog box and select from a comprehensive list of currencies. Note: The same dialog box can be brought up by right clicking on the desired cell or selected range of cells and selecting Format Cells… option from the context menu. Exercise Open the saved workbook of Session 10 and apply suitable currency symbol and format for Unit Price and Closing Balance Value. Format Cell and Its Contents Wrap Text 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. 3. Click on Alignment tab. 4. In Text control section, there are three options (in the form of check boxes). Select Wrap text which wraps the text in the selected cell. 5. Click on OK button. Note: You can also click on Wrap Text option in Alignment section of Home ribbon. 177

Change Font 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. 1. Click on Font tab. 2. Now, click on the required option. ¤ Font: It allows you to choose a font (typeface), by clicking a name in the list. ¤ Font style: It allows you to set the font style i.e. Regular, Italic, Bold and Bold Italic. ¤ Size: It allows you to set the font size. ¤ Underline: It allows you to set an underline (of different types). ¤ Color: It allows you to choose the font colour. ¤ Normal text: You can select this check box to set the font, font style, size and effects to the Normal style. ¤ Effects: 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. 3. Click on the required check boxes to select. 4. Click on OK button. Note: You can also use various options available in Font section and Alignment section of Home ribbon. Change Column Width Width of a column can be adjusted before or after entering the data. Method 1 (using mouse): This method is used to adjust the width of a single column at a time. Steps to adjust the column width are: 1. Put mouse cursor on the right border of the column (header), the cursor changes to a double headed black arrow . 2. Now, click and drag the border of column header to increase or decrease the column width. 3. After getting the desired width release the mouse button. 178

Method 2 (using command on the Ribbon): ~ This method is used to adjust the width of a single/multiple columns at a time. Steps to adjust the column width are: 1. Select the columns (using column header). 2. On Home tab, in Cells group, click on Format button. A drop-down menu appears. 3. Click on Auto Fit Column Width option to automatically adjust the column width. Or, to manually adjust the column width, click on Column Width option. 4. The Column Width dialog box appears. 5. Type the required width and click on OK button. See the changes in the width of the selected columns. Cell Content Alignment The Format Cells dialog box is a “one-stop” formatting source. From within this 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. 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: 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. ¤ Orientation: Degree of orientation can be set through the Orientation section. 179

Exercise Open an existing worksheet created in any previous session. Select various ranges of cells like headings, data, totals etc. and open the Format Cell dialog box by right clicking on the selected cells. Try and apply various cell formatting settings. Session-7 Fill Handle Fill handle is the part of the autofill feature of a spreadsheet. Fill handle is denoted by a thick, black mouse cursor when the mouse pointer is brought over the lower right corner of the active cell. Using AutoFill Feature Sometimes you need to enter a series of data (numbers, days, dates, etc.) then instead of typing every entry manually, AutoFill option can be used. AutoFill feature is used to fill a predefined series of data in adjacent cells quickly. Some of the frequently used popular series are: Number series Weekdays and dates series Months series 180

The steps to create a number series using AutoFill feature are: 1. Type first two values (numbers) from 1 which you want to start the list. For example, type ‘1’ and ‘2’ in A3 and A4 + cells. 2 2. Select both the cells and position the mouse pointer at the lower right corner 3 of the selection. The mouse pointer changes to ‘+’ sign (AutoFill Handle). 3. Click and drag the AutoFill handle to the cell, till where you want to fill the list. For example, fill up to cell A8. 4. Release the mouse button. The required series of data gets displayed. When you release the mouse button after dragging, an AutoFill icon is displayed. Click on AutoFill icon, a submenu is displayed where you can select what you want to fill. AutoFill option can be used to fill data within a series of rows or columns. In case of numbers, type two consecutive values to fill the series. In case of dates, days or months, series can be entered using a value in one cell only. Some of the days, dates and months AutoFill data series are given below: Exercise In the workbook ‘Household Goods’ do the following: 1. Create a weekdays series from Sunday, in the cells starting from D3 using AutoFill feature. 2. Create a numeric series from 101 to 107, in the cells starting from E3 to E9 using AutoFill feature. 3. Save the changes. 181

Session-8 Cell Referencing Accessing the value of a cell by its address in any Cell containing function to calculate formula or function is called cell referencing. It identifies sum of values of cells A1 to A5. the location of a cell in the worksheet. The 3 types of cell referencing are described here. 1. Relative cell referencing Relative cell referencing is by default. If you move or copy a formula to another cell, the cells addresses in the formula will change accordingly. As shown here, SUM() is used in cell A6 to get the sum of the range A1 to A5 i.e. =SUM (A1:A5). Then, it is copied to cell B6. The cell references are changed to B1: B5 i.e., =SUM (B1:B5). 2. Absolute cell referencing In absolute cell reference if formula is moved or copied Cell B6, where function is copied to another cell, the cell addresses in the formula will not change. This is done by preceding a ‘$’ (dollar) sign in front of the columns letter as well as the row number. As shown here, formula in cell C4 is =B4*$A$4 and after copying the formula in cell C5 it is =B5*$A$4. In cell C5, reference of cell A4 is not changed. Cell containing the formula to Formula is copied calculate the amount after discount. to cell C5. 3. Mixed cell referencing 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 as shown here. Formula is copied to cell C4. Cell containing the formula to calculate the amount after discount of 0.15. 182

Exercise 1. In the following data set, the discount %age is given at the top as 5, 8.5, 10 and 12. Apply and copy formula to calculate the discounted prices for each company. Four calculations are done for you to understand the problem. [Hint: Apply mixed referencing] Session-9 Create Modify and Format Charts In Excel, 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 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. 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. 183

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. 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. 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. 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. 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. 184

5. The plot area is the area of the chart bounded by the axis. 6. D a t a s e r i e s a r e t h e representation of data in the form of bars and other elements. 7. The legend identifies the 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 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 group, click on desired chart type. 3. Select the chart sub-type from the drop-down list. For example, Column chart and select the Clustered Columns. 4. The selected chart appears on the worksheet. 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. You shall learn about them in the coming sections. 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. 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. 185

In the Chart Options category, you can format the following sub-categories: Fill & Line, Effects and Size & Properties of the chart. In the Text Options category, you can format the following sub-categories: Text Fill & Outline, Text Effects and Textbox. 4. Select the required category, for example, Chart Options, and select the required values in different sub-categories. After setting the various sub-categories, we get the changes in the chart 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. 4. In All Charts tab, select the chart type in left pane and its sub-type in right pane. 5. Click on OK button. 186

Exercise 1. Open MS Excel, create a worksheet as shown here. Select the cells and create a Pie chart. Save the file. 2. Convert the chart created to a bar chart. 3. Select the bar chart and do the following: a) Format the chart area by setting its Fill & Line and Border properties. b) Format the Plot area by setting its border. Save the changes. 4. Create a worksheet that shows the population, literacy percentage and gender ratio in nine major states in India of a year. Then, create a Bar chart of the data with proper formatting effects. 187

Assessment 1. Explain any 3 advantages of MS Excel. 2. How can we open Excel using Run window? 3. Name all the components of MS Excel window. 4. Differentiate between a Workbook and a Worksheet. 5. What do you mean by AutoFill feature? 6. How is a formula different from a function? 7. What do you mean by cell range? How a cell range is mentioned in a formula? 8. Write the steps to format the numbers in a cell in a worksheet. 9. What are the uses of Alignment and Font tabs in Format Cells dialog box in a worksheet? 10. Describe a few properties you can change in font while formatting a cell. 11. Name any 4 operators that are used for arithmetic calculations in Excel. 12. What is the use of fill handle? 13. How is mixed referencing different from absolute referencing? Give example. 14. For what purpose a Pie, Line and Area chart are used? Keyboard Common Keyboard Shortcuts Shortcut Function Ctrl + N Ctrl + O Create a new workbook Ctrl + S Open an existing workbook. F12 Save the active workbook. Ctrl + W Open Save as dialog box. Ctrl + C Close the active workbook. Ctrl + X Copy the contents of the selected cells to Clipboard. Ctrl + V Cut the contents of the selected cells to Clipboard. Ctrl + Z Insert the contents of the Clipboard into the selected cell(s). Ctrl + P Undo your last action. Panic button :) Ctrl + 1 Open the \"Print\" dialog. Ctrl + T Open the \"Format Cells\" dialog. Tab Convert selected cells to a table. Autocomplete any function name. Example: Enter = and start typing av, press Ctrl + ` Tab and you will get =average( Toggle between displaying cell values and formulas. 188

Ctrl + F1 Show/hide the Excel Ribbon. Hide the ribbon to view more than 4 rows of data. Ctrl + Tab Switch to the next open Excel workbook. Ctrl + PgDown Switch to the next worksheet. Press Ctrl+PgUp to switch to the previous sheet. Ctrl + G Open the \"Go to\" dialog. Pressing F5 displays the same dialog. Ctrl + F Display the \"Find\" dialog box.HomeReturn to the 1st cell of the current row in a worksheet. Ctrl + Home Move to the beginning of a worksheet (A1 cell). Ctrl + End Move to the last used cell of the current worksheet, i.e. the lowest row of the rightmost column. F2 Edit the current cell. Alt + Enter In cell editing mode, enter a new line (carriage return) into a cell. Ctrl + ; Enter the current date. Press Ctrl + Shift + ; to enter the current time. Ctrl + Shift + V Open the \"Paste Special\" dialog when clipboard is not empty. Ctrl + Y Repeat (Redo) the last action, if possible. Ctrl + A Select the entire worksheet. Ctrl + Space Select the entire column. Shift + Space Select the entire row. TEST YOUR KNOWLEDGE 1. Mr. Joshi is a business manager. He needs to analyse data by applying some automatic calculations. Which software is best suited for this? a. MS Excel b. Open Office Calc c. Google Sheets online d. Any of these 2. Ravi has typed the heading NAMES in cell B3. In the cells below it, he typed names of his 5 friends, one in each consecutive row. What is the cell address of the last friend’s name? a. B9 b. B8 c. G3 d. G9 3. If cell A1 contains a formula =5*2, then what will Formula bar display? a. =5*2 b. 10 c. A1 d. Error 4. An active cell is represented by ___________ cursor. a. Blinking b. Rectangular c. Mouse d. + shaped 5. Ravi has entered headings Name, Date of birth and Address in cells A1, B1 and C1. Below each heading, in consecutive cells, he entered names, dates of birth and addresses of his 10 friends, one record in each row. He wants to select entire range of headings and data. What will be the valid range? 189

a. A1:C11 b. A2:C11 c. A1:C10 d. A2:C11 6. Cell A1 has value 1 in it. Dragging fill handle downwards will fill the cells below A1 with: a. 1 b. 1, 2, 3, and so on c. Nothing d. 1,3,5,7 and so on 7. Ravi copied the formula =B5*D5 from cell C2 to cell D1. How will the formula in cell C1 look like? a. C4*E4 b. C5*E5 c. A4*C4 d. A5*C5 8. Ravi wants to create a formula to calculate percentage marks in MS Excel. Which operators he must use? a. * and / b. * and \\ c. percentage function d. - and + 9. How many text alignments are there in MS Excel spreadsheet? a. 3 b. 6 c. We cannot align text in spreadsheet. d. 8 10. Wrap text changes the ___________ of the cell. a. Height b. Width c. Both a) and b) d. None of these 11. Middle alignment is different from Center alignment in MS Excel in that: a. It aligns the text horizontally in the cell’s center. b. It aligns the text vertically in the cell’s center. c. Middle and Center are same. d. There is no option by the name Middle. 12. Which of the following is an example of mixed referencing? a. $B5 b. D$9 c. $A$7 d. All of these 13. The currency symbol can be changed using ____________ dialog box. a. Format Number b. Format Currency c. Format Cells d. Format Data 14. Font style does not include Font size. a. True b. False 15. Which of the following charts does not have axis? a. Bar b. Pie c. Area d. All of these Watch www.eduitspl.com le&arn www.youtube.com/edusoftknowledgeverse 190

Unit Digital Presentation 5 Session-1 Introduction to Digital Presentation So far, we have learnt about basics of word processing and spreadsheets. In various businesses and enterprises, many times people need to present their ideas, business figures, plans, agendas, product information, project details, training content, educational material and a variety of information formally to a number of audiences. Such presentations can be as simple as in plain text but modern presentations make use of multimedia – text, audio-video, images and animations to ensure that the content presented puts across the ideas and messages in the most engaging, interesting and effective way. To create multimedia presentations, Microsoft Office provides MS PowerPoint – a digital presentation software application. A digital presentation application lets the user arrange the information and content on multiple slides which can be arranged in a particular sequence. A digital presentation application provides features of creating and editing the presentation, managing the slides and generating slide show for the intended audiences. Once the presentation is created, it can be presented using a projector in front of the audiences or it can be used to cast over a web meeting or webinar using web meeting tools over Internet to the audiences from any part of the world. Elements of a Presentation Since presentation is the mean to convey ideas to the audience n the most effective way, it is very important that the presentation has following 3 qualities – engaging (interesting), effective (message conveyed strongly) and communicative (message presented in a concised form). Following major elements make a presentation engaging, effective and communicative: Before the presentation: The most important factor to consider before going for the final presentation is preparedness. Are you completely ready to deliver the presentation? Preparation and confidence go hand-in-hand. Better you prepare, higher would be the confidence. Preparedness includes having all the facts at hand, answers to anticipated questions, explanation for each slide, appearance and body language of the presenter, knowledge about the audience's profile, know-how of digital equipment and software application. During the presentation: When presentation begins, following factors influence it throughout. ¤ Environment: The venue or location and its ambience make the environment of the presentation. Presentations are mostly formal so a formal setup is required. Audiences should feel suitable. The lighting should be suitable and well controlled during the presentation. The 191

place should be noise-free and audience should be seated suitable to see and listen to what has been presented. ¤ Audience preparedness: Audience should know the agenda of the presentation beforehand and they should be settled with the topics of the agenda in the opening of the presentation. During many presentations, presenters have the notes pages distributed among the audiences for reference. ¤ Audience Interaction: Is the presentation too formal? In such cases, mostly there is less or no scope for audience to interact with the presenter during the presentation. On the other hand, some presenters need to interact with the audience. This depends on the nature and need of the presentation. Mostly, at the end of the presentation comes the question-answer session wherein audience get clarity to their doubts if any. ¤ Winding up: Presentations usually end with short question-answer session and certain presentations may collect feedback from the audience too. The QA sessions should not be too long. As a thumb rule, dedicate 10% 20% of the total time for QA. The presentation should end up with an effective summary to reinforce the core objective and audience should have a feeling of motivation or urge to connect with the objectives. Certain presentations use the ending to set the course of actions to be followed next. Many formal presentations end up in solid action plans for future and a tentative schedule for the next meet. ¤ After the Presentation: Many presentations are followed by working on the follow-up of future actionables with the participants. This usually happens in the official meetings and product promotion presentations. This also happens where there is a series of presentations to be done and next presentation depends on the outcome of the previous one. One common example is the training and education field. Characteristics of an Effective Presentation Attending a presentation or participating in a presentation should be an effective and memorable experience for the audience. If you keep this key in mind then you can work on delivering your presentation in the most effective ways. An effective presentation has following qualities: ¤ Presentation ideas should be relevant rational. They should tally with the core agenda and should convey the idea collectively in an effective way. This is the first pre-requisite that piques the interest of your audience. ¤ Suitable communication skills and inter-personal skills with good command on the language and the subject is of utmost importance. ¤ The opening of the presentation should create the urgency to engage the audience. It should present the problem and issue at hand in the most captivating manner possible – through an information slide, rhetorical question, interesting fact or a short video clip etc. It should work like a teaser of the movie. The objectives of the presentation should be set in the beginning. ¤ The sequence of subtopics and discussions should be well-formed and should carry forward the message you want to convey. An improper sequence confuses the audience and raises a lot of questions. ¤ Keep the content short and concise with bullet points. Use suitable images, charts and infographics (graphical summary) and keep the detail for explanation in your speech. ¤ Use the opening to connect with the audience, let them settle comfortably with your talk and do the expectation management by clearing what is the agenda and what you are going to talk about. Information about the presentation and what will follow in its course makes the 192

audience comfortable. ¤ A good presentation is a nice blend of information presentation with visual aids. ¤ A good presentation has a well defined and rehearsed opening, mid-part and ending. ¤ The appearance of the speaker is a major factor that should go with the presentation. It adds to the environment of the presentation. Any thing out of the setting in the appearance of the presentation affects entire presentation adversely. Appearance includes suitable attire, confident body language (voice, gestures, eye contact, posture and expressions) and suitable movements during the presentation. ¤ The language of explanation should have positive tones. Avoid using negative tones such as “cannot”, “should not”, “don't” etc. ¤ Overall, a presentation should be well-designed, planned, rehearsed and anticipate situations such as possible questions. It should not be under or over loaded with information. It should involve the audience. It should be an experience for the audience instead of something started and finished. PowerPoint Home Tab The Home Tab, like other MS Office applications, contains commonly used options like copying and moving the text, adding new slides with various slide layouts, text formatting options, shapes library, find and replace text etc. Clipboard ¤ Paste the cut or copied information in Clipboard at the location where cursor is positioned. • Paste Special – To paste in a particular format. • Paste as Hyperlink – Used with copy to paste the copied information as a hyperlink. • Duplicate – To duplicate a slide). ¤ Cut removes the selected text from the presentation and puts into Clipboard for later retrieval. ¤ Copy copies the selected item or text and puts it on the Clipboard. ¤ Format painter copies formatting from a selected position and applies it to another (Note: To apply same formatting to many places, double-click the format painter button). Slides ¤ Add a new slide to the Presentation. ¤ Change the layout of the selected slide. ¤ Reset the position, size and setting of the slide to default values. ¤ Delete the selected slide from the presentation. Font ¤ Change Font face. ¤ Change font size. Increase Font size, decrease Font size. 193

¤ Clear formatting (Clear all formatting from selection, leave only plain text). ¤ Bold, Italics, Underline. ¤ Strikethrough (draw a line through middle of selected text). ¤ Shadow adds a shadow behind the selected text. ¤ Adjust the spacing between characters. ¤ Change case. Changes the case – sentence, lower, upper, title, or toggle. Select the text and click on an option. ¤ Change the Font color. Paragraph ¤ Bullets (choose from different styles). ¤ Numbered list (choose different formats). ¤ Increase or decrease indent of the paragraph. ¤ Change spacing between lines. ¤ Change the direction of the text to vertical, stacked or rotate the text. ¤ Align the text – change how the text is aligned within the text box. ¤ Align text left, right, centre, margins. ¤ Split text into two or more columns. ¤ Convert text into Smart Art graphics. Drawing ¤ Insert ready made shapes like circle, rectangles, arrow lines, flowchart etc. ¤ Arrange objects on the slide by changing their order, position and rota,tion. ¤ Quick styles enable selecting a visual style for the shape or line. ¤ Fill the selected shape with a solid color, gradient, picture or texture. ¤ Specify the color, width, and line style for the outline of the selected shape. ¤ Apply a visual effect to the selected shape like glow, shadow, reflection and 3-D rotation. Starting MS PowerPoint The steps to start MS PowerPoint are: Start button > All Programs > Microsoft Office 2013 > PowerPoint 2013. In New presentation selection window, click on the Blank Presentation thumbnail. 194

Components of PowerPoint Window Microsoft PowerPoint consists of the following components: Quick Access Toolbar: It shows frequently used commands like Save, Undo, Redo, etc. Title Bar: It shows the filename and Minimize, Maximize & Close buttons. Ribbon: It contains various tabs. Each tab contains commands in different groups. Slides Pane: The Slides pane displays thumbnails of the slides in the presentation. Slide Area: It is the working space to add elements like text, pictures, animations etc. Placeholder: It is the area with a dashed border where you insert various slide elements. Status Bar: It displays current slide number, total number of slides, zoom slider etc. Exercise Open Powerpoint and explore its ribbons of Insert tab and Design tab. Observe how various options are organised in different groups in both the ribbons. Hover the mouse pointer over the options to display the brief description about that option. Session-2 Open, Save and Close Presentation Opening a Presentation 1. File tab > Open option. 2. In the Open pane, if the required file is listed in the Recent Presentations group, click on it or, click on Computer. 195

3. Click on the Browse button. The Open dialog box appears. Select the location where the required file is saved. Select the file. 4. Click on the Open button. Saving a Presentation After completing the work, save your presentation so that you can use it later. To do so: 1. File tab > Save option. 2. In the Save As pane, to save the file in the local (memory) drive, click on Computer and then on the Browse button. The Save As dialog box appears. 3. Select the location to save the file and enter the file name in the File name text box. 4. Click on the Save button. By default, PowerPoint 2013 saves the file with .pptx extension. Closing the Presentation To close the presentation (not the PowerPoint window): File tab > Close option Note: If you have not saved your file before closing, PowerPoint displays a confirmation dialog box asking you whether you want to save your file before closing or not. Ÿ Click on Save, if you wish to save the file Ÿ Click on Don't Save, if you don't wish to save the file. Ÿ Click on Cancel, if you wish to cancel the action and return to the file. Exercise 1. Open PowerPoint and select Blank Presentation template. 2. In the slide, click in inside the Title placeholder and type WELCOME 3. Click inside the Subtitle placeholder and type GUESTS 4. Save the presentation by any name. 196

Session-3 Duplicate and Rearrange slides To create a duplicate of a slide, in the slide pane, right click on the desired slide and select Duplicate option. To rearrange slides, first switch to Slide Sorter view by clicking Slide Sorter view in the Presentation Views group under View tab or by clicking Slide Sorter icon on the right-hand side of Status bar. Then arrange the slide sequence by simply drag-dropping the slides with the help of mouse or by using Cut-Paste technique. Control size of the view Various panels in any view can be resized by the help of mouse. You can zoom in and out of a slide using zoom slider on the right-hand side of the Status bar. Create a Simple Presentation with Theme or Template Let us learn to create a presentation using a Blank Presentation (which is also a theme). To do so, File tab > New option > Blank Presentation. You can also select a predesigned template to modify and save, such as Education, Diagrams etc. If you save your presentation as a template then save it as .potx file. Shortcut To insert a new blank presentation: Ctrl + N 197

Inserting a Text Box on the Slide You can insert additional text boxes in the slide. The steps are: 1. Select the desired slide and click on the Insert tab. 2. In the Text group, click on the Text Box button. 3. Drag the mouse (on the slide, where you want to add the text) to draw the text box. Adding a New Slide While creating a presentation, you need to insert new slides. You can use any number of slides in the presentation. The steps to add a new slide in the presentation are: 1. On the Home tab, in the Slides group, click on the drop-down arrow on the New Slide button. 2. Click on the desired layout. Slide Layouts The slide layout in PowerPoint is the arrangement of all the items that prepare your slide. Slide layouts contain formatting of text (including body text, bulleted lists, and titles), tables, charts, SmartArt graphics, movies, sounds, pictures and clipart (from online picture option). Shortcut There are nine basic slide layouts in MS PowerPoint 2013. To insert a new slide: Ctrl + M Let us have a look on all the nine slide layouts. 1. Title Slide: This is used at the starting of your presentation. 2. Title and Content: This is a default slide layout. It is used to give topic and its content. 3. Section Header: This slide type is used to separate different sections of the same presentation. It can also be used as an alternate to the Title slide layout. 4. Two Content: This slide layout is used to show text in addition to a graphic content type. 5. Comparison: It is similar to the Two Content slide layout, but this slide type also includes a heading text box over each type of content. This type of slide layout is used to: ¤ Compare two types of the same content type (for example- two different charts). ¤ Show text in addition to a graphic content type. 6. Title Only: This slide layout is used, if you want to place only a title on the page. 7. Blank: A blank slide layout is often used when a picture or other graphic object that needs no further information, will be inserted to cover the whole slide. 8. Content with Caption: This layout is used to place content or an object on the right side of the slide. The left side is used for a title and text to describe the topic/object. 9. Picture with Caption: The right part of the slide is used to place a picture. The left side is used for a title and text to describe the topic/picture. Using 'Title and Content' layout, inserting the different contents on the slide. 198

Create a Presentation 1. Open MS PowerPoint and take a Blank Presentation. A new presentation of single slide with Title Slide layout appears. 2. On Home tab, in Slides group, click on Layout button. A list of slide layouts appears. Click on Title and Content layout option. Or, you can insert a new slide of Title and Content layout. 3. There are some iconic images at the center of the content area. These are the links to insert objects (Table, Chart, SmartArt Graphic, Picture, Online Picture and Video) in the slide. Copy, Move, Delete Slide Content To copy or move any slide content, same standard steps are there as in any software that is Ctrl+C (Copy) or Ctrl+X (Cut) and then Ctrl+V (paste on the desired location.) Copy, Move, Delete and Duplicate Slides In side pane or in Slide Sorter View, select the slide and Ctrl+C (Copy) or Ctrl+X (Cut) and then Ctrl+V (paste on the desired location.). To delete a slide, select it and press Delete key on the keyboard. To duplicate a slide, right click on it and select Duplicate option. Exercise Create a presentation of following 4 slides. Try taking slides of different layouts. Slide 1: Slide Title of the presentation. (Take a Title Only slide). Slide 2: Name and address of your school. (Take a Title Slide). Slide 3: Name of the sports activities provided in your school. (Take Blank Slide and add a Text Box in it). Slide 4: 'Created by your_name_rollno' (Take Blank Slide and add a Text Box in it). Save the file by some name. Session-4 View a Presentation - Slide Show View In a slide show, all the slides in the presentation are displayed one by one in full screen. Steps to run a slide show are, 199