Hrdiscussion.COM Module 4 PDF

Preview

Summary

Download Hrdiscussion.COM Module 4 PDF

Description

ECDL Module 4 REFERENCE MANUAL Spreadsheets Microsoft Excel XP Edition for ECDL Syllabus Four

PAGE 2 - ECDL MODULE 4 (USING OFFICE XP) - MANUAL

© 1995-2005 Cheltenham Courseware Ltd. Crescent House 24 Lansdown Crescent Lane Cheltenham Gloucestershire GL50 2LD, UK Tel: +44 (0)1242 227200 Fax: +44 (0)1242 253200 Email: [email protected] Internet: http://www.cheltenhamcourseware.com

All trademarks acknowledged. E&OE. © Cheltenham Courseware Ltd. 1995-2005 No part of this document may be copied without written permission from Cheltenham Courseware unless produced under the terms of a courseware site license agreement with Cheltenham Courseware. All reasonable precautions have been taken in the preparation of this document, including both technical and non-technical proofing. Cheltenham Courseware and all staff assume no responsibility for any errors or omissions. No warranties are made, expressed or implied with regard to these notes. Cheltenham Courseware shall not be responsible for any direct, incidental or consequential damages arising from the use of any material contained in this document. If you find any errors in these training modules, please inform Cheltenham Courseware. Whilst every effort is made to eradicate typing or technical mistakes, we apologise for any errors you may detect. All courses are updated on a regular basis, so your feedback is both valued by us and will help us to maintain the highest possible standards.

Sample versions of courseware from Cheltenham Courseware (Normally supplied in Adobe Acrobat format) If the version of courseware that you are viewing is marked as NOT FOR TRAINING, SAMPLE, or similar, then it cannot be used as part of a training course, and is made available purely for content and style review. This is to give you the opportunity to preview our courseware, prior to making a purchasing decision. Sample versions may not be re-sold to a third party.

For current license information Cheltenham Courseware reserves the right to alter the licensing conditions at any time, without prior notice. No terms or conditions will affect your rights as defined under UK law. Please see the site license agreement available at: www.cheltenhamcourseware.com/agreement

PLEASE NOTE: Web sites listed or illustrated within this document are purely for illustrative purposes and their inclusion does not imply endorsement by the authors or suppliers of this document. No company or organisation has paid to have their web sites or other information included within this document.

FOR USE AT THE LICENSED SITE(S) ONLY © Cheltenham Courseware Ltd. 1995-2005 www.cheltenhamcourseware.com

PAGE 3 - ECDL MODULE 4 (USING OFFICE XP) - MANUAL

ECDL Approved Courseware IMPORTANT - PLEASE READ As a condition of providing official approval for this courseware, the ECDL Foundation demand that the following statement appear within all approved ECDL courseware, produced by third party courseware providers: ""European Computer Driving Licence" and ECDL and Stars device are registered trade marks of the European Computer Driving Licence Foundation Limited in Ireland and other countries. Cheltenham Courseware Ltd is an independent entity from the European Computer Driving Licence Foundation Limited, and not affiliated with the European Computer Driving Licence Foundation Limited in any manner. ‘Cheltenham Courseware Ltd ECDL Courseware’ may be used in assisting students to prepare for the European Computer Driving Licence Examination. Neither the European Computer Driving Licence Foundation Limited nor Cheltenham Courseware Ltd warrants that the use of this ‘Cheltenham Courseware Ltd ECDL Courseware’ will ensure passing the relevant Examination. Use of the ECDL-F approved Courseware Logo on this product signifies that it has been independently reviewed and approved in complying with the following standards: Acceptable coverage of all courseware content related to the ECDL Syllabus Version 4.0. This courseware material has not been reviewed for technical accuracy and does not guarantee that the end user will pass the associated ECDL Examinations. Any and all assessment tests and/or performance based exercises contained in this ‘Cheltenham Courseware Ltd ECDL Courseware’ relate solely to this ‘Cheltenham Courseware Ltd ECDL Courseware’ and do not constitute, or imply, certification by the European Driving Licence Foundation in respect of any ECDL Examinations. For details on sitting ECDL Examinations in your country please contact the local ECDL Licensee or visit the European Computer Driving Licence Foundation Limited web site at http://www.ecdl.com. “Candidates using this courseware material should have a valid ECDL/ICDL Skills Card/Log book. Without such a skills card/Log book no ECDL/ICDL tests can be taken, no ECDL/ICDL certificate, nor any other form of recognition can be given to the candidate. ECDL/ICDL Skills Cards may be obtained from any accredited ECDL/ICDL Test Centre or from your country's National ECDL/ICDL designated Licensee". References to the European Computer Driving Licence (ECDL) include the International Computer Driving Licence (ICDL). ECDL Syllabus Version 4.0 is published as the official syllabus for use within the European Computer Driving Licence (ECDL) and International Computer Driving Licence (ICDL) certification programme."

