Computing Skill II - PDF

B. VOC. SEMESTER 2 COMPUTING SKILLS – 2 PARTICIPANT WORKBOOK  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- INDEX Type of...

B. VOC. SEMESTER 2 COMPUTING SKILLS – 2 PARTICIPANT WORKBOOK  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- INDEX Type of Topic Page Sub topics Session session No. Session 1 Theory  To gauge the level of 7 understanding of the batch of level 1.  Revise an operating system Session 2 Theory  Internet and purposive 8 Revision of surfing Semester 1  Microsoft word Session 3& Practical  Creating a file in MS word with 11 4 all the basic features covered in Semester 1 Session 5 Practical  Internet surfing 13  Creating a Google account, email Session 6 Theory  Recap of Semester 1 14 o Creating a spreadsheet and entering data o Saving, deleting a workbook o Moving around in the workbook Session 7 Theory  Inserting a formula 15  Excel – copy and paste  Paste special Microsoft  Find and replace excel  Special symbols and characters  Adding a Comment to a Cell Session 8 Practical  Type a table of two columns and 27 insert simple formulas using * and /  Copy paste the table in another sheet as such and with values  Use the find and replace option  Insert 5 symbols and characters  Inserting comments in two cells _____________________________________________________________________________ 2 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Session 9 Theory  Format cells for different types 27 of data  Setting fonts in Excel  Background colour  Text alignment  Merge cells  Wrap text and shrink to fit Borders and shading  Session 10 Practical Create a table  41 Show two font options  Change a background colour  Vertical and horizontal alignment  Format the table with borders and shading  Type a header and merge it Session 11 Theory  Freeze panes 42  Conditional formatting  Creating a formula  Copying a formula Session 12 Practical Create a table with imaginary data 52 and do the following:  Freeze panes  Conditional formatting  Creating a formula  Copying a formula Session 13 Theory  Cell references in formula 53  Using functions in excel  Built in functions Session 14 Practical  How to create cell references in 61 formula – all types  How to insert functions and use one built-in function from each category Session 15 Theory  Data filtering 62  Data sorting  Using ranges _____________________________________________________________________________ 3 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE -----------------------------------------------------  Using macros Session 16 Practical For the list created earlier 74  Filter  Sort in ascending and descending order  Create a macro to make a cell bold, italic, underline  Show cell ranges Session 17 Theory  Cross referencing – VLOOKUP 75 function  Pivot table Session 18 Practical  Create a pivot table report from a 81 data table  Show the VLOOKUP function Session 19 Theory  Charts 81  Pivot charts  List of excel keyboard short cuts Session 20 Practical  Provide sample data and ask 89 students to create: o Bar graph o Pie graph o Format chart area  Make a pivot chart  Use keyboard shortcuts (any 10) Session 21 Theory  Starting power point 90  Areas in a standard power point file  Create a presentation Session 22 Theory  Add new slides 98  Entering text in boxes Microsoft power  Adding a text box point Session 23 Practical  Starting power point 108 & 24  Areas in a standard power point file  Create a presentation  Adding new slides  Entering a text  Adding a text box _____________________________________________________________________________ 4 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Session 25 Theory  Deleting an existing slide 109  Rearranging slides o Normal view o Slide sorter view  Working with outlines Session 26 Theory  Sidebars 120  Presentation views Session 27 Practical  Sidebars 128  Presentation views  Setting backgrounds  Slide orientation Session 28 Theory  Saving presentations 129  Reviewing presentations Session 29 Theory  Running a slideshow 134  Using slide master Session 30 Theory  Save a design template 140  Adding pictures Session 31 Practical  Running a slide show of the 147 slides created in the earlier session  Using slide master Session 32 Theory  Add and preview animations 147  Add and preview transitions Session 33 Practical  Adding / editing / formatting 156 pictures / images in powerpoint  Saving, reviewing presentations Session 34 Practical  Adding animations and 157 transitions  Editing the above Session 35 Practical Create a full presentation using the 157 & 36 features studied in this section on any chapter related to their curriculum Session 37 Theory  Introduction 158 Cyber laws  Categories of Cyber laws _____________________________________________________________________________ 5 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Session 38 Theory  Countering cyber crimes 163  Software piracy  Code of ethics - Dos and Don’ts Session 39 Theory Introduction 169 Create an account Create a new mail message Add an email signature MS Outlook Session 40 Theory  Using the calendar 172  Create a contact  Add / create notes/ tasks  Print an email message, contact or calendar item Session 41 Theory / Social media  What is social media? 176 Practical  Various avenues  Practical training Session 42 Theory Video  Introduction 178 conferencing  Types of Video Conference Sessions  Desktop conferencing  Managing video conferencing  Live streaming Sessions Practical Based on the Related to the work situation to be 182 43-45 vertical conducted; different for each vertical _____________________________________________________________________________ 6 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Session 1 Session objective  To gauge the level of understanding of the batch of level 1.  Revise operating system 1) Recap on the following basics from the term 1 book a. Types of computers (from session 1) b. Hardware and software (session 2) c. Operating system (from sessions 4 and 5) i. Windows 10 desktop ii. Icons iii. Background iv. Start menu v. Search box vi. Task bar vii. Notification area viii. File explorer _____________________________________________________________________________ 7 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Session 2 Session objectives Revise  Internet and purposive surfing  Microsoft word 1) Cover the following (from sessions 8 and 9, Term 1 book) a. What is Internet? b. IP address c. Domain name d. Web browsers 2) Write the definition of the above terms. ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ _____________________________________________________________________________ 8 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ____________________________________________________ 3) Revise Microsoft word a. Starting MS word, various tabs (session 11) b. Options under each tab (session 11) c. Moving in the workbook with a keyboard / mouse (session 14) d. Editing text – inserting / selecting/ deleting/ moving - sentence / paragraph (session 16) e. Cut / copy / paste (session 17) f. Find and replace (session 17) g. Spell check and grammar (session 17) h. Formatting text (session 17) i. Password protection (session 19) j. Types of paragraph alignment (session 20) k. Format painter (session 23) NOTES ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ _____________________________________________________________________________ 9 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ _________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ _________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ _____________________________________________________________________________ 10 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ _________________________________________________________________ Session 3 & 4 Confirmation on practicals Task Tick whichever applicable Yes No Start MS word, create a new file. Type a paragraph. Do the following a. Cut copy paste b. Bold, italic, underline c. Paragraph alignment d. Edit / delete text e. Insert page numbers f. Insert table g. Header and footer h. Check spelling and grammar i. Word art j. Change text _____________________________________________________________________________ 11 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- colour / font k. Bullets and numbering l. Save file m. Rename file n. Password protect the file o. Any other feature based on student request Signature of the teacher: Signature of the student: _____________________________________________________________________________ 12 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Session 5 Internet surfing, Creating a Google account, Email Confirmation on practicals Task Tick whichever applicable Yes No Open web browsers Browse for 'top business news of the day', pick up 3 important pages and save the same Send / reply and forward an email to / from a friend inviting him / her for your birthday party. Use the formatting tools available in Gmail Learn to upload / download files from Google drive Use Google talk and transfer files using it Signature of the teacher: Signature of the student: _____________________________________________________________________________ 13 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Session 6 Session objectives Revise basics of MS Excel from Semester 1  Creating a spreadsheet and entering data  Saving, deleting a workbook  Moving around in the workbook 1) Revise the following from Sem 1 book a. Excel overview i. File tab ii. Ribbons iii. Title bar iv. Sheet area v. View buttons b. Entering data c. Moving on the spreadsheet with the keyboard and mouse d. Saving a workbook e. Inserting / hiding sheets f. What is a cell/ row / column 2) Notes for the quiz that will be conducted (questions / answers / clarifications) ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ _____________________________________________________________________________ 14 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ____________________________________________ Session 7 Session objectives  Inserting a formula  Excel – copy and paste  Paste special  Find and replace  Special symbols and characters  Adding a Comment to Cell 1) Inserting formula To insert data in excel, we just activate the cell type text or number and press enter or the navigation keys. Similarly, for inserting a formula in MS Excel, go to the formula bar, enter the formula and then press enter or the navigation key. (Shown below) _____________________________________________________________________________ 15 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- 2) Excel – copy and paste MS Excel provides the copy paste option in different ways. Method 1 1) To copy and paste, just select the cells you want to copy. Choose copy option after you right click or press Control + C. 2. Select the cell where you need to paste this copied content. Right click and select paste option or press Control + V. _____________________________________________________________________________ 16 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- In this case, MS Excel will copy everything such as values, formulas, formats, comments and validation. MS Excel will overwrite the content with paste. If you want to undo this, press Control + Z from the keyboard. Method 2 Select the content you wish to copy, write click the mouse and select copy. Go to the desired location and click on paste. Copy paste using office clipboard When you copy data in MS Excel, it puts the copied content in Windows and Office Clipboard. You can view the clipboard content by going to Home -> Clipboard. View the clipboard content. Select the cell where you need to paste; click on paste to paste the content. _____________________________________________________________________________ 17 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- 2) Copy Paste in a Special way In some cases, you may not want to copy everything. For example, you may want to copy only values or you want to copy only the formatting of cells. Select the paste special option as shown below. _____________________________________________________________________________ 18 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Below are the various options available in paste special.  All: Pastes the cell’s contents, formats and data validation from the Windows Clipboard.  Formulas: Pastes formulas, but not formatting.  Values: Pastes only the values, not the formulas.  Formats: Pastes only the formatting of the source range.  Comments: Pastes the comments with the respective cells.  Validation: Pastes validation applied in the cells.  All using source theme: Pastes formulas and all formatting.  All except borders: Pastes everything except borders that appear in the source range.  Column Width: Pastes formulas and also duplicates the column width of the copied cells.  Formulas & Number Formats: Pastes formulas and number formatting only.  Values & Number Formats: Pastes the results of formulas plus the number.  Merge Conditional Formatting: This icon is displayed only when the copied cells contain conditional formatting. When clicked, it merges the copied conditional formatting with any conditional formatting in the destination range.  Transpose: Changes the orientation of the copied range. Rows become columns and columns become rows. Any formulas in the copied range are adjusted so that they work properly when transposed. _____________________________________________________________________________ 19 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- 3) Find and Replace To access the Find & Replace, Choose Home -> Find & Select -> Find or press Control + F Key. See the image below. _____________________________________________________________________________ 20 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Find and Replace dialogue appears as below You can replace the found text with the new text in the Replace tab. _____________________________________________________________________________ 21 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Now, let us see the various options available under the Find dialogue.  Within: Specifying the search should be in the sheet or workbook.  Search By: Specifying the internal search method by rows or by columns.  Look In: If you want to find text in the formula as well, then select this option.  Match Case: If you want to match the case, like the lower case or upper case of words, then check this option.  Match Entire Cell Content: If you want the exact match of the word with the cell, then check this option. _____________________________________________________________________________ 22 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- 4) Excel – Special Symbols Go to Insert » Symbols » Symbol to view available symbols You can see many symbols available there like Pi, alpha, beta, etc. Select the symbol you want to add and click insert to use the symbol. _____________________________________________________________________________ 23 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Special characters Go to Insert » Symbols » Special Characters to view the available special characters. You can see many special characters available there like Copyright, Registered, etc. Select the special character you want to add and click insert to use the special character. 5) Adding a Comment to a Cell Adding a comment to a cell helps in understanding the purpose of cell, what input it should have, etc. It helps in proper documentation. To add a comment to a cell, select the cell and perform any of the actions mentioned below.  Choose Review » Comments » New Comment.  Right click the cell and choose Insert Comment from the available options.  Press Shift+F2. Initially, a comment consists of a computer's user name. You have to modify it with the text for the cell comment. _____________________________________________________________________________ 24 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Modifying a Comment You can modify the comment you have entered as mentioned below.  Select the cell on which the comment appears.  Right click the cell and choose the Edit Comment from the available options.  Modify the comment. _____________________________________________________________________________ 25 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Formatting a Comment Various formatting options are available for comments. For formatting a comment, Right click on cell » Edit comment » Select comment » Right click on it » Format comment. You can change the colour, font, size etc. of the comment. Exercises State whether the following are ‘True’ or ‘False’ 1. It is not possible to insert a comment in a cell in MS excel. 2. Email stands for easy mail. 3. Firefox is a web browser. 4. An operating system is an application software. 5. A motherboard is the main circuit of the computer. 6. In MS Office, Ctrl + S is for spell check. 7. Files and folders deleted from the hard disk are stored in the recycle bin. Match the following 1. Microphone a. Output device 2. Monitor b. 8 bits 3. Ctrl + C c. Input device 4. 1 byte d. Set of interlinked programs 5. Software e. Copy _____________________________________________________________________________ 26 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Session 8 Confirmation on practicals Task Tick whichever applicable Yes No Type a table of two columns and insert simple formulas using * and / Copy paste the table in another sheet as such and with values Use the find and replace option Insert 5 symbols and characters Inserting comments in two cells Signature of the teacher: Signature of the student: Session 9 Session objectives  Format cells for different types of data  Setting fonts in Excel  Background colour  Text alignment  Merge cells  Wrap text and shrink to fit  Borders and shading _____________________________________________________________________________ 27 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- 1) Format cells for different types of data An MS Excel cell can hold different types of data like Numbers, Currency, Dates, etc. You can set the cell type in various ways as shown below:  Right click on the cell » Format cells » Number.  Click on the Ribbon from the ribbon. Cell formats  General: This is the default cell format of a cell.  Number: This displays the cell as a number with a separator.  Currency: This displays a cell as currency, i.e., with a currency sign.  Accounting: Similar to currency and is used for the purpose of accounting.  Date: Various date formats are available under this, like 17-09-2013, 17th-Sep-2013, etc.  Time: Various time formats are available under this like 1.30 PM, 13.30, etc.  Percentage: This displays the cell as a percentage with decimal places, like 50.00%.  Fraction: This displays the cell as a fraction like 1/4, 1/2, etc.  Scientific: This displays the cell as an exponential, like 5.6E+01. _____________________________________________________________________________ 28 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE -----------------------------------------------------  Text: This displays cell as normal text.  Special: Special formats of cell like Zip code, Phone Number.  Custom: You can use custom format by using this. 2) Settings fonts in Excel You can set the font of the selected text from Home » Font group » select the font. Setting a Font from the Format Cell Dialogue  Right click on the cell » Format cells » Font Tab  Press Control + 1 or Shift + Control + F _____________________________________________________________________________ 29 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- 3) Changing the Background Colour By default, the background colour of the cell is white in MS Excel. This can be changed as per the need from Home tab » Font group » Background color. _____________________________________________________________________________ 30 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Changing the Foreground Colour By default, the foreground or text colour is black in MS Excel. This can be changed as per the need from Home tab » Font group » Foreground color. You can also change the foreground colour by selecting the cell Right click » Format cells » Font Tab » Color. _____________________________________________________________________________ 31 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- _____________________________________________________________________________ 32 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- 4) Text Alignment There are two types of alignment in MS excel – horizontal and vertical. Horizontal Alignment: You can set the horizontal alignment to Left, Centre, Right, etc.  Left: Aligns the cell contents to the left side of the cell.  Center: Centres the cell contents in the cell.  Right: Aligns the cell contents to the right side of the cell.  Fill: Repeats the contents of the cell until the cell’s width is filled.  Justify: Justifies the text to the left and right of the cell. This option is applicable only if the cell is formatted as wrapped text and uses more than one line. Vertical Alignment: You can set Vertical alignment to Top, Middle, bottom, etc.  Top: Aligns the cell contents to the top of the cell.  Center: Centers the cell contents vertically in the cell.  Bottom: Aligns the cell contents to the bottom of the cell.  Justify: Justifies the text vertically in the cell; this option is applicable only if the cell is formatted as wrapped text and uses more than one line. _____________________________________________________________________________ 33 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- 5) Merge cells MS Excel enables you to merge two or more cells. When you merge cells, you don’t combine the contents of the cells. Rather, you combine a group of cells into a single cell that occupies the same space. _____________________________________________________________________________ 34 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- You can merge cells by various ways as mentioned below.  Choose Merge & Center control on the Ribbon, which is simpler. To merge cells, select the cells that you want to merge and then click the Merge & Center button.  Choose the Alignment tab of the Format Cells dialogue box to merge the cells. _____________________________________________________________________________ 35 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Additional Options The Home » Alignment group » Merge & Center control contains a drop- down list with these additional options:  Merge Across: When a multi-row range is selected, this command creates multiple merged cells—one for each row.  Merge Cells: Merges the selected cells without applying the Center attribute.  Unmerge Cells: Unmerges the selected cells. 6) Wrap Text and Shrink to Fit If the text is too wide to fit the column width but you don’t want that text to spill over into the adjacent cells, you can either use the Wrap Text option or the shrink to fit option to accommodate that text. _____________________________________________________________________________ 36 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- 7) Borders and Shading MS Excel enables you to apply borders to the cells. For applying a border, select the range of cells Right Click » Format cells » Border Tab » Select the Border Style. Then you can apply a border by going to Home Tab » Font group »Apply Borders _____________________________________________________________________________ 37 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Apply shading You can add shading to the cell from the Home tab » Font Group » Select the Color. _____________________________________________________________________________ 38 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Exercises Write short notes on: a. Cell formats in MS excel (any 5) b. Alignment in MS excel c. Applying borders to a cell / table ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ _____________________________________________________________________________ 39 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ __________ _____________________________________________________________________________ 40 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Session 10 Confirmation on practicals Task Tick whichever applicable Yes No Create a table with 5 columns – name of state, sales in units, sales in value, percentage contribution to total, time of information (5 rows). Show a different cell format required for each column, i.e., general, number, accounting, percentage and time. Show two font options in excel Change the background colour to yellow Vertical and horizontal alignment Format the table with borders and shading Type a header and merge it to cover the 5 columns Signature of the teacher: Signature of the student: _____________________________________________________________________________ 41 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Session 11 Session objectives  Freeze panes  Conditional formatting  Creating a formula  Copying a formula Freeze panes A worksheet with row or column headings will not be visible when you scroll down or to the right. Freezing panes keeps the headings visible while you’re scrolling through the worksheet. Follow the steps mentioned below to freeze panes.  Select the First row or First Column or the row below, which you want to freeze, or the Column right to the area which you want to freeze.  Choose View Tab » Freeze Panes.  Select the suitable option:  Freeze Panes: To freeze area of cells.  Freeze Top Row: To freeze the first row of the worksheet.  Freeze First Column: To freeze the first Column of theworksheet. _____________________________________________________________________________ 42 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE -----------------------------------------------------  If you have selected Freeze top row, you can see the first row appears at the top, even after scrolling. See the screenshot below. Unfreeze Panes Choose View Tab » Unfreeze Panes. Conditional formatting The MS Excel 2010 Conditional Formatting feature enables you to format a range of values so that the values outside certain limits are automatically formatted. Choose Home Tab » Style group » Conditional Formatting dropdown Conditional formatting options  Highlight Cells Rules: It opens a continuation menu with various options for defining the formatting rules that highlight the cells in the cell selection that contain certain values, text or dates, or that have values greater or less than a particular value or that fall within a certain ranges of values. _____________________________________________________________________________ 43 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Suppose you want to find a cell(s) with the Amount 0 and mark them as red. Choose Range of cell » Home Tab » Conditional Formatting Drop Down » Highlight Cell Rules » Equal To. After clicking OK, the cells with value zero are marked as red. _____________________________________________________________________________ 44 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE -----------------------------------------------------  Top/Bottom Rules: It opens a continuation menu with various options for defining the formatting rules that highlight the top and bottom values, percentages and above and below average values in the cell selection. Suppose you want to highlight the top 10% rows, you can do this with these Top/Bottom rules.  Data Bars: It opens a palette with different colour data bars that you can apply to the cell selection to indicate their values relative to each other by clicking the data bar thumbnail. _____________________________________________________________________________ 45 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- With this conditional formatting, data bars will appear in each cell. o Color Scales: It opens a palette with different three- and two-coloured scales that can be applied to the cell selection to indicate their values relative to each other by clicking the color scale thumbnail. _____________________________________________________________________________ 46 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE -----------------------------------------------------  Icon Sets: It opens a palette with different sets of icons that can be applied to the cell selection to indicate their values relative to each other by clicking the icon set.  New Rule: It opens the New Formatting Rule dialog box, where you define a custom conditional formatting rule to apply to the cell selection.  Clear Rules: It opens a continuation menu, where you can remove the conditional formatting rules for the cell selection by clicking the Selected Cells option, for the entire worksheet by clicking the Entire Sheet option or for just the current data table by clicking the This Table option.  Manage Rules: It opens the Conditional Formatting Rules Manager dialog box where you edit and delete particular rules, as well as adjust their rule precedence by moving them up or down in the Rules List box. Formulas play an important role in any job involving data entry and analysis. Once you learn how to use the formulas properly and create them as required, it will significantly reduce the effort involved in entering data. _____________________________________________________________________________ 47 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Creating formulas Formulas use a variety of operators and worksheet functions to work with values and text. The values and text used in formulas can be located in other cells, which makes changing the data easy and gives worksheets their dynamic nature. For example, you can quickly change the data in a worksheet and this makes the formulas works. Elements of formulas A formula can consist of any of these elements:  Mathematical operators, such as + (for addition) and * (for multiplication). E.g., =A1+A2 adds the values in cells A1 and A2.  Values or text E.g., =200*0.5 multiplies 200 times 0.15. This formula uses only values and it always returns the same result as 100.  Cell references (including named cells and ranges) Example: =A1=C12 compares cell A1 with cell C12. If the cells are identical, the formula returns TRUE; otherwise, it returns FALSE.  Worksheet functions (such as, SUM or AVERAGE) Example: =SUM (A1:A12) adds the values in the range A1:A12. Creating a formula To create a formula, you need to type in the Formula Bar. A formula begins with an '=' sign. When building formulas manually, you can either type in the cell addresses or you can point to them in the worksheet. Using the Pointing method to supply the cell addresses for formulas is often easier and a more powerful method of formula building. When you are using built-in functions, you click the cell or drag through the cell range that you want to use when defining the function’s arguments in the Function Arguments dialog box. See the screenshot below. _____________________________________________________________________________ 48 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- As soon as you complete a formula entry, Excel calculates the result, which is then displayed inside the cell within the worksheet (the contents of the formula, however, continue to be visible on the formula bar anytime the cell is active). If you make an error in the formula that prevents Excel from being able to calculate the formula at all, Excel displays an Alert dialog box suggesting how to fix the problem. Copying formulas When a formula uses cell references rather than constant values, Excel makes the task of copying an original formula to every place that requires a similar formula. MS Excel does it automatically adjusting the cell references in the original formula to suit the position of the copies that you make. It does this through a system known as relative cell addresses, whereby the column references in the cell address in the formula change to suit their new column position and the row references change to suit their new row position. Let us see this with the help of an example. Suppose we want the sum of all the rows in the last row, then we will write a formula for the first column, i.e., B. We want the sum of the rows from 3 to 8 in the 9th row. _____________________________________________________________________________ 49 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- After writing the formula in the 9th row, we can drag it to the remaining columns and the formula gets copied. After dragging, we can see the formula in the remaining columns as:  column C : =SUM(C3:C8)  column D : =SUM(D3:D8)  column E : =SUM(E3:E8)  column F : =SUM(F3:F8)  column G : =SUM(G3:G8) _____________________________________________________________________________ 50 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Exercise Fill in the blanks a. ____________________ keeps the headings visible while you are scrolling through the worksheet. (freeze panes / unfreeze panes / borders / filter) b. The ___________________________ feature enables you to format a range of values so that the values outside certain limits are automatically formatted. (formatting/ conditional formatting / ranging / inserting formula) c. _________________________ use a variety of operators and worksheet functions to work with values and text. (Formulas/ filter functions / text alignment / sort functions) d. Formulas must begin with ______ or ______ sign. (- or +, + or =, - or =, \ or *) Write the formulas for the following 1. Sum of cells A1 to A10 2. Multiply cells B12 with B16 3. Find the average of the cell range B10 to B25 4. Find the weighted average product of the cell range B10 to B25 _____________________________________________________________________________ 51 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Session 12 Task Tick whichever applicable Yes No Create a table with imaginary data of marks of 10 students and do the following:  Freeze panes  Conditional formatting  Creating a formula Signature of the teacher: Signature of the student: _____________________________________________________________________________ 52 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Session 13 Session objectives  Cell references in formula  Using functions in excel  Built-in functions 1) Cell references in formula Most formulas you create include references to cells or ranges. These references enable your formulas to work dynamically with the data contained in those cells or ranges. For example, if your formula refers to cell C2 and you change the value contained in C2, the formula result reflects a new value automatically. If you didn’t use references in your formulas, you would need to edit the formulas themselves in order to change the values used in the formulas. When you use a cell (or range) reference in a formula, you can use three types of references: relative, absolute and mixed references. Relative Cell references The row and column references can change when you copy the formula to another cell because the references are actually offsets from the current row and column. By default, Excel creates relative cell references in formulas. _____________________________________________________________________________ 53 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Absolute cell references The row and column references do not change when you copy the formula because the reference is to an actual cell address. An absolute reference uses two dollar signs in its address: one for the column letter and one for the row number (for example, $A$5). Mixed cell references Both the row or column reference is relative and the other is absolute. Only one of the address parts is absolute (for example, $A5 or A$5). _____________________________________________________________________________ 54 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- 2) Using functions in excel When you type the = sign and then type any alphabet, you will see the function that they had searched earlier as shown below. If you want to determine the largest value in a range—a formula can’t tell the answer without using a function. We will use formula that uses the MAX function to return the largest value in the range B3:B8 as =MAX (A1:D100). _____________________________________________________________________________ 55 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Suppose you want to find if the cell of month is greater than 1900, then we can give a bonus to the sales representative. We can achieve this by writing a formula with the IF function as =IF (B9>1900,"Yes","No") _____________________________________________________________________________ 56 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Function arguments Functions vary in how they use arguments. Depending on what it has to do, a function may use:  No arguments: Examples: Now(),Date(),etc.  One argument: UPPER(),LOWER(),etc.  A fixed number of arguments: IF(),MAX(),MIN(),AVERGAGE(),etc.  Infinite number of arguments  Optional arguments 3) Built-in functions MS Excel has many built-in functions, which we can use in our formula. To see all the functions by category, choose Formulas Tab » Insert Function. Then the Insert function Dialog appears, from which we can choose the function. _____________________________________________________________________________ 57 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Functions by category  Text Functions  LOWER: Converts all characters in a supplied text string to lower case  UPPER: Converts all characters in a supplied text string to upper case  TRIM: Removes duplicate spaces and spaces at the start and end of a text string  CONCATENATE: Joins together two or more text strings.  LEFT: Returns a specified number of characters from the start of a supplied text string  MID: Returns a specified number of characters from the middle of a supplied text string  RIGHT: Returns a specified number of characters from the end of a supplied text string  LEN: Returns the length of a supplied text string  FIND: Returns the position of a supplied character or text string from within a supplied text string (case-sensitive)  Date & Time  DATE: Returns a date from a user-supplied year, month and day  TIME: Returns a time from a user-supplied hour, minute and second  DATEVALUE: Converts a text string showing a date, to an integer that represents the date in Excel's date-time code  TIMEVALUE: Converts a text string showing a time, to a decimal that represents the time in Excel.  NOW: Returns the current date & time.  TODAY: Returns today's date.  Statistical  MAX: Returns the largest value from a list of supplied numbers  MIN: Returns the smallest value from a list of supplied numbers  AVERAGE: Returns the Average of a list of supplied numbers _____________________________________________________________________________ 58 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE -----------------------------------------------------  COUNT: Returns the number of numerical values in a supplied set of cells or values.  COUNTIF: Returns the number of cells (of a supplied range) that satisfies a given criteria.  SUM: Returns the sum of a supplied list of numbers  Logical  AND: Tests a number of user-defined conditions and returns TRUE if ALL of the conditions evaluate to TRUE, or otherwise FALSE  OR: Tests a number of user-defined conditions and returns TRUE if ANY of the conditions evaluate to TRUE or FALSE otherwise.  NOT: Returns a logical value that is the opposite of a user supplied logical value or expression, i.e., returns FALSE if the supplied argument is TRUE and returns TRUE if the supplied argument is FALSE.  Math & Trig  ABS: Returns the absolute value (i.e., the modulus) of a supplied number  SIGN: Returns the sign (+1, -1 or 0) of a supplied number  SQRT: Returns the positive square root of a given number  MOD: Returns the remainder from a division between two supplied numbers Exercises: 1. Write the function that needs to be used to perform the task given: 1. Tests a number of user-defined conditions and returns TRUE if ALL of the conditions evaluate to TRUE, or FALSE otherwise. 2. Returns the Average of a list of supplied numbers. 3. Returns today's date 4. Returns the positive square root of a given number. 5. Joins together two or more text strings. 6. Converts all characters in a supplied text string to lower case. 7. Returns the current date & time. _____________________________________________________________________________ 59 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- 8. Returns the number of numerical values in a supplied set of cells or values. 9. Tests a number of user-defined conditions and returns TRUE if ANY of the conditions evaluate to TRUE or FALSE otherwise. 10. Returns the number of cells (of a supplied range) that satisfy a given criteria. B. Distinguish between relative cell reference and absolute cell reference. ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ________________________ _____________________________________________________________________________ 60 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Session 14 Confirmation of practicals Task Tick whichever applicable Yes No  How to create cell references in a formula – all types  How to insert functions and use one built-in function from each category  Usage of commonly used built-in functions Signature of the teacher: Signature of the student: _____________________________________________________________________________ 61 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Session 15 Session objectives  Data filtering  Data sorting  Using ranges  Using macros 1) Data filtering Filtering data in MS Excel refers to displaying only the rows that meet certain conditions; the other rows get hidden. Using the store data, if you are interested in seeing data where the shoe size is 36, then you can set a filter to do this. Follow the below mentioned steps to do this.  Place a cursor on the Header Row.  Choose Data Tab » Filter to set the filter. _____________________________________________________________________________ 62 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE -----------------------------------------------------  Click the drop-down arrow in the Area Row Header and remove the check mark from Select All, which unselects everything.  Then select the check mark for size 36, which will filter the data and display data of shoe size 36.  Some of the row numbers are missing; these rows contain the filtered (hidden) data.  There is a drop-down arrow in the Area column, which now shows a different graphic — an icon that indicates the column is filtered. _____________________________________________________________________________ 63 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Using multiple filters You can filter the records by multiple conditions, i.e., by multiple column values. For instance, after size 36 is filtered, if you need to have the filter where the colour is equal to coffee; then, after setting the filter for the shoe size, choose the color column and then set the filter for color. 2) Data sorting Sorting data in MS Excel rearranges the rows based on the contents of a particular column. You may want to sort a table to put the names in alphabetical order. Or, maybe you want to sort data by amount from smallest to largest or largest to smallest. To Sort the data, follow the steps mentioned below:  Select the Column by which you want to sort data.  Choose Data Tab » Sort Below dialog appears. _____________________________________________________________________________ 64 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE -----------------------------------------------------  To sort data based on a selected column, Choose Continue with the selection or if you want sorting based on other columns, choose the Expand Selection.  Sorting can be based on the below conditions: Values: Alphabetically or numerically Cell Color: Based on the colour of the cell Font Color: Based on the colour of the font Cell Icon: Based on the cell icon _____________________________________________________________________________ 65 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE -----------------------------------------------------  Clicking OK will sort the data  The sorting option is also available from the Home Tab. Choose Home Tab » Sort & Filter. You can see the same dialog to sort records. _____________________________________________________________________________ 66 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- 3) Using ranges A cell is a single element in a worksheet that can hold a value, some text or a formula. A cell is identified by its address, which consists of its column letter and row number. For example, cell B1 is the cell in the second column and the first row. A group of cells is called a range. You designate a range address by specifying its upper-left cell address and its lower-right cell address, separated by a colon. Example of Ranges:  C24: A range that consists of a single cell.  A1:B1: Two cells that occupy one row and two columns.  A1:A100: 100 cells in column A.  A1:D4: 16 cells (four rows by four columns). Selecting ranges You can select a range in several ways:  Press the left mouse button and drag, highlighting the range. Then release the mouse button. If you drag to the end of the screen, the worksheet will scroll.  Press the Shift key while you use the navigation keys to select a range.  Press F8 and then move the cell pointer with the navigation keys to highlight the range. Press F8 again to return the navigation keys to normal movement.  Type the cell or range address into the Name box and press Enter. Excel selects the cell or range that you specified. _____________________________________________________________________________ 67 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Selecting rows and columns When you need to select an entire row or column, you can select entire rows and columns in much the same manner as you select ranges:  Click the row or column border to select a single row or column.  To select multiple adjacent rows or columns, click a row or column border and drag to highlight additional rows or columns.  To select multiple (nonadjacent) rows or columns, press Ctrl while you click the row or column borders that you want. _____________________________________________________________________________ 68 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- 4) Using macros Macros enable you to automate almost any task that you can undertake in Excel 2010. By using the macro recorder from View Tab » Macro Dropdown to record tasks that you perform routinely, not only do you speed up the procedure considerably, but you are also assured that each step in a task is carried out the same way each and every time you perform a task. To view macros, choose View Tab » Macro dropdown. Macro options The View tab contains a Macros command button, which has a dropdown menu that has the following three options.  View Macros: Opens the Macro dialog box where you can select a macro to run or edit.  Record Macro: Opens the Record Macro dialog box where you define the settings for your new macro and then start the macro recorder; this is the same as clicking the Record Macro button on the Status bar.  Use Relative References: Uses relative cell addresses when recording a macro, making the macro more versatile by enabling you to run it in areas of a worksheet other than the ones originally used in the macro’s recording. _____________________________________________________________________________ 69 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Creating macros You can create macros in one of two ways:  Use MS Excel’s macro recorder to record your actions as you undertake them in a worksheet.  Enter the instructions that you want to be followed in a VBA code in the Visual Basic Editor. Example - Create a simple macro that will automate the task of making the cell content Bold and apply the cell colour. Choose View Tab » Macro dropdown. Click on Record Macro as below. Now, the Macro recording will start. Do the steps of action, which are to be performed repeatedly. Macro will record those steps. You can stop the macro recording once you are done with all the steps. _____________________________________________________________________________ 70 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Editing macros Editing macro will take you to the VBA programming editor. _____________________________________________________________________________ 71 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Exercise Answer the following 1. How do you create macros in MS excel? ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ _______________ 2. Which option do you need to use to arrange the data in alphabetical order? ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________ _____________________________________________________________________________ 72 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- 3. Write the steps that are required to display the students with top 20% marks from a list of marks scored by 50 students. ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ________________________ _____________________________________________________________________________ 73 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Session 16 Confirmation of practicals Task Tick whichever applicable Yes No For the list that was created earlier of the marks of students:  Filter students with marks over the passing limit  Sort students in ascending and descending order of marks  Create a macro to make the cell bold, italic, underline  Show cell ranges Signature of the teacher: Signature of the student: _____________________________________________________________________________ 74 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Session 17 Session objectives  Cross referencing – VLOOKUP function  Pivot table 1) Cross referencing – VLOOKUP function Look up functions help to bring data together into a meaningful list or table. VLOOKUP function VLOOKUP function searches for a value vertically down for the lookup table. VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) has 4 parameters as shown below:  lookup_value: It is the user input. This is the value that the function uses to search on.  The table_array: It is the area of cells in which the table is located. This includes not only the column being searched on, but the data columns for which you are going to get the values that you need.  Col_index_num: It is the column of data that contains the answer that you want.  Range_lookup: It is a TRUE or FALSE value. When set to TRUE, the lookup function gives the closest match to the lookup_value without going over the lookup_value. When set to FALSE, an exact match must be found to the lookup_value or the function will return #N/A. Note, this requires that the column containing the lookup_value must be formatted in ascending order. Example: Each spreadsheet contains information about the same group of people. The first spreadsheet has their dates of birth and the second shows their favourite colour. How do we build a list showing the person's name, their date of birth and their favourite colour? VLOOOKUP will help in this case. First of all, let us see data in both the sheets. _____________________________________________________________________________ 75 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- This is the data in the first sheet. This is the data in the second sheet. _____________________________________________________________________________ 76 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Now, to find the respective favourite colour for that person from another sheet, we need to vlookup the data. The first argument to VLOOKUP is the lookup value (in this case, it is the person’s name). The second argument is the table array, which is the table in the second sheet from B2 to C11. The third argument to VLOOKUP is the Column index num, which is the answer we are looking for. In this case, it is 2; the colour column number is 2. The fourth argument is True, returning a partial match or False, returning an exact match. After applying the VLOOKUP formula, it will calculate the colour and the results are displayed as shown below. As you can see in the above screenshot, the results of VLOOKUP have searched for the colour in the second sheet table. It has returned #N/A where a match was not found. In this case Andy's data was not present in the second sheet, so it returned #N/A. _____________________________________________________________________________ 77 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- 2) Pivot table A pivot table is essentially a dynamic summary report generated from a database. The database can reside in a worksheet (in the form of a table) or in an external data file. A pivot table can help transform endless rows and columns of numbers into a meaningful presentation of the data. Pivot tables are a very powerful tool for a summarized analysis of data. Pivot tables are available under - Insert tab » PivotTable dropdown » PivotTable. Example: If you have a huge data of voters and you want to see the summarized data of voter information per party, then you can use the Pivot table for it. Choose the Insert tab » Pivot Table to insert a pivot table. MS Excel selects the data of the table. You can select the pivot table location as an existing sheet or a new sheet. This will generate the Pivot table pane as shown below. There are various options available in the Pivot table pane. You can select the fields for the generated pivot table. _____________________________________________________________________________ 78 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE -----------------------------------------------------  Column labels: It is a field that has a column orientation in the pivot table. Each item in the field occupies a column.  Report Filter: You can set the filter for the report as year; then the data gets filtered as per the year.  Row labels: It is a field that has a row orientation in the pivot table. Each item in the field occupies a row.  Values area: The cells in a pivot table that contain the summary data. Excel offers several ways to summarize the data (sum, average, count, and so on).  After giving the input fields to the pivot table, it generates the pivot table with the data as shown below. _____________________________________________________________________________ 79 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- _____________________________________________________________________________ 80 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Session 18 Confirmation of practicals Task Tick whichever applicable Yes No  Create a pivot table report from a data table  Show the VLOOKUP function Signature of the teacher: Signature of the student: Session 19 Session objectives  Charts  Pivot charts  List of excel keyboard short cuts 1) Charts A chart is a visual representation of numeric values. Charts (also known as graphs) have been an integral part of spreadsheets. Excel provides the tools to create a wide variety of highly customizable charts. Displaying data in a well-conceived chart can make numbers more understandable. Because a chart presents a picture, charts are particularly useful for summarizing a series of numbers and their interrelationships. _____________________________________________________________________________ 81 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Types of charts There are various types of charts available in MS Excel as shown in the screenshot below.  Column: A column chart shows data changes over a period of time or illustrates comparisons among items.  Bar: A bar chart illustrates comparisons among individual items. Pie: A pie chart shows the size of items that make up a data series, proportional to the sum of the items. It always shows only one data series and is useful when you want to emphasize a significant element in the data.  Line: A line chart shows trends in data at equal intervals.  Area: An area chart emphasizes the magnitude of change over time.  X Y Scatter: An xy (scatter) chart shows the relationships among the numeric values in several data series or plots two groups of numbers as one series of xy coordinates.  Stock: This chart type is most often used for stock price data, but can also be used for scientific data (for example, to indicate temperature changes).  Surface: A surface chart is useful when you want to find the optimum combinations between two sets of data. As in a topographic map, colours and patterns indicate areas that are in the same range of values.  Doughnut: Like a pie chart, a doughnut chart shows the relationship of parts to a whole; however, it can contain more than one data series. _____________________________________________________________________________ 82 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE -----------------------------------------------------  Bubble: Data arranged in columns on a worksheet, so that x values are listed in the first column and corresponding y values and bubble size values are listed in adjacent columns, can be plotted in a bubble chart.  Radar: A radar chart compares the aggregate values of a number of data series. Creating a chart To create charts for the data, follow the steps mentioned below:  Select the data for which you want to create the chart.  Choose Insert Tab » Select the chart or click on the Chart group to see various chart types.  Select the chart of your choice and click OK to generate the chart. _____________________________________________________________________________ 83 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- Editing a chart A chart can be edited at any time after creating it. You can select the different data for the chart input with: Right click on chart » Select data. Selecting new data will generate the chart as per the new data, as shown in the screenshot below. You can change the X axis and Y axis of the chart by giving different inputs to the X-axis and Y-axis of the chart. _____________________________________________________________________________ 84 | Computing Skills – 2 | PW  ------------------------------------------------------------ TISS – SVE ----------------------------------------------------- 2) Pivot charts A pivot chart is a graphical representation of a data summary, displayed in a pivot table. It is always based on a pivot table. All Excel charting features are available in a pivot chart. Pivot charts are available under the Insert tab » PivotTable dropdown » PivotChart. Example: If