ECDL Module 4

ECDL Module 4Document 353Version 2Information Systems EISD

Information SystemsPart of the Education & Information Support DivisionTitle:Excel 2002 (XP) Part 1Authors:Rachel Healy and Fiona StrawbridgeReference:Doc 353 v2ECDL:Module 4 Spreadsheets (Part 1 of 2)Date:September 2002Revisions:Updated from Excel 97 to Excel 2002 (XP) - Aug 2002 by FionaStrawbridge and Tamsin GriffithAbstractMicrosoft Excel is a spreadsheet application used for manipulating and calculating numericaldata, and forms part of the Microsoft Office XP suite application. This workbook is aimed atusers who are new to spreadsheets and to the Excel package. It has been designed toaccompany the Information Systems Excel 2002 (XP) Part 1 course (seewww.ucl.ac.uk/is/training for course details) and it can be used as a self-paced tutorial.The European Computer Driving Licence (ECDL)Excel 2002 Part 1 is the first of two workbooks designed to cover the ECDL Module 4Spreadsheet syllabus. It is one in a series of workbooks designed to cover the seven modulesof the ECDL Syllabus (Version 3.0). For further information visit the ECDL web pages It is assumed in this Workbook that you have the requisite keyboard skills and knowledge ofa PC including file handling and data storage. It is also assumed that you are familiar withWindows and know how to use a mouse. If you are unfamiliar with any of these topics pleaseconsult the other workbooks in the series.Please NoteExcel 2002 (XP) can be accessed from UCL Information Systems (IS) PC Workstationsrunning WTS1. It is assumed in this Workbook that you are a registered user (i.e. you have anIS user ID and password) using a PC on the Information Systems WTS Service.Microsoft is a registered trademark and Windows is a trademark of Microsoft Corporation. Screenshots re-printed by permission from Microsoft Corporation.1WTS is the Managed PC Service. It provides a Windows 2000 environment.

Part 1ExcelContents1.What is a Spreadsheet? .11.11.21.31.41.52.Help Features .72.13.To Edit in the Formula Bar .17To Edit in the Cell.17Selecting Data .18Copying and Moving Data.19Deleting Data .20Deleting and Inserting Rows and Columns .20Undo and Redo .21Find and Replace.21Creating Simple Formulae .236.16.26.37.To Close a Workbook .16To Exit Excel .16Editing a Worksheet .175.15.25.35.45.55.65.75.86.To Create a New Workbook .9To Open a Workbook.9Entering Data .10Data Entry Techniques.10Entering Dates and Times.11Entering a Series of Numbers or Dates.12Saving Your Work .154.24.35.Getting Help.7Using a Worksheet .93.13.23.33.43.53.64.The Excel Environment .1Task Panes .2Workbooks and Worksheets .3Accessing Commands.3Moving around the Worksheet.5Essential Facts about Formulae .23Simple Formulae.23Copying Formulae .25Functions.277.17.27.37.47.57.6Functions and Arguments .27Using the Function Wizard .27The Sum Function.30Statistical Functions .31Mathematical Functions.31Combining or Nesting Functions .328.Understanding Error Messages .339.Viewing Formulae.34UCL Information Systemsi

ExcelPart 110. Formatting a Worksheet .3610.110.210.310.410.510.610.710.8Changing Column Width .36Assigning a Number Format.37More on Number Formats.39To Set the Precision of Number Formats.39Number Formats - Keyboard Shortcuts .39Formatting Characters.41Borders, Patterns and Colours.41Aligning Data.4211. Controlling the Worksheet Display.4511.111.211.311.411.511.611.7To Freeze Horizontal or Vertical Titles Only.45To Freeze both Horizontal and Vertical Titles .45To Unfreeze Titles and Panes .45To Hide a Column or Row.45To Reveal the Column or Row .45Splitting Panes .46To Remove the Split .4612. Preparing to Print .4712.112.212.312.412.512.6Page Setup: The Page Tab .47Page Setup: The Margins Tab.48Page Setup: The Headers and Footers Tab .49Page Setup: The Sheet Tab .50Print Preview.51Printing a Worksheet.5213. Exercises.54iiUCL Information Systems

Part 1ExcelConventions Used in this WorkbookThe following table outlines the formatting conventions used in this workbook.CommandsCommandsMenu commandsButtons to pressKeys to pressEnter/Return keyKey combinationsKey sequencesRepresented asCourier regularArial Narrow boldenclosed in square bracketse.g. [Ctrl] or [Shift][Return]square brackets with combined keys linked with plus signe.g. [Ctrl C] hold down the Control key and press Cpress each key enclosed in bracketse.g. [ ] [ ] press right arrow key twice in successionToolbar TipsWhere possible a toolbar shortcut has been provided,shown in a bubble alongside the relevant text. This buttoncan be used instead of the menu method described in thetext.Toolbar TipOpenHow to Use this WorkbookThis guide can be used as a reference or tutorial document. To facilitate the learning process,a series of practical tasks are contained within the text. It is recommended that you try each ofthese tasks as you progress through the workbook. For further practice and as a means of selfassessment, a number of additional staged exercises with solutions have been included. Theseshould be attempted where recommended.Training FilesIf you wish to attempt the exercises contained in this document and you are not using atraining account it is necessary to download the training files used in this workbook from theIS training web site at: http://www.ucl.ac.uk/is/training/exercises.htm Full instructions onhow to do this are provided on this web page.UCL Information Systemsiii