IMPORTANT: - Regarding ECDL courseware purchased from Cheltenham Courseware Ltd. Cheltenham Courseware Ltd accept no liability whatsoever arising from any changes which you make to this courseware, with, or without, ECDL Foundation approval.

FOR USE AT THE LICENSED SITE(S) ONLY © Cheltenham Courseware Ltd. 1995-2005 www.cheltenhamcourseware.com

PAGE 4 - ECDL MODULE 4 (USING OFFICE XP) - MANUAL

4.1 USING THE APPLICATION........................................................................................................................ 6 4.1.1 FIRST STEPS WITH SPREADSHEETS ........................................................................................................ 6 4.1.1.1 Open (and close) a spreadsheet application. ............................................................................ 6 4.1.1.2 Open one, several spreadsheets................................................................................................. 6 4.1.1.3 Create a new spreadsheet (default template). .......................................................................... 8 4.1.1.4 Save a spreadsheet to a location on a drive.............................................................................. 9 4.1.1.5 Save a spreadsheet under another name.................................................................................. 9 4.1.1.6 Save a spreadsheet in another file type such as: text file, HTML, template, software specific file extension, version number. ................................................................................................. 10 4.1.1.7 Switch between worksheets, open spreadsheets................................................................... 13 4.1.1.8 Use available Help functions...................................................................................................... 13 4.1.1.9 Close a spreadsheet. .................................................................................................................. 15 4.1.2 ADJUST SETTINGS .................................................................................................................................. 16 4.1.2.1 Use magnification/zoom tool...................................................................................................... 16 4.1.2.2 Display or hide built-in toolbars.................................................................................................. 16 4.1.2.3 Freeze, unfreeze row and/or column titles............................................................................... 16 4.1.2.4 Modify basic options/preferences in the application: user name, default directory/ folder to open, save spreadsheets......................................................................................................................... 17 4.2 CELLS .......................................................................................................................................................... 19 4.2.1 INSERT DATA .......................................................................................................................................... 19 4.2.1.1 Enter a number, a date, text in a cell. ....................................................................................... 19 4.2.2 SELECT CELLS ....................................................................................................................................... 19 4.2.3.1 Select a cell, range of adjacent cells, range of non-adjacent cells, entire worksheet. ...... 19 4.2.2.2 Select a row, range of adjacent rows, range of non-adjacent rows. .................................... 21 4.2.2.3 Select a column, range of adjacent columns, range of non-adjacent columns. ................. 21 4.2.3 ROWS AND COLUMNS............................................................................................................................. 22 4.2.3.1 Insert rows, columns in a worksheet......................................................................................... 22 4.2.3.2 Delete rows and columns in a worksheet................................................................................. 23 4.2.3.3 Modify column widths and row heights..................................................................................... 23 4.2.4 EDIT DATA .............................................................................................................................................. 24 4.2.4.1 Insert additional cell content, replace existing cell content.................................................... 24 4.2.4.2 Use the undo, redo command.................................................................................................... 25 4.2.5 DUPLICATE, MOVE, DELETE................................................................................................................... 25 4.2.5.1 Duplicate the content of a cell, cell range within a worksheet, between worksheets, between open spreadsheets. .................................................................................................................. 26 4.2.5.2 Use the autofill tool/copy handle tool to copy, increment data entries................................. 27 4.2.5.3 Move cell range contents, entire row(s), entire column(s) within a worksheet, between worksheets, between open spreadsheets............................................................................................. 28 4.2.5.4 Delete cell contents. .................................................................................................................... 29 4.2.6 SEARCH AND REPLACE .......................................................................................................................... 30 4.2.6.1 Use the search command for specific content in a worksheet.............................................. 30 4.2.6.2 Use the replace command for specific content in a worksheet............................................. 31 4.2.7 SORT DATA............................................................................................................................................. 31 4.2.7.1 Sort a cell range by one criterion in ascending, descending numeric order, ascending, descending alphabetic order. .................................................................................................................. 31 4.3 WORKSHEETS........................................................................................................................................... 33 4.3.1 HANDLING WORKSHEETS ....................................................................................................................... 33 4.3.1.1 Insert a new worksheet. .............................................................................................................. 33 4.3.1.2 Rename a worksheet. ................................................................................................................. 34 4.3.1.3 Delete a worksheet...................................................................................................................... 34 4.3.1.4 Duplicate a worksheet within a spreadsheet or between open spreadsheets.................... 35 4.3.1.5 Move a worksheet within a spreadsheet or between open spreadsheets........................... 37 4.4 FORMULAS AND FUNCTIONS............................................................................................................... 40 4.4.1 ARITHMETIC FORMULAS ......................................................................................................................... 40

FOR USE AT THE LICENSED SITE(S) ONLY © Cheltenham Courseware Ltd. 1995-2005 www.cheltenhamcourseware.com

PAGE 5 - ECDL MODULE 4 (USING OFFICE XP) - MANUAL 4.4.1.1 Generate formulas using cell references and arithmetic operators (addition, subtraction, multiplication, division). ............................................................................................................................ 40 4.4.2.1 Recognise and understand standard error values associated with using formulas........... 41 4.4.2 CELL REFERENCING ............................................................................................................................... 44 4.4.2.1 Understand and use relative, mixed, absolute cell referencing in formulas........................ 44 4.4.3 WORKING WITH FUNCTIONS................................................................................................................... 46 4.4.3.1 Generate formulas using sum, average, minimum, maximum, count, functions................ 47 4.4.3.2 Generate formulas using the logical function if (yielding one of two specified values)...... 52 4.5 FORMATTING............................................................................................................................................. 56 4.5.1 NUMBERS/DATES ................................................................................................................................... 56 4.5.1.1 Format cells to display numbers to a specific number of decimal places, to display numbers with, without commas to indicate thousands. ....................................................................... 56 4.5.1.2 Format cells to display a date style. .......................................................................................... 57 4.5.1.3 Format cells to display a currency symbol. .............................................................................. 58 4.5.1.4 Format cells to display numbers as percentages.................................................................... 58 4.5.2 CONTENTS .............................................................................................................................................. 60 4.5.2.1 Change cell content appearance: font sizes, font types. ....................................................... 60 4.5.2.2 Apply formatting to cell contents such as: bold, italic, underline, double underline........... 60 4.5.2.3 Apply different colours to cell content, cell background......................................................... 62 4.5.2.4 Copy the formatting from a cell, cell range to another cell, cell range................................. 63 4.4.2.5 Apply text wrapping to contents within a cell. .......................................................................... 63 4.5.3 ALIGNMENT BORDER EFFECTS .............................................................................................................. 64 4.5.3.1 Align contents in a cell range: left, centre, right; top and, bottom......................................... 64 4.5.3.2 Centre a title over a cell range................................................................................................... 65 4.5.3.3 Adjust cell content orientation.................................................................................................... 66 4.5.3.4 Add border effects to a cell, cell range. .................................................................................... 66 4.6 CHARTS / GRAPHS .................................................................................................................................. 69 4.6.1 USING CHARTS/GRAPHS........................................................................................................................ 69 4.6.1.1 Create different types of charts/graphs from spreadsheet data: column chart, bar chart, line chart, pie chart.................................................................................................................................... 69 4.6.1.2 Add a title or label to a chart/graph. Remove a title or label from a chart/graph. ............... 71 4.6.1.3 Change the background colour in a chart/graph..................................................................... 72 4.6.1.4 Change the column, bar, line, pie slice colours in a chart/graph. ......................................... 73 4.6.1.5 Change the chart/graph type...................................................................................................... 74 4.6.1.6 Duplicate, move charts/graphs within a worksheet, between open spreadsheets. ........... 75 4.6.1.7 Resize, delete charts/graphs...................................................................................................... 76 4.7 PREPARE OUTPUTS ................................................................................................................................ 78 4.7.1 WORKSHEET SETUP............................................................................................................................... 78 4.7.1.1 Change worksheet margins: top, bottom, left, right ................................................................ 78 4.7.1.2 Change worksheet orientation: portrait, landscape. Change paper size............................. 78 4.7.1.3 Adjust page setup to fit worksheet contents on one page, on a specific number of pages. ..................................................................................................................................................................... 79 4.7.1.4 Add, modify text in Headers, Footers in a worksheet............................................................. 79 4.7.1.5 Insert fields: page numbering information, date, time, file name, worksheet name into headers....................................................................................................................................................... 80 4.7.2 PREPARATION......................................................................................................................................... 82 4.7.2.1 Understand the importance of checking spreadsheet calculations and text before distribution.................................................................................................................................................. 82 4.7.2.2 Preview a worksheet. .................................................................................................................. 83 4.7.2.3 Turn on, off display of gridlines, display of row and column headings for printing purposes..................................................................................................................................................... 83 4.7.2.4 Apply automatic title row(s) printing on every page of a printed worksheet........................ 84 4.7.3 PRINTING ................................................................................................................................................ 84 4.7.3.1 Print a cell range from a worksheet, an entire worksheet, number of copies of a worksheet, the entire spreadsheet, a selected chart. ..........................................................................