Part 1Excel1. What is a Spreadsheet?A spreadsheet (called a workbook within Excel) is a powerful application which can be usedto store, manipulate, calculate and analyse data such as numbers, text and formulae. Ananalogy can be drawn between a spreadsheet and an accountant’s ledger. A ledger is made upof many pages, each page arranged into a series of rows and columns.At its simplest level, a spreadsheet is used to enter numbers and perform simple calculationsbut the capabilities of Excel extend far beyond this. Excel provides a number of featuresincluding: A range of functions including mathematical, financial and other calculations. A selection of tools to facilitate What If type analyses. A Chart Wizard - to produce graphical representations of data held withinworkbooks. Graphics to highlight information in worksheets and charts. Database features which enable sorting, filtering and analysing of information. Macros to allow the user to automate routines.There are many different practical applications for which a spreadsheet can be used. Theobvious ones, which come to mind, are financial applications, such as maintaining budgetsand accounts. Other applications include processing course marks, analysing results fromexperiments and maintaining lists and audits.1.11.The Excel EnvironmentTo launch Excel from the Start menu point to Programs and choose Excel. Your screenshould look the same as the one below.Menu BarTitle gToolbarName Box(ShowingActive Cell)A1ColumnHeadingFormula BarTask PaneRowHeadingScroll BarsStatus BarSheet TabFigure 1-1 – The Excel WindowUCL Information Systems1

Excel2.Part 1The Formula bar, Status bar and the Scroll bars are all visible. Take a moment tolocate these on your screen.It is possible to change the look of the default environment, a number of the view options canbe changed from the Tools menu under Options and View.1.2Task PanesAll of the Microsoft Office XP applications have a newfeature called the Task Pane (visible in the right-hand partof the screen). This feature is a web-style command areawhich is an alternative to a dialogue box, and allows you tocarry out certain basic operations or choose selectedoptions. Most Office applications contain the followingtask panes: New File/Workbook/Document (the namevaries with the application), Clipboard, Search and InsertClipArt.Other TaskPanes arrow§New Workbook– this task pane provides options forstarting a new workbook or opening an existing one.§Clipboard – this task pane is used for copying andpasting multiple items into Excel, and between Exceland other Microsoft Office XP applications.§Search – this allows you to look for your work(files, web pages etc.) in locations on yourcomputer and on the web.§Insert Clipart – this task pane is used for inserting images and clipart into yourspreadsheet.1.2.1Figure 1-2 New Workbook Task PaneTo Display the Task PaneIf the task pane is not visible down the right hand side of the application window, you candisplay it as follows:1. From the View menu choose Task Pane. The task pane as shown in Figure 1-2 will display.1.2.2To change the Task Pane:1. Click on the Other Task Panes arrow to display the optionsshown in Figure 1-3.2. Select the required option.3. Note that you can also use the arrows to go backwards andforwards to previously displayed task panes.2Figure 1-3 – Task Pane OptionsUCL Information Systems

Part 11.3ExcelWorkbooks and WorksheetsOn start up, Excel automatically loads a Workbook – Book1 as identified in the Title bar (seeFigure 1-1). This workbook is a file in which you work and store your data. Each Workbookcan contain a number of Worksheets. The default Workbook has three Worksheets, eachhaving a tab to mark the sheet (i.e. Sheet1, Sheet2.).A worksheet is a grid like area divided into Columns andRows. Columns are labelled A, B, C. and rows numbered 1,2, 3.etc. Each worksheet is made up of 256 columns andA165,536 rows. The intersection of a column and a row isknown as a cell.Each cell on a worksheet can be uniquely addressed by itscolumn letter followed by its row number i.e. the first cellFigure 1-4 - The Excel Worksheetin the worksheet is A1. The active cell is now indicated byboth the column and the row headings being highlighted in blue.1.4Accessing CommandsAll commands may be accessed through the menu system although some are also availablethough buttons on toolbars and through the task pane.1.4.1Task PanesSome commonly used commands are accessed through the task panes as describedpreviously.1.4.2MenusCommands may also be accessed through theMenu bar at the top of the Excel window.Using the Mouse1.2.Press [Alt E]to select theEdit menu.Click on the menu item on the menu bar andclick on the option you require in the dropdown menu.PressOr[Alt E D] toRight-click for context-sensitive options.select DeleteUsing the KeyboardPress[Ctrl F] toThere are two keyboard methods for accessingmenu commands; using the [Alt] key, or using the select Find[Ctrl] key.1.2.Hold down the [Alt] key and press the letterunderlined in the menu item. For example,to access the Edit menu press [Alt E].ORUse the [Ctrl] combinations where indicatedin the menu. For example, use [Ctrl c] tocopy, [Ctrl v] to paste etc. Note only somecommands are available using the [Ctrl] key.UCL Information SystemsFigure 1-5 - Using Keyboard Shortcuts3