I found this, but appologies if it is off one of the usual sites we all use. I just know I've found it very useful. Hope attachments are allowed on this list. it's only a text file, but it's great. Peter ----- Original Message ----- From: Jim Ronald To: jfw@xxxxxxxxxxxxx Sent: Friday, January 19, 2007 4:30 PM Subject: Excel and Jaws Is there a Jaws List exclusively for Excel? Thanks, Jim.
MICROSOFT EXCEL 97, 2000, 2002 AND 2003 FROM THE KEYBOARD By John Wilson Copyright 2006 ******** Table of Contents (To find a particular section or heading, use your word- processor's or editor's search facility, e.g. type the string Creating a Multi-Page Workbook" to find that subheading or just type "20.1." to find it via its paragraph number. Additionally, all main sections are separated by a centred row of eight asterisks.) Foreword and Restrictions Target Group Conventions Available Tutorial Formats Suggested Approaches for Effective Learning with this Tutorial 1.1. Introduction 2.1. What is a Spreadsheet and how are they Structured? 3.1. Pen-Picture of the Excel Screen 4.1. Keyboard Shortcuts and Hot Keys for Excel 4.2. EXCEL General Shortcuts 4.3. JAWS 4, 4.5, 5 and 6 Special Spreadsheet Hot Keys 4.4. Brief Comments on JAWS Performance in Excel 4.5. HAL 5, 6 and 6.5 Special Spreadsheet Hot Keys 4.6. Brief Comments on HALs Performance in Excel 4.7. Window-Eyes 4.0-4.5, 5.0 and 5.5 Special Spreadsheet Hot Keys and Set-Up 4.7.1. Configuring Window-Eyes to Work with Excel 4.7.2. Window-Eyes Hot Keys 4.8. Brief comments on Window-Eyes Performance in Excel 5.1. Customising Excel for Visually Impaired People and Turning the Office Assistant Off 6.1. Microsoft Excel Specifications 7.1. How to Launch Excel and How to Place a Shortcut on Your Desktop to Launch Excel 7.2. The Path to Excel 7.3. Creating a Desktop Quick Launch Icon 8.1. Getting a Feel for How to Move Around a Worksheet 9.1. Excel's Unique Way of Highlighting/Selecting Cells and Ranges 10.1. Excel's Default Workbook Formats and Conventions 11.1. Entering Data into a Worksheet 11.2. Workbook Exercise 1--Creating and saving a Worksheet with headings and Formulae for totals 12.1. Excel's Ability to Automatically work Out which Cells you are Likely to Wish to Add UP--AutoSum 13.1. Saving a Worksheet to Hard or Floppy Disk 13.2. Saving in the Default XLS Format 13.3. Shortcuts Available in the Save and Open Dialogue Boxes 13.4. Saving a Workbook in Web Page HTML format 13.5. Saving Your Workspace to a file for Quick Retrieval of a Previous Working Environment 14.1. Printing Workbooks, Worksheets and Cell Ranges 14.2. Portrait Printing 14.3. Landscape Printing 14.4. Workbook Margins when printed and Page Set-Up 15.1. Reopening an Already Created Workbook 15.2. Using the Open Dialogue to rename, Copy, Move, delete and Create a Shortcut to a Workbook File 15.3. Shortcuts Available in the Open and Save Dialogue Boxes 16.1. Cut, Copy and Paste with the Windows and Excel Clipboards 16.2. The Traditional Windows Clipboard 16.3. The Excel 2002 Multiple Clipboard 16.4. The Excel 2002 and 2003 Multiple Clipboards 17.1. More about Highlighting--The General Windows Way 18.1. Highlighting Several Worksheets on which to Simultaneously Effect Changes 19.1. Spell- and Formula-Checking Your Worksheets 19.2. Spell-checking the Text in Your Worksheets 19.3. Worksheet Formula Error-checking in Excel XP 20.1. Creating a Multi-Page Workbook 21.1. Working with Several Workbooks open at Once 21.2. Closing Several Open Workbooks Simultaneously 22.1. Changing the Column Width and Row Height of Your Worksheets 22.2. Columns 22.3. Rows 22.4. Workbook Exercise 2--Altering Column width, using Formulae for Averaging and copying formulae Down a Column 23.1. Automatically Formatting a Worksheet or Pivot Table Report 24.1. Manually Formatting Cells 24.2. Selecting a Border Style 24.3. Selecting a Font Type and Size 24.4. Selecting a Worksheet Background Pattern or Shading Colour 24.5. Opting to Protect or Hide Blocks of Information 24.6. Selecting the Type of Numbering and Currency Symbol System 24.7. Selecting the Way You Would Like Text and Values Aligned 24.8. Saving a Cell's Formatting Choices to a Style Sheet for Use in Other Excel Sessions 24.8.1. Creating and Saving a Cell Formatting Style 24.8.2. Retrieving a Cell Formatting Style for Use 25.1. Editing a Single Cell 26.1. Clearing and Deleting Cells, Rows, Columns and Worksheets 26.2. Clearing 26.3. Deleting 27.1. Changing Worksheet Borders 27.2. Workbook Exercise 3--Creating Borders 27.3. Double Border Around the Whole Worksheet 27.4. Single line Under a Row of Cells 28.1. Naming Cells, Naming and Renaming and Hiding Worksheets and Workbooks 28.2. Naming Worksheets 28.3. Naming Individual Cells or Blocks of Cells 28.3.1. Naming Cells 28.3.2. Changing Cell References in formulae to Names 28.3.3. Using Goto to Jump to Named Cells and Cell References 28.4. Hiding Worksheets 28.5. Hiding Workbooks 29.1. Creating, formatting and Totalling an Invoice to Include VAT 29.2. Workbook Exercise 4--Calculating VAT 30.1. Entering Dates Manually 31.1. Using, Viewing and Printing Common formulae 31.2. Formulae Example List 31.3. Workbook Exercise 5--Amending Data and Formulae 31.4. Printing a Highlighted Area of a Worksheet 31.5. Printing Spreadsheets Showing Their Formulae 32.1. Statistical functional and formula Operators 32.2. Workbook Exercise 6--using Excel's Functions and Inserting Extra Columns and Rows into a Worksheet 33.1. Decimal Fractions and Percentages 33.2. Workbook Exercise 7--Fractions and Percentages 34.1. Relative and Absolute Cell References 34.2. The A1 Reference style 34.3. Examples of Cell and Range References 34.4. Relative and Absolute Cell References 34.5. Making Formulae Absolute 34.6. Workbook Exercise 8--Copying absolute formulae across a Worksheet 35.1. How to Enter and Use Array Formulae 35.2. Workbook Exercise 9--Using an Array formula 36.1. Using 3D References to Analyse Data Over Multiple Worksheets 37.1. Inserting Hyperlinks, Objects and AutoShapes into Your Worksheets 37.2. Inserting Hyperlinks into Worksheets from which to Launch Your E-Mail or Internet Client 37.3. Inserting Pictures into Your Worksheets Directly from a Scanner or Camera 37.4. Inserting an AutoShape into Your Worksheet 37.5. Formatting a Picture or other Object 38.1. Headers and Footers 39.1. Filtering Out Specific Data or Records Before Printing 40.1. Sorting and Auto-Filling Data 40.2. Sorting Lists of data in Ascending or Descending Order 40.3. Workbook Exercise 10--Sorting 40.4. Auto-Filling Dates, Days and Months Down Columns or Across Rows 41.1. The Find and Find and Replace Features 41.2. Find 41.3. Find and Replace 42.1. Creating Charts with the Chart Wizard 42.2. Workbook Exercise 11--Creating a Chart 42.3. Printing a Chart 43.1. Creating and Retrieving Custom Templates 43.2. Formatting and Creating a Template 43.3. Retrieving a Template for Use 43.4. Workbook Exercise 12--Creating a Custom Template 43.5. Special Templates Provided in Excel 44.1. Excel Add-Ins 44.2. Euro Symbol Add-In 44.3. How to Manually Type/Generate the Euro Symbol 44.4. AutoSave Add-In 44.5. Template Utilities Add-In 44.6. Analysis Toolpack Add-In 44.7. Unloading an Add-In 45.1. Quickly E-Mailing a Worksheet or Report in Excel 2002 or 2003 46.1. Importing Data and Text from Other Programs 47.1. Using Excel Online Help 47.2. The Excel 97 Contents and Index Help System 47.3. The Excel 2000 Contents, Index and Answer Wizard Help System 47.4. The Excel XP/2002 Contents, Index and Answer Wizard Help System 47.5. The Excel 2003 Online and Internet Links Help system 47.6. The Excel Tips Website 48.1. More Examples of commonly Use Formulae and Functions 48.2. Running Balances 48.3. Workbook Exercise 13--Running Bank Balances 48.4. calculating How Much You Will Pay on a Loan or Mortgage 48.5. Calculating Your Long Term Savings Potential 48.6. Getting Advice About the Need to Re-Order Stock 48.7. Counting the incidence of Unique Entries in a Worksheet 48.8. Generating Automatic Notification of Action to Take when a specified date is Reached 48.9. Changing the Case of Text 48.10. Increasing or Decreasing a Number by a Percentage 48.11. Counting Numbers Greater than or Less than a Specified Number 48.12. Converting measurements 48.13. Calculating the Difference Between Two Figures as a Percentage 48.14. Calculating the Difference Between Two Dates in Days, Months or Years 48.15. Adding Days to a Date to Forecast a Future Date 49.1. Sharing Workbooks with Others Over a Network 49.2. Viewing Who is Simultaneously Sharing a Workbook and How Sharing is being Managed 49.3. Tracking Changes in a Shared Workbook 49.4. Comparing and Merging a Workbook 50.1. Protecting Worksheets and Workbooks from Alterations by Others 51.1. Using Goal Seek to Adjust the Value of One Cell to Obtain a Specific Result for Another Cell 51.2. Workbook Exercise 14--Goal Seek 52.1. Creating Forms for Printing out or for Online Completion 52.2. Enabling the Forms Toolbar 52.3. Designing Your Own Form for Completion Online on Your Company Network 52.4. Example of Inserting a Checkbox and Listbox Control into Your Form 52.4.1. Example 1--Checkboxes 52.4.2. Example 2--Listboxes 52.5. How to View and Verify That Your Control Object Has Been Inserted 52.6. Printing a Form out for Manual Completion 53.1. Basic Excel Macros 53.2. Recording a Macro 53.3. Running a Macro 54.1. Using Lists in Excel as Database Forms 54.2. Creating Data Forms for Storing and Manipulating Data 54.3. Workbook Exercise 15--Creating a Data form and Searching in It 55.1. Automatically Generating Sub-Totals and Grand totals for your worksheets 56.1. Using the Excel 2003 Research Services Feature to Find Information 56.2. Searching for Thesaurus, Encyclopedia and Language Translation Resources 56.3. Searching for Online resources and downloads on the Microsoft Office Marketplace Site 56.4. Changing Research Options and Enabling Parental Control over what can be accessed and Viewed by Children 57.1. Appendix 1: Comprehensive List of Excel's Shortcut Keystrokes 58.1. Appendix 2: Other Tutorials by this Author ******** Foreword and Restrictions I have written this manual and tutorial for the use of blind and otherwise visually impaired computer users and/or their trainers. It is free of charge and only available from its author's Website and from no other distributer. No individual or organisation is permitted to sell copies of this tutorial either as a stand-alone tutorial or as an integral part of any other literary, software or training package. ******** AVAILABLE MANUAL FORMATS The manual is only available in ASCII text format, as a free download from the author's Website at: http://web.onetel.com/~fromthekeyboard This tutorial and guide has been created with a minimum of formatting, in plain text, so that any word-processor or text editor can read it. In this format it should also be suitable for any one to run it through an embosser but, with some embossing software, you may still wish to make some line spacing and heading format changes to suit yourself and your software. A simple construction such as this should also make reading by arrowing up and down in your word-processor less labour intensive than would be the case with columns, shorter lines, and the like. Colloquialisms, such as don't, haven't, doesn't, etc, have been avoided in this guide in order to make it easier to follow and understand via a speech package. Hopefully, any loss of conversationality and warmth will be compensated for by increased clarity. ******** TARGET GROUP Visually impaired computer users are the target group for this tutorial. Keyboard access methods and descriptions, using screenreaders and no mouse or monitor, are the basis of this work. The guide assumes a basic understanding of the Windows operating system and of your particular screenreader's general hot keys. It is also recommended that you study the tutorial tapes or online manuals which come with your screenreader and get to know the main hot keys for use in spreadsheet programs, although a list of many of them for JAWS, HAL and Window-Eyes is given below. ******** CONVENTIONS In the writing of this Tutorial, terms have the following meanings: ALT F, A Means hold down the left ALT key and whilst still holding it down press the letter f, then release both and press the letter A. CONTROL S Means hold down the control key and whilst keeping it held down press the letter S and then release both. SHIFT END Means hold down the SHIFT key and whilst keeping it held down press the END key. ALT E, C, and press ENTER Means hold down the left ALT key and whilst keeping it held down press the letter E key, then release both and then press the letter C key followed by the enter key. When a key combination such as ALT T (for Tools), O (for Options) is suggested to go into the "Tools" menu and run the "Options" menu option, the user may follow this method of operation or may prefer to ARROW up and down a menu and press ENTER. In this latter case, the keystrokes would be: press the ALT key then release it, right ARROW to the "Tools" menu heading, then ARROW down (or up) until the "Options" line is spoken, then press ENTER. All individual and conbinations of keys you actually have to press during a procedure which have been referred to have been put in capital letters so that they stand out to anyone reading this tutorial visually, e.g. to bring up the Open dialogue box press CONTROL O. If, in a menu, your screenreader announces an arrow or says something like submenu, this means that pressing ENTER or right ARROWING on this menu item will take you into a sub-menu to ARROW up and down in and make a choice. If your screenreader announces a row of three dots or says something like dialogue, you will open up a dialogue box to work in if you press ENTER on it. Note that with some screenreaders you may encounter a conflict between the generic Windows shortcuts and the screenreader's own hot keys. If this happens, you may, for example, have to press such as ALT and then release it followed by T (for Tools) to get into the Tools menu instead of pressing ALT and T together. In other instances you may find it necessary to use your screenreader's bypass or skip next keystroke hot key to get your screenreader to ignore your next key combination and therefore allow that key combination to pass through to the program instead of intercepting it as a screenreader hot key, e.g. use the bypass hot key of INSERT B in Window-Eyes, INSERT 3 in JAWS and CONTROL NumPad 7 in HAL. ******** Suggested Approaches for Effective Learning with this Tutorial It is, of course, entirely up to the individual as to how they glean information and work through this tutorial, but a few suggestions might assist the learner who is relatively new to computers. I would propose that you read through the whole of a section before attempting to practise it to obtain an overview of what is being done. There are a number of approaches which might be taken to make reading the tutorial as a text file and simultaneously carrying out the instructions more fluid and easier to follow. Try one of the below methods. Ideally, if you have two computers, you can load the tutorial into your text editor or word-processor on one PC and have the software program running on the other. You can then listen to the directions on one computer whilst practising them on the other. Alternatively, as is likely to be the case, if you only have the one computer, you could launch your word-processor and load the tutorial into it for reading. You could then launch the program you wish to learn how to use in order to practise the lessons. You would have to keep cycling between each running program by pressing ALT TAB in this case. Yet another approach might be to take a tape recorder or dictaphone and get your screenreader to read the contents of a given section or sub-section onto the tape. You could then play the tape back and follow the instructions through on your PC without having to keep moving from one running program to another. Other options would be for you to print out a copy of the tutorial in large print if you can use this and work from this hard copy, or you could get your local library or resource centre to produce a Braille version for you to work from if you have one in your area and you are a Braillist. ******** Introduction 1.1. Tutorial Aims This tutorial aims to take the learner not previously acquainted with spreadsheets through the beginner's level concepts and practical stages of MS Excel and then move on to some intermediate level features. It does not address many of the more advanced features of Excel, some of which are very specialised indeed, such as engineering analyses. The Tutorial will use the generic hot key shortcuts provided by Microsoft Windows themselves and Excel in particular to achieve a given goal but, from time to time, JAWS, HAL and Window-Eyes special shortcuts will be mentioned. In addition a short list of the JAWS, Window- Eyes and HAL screenreader hot keys has been provided in an early section to get you acquainted with these at the outset. You should work through the tutorial chronologically from section 1 onwards, as the sections build on one another and jumping straight to a middle or end section may not make sense if you have not read and practised earlier sections. The tutorial may be used as a stand-alone learning and reference guide and will include several short spreadsheet examples for you to produce in order to practise and test specific formulae and procedures. As a visually impaired person you may wish to create spreadsheets for yourself which are quite compact rather than with fancy formatting and blank rows and columns so that you can more easily keep track of where you are. However, if others are to view them or you are to print them out for others, you may then wish to change the layout, formatting and spacing to give a more professional look before printing. ******** 2.1. What is a Spreadsheet and How are They Structured? Microsoft Excel is a spreadsheet program. It provides a grid of vertical columns and horizontal rows whose intersections are called "cells" into which you can type figures, words or formulae to do calculations and elicit information. It is the same sort of program as the older DOS spreadsheets of D-Base and SuperCalc; whereas more modern Windows spreadsheet equivalents would be Quatro Pro and Lotus 1-2-3. The text you type into a cell is referred to as a "label" and the figures you type in are called "values". One or many pages of a spreadsheet are known as a file, although Excel has its own name for such a file, referring to it as a "workbook". So, if a workbook had 100 pages (known as worksheets) within it, the workbook would be analogous to a ring binder and the 100 worksheets within it would represent the pages within the encompassing binder. Each Workbook will have its own unique filename and an extension of ".XLS", e.g. book1.xls, invoice3.xls, smith44salesledger.xls, and the like. Each individual page in a workbook has 256 columns running down the sheet which are labelled from left to right A, B, C, D, etc, and it has rows running across the sheet which are numbered down the left-hand side 1, 2, 3, etc, up to a maximum of 65,536. Therefore, by knowing the identification letter at the top of a column and the identification number at the left of a row, you can easily work out the co-ordinates of a particular cell within a sheet. For example, a cell which is two in from the left and two down from the top would have a cell reference position co- ordinate of B2, a cell which is five in from the left and 124 rows down could be found at the cell co-ordinate of E124, etc. The default name for the first page in a workbook is "Sheet1" but, of course, you can give the separate sheets their own names. A "cell", as you will already have gathered, is a kind of box and is the point or co-ordinate at which both a column and row intersect. Such a cell can be of varying characters wide and either one or several lines deep, depending on your requirements, for instance, it may simply contain one character, such as the number 2 or it might contain ten words spread over three separate lines. A worksheet can be created as a "chart", which is a graphical representation of the data on a worksheet. A "database" is a part of a worksheet employed to manage, organise and retrieve information. You would, therefore, create or read a spreadsheet like reading a bus or train timetable or looking up logarithms manually. It is simply a grid of small (but expandable) boxes, each of which has a known place on the grid, depending on its column and row intersection letter/number. To conclude and summarise, the hierarchy of Excel's structural levels, in order of largest magnitude downwards, is: workbook, worksheet and then cell. ******** 3.1. Pen-Picture of the Excel Screen A single Excel screen of information will typically contain: 1. The "Title Bar" containing the words Microsoft Excel and then the name of the worksheet which is on screen, e.g. "Book1.xls" if you have not yet replaced this default name with a new name of your own preference. 2. Below the Title bar is the "Menu Bar" with the typical File, Edit, View, etc, menu options. 3. Under the above are any toolbars you may have decided to keep on screen, e.g. with iconised buttons for clicking on such as New Document, Open Document, Save Document, etc. There will be more buttons on different levels if you have turned extra toolbars on in the View menu, such as the drawing and forms toolbars. To get easy access to these various toolbars via the keyboard, you would press the ALT key and then release it, then move through the toolbars by pressing CONTROL TAB followed by the left and right ARROW keys. 4. Next down comes the "Formula Bar" where a formula in a cell with current focus can sometimes be viewed. Look at this in mouse or virtual cursor mode unless your screenreder has a read Formula Bar hot key, e.g. CONTROL F2 with JAWS. 5. All the remainder of the main part of the screen down to the last two lines contains the worksheet grid, as described in the last section, i.e. with columns alphabeticised at the top A, B, C, etc, and rows numbered at the left 1, 2, 3, etc. However, only a fraction of a total potential worksheets full size can be displayed on one screen and, with the screen maximised (ALT SPACEBAR X) should display 12 columns from left to right and 25 rows from top to bottom. 6. On the penultimate line are found the default three worksheet tabs which can be used to move from worksheet 1 to worksheet 2, etc, but you can achieve this from the keyboard by pressing CONTROL PAGE up or down. Have a look at the stationary screen in your screenreader's mouse or virtual mode. Note that if you ARROW right or down the worksheet will move (scroll) to the left or down and automatically reveal columns further on than column 12 (which is column L) and more rows than the first 25. 7. Finally, on the last line, you will find the status bar, normally displaying the word "Ready" to indicate that the worksheet is ready and waiting for your input. It will also display other information at other times, such as the autoSum total of any cells you have highlighted. This is a way of quickly viewing what the additions of a range of cells amounts to by pressing your screenreaders read Status Bar hot key, e.g. Numpad 2 in HAL or / in HAL, INSERT Numpad 3 in JAWS and ALT S in Window-Eyes. 8. With Excel XP/2002 you may also get, near the top of the screen, "Handwriting" and "Microphone" buttons because XP has an ability to take this type of input in certain languages and situations. You can also invoke a smaller pane on screen called the "Task Pane" to carry out certain tasks in via the View, Task Pane" option. When enabled, this Task Pane occupies part of the right-hand side of the screen. 9. With Excel 2003, in addition to just having the above- mentioned standard Task Pane open, you can occupy this same area of the screen by opening a Research Pane" via Tools, Research. In fact, whether you open these panes via the View or Tools menu does not matter, as they both become available together. When you first invoke the Research Pane, you immediately get access to its research resources buttons to TAB through but thereafter to get to its options you can use the usual ALT, CONTROL TAB and then left and right ARROW keys. However, unless your screenreader is right up to date, it may not be able to read all of the options in these two new panes. How this new Research Pane works is explained in Section 56. ******** 4.1. Keyboard Shortcuts and Hot Keys for Excel I strongly recommend that you carefully study the training tapes which come with your particular screenreader to accustom yourself with the specialist shortcuts for operating a spreadsheet which it provides. Nonetheless, I have provided below in this section most of the main and more important screenreader hot keys for the JAWS, HAL and Window-Eyes screenreaders, plus a list of Excel's own hot keys for navigating in and carrying out commands in spreadsheets. 4.2. Excel General Shortcuts Whether the below hot keys do anything when you press them will, of course, depend on what type of worksheet environment you are in, what stage you are at and whether or not you have highlighted any cells, etc. This is a short list of Excel's own most useful and frequently used shortcuts which should prove particularly helpful for those who prefer shortcut keystrokes over using menu choices. However, it is a fraction only of the Excel general shortcuts. For a more exhaustive list of these shortcuts, see Appendix 1. Press BACKSPACE: To delete the character to the left of the insertion point. Press F1: To invoke Excel's online help system. Press F2: To be able to edit the contents of the current cell. Press F8: To turn on extending a selection by using the ARROW keys. Press F11: To create a chart which uses the current range. Press F7: To invoke the spell-checker for any text in your spreadsheet. Press F9: To calculate all sheets in all open workbooks. Press right, left, up or down ARROW keys: To move one cell at a time in any direction. Pressing TAB also moves you one cell at a time along a row from left to right and SHIFT TAB moves you backwards. Pressing ENTER after completing a cell moves you down a row in the current column. Press PAGE up or down: To move through a spreadsheet upwards or downwards respectively by 25 lines each press. In some set-ups this may move you by only 23 lines. Press ALT =: To generate an auto sum. Press =: To then be able to type a formula into the current cell. Press CONTROL SPACEBAR: To highlight/select the whole of the column the cursor is currently in. Press SHIFT SPACEBAR: To highlight/select the entire row the cursor is in. Press ALT ENTER: to insert a carriage return and start a new line in the same cell. Press CONTROL F: To bring up the find dialogue box. Press CONTROL 1: To display the format cells dialogue box. Press CONTROL with any of the ARROW keys: To move to the edge of data regions, e.g. CONTROL down ARROW when in the middle of row D of a page with several rows in it will jump you to the last cell in row D with data in it. Press CONTROL PAGE down: To switch to the next spreadsheet page including any with charts on them. Press CONTROL PAGE up: Opposite of the above. Press ALT PAGE down: To move right one screenful of information, which will normally mean jumping 11 rows to your right each time you do this. Press HOME: To jump to the beginning of the current row. Press END and then left ARROW: To jump to the end of the current row. Press CONTROL HOME: to jump to Cell A1. Press CONTROL END: to jump to the last cell in your current data range. Press CONTROL ;: To insert the date in the current cell. Press CONTROL SHIFT 7: To obtain a border format outline. Press CONTROL SHIFT -: To remove a border. Press CONTROL SHIFT ;: to insert the time. Press CONTROL `: to toggle displaying formulae or values, i.e. displaying a formula in a cell or the results of that formula. Press CONTROL N: to open a new workbook. Press SHIFT F11: To insert a new Excel worksheet. Press CONTROL SHIFT SPACEBAR: To highlight all objects. Press CONTROL SHIFT !: To invoke two decimal place format. Press CONTROL SHIFT &: to generate an outline border around the worksheet. Press CONTROL SHIFT _: To remove borders. Press CONTROL SHIFT 8: To highlight the whole region of the worksheet data. Press CONTROL SHIFT ~: To obtain general number format. Press SHIFT up, down, left or right ARROW: To highlight/select cells as you go. Note 1: You can use the standard Windows highlighting methods to highlight/select text in cells, i.e. combinations of CONTROL, SHIFT, the ARROW keys and the HOME and END keys, e.g. CONTROL SHIFT PAGE up to select one screen of information, SHIFT HOME to select from the beginning of the current row to the cell the cursor is currently in, CONTROL SHIFT END to select from the current cursor position to the end of the worksheet, etc. Note 2: The standard Windows shortcuts for copying to the Clipboard, pasting, etc, all work as normal, e.g. CONTROL C, CONTROL X AND CONTROL V. 4.3. JAWS 4, 4.5, 5 and 6 Special Spreadsheet Hot Keys As you move to a new cell, JAWS will automatically read out the cell's contents and the column and row co-ordinates. Press ALT 1: To hear the contents of the first cell in the current column. Press ALT 2: To hear the contents of the second cell down in the current column. Press ALT 3: To hear the contents of the third cell down in the current column. Press ALT 4: To hear the contents of the fourth cell down in the current column. Press ALT CONTROL 1: To hear the contents of the first cell on the left in the current row. Press ALT CONTROL 2: To hear the contents of the second cell in the current row. Press ALT CONTROL 3: to hear the contents of the third cell in the current row. Press ALT CONTROL 4: To hear the contents of the fourth cell in the current row. Press CONTROL SHIFT C: To List the cells in the current column. Press INSERT Numpad ENTER: To read the column total. Press ALT SHIFT C: To Hear the column title if it has one. Press ALT CONTROL SHIFT C: to Set the column titles to row range. Press ALT SHIFT right ARROW: to group rows or columns. Press ALT SHIFT left ARROW: to ungroup rows or columns. Press CONTROL INSERT ENTER: to Set the current column to the column containing row totals. Press CONTROL SHIFT R: To List the cells in the current row. Press INSERT DELETE: to read the row total. Press ALT SHIFT R: To hear the row title if it has one. Press ALT CONTROL SHIFT R: to set row titles to column. Press CONTROL INSERT DELETE: To set current row to the row containing column totals. Press ALT SHIFT B: to obtain a description of the type of border around a single cell. Press CONTROL SHIFT B: to list the cells at the page break. Press CONTROL SHIFT ': To list cells which contain a comment. Press ALT SHIFT ': To read the comment in a cell. Press CONTROL SHIFT D: to list the cells which have data visible on the screen. It will not list data in cells in the worksheet which are off the screen. This works like the links list in JAWS for web pages. If you ARROW down the list, you will be told the contents of each cell from left to right and then from top to bottom and if you press ENTER on any cell you will be jumped straight to it. Press CONTROL SHIFT S: to list the sheets in the current workbook. You can then go to one of these. Works the same as the above cells list. Press ALT SHIFT V: To hear the range of cells visible in the active window and how many cells contain data. Press ALT SHIFT H: To hear the hyperlink in the cell with focus read out if it contains one. Press ALT SHIFT G: To hear a report of the grid line status. Press INSERT C: To hear the active cell co-ordinates and the cell's contents. Press INSERT N: To hear the contents of the rectangle with focus. Press INSERT F: To hear the cell font and other attributes plus horizontal and vertical alignment and the cell's width and hight. Press CONTROL F2: to hear the formula in a cell if it contains one. quickly twice will allow you to review the formula with the JAWS cursor. Press INSERT F7: To hear any spelling error read out and the suggested replacement. This is, of course, after first pressing the general Excel shortcut of F7 to invoke the spell-checker, then proceed as normal, e.g. press ALT C to accept the first replacement spelling correction. Press CONTROL SHIFT H: To select a hyperlink if one exists on the worksheet. Press CONTROL SHIFT O: to highlight/select worksheet objects, such as OLE objects and auto shapes. Press CONTROL SHIFT A: to auto filter. Press CONTROL INSERT F4: to close the Office Assistant. Press INSERT up ARROW: To read the visible row. Press INSERT HOME: To read the visible row to the current cell. Press INSERT PAGE up: to read the visible row from the current cell. Press INSERT CONTROL up ARROW: to read the visible column. Press INSERT CONTROL HOME: to read the visible column to the current cell. Press INSERT CONTROL PAGE up: to read the visible column from the current cell. Press INSERT F1: To hear a chart description when the chart has focus. Up to 14 different chart types and titles will be described. Press Numpad -: To read a chart with the virtual cursor in JAWS mode and navigate the same as you would for a web page. Then press ESCAPE to return to the worksheet. Press INSERT V: To open a JAWS Verbosity list to make changes to what JAWS automatically announces in a spreadsheet, e.g. Press INSERT V, then ARROW up and down over 20 options to turn on or off or alter the default way things work, such as leave focus on "Cell Verbosity - Content plus Co-ordinate", TAB to "Execute" and press ENTER, when the option will change to "Cell Verbosity - Content Only". Press INSERT SHIFT C: to hear/confirm selected cells. Press ALT CONTROL SHIFT 1 THROUGH 0: To set up to 10 monitor cells for the current worksheet. Press ALT SHIFT 1 through 0: To Speak any of the already set 10 monitor cells for the current worksheet. What happens is that JAWS will announce both the cell co-ordinate of the monitored cell and its contents so that you can obtain this information as a reminder from anywhere else in the worksheet at any time. Press CONTROL SHIFT M: To move to an already set monitor cell. Press CONTROL SHIFT `: To move back to the last cell which had focus before you moved to a monitor cell as above. 4.4. Brief Comments on JAWS Performance in Excel In addition to JAWS automatically speaking a cell's column and row heading co-ordinates, it has a number of other good automatically spoken abilities. For example, when you move to a cell which has a value in it which has been derived from a formula, JAWS will also tell you that cell has a formula in it by saying such as "1000 has formula B7". JAWS will also tell you if the cell you move to has a currency sign in it, such as ? or $. Some other screenreaders cannot 'see' currency signs and so do not advise you of their presence, although you can always go into editing mode in the cell (press F2) and observe its contents including currency symbols. Other nice informational reporting features of JAWS are the CONTROL F2 hot key to advise you of what formula is in a cell rather than you having to go into mouse mode and read the Formula Bar, its ability to tell you many of a cell's formatting features by pressing INSERT f, its ALT SHIFT B command to tell you what type of borders a cell has around it, its CONTROL SHIFT 8 hot key to highlight the whole worksheet data area for you, and so forth. You can quickly change JAWSs default way of reading things by pressing INSERT V and ARROWING down a list of over 20 options to switch off, on or alter the working of, such as whether cell contents and co-ordinates are spoken by JAWS or just the cell's contents, whether border types in cells are announced or not, etc. If you wish to use Excel XP/2002 with JAWS, you will need Version 4.0 upwards for full functionality. To use Excel 2003, you will need JAWS 4.5 or later with the full set of JAWS scripts for Excel 2003. Note: You will benefit from also learning the above general Excel hot keys as well as the JAWS shortcuts. 4.5. HAL 5, 6 and 6.5 Special Spreadsheet Hot Keys With HAL 5.2 and earlier, as you move to a new cell, HAL will automatically read out the cell's column and row co-ordinates followed by its contents. With later versions of HAL, you may either get the cell contents verbalised first or the cell's co- ordinates. However, note that how much is conveyed to you as you move around a worksheet using HAL will depend on the "Default Verbosity" settings in HAL's "Speech Output Settings" in HAL's Control Panel. A setting of medium or high will speak a cell's reference/co-ordinate as well as its contents, whereas a setting of either low or minimum will only speak a cell's contents and not its co-ordinate reference labels. Press Numpad 3: to read the column letter heading followed by the row numeric heading of your current cell. Press Numpad 3 twice: To spell the current cell column and row headings, although this seems to have stopped working in HAL 6. Press left SHIFT Numpad 4: To activate the AutoSum feature. Press left SHIFT Numpad 7: To read the Formula Bar. Press left SHIFT Numpad 8: To jump to the Formula Bar and ESCAPE to leave it. Press left SHIFT Numpad 9: To jump to the Name box if there is one. Up to HAL 5.03, these were the only special spreadsheet hot keys which HAL possessed, so you will have to also use a combination of HAL's standard F key and numpad navigation and status reporting keys and the general Excel shortcuts given above. >From HAL 6.0 to 6.03, provided that you have the latest map file for MS Excel available at that time, you are also provided with the following hot keys to obtain lists of related information: Press CAPSLOCK 1: to obtain a list of links if your worksheet has any in its cells. Press CAPSLOCK 2: To obtain a list of objects if there are any in the spreadsheet's cells. Press CAPSLOCK 3: To obtain a list of worksheets in a workbook. Press left SHIFT CAPSLOCK 1: To obtain a list of charts if there are any. Press left SHIFT CAPSLOCK 8: to obtain information about the active chart. To get this information the chart must have focus. Press left SHIFT CAPSLOCK 3: To obtain a list of the visible cells which have formulae in them. Press left SHIFT CAPSLOCK 4: to obtain a list of the visible cells with content data in them. Press left SHIFT CAPSLOCK 5: To obtain a list of visible cells which have comments associated with them. >From HAL 6.5 some of the above hot key actions change and others are suppplied, provided that you have the latest map file for MS Excel: Press CAPSLOCK 1: To obtain a list of links if your worksheet has any in its cells. Press left SHIFT CAPSLOCK 1: To obtain a list of objects if the worksheet has any in its cells. Press left shift CAPSLOCK 8: to obtain a list of worksheets in a Workbook. Press left SHIFT CAPSLOCK 3: To obtain a list of charts if there are any. Press left SHIFT CAPSLOCK 4: To obtain information about the active chart. To get this information the chart must have focus. Press left SHIFT CAPSLOCK 5: To obtain a list of the visible cells which have formulae in them. Press left SHIFT CAPSLOCK 6: to obtain a list of the visible cells with content data in them. Press left SHIFT CAPSLOCK 7: To obtain a list of visible cells which have comments associated with them. Note 1: If no list of any of the above controls and objects exists in the worksheet, these SHIFT CAPSLOCK hot keys will simply elicit a not found message from HAL. Note 2: The CAPSLOCK key is also known as the DOLPHIN KEY. Note 3: You can download the most recent HAL map files from: www.dolphinuk.co.uk/updates 4.6. Brief Comments on HALs Performance in Excel AS you move around a worksheet from cell to cell, HAL does not advise you of such as currency signs in a cell. It also does not tell you when the value in a cell is the result of a formula, unless you have HAL 6.5 or later. However, if HAL is in its high verbosity mode, it will automatically advise you of the background colour of a cell as you go to it, which may be of value in spreadsheets which use such as a red background to signify that a customer is in debt to your company. Good HAL general hot keys which will give you valuable information in Excel are Numpad 2 to tell you the contents of the Status Bar; Numpad 7 to tell you the title of the workbook you are on, the contents of the cell you are currently on and that cell's reference co-ordinates; and pressing Numpad 0 tells you the value/text in a cell and pressing it twice spells it with three presses speaking it phonetically. Pressing this NumPad 0 key will also advise you of such as per cent, pound and dollar signs when you press it two or three times. If you are in edit mode with your cursor on a figure or letter (press F2 to enter edit mode), you can press the Numpad 4 key up to four times to get different text attributes reported to you, e.g. font type, font size, etc. Use of the left SHIFT Numpad 7 hot key to read the Formula Bar should also prove helpful. HAL has a speak highlighted text feature (by pressing Numpad 1) but this does not always work in Excel. If you wish to use Excel XP/2002 with HAL you will need Version 5.0 upwards. To use HAL with Excel 2003, you will need Version 5.3 upwards and have to be running your Windows XP operating system in "Classic" mode. Note: You will benefit from also learning the above general Excel hot keys as well as the HAL shortcuts. 4.7. Window-Eyes 4.0-4.5, 5.0 and 5.5 Special Spreadsheet Hot Keys and Set-Up The following configuration and hot key tips should make Window- Eyes work optimally with Excel. 4.7.1. Configuring Window-Eyes to Work with Excel If Window-Eyes does not work with Excel straight from the box, you may have to do a little customising, as follows: Launch Excel and then press CONTROL BACKSLASH to enter the Window-Eyes Control Panel, then press ALT F and then F again. In the list of set files you will come into press the letter O once or more times to jump to "Office 2000, Word, Excel, Outlook" (or whichever version of Office you are running) and then TAB twice to "Install" and press ENTER. Next, press ALT V (for View) and then T (for Toolbars). Ensure that the "Standard" and "Formatting" toolbars only are checked on by pressing ENTER on them if they are not already checked. Uncheck any others which may be checked. Again, press ALT V and then T but this time ARROW up to "Customise" and press ENTER. Ensure that the "Show Standard and Formatting Toolbars on Two Rows" Is Checked on and then press ENTER on "OK". Now press ALT and then T and in the Tools menu press ENTER on "AutoCorrect Options". Then move to the "AutoCorrect" property sheet with CONTROL TAB and in here TAB to and uncheck by pressing SPACEBAR on it the "Show AutoCorrect Options Buttons" option and press ENTER on "OK". 4.7.2. Window-Eyes Hot Keys In Excel 97, 200 and 2002 as you move to a new cell, Window-Eyes will automatically read out the cell's column and row co- ordinates followed by its contents. In Excel 2003, it will speak the cell's contents first and then its reference co-ordinates. Press ALT A: To read the current cell location co-ordinates. Press ALT D: To hear the cell data or formula. Press ALT C: To read the current cell contents. Press it twice quickly to get the contents spelled out and three times to hear it phonetically. Press ALT S: to read the status line. Press ALT W: to hear the currently displayed active worksheet. Press ALT T: To hear the current font type, size and style. Press ALT J: To hear the current justification in a cell. Press ALT CONTROL R: To hear the current row from the left-most cell displayed to the current cell. Press ALT CONTROL C: to hear the current column from the top cell down to the current cell. The about to be released Window-Eyes 5.5 specialises in extra functionality and information gathering for Excel and some of its additional hot keys for Excel as far as Beta 1 of WE5.5 is concerned are: Press INSERT V: To enter the Excel verbosity settings dialogue which now contains a new option for Excel. Within here you can view and change what Window-Eyes will -say and announce as you use it on Excel and move around cells, worksheets and workbooks. The Excel verbosity group contains four elements: cell information, miscellaneous, selection and summary. Go into this to see the defaults and make any changes which might suit how you want to work and experiment with the many new settings. Press INSERT E: To display the properties of the current element such as discovering the position of a cell, the font type/name, the font size, the text attributes, the text colour, the horizontal and vertical alignment, whether a cell has borders and their type, the number format, the cell size, and so forth. For charts you can discover: area type, chart name, chart type, chart size, border weight, chart values, and so forth. For objects in a spreadsheet you can discover: object type and object text. Press INSERT TAB: To Be able to quickly navigate through or review the components of a worksheet or workbook. You can activate buttons to list such items in a workbook or worksheet as hyperlinks, objects, named areas, cells in current column, cells in current row, page breaks, monitor cells, worksheets in the current workbook and charts in the worksheet or workbook. Press ALT SHIFT H: To obtain headers and totals information for the cell you are currently in. Then pressing such as F will select the full worksheet and pressing H will select both the row and column headers for your cell. Pressing ENTER will save these parameters for the current worksheet. There are also other information gathering parameters you can elect to turn on in this control as well. So, for example, to turn speaking of column headers on, ARROW to the column in your spreadsheet where the column headers are located, then press ALT SHIFT H to open the header dialogue box, then TAB to "Region type" and down ARROW to "Column Header". You now TAB to the "Add" button and press SPACEBAR, followed by TABBING to "OK" and pressing ENTER. Press ALT SHIFT M: To set up a single cell or group of cells you wish to monitor to be able to monitor them manually or make them speak automatically if they change. Press INSERT NUMPAD +: To route the mouse to the centre of the active cell or selected object. Press CONTROL SHIFT N: To hear the position of the active cell. If you press this twice you will hear the cell position and any defined header. Press ALT SHIFT F: To read the formula of the current cell. Pressing this twice will spell it. Press CONTROL SHIFT D: To hear the field data of the active cell. Subsequent presses of this hot key will spell and then spell phonetically the cell contents. Press CONTROL SHIFT S: To get a summary of the cell information spoken using the current verbosity options. Press it again to hear all verbosity for the cell, not just the current verbosity options. Press CONTROL SHIFT M: To get the range of the highlighted block of cells announced plus which cell is currently active. 4.8. Brief Comments on Window-Eyes Performance in Excel Before Version 4.5, Window-Eyes does not automatically advise you if a cell has a formula in it, nor does it announce currency symbols, per cent signs, etc. However, if you suspect a cell might contain a formula, you can press ALT D to get it announced. A press of ALT S will read the Status Bar for you. Another good hot key to remember is ALT t to hear font attributes of text or numbers in a cell, such as font type, size, if emboldened, etc, although this hot key may conflict with pressing ALT T to go into the Tools menu. Window-Eyes hot key for confirming highlighted items (CONTROL SHIFT M) does not work with highlighted cells. However, as soon as you have highlighted a cell or block of cells, WE does confirm this at this stage, e.g. if you highlight from Cell A1 to the end of the worksheet data area, it will say "Select to end of Worksheet". An improvement with Window-Eyes 4.5 and 5.0 is that currency, per cent and other symbols are announced when you move to a cell containing these. If you wish to use Excel XP/2002 with Window-Eyes, you will need Version 4.2 upwards. To use Excel 2003, you will require version 4.5 or later with the new Office 2003 set files. Note 1: You will benefit from also learning the above general Excel hot keys as well as the Window-Eyes shortcuts. Note 2: If you purchased or updated your Window-eyes to Version 4.5 before mid-December 2003, you may find that you do not have the new set files for Office 2003, so you should download them from: ftp://ftp.gwmicro.com/weset/office11.exe and run this exe file to install them. Further improvements are about to be made in how Window-Eyes works with Excel with Window-eyes Version 5.5, although at the present time only the first beta is available. If you like, as you move through cells, Window-Eyes can now tell you things like cell contents, cell position, cell formula and if this cell is used in a formula elsewhere in the worksheet. It can tell you if a cell holds a comment, if it has an hyperlink, if it has text within it which has been shrunk to fit inside the cell, if it has been clipped or wrapped within the cell boundary. You can be advised whether a cell is a merged cell and if it overlaps other cells. You can be told what kind of number formatting has been defined for the cell and if the cell has a border, amongst other things. Window-Eyes 5.5 can also tell you whether a workbook has a name, the name of the current worksheet, the number of worksheets in the current workbook and the number of comments in an entire workbook or just the current worksheet. You can find out the number of hyperlinks in the whole workbook or current worksheet and the number of objects and charts. ******** 5.1. Customising Excel for Visually Impaired People and Turning the Office Assistant Off If you use a screenreader and cannot make any reasonable use of your computer monitor screen, I recommend that you ensure that your copy of Excel is set-up as follows: 1. Ensure that the formatting and standard tool bars are on screen by: A. Launch Excel by pressing Windows key, then P (for Programs) followed by M (for MS Excel). You may have to press P and M more than once before you get there. B. With Excel launched, press ALT V (for View) then T (for Toolbars) and then ARROW TO both "Standard" and "Formatting" and press ENTER to turn them on if they are not already checked (they probably will be by default). Ensure that all other options in here are unchecked. 2. Turn the formula bar on by Pressing ALT T (for Tools), then O (for Options) and CONTROL TAB to the "View" property sheet if you are not already on it. Now TAB to "Formula Bar" and press SPACEBAR to check this on if it is not already checked. 3. Again in the Tools, Options menu (ALT t, then O), in the View menu, TAB through the various options and ensure that the following are checked on: "Status Bar" and "Grid Lines", then TAB to "OK" and press ENTER to finish. 4. Ensure that Excel is set up to work in what is called the A1 reference style rather than the R1C1 reference style. This tutorial is written from the perspective of Excel's default reference style, which is the A1 style. press ALT T (for Tools), O (for Options) and then CONTROL TAB to the "General" property sheet. TAB to "R1C1 Reference Style" and if this is checked on, press SPACEBAR to turn it off and thereby switch things to the A1 reference style. 5. In Excel 2000, 2002 and 2003 (but not earlier versions of Excel), press ALT V, then T to go back into the View, Toolbars list and ARROW up TO "Customise" and press ENTER. You should come into a three property sheet multi-tab dialogue box on the "Options" sheet but if you do not land on this sheet, press CONTROL TAB until you get there. Now, depending on the version of Excel you are using, either: A. press SPACEBAR on "Show Standard and Formatting Toolbars on Two Rows" to turn this on if it is not already on; or B. Press SPACEBAR on "Standard and Formatting Toolbars Share One Row to turn this off if it is not already off. Then TAB to "Close" or "OK" and press ENTER. 6. Ensure that the automatic calculation option is on by Pressing ALT T (for Tools), O (for Options) and then CONTROL TAB to the "Calculations" property sheet. Then TAB to a list of three radio buttons which you can ARROW up and down and ensure that "Automatic" is selected before TABBING to "OK" and pressing ENTER on this to finish. 7. In Excel 2000 and XP, turn the Office Assistant off by Going into the Help Menu (ALT H) and ARROW down then press ENTER on "Hide the Office Assistant" to hide it. The option will then read "Show the Office Assistant". You disable the Office Assistant in Excel 97 differently. On installation of Word 97, you should have used the custom option of installing and thereby not permitted the Office Assistant to be installed. If this has not been done, you can disable it by navigating to it and by either deleting it or renaming it. To do this, via Windows Explorer, go to \Program Files\Microsoft office\Office\Actors and either delete the Actors folder or rename it. As the Office Assistant is shared by all MS Office programs, e.g. Access, Excel, Word, etc, turning it off in one of them will result in it being turned off in them all. 8. Always work with your worksheet windows maximised by pressing ALT SPACEBAR and then X if they are not already maximised. 9. Do not bother with the "Preview", "Freeze Panes" or "Split Screen" options in the Window menu unless you can see good enough to do so. Whilst these are useful for sighted people, they are of little or no use to people who cannot see the screen or who only have access to a small magnified portion of the screen. Instead of using "Freeze Screen" use your screenreader's speak column and row headings hot key to keep track of where you are and keep several open worksheets or workbooks in their own individual windows and CONTROL F6 between them as required. 10. With Excel 2000, 2002 and 2003 (but not Excel 97), press ALT T, followed by C to enter the Customise dialogue box. You should be in the "Options" property sheet, so TAB to "Always Show Full Menus" and press SPACEBAR to check this on. This will give you access to the full range of menu commands, rather than the restricted range which versions of Excel after the 97 version show as their default. 11. If you have not already done this and you are a Window-Eyes user and WE does not read Excel spreadsheets correctly straight from the box, you will need to configure it to do so. Do this by: A. Launch Excel as normal. B. Press CONTROL \ to get into the WE Control Panel. C. Press ALT F (for File) and then F (for Set File). D. Press either E or O until you get to the correct set file for your version of Excel, e.g. "Excel 97", "Office 2000, Word, Excel, Outlook", etc. E. Press TAB twice to an "Install" button and then press ENTER. F. You may now have to close Excel and re-launch it before use. 12. Again, if you are using Window-Eyes and your version of Excel has this in its View menu, ensure that you do not have "Large Icons" on in the View menu; just leave them on small. ******** 6.1. Microsoft Excel Specifications Excel's specifications are frequently limited to your system's resources and memory. However, some which may be of interest and should apply even with Excel's minimum resource requirements are as follows. Worksheet and workbook specifications: - Number of rows: 65,536. Number of columns: 256. Maximum column width: 255 characters. Maximum row height: 400 points, i.e. about 5.5 inches. Maximum length of cell contents for text: 1,024 displayed in the cell but up to 32,767 can be displayed in the formula bar. Default number of sheets in a workbook: 3 but infinite depending on system memory. Maximum colours in a workbook: 56. Maximum panes in a window: 4. Zoom range: 10% to 400%. Maximum sort references: Three in a single sort but unlimited when using sequential sorts. Maximum undo levels: 16. Maximum fields in a data form: 32. Calculation Specifications: - Number precision: 15 digits. Largest allowed positive number: 9.9999999999999E307. Smallest allowed negative number: -9.99999999999999E307. Smallest allowed positive number: 1E-307. Largest allowed negative number: -1E-307. Maximum length of formula contents: 1,024 characters. Maximum iterations: 32,767. Maximum selected ranges: 32,048. Maximum arguments in a function: 30. Maximum nested levels of functions: 7. Maximum number of available worksheet functions: 329. Earliest date allowed for calculations: 1 January 1900. Latest date allowed for calculations: 31 December 9999. Largest amount of time which can be entered: 9999:99:99. Pivot table report specifications: - Maximum items in a pivot able report: 8,000. Maximum page fields in a pivot table report: 256. Maximum data fields in a pivot table report: 256. ******** 7.1. How to Launch Excel and How to Place a Shortcut on Your Desktop to Launch Excel As usual with MS Windows programs, you can start Excel in a number of different ways. A selection of these are given below. 7.2. The Path to Excel You can start Excel either by: 1. Press Windows key, then P (until you reach Programs) followed by M (until you reach Microsoft Excel) and press ENTER, when it will start up. or 2. Try the Run command by pressing Windows key and R (for Run) together and then type in the editfield: excel.exe and press ENTER. or 3. Using Your Quick Launch Icon on the Desktop by: Press Windows key m or Windows key D to reach the Desktop, then press E (for Excel) until you reach it and then press ENTER to start it. This latter method assumes that you already have a shortcut on your Desktop to launch Excel from. If you do not, you can create one for future convenience as outline below. 7.3. Creating a Desktop Quick Launch Icon If you prefer this, you may wish to put a shortcut icon on your Desktop from which to launch Excel. You can use this procedure to create shortcuts for any of your other programs as well. To do this: 1. With Windows 98 and some versions of Windows 95: A. place focus on Excel by navigating to it as in 1 above but do not launch it. B. Now press SHIFT F10 to bring up a Context Menu and ARROW to "Send To" and press ENTER. C. Lastly, ARROW down to "Desktop" and press ENTER. Note: You can also create shortcuts in the above way for any other file, folder or disk drive on your computer and to Web pages. 2. If the above is not possible with your operating system, the longer way to achieve this is: A. Press Windows LOGO key followed by the letter S, then press T. B. Then press CONTROL TAB to the 'Start Menu Programs Property Sheet'. C. You will and on the 'Add' button, so press ENTER. Then tab to the 'Browse' button and press ENTER. D. You will be asked for the executable filename, so type in 'excel.exe' and then TAB to the list of folders on your c: drive under the 'Look In' line. Press P until 'Program Files' is spoken and then press ENTER. E. Then press M until 'Microsoft Office' is spoken and press ENTER. F. Press O until 'Office' is highlighted and then press ENTER. G. Now press E until excel.exe is spoken. H. Press the TAB key to the 'Open' option and then press ENTER. I. Then TAB to the 'Next' button and press ENTER. You are asked where you want to place the shortcut, so ARROW up to 'Desktop' and then press TAB to 'Next' and press ENTER. J. You are asked to select a name for the shortcut and given 'excel.exe' as an option. If you want to change this, just type over it, e.g. with "Excel 2000", and then press TAB to the 'Finish' button and press ENTER. K. Now press TAB to the 'OK' button and press ENTER to complete the procedure. L. You can now, in the normal way, go to this shortcut on your Desktop by pressing Windows Logo key M followed by E until 'Excel 2000' is spoken and then press ENTER to load it. ******** 8.1. Getting a Feel for How to Move Around a Worksheet Launch Excel as instructed in one of the methods in the last section. If you do not land in cell A1 (Column A, Row 1) automatically, press CONTROL HOME to jump straight there. Now just experiment with some of the Excel general movement and command shortcuts listed in section 4 above to get a feel for both a single screen of a worksheet and for the colossal size of the entire possible number of columns and rows a sheet can accommodate. Do this both in normal live mode and in your screenreader's mouse or virtual modes. For example: 1. Use your up, down, left and right ARROW keys to move linearly from one blank cell to another and notice how your particular screen reader announces the cell top title and left label alphabetic and numeric headings and the combined cell co- ordinates derived from those headings. Note also that you can move from your current cell to the next cell to the right by pressing the TAB key, backwards with SHIFT TAB and that pressing ENTER in a given cell moves you down a cell in the same column. 2. If you wish to go directly to a cell some way into a worksheet, press CONTROL G (for Goto) and then type into the editfield the cell co-ordinates of where you wish to jump to, e.g. V99, and press ENTER. Check your position by simply ARROWING up once and then back down once to get the co-ordinate of your current cell announced to you. You could also press your screenreader's read column and row co-ordinates hot key, e.g. Numpad 3 once or twice with HAL, INSERT C with JAWS and ALT A with WE. 3. Now Move back to cell A1 with CONTROL HOME. Next press CONTROL END and note that this does not take you to the last cell in an empty sheet. What it would do if your worksheet had data in some of the cells is take you to the last cell in the sheet which has data in it. 4. Move to cell b4 and then press the PAGE down key and observe that you are moved 25 (or sometimes only 23) rows further forward in the same column, e.g. to row B29 or B27. Another press of PAGE down takes you to a place the same number of rows further down the sheet as the first press of PAGE down did.Pressing PAGE up takes you back 25 or 23 rows. 5. Go back to A1 with CONTROL HOME and then press CONTROL right ARROW and note that you are taken directly to the last column in row 1, which is IV1 (the 256th column) in a blank sheet. If you did this whilst in a worksheet which had data typed into it in 9 cells from A1 down to C3, i.e. columns A, B and C plus rows 1, 2 and 3 were all completed, you would only have been taken to the left edge of the data which would have been C1 in this case. 6. To move up or down a column to the edge of the data in it, press CONTROL up or down ARROW. If there is no data in the column you are in you would be taken either to the first row in the column or to the last possible row which is row 65,536. If the sheet had data in it, you would have gone to the earliest or latest cell which had data in it in your current column, provided that your cursor was within the area of the worksheet data when you started. 7. If you would like to open another blank worksheet to enter data into, you just press CONTROL PAGE down. Pressing CONTROL PAGE up will take you back to your first sheet with any data you may have entered into it. 8. Move to one of the empty cells and press CONTROL ; (semicolon) and observe that the current date is entered into the cell for you. ARROW down to the next empty cell and press CONTROL SHIFT ; and check that the current time has been entered for you. Note that you can delete or modify the contents of a cell by simply pressing the DELETE key whilst in it or by pressing F2 and then BACKSPACING it out-with the latter you could also edit the data to produce a slightly different figure, etc. 9. Complete a few rows and columns and keep on experimenting in this way, using the Excel general and your particular screenreader's shortcuts and hot keys, until you are happy that you know what happens when you do certain things in a blank worksheet and in a worksheet with a small block of data in it. ******** 9.1. Excel's Unique Way of Highlighting/Selecting Cells and Ranges It is necessary to highlight (also known as selecting) existing text, cells or formulae before carrying out certain actions on them, such as deleting them, moving or copying them elsewhere, emboldening them or having the font on them changed, etc. 1. You can use standard Windows highlighting procedures with the SHIFT key (explained in more detail in Section 17). Additionally, Excel also has some of its own special extended highlighting features by pressing the F8 key. In this case, you will be able to ARROW around and select cells, columns, rows and ranges of cells, etc, but without having to hold down the SHIFT key, e.g. press F8 release it and then press the right ARROW key to select the next cell to the right, F8 down ARROW highlights the next cell down, F8 home highlights from the current cell to the first cell in the row, F8 CONTROL END selects from the cell the cursor is currently in to the end of the data area, etc. To remove this type of highlighting you have to press ESCAPE followed by the left ARROW key, which will return you to the first cell which was highlighted. 2. After pressing F8 and highlighting a first cell or range of cells, you can then add another range of cells to the first selection by pressing SHIFT F8 and ARROWING to the first cell to be highlighted in the second range of cells, pressing F8 again and then ARROWING again until the second range is highlighted. You then press CONTROL C to copy or CONTROL X to cut both ranges simultaneously to the Clipboard and then move to the place you would like both ranges pasting in and press CONTROL V. 3. If you place your cursor in the right bottom most cell of the range you wish to highlight, press SCROLL LOCK and then F8 followed by SHIFT HOME, you will highlight from this bottom most cell to the top left most cell in the window. You must then press SCROLL LOCK again to turn it off. The reverse will happen if you are currently in an upper cell and wish to highlight from there to the bottom most cell in the window with SCROLL LOCK, F8 and then SHIFT END, e.g. if you are currently in A1 and do the latter, you are likely to select the range from A1 to L23., being a whole screen/window of cells. 4. You can also highlight with what is known as "End Mode". If you press the END key first, you can then make use of several highlighting features, e.g.: A. Press END then SHIFT down ARROW and you will highlight from the cell the cursor is in in the current column down to the last cell in the same column or to the first blank cell if this occurs first. B. Press END and then SHIFT right ARROW and you will select all cells from the cursor to the right-hand edge of your worksheet data in the same row or to the first instance of a blank cell if this occurs first. C. Press END and then CONTROL SHIFT HOME and you will extend the highlighting to the first cell used on the worksheet in the top left corner. D. Press END and then SHIFT ENTER and you will Extend the selection to the last cell in the current row (same effect as in B above). Note: "End Mode" also works as a means of quickly jumping from place to place on a worksheet without highlighting it if you use it without the SHIFT key depressed. It is thus also a means of quick worksheet navigation. Irrespective of the way you choose to do it, as soon as you have the cell, row, column or range highlighted you can, for example: 1. Press the DEL key to delete it. 2. Press CONTROL X to cut/move it to the Clipboard for pasting elsewhere with CONTROL V. 3. Press CONTROL C to copy it to the Clipboard. 4. Press CONTROL B to embolden it if it is text (make it thicker and darker). 5. Press CONTROL U to underline it if it is text. 6. Press CONTROL I to make it italic if it is text (lean to the right). 7. Press ALT O, then E and CONTROL TAB to "Font" and then select any font type and size you want and then press ENTER on "OK" to have it applied to the highlighted words. 8. Press SHIFT CONTROL P to be able to type a new point size into the editfield you are in and, in Excel 2000, 2002 and 2003, you can then ARROW down a list of other attribute changes to also apply to the text. 9.Press CONTROL P to print the highlighted range of cells. Note: To see how to highlight in the general Windows way by using the SHIFT key, go to (More about Highlighting--The General Windows Way" below. ******** 10.1. Excel's Default Workbook Formats and Conventions If you create a workbook without modifying any of the formatting and other defaults (the normal way it does things), what your spreadsheets will look like is: 1. Text and labels typed into a cell will be left aligned. 2. Figures and values entered into a cell will be right aligned. 3. The font will be Ariel, normal, in 10 point. 4. The vertical alignment will be to have cell entries aligned to the bottom of a multi-line cell. 5. The cell numbering format will be general, i.e. continuous numbers without comas, decimal points, pound signs, etc. 6. The width of a cell will be 8 characters or figures. 7. The height of a cell will be 12 points, i.e. deep enough for 1 line of text/figures only. 8. Cell borders will be of continuous thin lines. 9. The cell foreground Colour will be automatic, i.e. normally black. 10. The background cell pattern/colour will be set to off, meaning no background cell patterns will be used and the background will be left the colour of the paper if printed. 11. Cells will not be hidden or protected. 12. A worksheet will have 256 columns. 13. A worksheet will have up to 65,536 rows. You will, in most circumstances, wish to alter some of these defaults to make the workbook look more balanced and professional. In some cases you will wish to do this wholly by yourself manually; in others you will be happy to allow Excel to do this using some of its "auto" features or its (or your own created) templates. How this is done is explained in the below sections. ******** 11.1. Entering Data into a Worksheet You type figures or text into a cell in the same way as you would into any other editfield. Create the following small worksheet of fish, chips and bread rolls sold for the week for a chip shop to practise on by following the below instructions. 11.2. Workbook Exercise 1--Creating and Saving a Worksheet with headings and Formulae for totals 1. Launch Excel and press CONTROL HOME to go to cell A1 if you are not already there. Use one of the already-mentioned launch methods, e.g. press Windows key R, type excel.exe and press ENTER. 2. In A1 type the heading "Day" and press the right ARROW or the TAB key. If you make a typing mistake, just erase it by pressing the BACKSPACE key as many times as necessary. 3. In Cell B1 type the heading "Fish" and press the right ARROW key. 3. In C1 type the heading "Chips" and press right ARROW. 4. In D1 type the heading "Rolls" and now press the down ARROW key once followed by the HOME key to move to A2, immediately below your A1 heading of "Days". Note that your screenreader may speak the column heading when you get back to column A. 5. In A2 type "Monday" and press either TAB or right ARROW. 6. In B2 type "100 and press right ARROW. 7. In C2 type "150 and press right ARROW or TAB. 8. In D2 type "50 and press down ARROW followed by HOME to go to A3. 9. In A3 type "Tuesday" and press right ARROW. 10. In B3 type "120 and press right ARROW. 11. In C3 type "140 and press right ARROW. 12. In D3 type "50 and press down ARROW followed by HOME. 13. In cell A4 type "Wednesday" and press right ARROW. 14. In B4 type "177 and press right ARROW. 15. In C4 type "190" and press right ARROW. 16. In D4 type "90 and press down ARROW followed by HOME. 17. In A5 type "Thursday and press right ARROW. 18. In B5 type "120 and press right ARROW. 19. In C5 type "140 and press right ARROW. 20. In D5 type "85 and press down ARROW followed by HOME. 21. In A6 type "friday" and press right ARROW. 22. In B6 type "200 and press right ARROW. 23. In C6 type "170 and press right ARROW. 24. In D6 type "105 and press down ARROW followed by HOME. 25. In A7 type "Totals" and press right ARROW. 26. In B7 press = (equals sign) and then in the editfield type in the formula: SUM(b2:b6) and press ENTER. Note that Excel moves you to the next row down, so press up ARROW to view the resulting total of fish sold for the week. The figure should be 717. 27. Now, whilst in the fish total cell, press CONTROL C to copy the formula to the Clipboard and then move to the total cell for chips with right ARROW and press CONTROL V to copy the same formula in there. do the same for the total of rolls with CONTROL V (you do not need to copy it to the Clipboard first this time as the formula is kept in the Clipboard's memory until you overwrite it with something else or turn your computer off). Observe that the formulae have been copied and the totals automatically calculated for you. The chips total should be 790 and the rolls total 380. Remember, if you are at the top or in the middle of a column with a total at the bottom which you want to be advised of, use your screenreader's read column total hot key, e.g. INSERT Numpad ENTER in JAWS. 28. We will save this simple worksheet (when it then becomes a single sheet workbook( to a filename for retrieving and further manipulation at a later point in this tutorial and so that you do not have to re-type it or something similar. do this as follows. 29. To save the workbook, press CONTROL S (for Save) and then type a filename into the editfield you come into, e.g. fishandchips, and press ENTER. The file will save to the hard disk and automatically be given an ".XLS" file extension by Excel. Saving is explained in more detail in a later section. 30. To clear the data from the screen press ALT F (for File) and then C (for Clear). When you want to create another workbook, you just press CONTROL N (for New Workbook). Note 1: Be aware that the default way Excel aligns text is to the left of a cell but it aligns other values to the right. You will want to make the alignment uniform or perhaps centred at some stage for cosmetic appearance reasons but we will go into this later. Note 2: If you make a mistake when typing a formula or function into a cell, the error message you will likely get from Excel is either "#name?" or "#value?". You will have to go back to the cell in question, press the DELETE key and then re-enter the formula correctly. Note 3: If you need to type a figure into a cell which starts with a 0, you must precede it with a ' (apostrophe), otherwise Excel will ignore the zero. Similarly, if you want to start typing text into a cell with the = sign, you must precede it with the ', otherwise Excel will think you want to type a formula or function in and go into formula mode. ******** 12.1. Excel's Ability to Automatically work Out which Cells you are Likely to Wish to Add UP--AutoSum In step 26 above, I advised you of how to type a formula into a cell to add up a column of figures in a specified range, i.e. cells B2 to B6. In reality, Excel is clever enough to be able to work out straightforward range calculation requirements itself for summing down a column. Thus, instead of pressing = and then typing the formula in in step 26 above, we could have elected to press ALT = and then press ENTER, as Excel will have intelligently worked out what you are likely to want to have added up. If you have not got the fish and chips workbook open at this point, just press CONTROL O (for Open) and type the filename of "fishandchips" into the editfield which you are now in and press ENTER to bring it to screen. Now go to Cell B7 and press DELETE to erase the contents of that total cell together with its formula. Then, with your cursor in that same cell, press ALT = and observe what happens. You will receive a message asking you if Excel has correctly worked out the cell range you want to add up and if it has, you only need to press ENTER to get the formula and total summed in D7 as it was before. If your screenreader does not automatically read out Excel's suggestion for your required summing, use its read Status Bar, or current line feature to view this or go into mouse mode to read the Formula Bar if you need to double check this. ******** 13.1. Saving A Worksheet to Hard or Floppy Disk After creating your first fish and chip workbook masterpiece above you will not want to loose it, so save it to either your hard disk or to a floppy disk for later re-opening for viewing or modifying. 13.2. Saving in the Default XLS Format Save your spreadsheet as follows: 1. Press either CONTROL S or SHIFT f12 to open the Save dialogue box. 2. Note that Excel will give the workbook a filename of its own, e.g. "book1.xls". You should overwrite this with a name of your own preference by just typing over it, e.g. "fishandchips". Excel will automatically give it an ".XLS" filename extension for you. 3. Tab to "Save In" to view where it will be saved to. This will normally be to your "My documents" folder where all Microsoft Office saved documents save by default unless you have changed this. 4. TAB to "Save" and press ENTER to complete the save command. Note that once you know where your workbooks will save to on your hard disk you need only press ENTER after typing the filename in in future and they will always go there. If you have an earlier version of the current workbook when you resave, you will be asked if you really want to overwrite it with the new version, so press Y (for Yes) if you do or N to cancel if you change your mind. 5. If you would like to save to a floppy disk in your A: drive instead of to My documents on your hard disk, you would, in the filename editfield, type "A:\fishandchips" and press ENTER and it will be saved there. Ensure that you have a 3.5 inch floppy disk in your A: drive before you do this, of course. If you have saved to the A: drive, all resaves will now automatically be saved to your floppy disk. To get Excel to start to save to your hard disk again, next time you save a workbook, type the full path to the hard disk default saving folder, i.e. C:\my documents\fishandchips, and press ENTER. 6. To clear the screen after saving, press ALT F (for File) and then C (for Clear). You can now start to create another workbook after pressing CONTROL N if you wish. Note 1: After saving the default filenames of "book1.xls", "book2.xls", etc, which Excel gives to workbooks will change to your chosen filename of "fishandchips.xls" and be reflected in the Title Bar at the top of your screen. Note 2: After you save your workbook it will remain on screen for you to continue to edit or modify as you wish. It is a good idea to save a workbook to a filename frequently as you create it in case you have a power or computer failure during its creation. You will then be able to retrieve most of it on reboot of your PC. You can continue to save to the same filename regularly, once you have first saved it and named it, by simply pressing CONTROL S every few minutes. If you would like to save a second copy to a different filename or to a different location, you would press ALT F (for File( and then A (for Save As) and provide a different filename this time. 13.3. Shortcuts Available in the Save and Open Dialogue Boxes When you have either the Save, Save AS or Open dialogue box open as above, there are several shortcuts you can make use of to quickly achieve given actions if these do not conflict with your screenreader's own hot keys, e.g. pressing ALT 5 will let you type in the name of a new folder to create a sub-folder from your current folder and most of the other numeric keys on the keyboard will also affect other shortcuts for you, depending on the version of Excel you are running. Examples of other ALT and number shortcuts which you might find are the ability to move up or down a folder level from where you are at present to save to, searching the Web, listing folders, obtaining a Context menu of commands, etc, but it really does depend on the version of Word as to what is available. 13.4. Saving a Workbook in Web Page HTML format If you would like your spreadsheet saving as a Web page so that it can be viewed in a Web browser either on your hard disk or on the Internet after you have uploaded it to your Website, at step 2 in "Saving in the Default XLS Format" above, TAB once to "Files of Type" and then ARROW to "HTML" or "Web Page (*.htm; *.html)" before pressing ENTER on the "Save" button. If you are using Excel 2003, you will find that when you elect to save in formats other than the default of .XML, you may find extra options available within the save dialogue. For instance, if you select to save in either of the "Single File Web Page" or "Web Page" formats, you can then TAB to extra options you may wish to make use of, such as to save the "Entire Workbook" or just the sheet involved, to "Publish" the workbook or single worksheet, to "Change Title", etc. 13.5. Saving Your Workspace to a file for Quick Retrieval of a Previous Working Environment To save your current working environment to a filename so that you can open exactly this same environment when you next use Excel to continue where you left off, including all workbooks/worksheets and their positions on screen which were open when you were last using Excel and their contents, etc: 1. With the workbook or workbooks and your general working environment as you were just using it, press ALT F (for File) and then W (for Save Workspace). 2. A save dialogue will appear and the filename editfield will likely be already completed with a default filename such as "resume.xlw". you can simply press ENTER at this stage to save to this filename or you can type over it with a name of your own choice, such as "accounts", when it will automatically be given the standard extension of .xlw to identify it as a workspace file. 3. Then TAB to "Save" and press ENTER to finish and save the workspace file to your My Documents folder. 4. When you next want to return to the exact set-up and workbook(s) position and contents you had at the time you saved the workspace, just reopen it as normal with CONTROL O and type in the workspace filename, e.g. resume.xlw, accounts.xlw, etc. ******** 14.1. Printing Workbooks, Worksheets and Cell Ranges You can print a whole workbook with all of its sheets and charts, a single worksheet or a range of worksheets from a workbook or a range of cells within a worksheet. 14.2. Portrait Printing The default way Excel prints is portrait, i.e. with the paper inserted into your printer the same way as you would print a letter, being inserted narrowest end first. If you wish to commit your worksheets to paper you should: 1. Go to the first cell in the worksheet (top left cell) and then highlight the whole range of your worksheet by pressing END then CONTROL SHIFT END or do it by pressing CONTROL SHIFT 8. 2. Press CONTROL P to open the print dialogue box. 3. You will fall on the "Number of Copies" spin box which will be on 1. If you want more than one copy, ARROW up to the number you want or just type the figure in here. 4. Press ENTER to commence printing. 5. If, before pressing ENTER to print as above, you would first like to customise how the print job is done, TAB through the other print options and note in particular: A. "Print to File"--If you press SPACEBAR to check this on, you can print your workbook to a filename just like saving it but it will be given a printer filename extension of ".prn" for later printing possibly via a printer on a different computer. B. "All"--If this is checked on (it is by default) you will get all worksheets of your workbook printed out. C. "From:"--This is where, if your workbook contains several sheets, you can ARROW up to have the print out start at a worksheet other than sheet 1, e.g. sheet 3. D. "To:"--This is where you tell Excel at what sheet you want the printing to end if you do not want it to continue to the end of a multi-paged workbook. E. After making any modifications to the default one copy and all pages printing you may require, TAB to "OK" and press ENTER to start printing. 14.3. Landscape Printing It is frequently preferable to print a spreadsheet in landscape orientation because worksheets often take up more space across the page than down it. Landscape printing is where the page is inserted into your printer widest end first. To print in landscape format: 1. Press ALT F (for File) and then u (for Page Setup). 2. If you are not already on it, CONTROL TAB to the "Page" property sheet. 3. TAB to "Portrait" and then ARROW down to "Landscape". 4. If you would like the size of the printed area to spread out to fit the size of the paper, ARROW to "Scaling" or "Fit To" and press SPACEBAR to check this on before TABBING on. Scaling may either reduce or increase the printed view of your worksheet, depending on its size. 5. TAB to "OK" and press ENTER. Excel will print one copy of the workbook out. 6. Whilst at stage 2 above, you should tab through the many other options to ascertain an idea of the various modifications you could make to a printed workbook before you print it, e.g. you can ARROW up or down the Page size" and "Print quality" lists to change these etc. Remember, you can press SHIFT F1 at any time to elicit a context-sensitive online help explanation of what the current option is for. Note: If you have highlighted a block of cells before pressing CONTROL P, you will be able to print out only that range of selected cells instead of the whole worksheet or workbook. 14.4. Workbook Margins when printed and Page Set-Up The default Excel margins when you print a workbook out are viewed or changed by: 1. Press ALT F (for File) and then U (for Print setup). 2. CONTROL TAB to "Margins". 3. TAB to "Top" and observe that this margin is set at 2.5 centimetres (equal to 1 inch), as is the "Bottom" margin. 4. If you would like to alter the size of the margins, just ARROW up or down the various other selections or simply type in your own requirement, e.g. type in "2.7" if you need your margins to be this finely set. 5. Continue to press the TAB key and note that the left and right margins are set at 1.9 cm. 6. Any headers and/or footers you may have elected to include on your printouts will print at 1.3 cm from the top or bottom of the page respectively, unless you change this. 7. The "Horizontally" and "Vertically" controls, if checked on by pressing SPACEBAR on any or both of them, ensure that your printed copy will be centred on the page either horizontally or vertically or both. 8. Ensure that you have chosen the correct size paper and orientation for the paper you are using in the "Page" property sheet before you TAB to any of the "Print" buttons and press ENTER to get your workbook, individual worksheet, selection of worksheets or cell range printed out. Note: See also the alternative menu method of printing shown in "Using, Viewing and Printing Common formulae" below. ******** 15.1. Reopening an Already Created Workbook Having created and saved your workbook you are likely to want to retrieve it at some time. To retrieve a workbook: 1. Launch Excel and then press ALT F (for File), then C (for Clear) to ensure that the workbook you are retrieving opens up as worksheet 1. 2. Then press CONTROL O (for Open). 3. Type the filename into the editfield which opens up, e.g. "fishandchips" (no need to type the .xls extension) and press ENTER. Excel will find it on your hard disk in the folder you normally use to save in and bring it to the screen, e.g. from My documents or from your floppy disk if you have been saving there. 4. If you prefer or cannot remember the name of the Workbook you want to open, you can also SHIT TAB backwards once from the above filename editfield to a list of your Workbook files, ARROW to the one you want and press ENTER to open it. 5. If your workbook is on a floppy disk or CD-ROM, you may also have to type in the drive letter of that drive as well, e.g. "a:\fishandchips", "D:\fishandchips", etc. 6. In Excel 2000 and 2002 there is an "History" button in the Open dialogue box which permits you to quickly and easily get access to your most recently opened 20 to 50 files and folders to re-open them. Press ENTER on it to avail yourself of this feature if you cannot remember the name of the file you want or if you find this otherwise useful. The History button has been renamed "My Recent Documents" in Excel 2003. You ARROW to the workbook you want and then press TAB to the "Open" button and press ENTER. Note: if there appears to be no files on a floppy disk or in a folder which you know should contain spreadsheet files, it could be because the 'Files of Type' box is set to a file format which the files on the floppy disk were not saved in. This means that Excel will not be able to 'see' them. To remedy this, TAB to the Files of Type list and press A for 'All Files' and then SHIFT tab back to the files list and all files, irrespective of their file extensions, will be displayed. 15.2. Using the Open Dialogue to rename, Copy, Move, delete and Create a Shortcut to a Workbook File When you have focus on one of your saved workbooks in the list of workbook files at step 4 in the above sub-section, you can invoke a Context Menu by pressing SHIFT F10 and then ARROW to and carry out a number of commands on that workbook, which will vary depending on the version of Excel you are using, e.g. delete it, copy or move it elsewhere, create a shortcut to launch it from, rename it, etc. Alternatively, if you wish to copy or move a worksheet somewhere else within its workbook wrapper, you can do this via the Edit, Move or Copy Sheet feature. 15.3. Shortcuts Available in the Open and Save Dialogue Boxes For information on these, please see "Shortcuts Available in the Save and Open Dialogue Boxes" above. ******** 16.1. Cut, Copy and Paste with the Windows and Excel Clipboards Clipboards are very useful when you want to pass information from one place on your worksheet to another, particularly for visually impaired users, who may not find the methods of drag and drop very compatible with keyboard operations. You can usually use the Clipboard as an alternative to dragging and dropping. You generally have to highlight data before copying or cutting (moving) it to the Clipboard, as explained in Sections 9 and 17. There are two distinct Clipboards available when you are using Microsoft Windows and any Microsoft Office program, such as Word or Excel. 16.2. The Traditional Windows Clipboard The Windows Clipboard is an area of your computer's memory which is used to copy or cut text, values, blocks or whole worksheets or workbooks to. These will be held in the Clipboard's memory until you either copy/cut another piece of information to the Clipboard, when the last one is over-written, or until you turn your PC off, when the clipboard is cleared. The Windows Clipboard used by Microsoft Office programs and just about any other program, e.g. Excel, Word, Access and Powerpoint, was, as described above, a one copy/cut area of memory up to and including Office 97. However, in MS Office programs since Office 2000 it has been possible to copy/cut up to either 12 or 24 blocks of information to a special Office Clipboard and then paste all or just some of them elsewhere, including across the Office suite of programs. This multiple copy/cut ability works reasonably well from the keyboard in both Ms Excel 2000, 2002 and 2003. It is described below. 16.3. The Excel 2000 Multiple Clipboard The Excel 2000 floating Clipboard toolbar, when activated, fills part of the document window and can be somewhat annoying, as it may cut off your screenreader's view of some of the text and data on screen. On the other hand, it is more versatile, as you can not only cut but also copy up to twelve separate times to the Clipboard without them over-writing one another. You may therefore wish to keep this Clipboard unchecked for most of the time and only enable it when you want to do multiple cut, copy and paste jobs. You can, of course, still use the Clipboard with the normal one cut, copy and paste method but any previous material in the Clipboard will be over-written. You activate the Clipboard toolbar to be able to view, empty, cut or copy its contents by pressing ALT V, T and then ARROWING down to "Clipboard" and pressing ENTER to check it on. In Excel 2002 and 2003, you activate it via the Edit menu, Clipboard. You now view and use the Clipboard by: 1. Press the left ALT key, release it and then press CONTROL SHIFT TAB. 2. You will land on a "Copy" button and can now TAB through the other facilities in the Clipboard toolbar. 3. The "Copy" option is likely to be unavailable if nothing has been highlighted at present. It works the same as the CONTROL C command. 4. TABBING or ARROWING takes you to "Clear Clipboard", which removes everything from the Clipboard and must be done if you already have material in the Clipboard. "Paste All" pastes all of the contents into your document in the order it is held in the Clipboard from the cursor position. 5. With the Clipboard cleared (empty) To cut or copy several blocks of text to the Clipboard and then paste them into your worksheet simultaneously in one move you would: A. Highlight and cut (CONTROL X) or copy (CONTROL C) several blocks of text or figures to the Clipboard in the order you want them to be pasted in. B. Move the cursor to the place in your current worksheet or in another worksheet or document in another open window where you wish the several Clipboard contents to be pasted. C. Press ALT, then CONTROL SHIFT TAB and then TAB to "Paste All" and press ENTER to finish, when you will return to your document. D. Leave the Clipboard toolbar at any time by pressing ESCAPE. E. After carrying out a multiple paste in this way, remember to turn off the Clipboard toolbar with ALT V, T, and press ENTER on "Clipboard" to uncheck it. 16.4. The Excel 2002 and 2003 Multiple Clipboards The Excel 2002 and 2003 (or Office) Clipboard has evolved since that featured in Word 2000. It is more powerful and can be both turned on and hidden from the screen at the same time if you wish. It can also accept 24 copying or cutting commands before it is full rather than the 12 which is the Excel2000 Clipboard maximum. For more general information about the Excel multiple cut, copy and paste Clipboard, see the last section. Each time you launch Excel 2002 and 2003, if you wish to use the Clipboard, you must activate it to be able to view, empty, cut or copy its contents plus modify its options by pressing ALT E and then B, followed by pressing ESCAPE. You now view and use the Clipboard as follows. With the Clipboard cleared (empty) To cut or copy several blocks of text to the Clipboard and then paste them into your worksheet simultaneously in one move you would: 1. Highlight and cut (CONTROL X) or copy (CONTROL C) several blocks of text or figures to the Clipboard in the order you want them to be pasted in up to 24 times. 2. Move the cursor to the place in your current worksheet or in another worksheet or document in another open window where you wish the several Clipboard contents to be pasted. 3. Now press ALT E and then B to activate the Clipboard if you have not already turned it on or use the shortcut of CONTROL C twice to achieve this. You can move between the Clipboard task pane and the document pane by pressing F6 or ESCAPE and F6. 4. Move the cursor to the place in your current document or in another document in another open window (in any Office program including Word, Excel, Powerpoint and Access) where you wish the several Clipboard contents to be pasted. 5. Press F6 back to the Clipboard pane and then TAB to and press the ENTER key on or double click your mouse left click simulation key (next to the NumPad on/off key) on the "Paste All" button to finish, when you will return to your document. If you do not, just press F6 or ESCAPE to return to it. 6. The contents of the Clipboard will have been pasted into your document at the cursor point and the Clipboard contents will be retained on the Clipboard until you either clear the Clipboard, turn your computer off or until you start to make cuts or copies in excess of 24, when they will start to overwrite one another, e.g. the 25th copy will overwrite the 1st. 7. In the clipboard pane you will find several buttons you can TAB through and press ENTER or click on, such as "Options", "Paste All", "Clear", etc. The "Options" button, if activated by pressing ENTER on it, allows you to ARROW to and leave focus on any one of four buttons to turn on or off several views of the Clipboard, including "Collect Without Showing Office Clipboard" but these are likely to make little difference to a non-monitor user's use of the multiple Clipboard. If you can see sufficiently to make use of one or several of these option views, experiment with them to find your preference. Note: If you wished only to cut or copy one of the several Clipboard individual contents to a document, you can do this but your screenreader may not be able to "see" each individual piece of data in the Clipboard list, so you may either have to remember which of the up to 24 different copies or cuts you made to be able to ARROW down to the one you want or you may find it simpler just to use the standard Windows one cut or copy Clipboard for this. However, you should be able to view the list of separate cuts or copies held on the multiple Clipboard in mouse mode. If you do want to highlight one of these for pasting, you will have to ARROW to it in the Clipboard task pane, press the SPACEBAR to highlight it and then press your screenreader's left mouse key to effect the pasting. The Clipboard contents list is one TAB press after the "Clear All" button. ******** 17.1. More about Highlighting--The General Windows Way As already stated in Section 9, "highlighting" (also known as selecting) is where you mark a cell or block of cells in order to perform one of a number of commands on them, e.g. to delete data in them simultaneously, to move them, to copy them elsewhere, to print them, to perform a calculation on them, etc. You can, as demonstrated in Section 9, highlight using the unique Excel methods to do this or you can achieve this using the general Windows way of highlighting with the SHIFT key, as follows: 1. When you move to a cell other than A1, that cell is automatically highlighted. If you are highlighting from cell A1, press SPACEBAR in A1 before starting to highlight the rest of the cell range required, e.g. by then keeping the SHIFT key depressed and ARROWING in the required direction to the last cell in the range you want to have selected. 2. If you wish to highlight the whole column your cursor is currently in, you would press CONTROL SPACEBAR. 3. to highlight the whole row the cursor is in, you press SHIFT SPACEBAR. 4. To highlight the block of text from cell B1 to E10, you would place the cursor in B1 and then ARROW right to E1 and down to E10 keeping the SHIFT key depressed all of the time. 5. In order to highlight the whole of a worksheet, you press CONTROL A, but this will highlight all cells including those with no data in them, i.e. 256 columns by 65,536 rows, which is not something you would normally want to do. 6. To highlight the full data area of your worksheet and not unwanted cells to the right and underneath the data region, with the cursor anywhere in the data area of the sheet, press CONTROL SHIFT 8. If this fails to highlight any portion of your worksheet, try the below method instead. 7. To highlight a portion of a sheet from the cursor position down to the end of the data in the sheet, press END followed by CONTROL SHIFT END. 8. To highlight the rest of the data area from the cursor position to the right of the sheet, press END and then CONTROL SHIFT right ARROW. 9. Remember, any of the standard Windows movement shortcuts which you carry out in conjunction with the CONTROL key can also be used to highlight sections of data if you also keep the SHIFT key depressed whilst moving over the data or cells. 10. If you wish to remove the highlighting from a block, you just press any of the ARROW keys once in any direction. Note: Sometimes highlighting fails when working from the top left of a block of cells right and downwards. You may get better results working back wards from bottom right to top left. Alternatively, I have found jumping to the top left cell to be highlighted with the CONTROL G shortcut, e.g. CONTROL G, then type "A1" and press ENTER, succeeds in getting the first cell in your block highlighted and you can then proceed as normal highlighting to the right and down as you go. After highlighting blocks of cells with data, numeric values and dates/times,etc, in this way, you could effect any of the formatting, printing, copying, etc, commands on it listed in 1 to 9 at the end of Section 9 above. Try some of these things on the fishandchips.xls file you created earlier but do not save these changes. If you make a change which you then decide you do not want, you can press CONTROL Z to undo that and up to 15 other changes, i.e. put things back to how they were before the change. If, before carrying out a command on a highlighted block of cells, you would like to ratify that you have indeed selected the correct cell range, you should use your screenreader's say highlight hot key to do this, e.g. SHIFT INSERT down ARROW with JAWS, ALT M with WE and Numpad 1 with HAL but note that these read highlighted hot keys do not always work in Excel.. ******** >>>>Check that the below does work. Kayvan says it doesn't because when you move back to sheet one, the highlighting is lost?>> 18.1. Highlighting Several Worksheets on which to Simultaneously Effect Changes If you highlight two or more consecutive or non-consecutive Worksheets and make a change in the active worksheet, the change will be passed through all of the highlighted sheets. This can help you to amend several related worksheets quickly. For instance: 1. Open a workbook and type 100 in cell A1, sheet1. 2. move to worksheet two with CONTROL PAGE down and enter 200 in cell A1. 3. Open a third sheet and in A1 type 300. 4. With your cursor in A1, CONTROL PAGE up twice to sheet 1 and highlight all sheets by pressing CONTROL SHIFT PAGE down twice. 5. Next move back to sheet 1 and press F2 to edit the cell A1 contents to make it read 5000 and press ENTER. 6. Now move to sheets 2 and 3 and observe the result. 5000 will have been passed through all sheets replacing the previous contents. ******** 19.1. Spell- and Formula-Checking Your Worksheets How much information you obtain whilst spell-checking will depend on which screenreader you possess. If your screenreader does not automatically tell you which word is wrongly spelled and read out the suggested replacements, you should configure it to read out any colour change which occurs in editfields, as Excel highlights what it believes to be the misspelled word in a different colour (usually red). You will thereby be able to identify the offending word. 19.2. Spell-Checking the Text in Your Worksheets To spell-check a document: 1. Press F7 to invoke the spell-checker. The spell-checker will stop on the first word in the current worksheet it finds and believes to be wrongly spelled. 2. You should press the TAB key once to move to the list of suggested word replacements which you are offered. If there is more than one suggested replacement, you can ARROW down them and put the focus on the one you want and press ENTER to accept it. 3. The spell-checker then moves onto the next word it thinks is wrongly spelled. 4. If Excel does not have any suggested replacement words for a wrongly spelled word, you can backspace the word out and type it back in correctly or edit it to produce a correct spelling. You then press ALT C to change the word to the newly spelled one. Within the spell-checker dialogue box you can use several shortcuts, as follows: Use ALT C to change a word to the first suggestion in the replacement list. Use ALT I to ignore the first instance of the spell-checker's suggestion that a word may be spelled wrongly. Use ALT G to ignore all instances of a word possibly being spelled wrongly if you know it is spelled correctly--the spell- checker does not know every word in the English language and will regularly think that acronyms and proper nouns are incorrect spellings. Use ALT L to change all instances of this same wrongly spelled word in the worksheet. Use ALT A to add the word to Excel's custom dictionary if you know that it is spelled correctly but Excel does not. Use ALT R to autocorrect wrongly spelled words for you, i.e. words which you choose this option on will automatically be corrected for you as you type them, such as words which you frequently inadvertently type wrongly. Note that if you would like to fine tune your autocorrect options or delete any word in the autocorrect list you can do so within the Tools, AutoCorrect Options feature. 19.3. Formula Error-checking in Excel XP Excel 2002 and 2003 have a formula syntax error checker, like a grammar checker, which you can run to be advised of how better to enter formulae if your formula constructions are not as correct as they could be or if your software is not set up to deal with a particular calculation requirement. With a work sheet loaded containing formulae, you start the checker by pressing ALT T (for Tools) and then by ARROWING down to "Error checking" and pressing ENTER. For example: 1. Type the figure 6 in cell A2 and ARROW down to A3. 2. Now try to do one of the Analysis Toolpack measurement conversion calculations on the figure 6 without the Toolpack add-in being installed. 3. In A3 type the formula: =CONVERT(A2,"TSP","TBS") and press ENTER. This should convert 6 teaspoons into its equivalent in tablespoons but . . . 4. You will get the ""#name?" error message indicating that Excel does not understand this and thinks it to be an error in formula or function entry syntax or a function it is not yet set up to deal with. 5. Now see what the formula error checker can do for you by pressing ALT T (for Tools) and then ARROWING down to "Error Checking" and pressing ENTER. 6. The checker will find the function in cell A3 and tell you that it is incorrect or unrecognised. If you then press ALT H (for Help) and wait a few seconds, you will be taken into a relevant help page to TAB and/or ARROW through to find the possible reasons for the error in formula or function syntax. 7. One of the reasons in here is that you may not yet have installed the "Analysis Toolpack" add-in and, indeed, you cannot use the "CONVERT" function without installing this. You will be told how to install it. 8. Do nothing about this now. Just ALT F4 out of help and clear the worksheet and carry on with the next exercise. some of the add-ins, including the Analysis Toolpack, will be covered later. Note 1: If you do not get an error message when you perform the above exercise but instead do receive the converted teaspoons/tablespoons result, then you already have the Toolpack installed. The next time you get an error message, use the error checker as above to experience this exercise. Note 2: If you only want to spell or formula-check a single cell, you can do this if you press F2 whilst in the cell and then proceed with the checking sequence as normal. ******** 20.1. Creating a Multi Page Workbook If you require more than one worksheet in your workbook, you can open up to three pages by pressing CONTROL PAGE down. Each sheet will be empty for you to complete as normal. If you need even more sheets, press SHIFT F11 to generate them, then use CONTROL PAGE up and down to move through them. When you save the workbook under a single filename, all worksheets will save together under this single filename and be retrieved simultaneously when you later open the saved workbook. You may wish to create several sheets with the same top and side headings and similar data to save in one workbook. One way you could do this is by opening the required number of worksheets, highlighting the whole of worksheet one with the data and formulae in it and then copy it to the Clipboard (CONTROL C), followed by pasting it to your other sheets (CONTROL V) and then making any necessary minor alterations in the second, third, etc, sheets before resaving the workbook. If you already have a workbook with several sheets in it and need to insert a new blank worksheet in-between the existing sheets, move to the sheet which you wish to have moved down one and then either press the shortcut of SHIFT F11 to insert it or use the Insert menu to do this with the "Worksheet" command. ******** 21.1. Working with Several Workbooks open at Once You can have several independent workbooks open at once in their own separate document windows and cycle through them by pressing CONTROL F6. SHIFT CONTROL F6 moves you backwards through them. You would have opened your several workbooks by retrieving already created ones with CONTROL O or by generating new blank workbooks by pressing CONTROL N to open each. You might want to do this to compare spreadsheets or to highlight a range of cells in one of them, press CONTROL C to copy it to the Clipboard and then press CONTROL F6 to your second open workbook, followed by pasting the block into the other at the appropriate place with CONTROL V. Try this by opening a new workbook with CONTROL N, type a few details into a few of the cells, then open another workbook with CONTROL N again and type a few details into a few of these cells as well and then open the workbook you have already created and saved. So, with CONTROL O, open fishandchips.xls. You will now have three workbooks open in three separate document windows. Practise cycling through them using CONTROL F6 and perhaps copy a few blocks of cells from one to another if you wish. Then close one of them with CONTROL F4 or ALT f, C, and cycle through the other two to verify that you have now only got two windows with workbooks in them. Use your screenreader's read Title Bar hot key to verify which document window/worksheet has focus at any given time, e.g. INSERT T with JAWS, CONTROL SHIFT T with WE, Numpad 7 with HAL. Lastly, close the other two windows and do not save any of their contents if asked to, just leave them as they were. To close an open Workbook window, press CONTROL F4 when it has focus. If you have not already saved the workbook or resaved it after a modification, you will be asked if you wish to save it and may have to provide a filename before pressing Y (for Yes) to do this; otherwise, press N (for No) to close without saving or resaving. 21.2. Closing Several Open Workbooks Simultaneously If you have several workbooks open at once, you can change the "Close" option on the File menu to a "Close All" option by pressing SHIFT ALT F instead of ALT F or CONTROL F4 to close them individually. Do this by: 1. With more than one workbook open when you want to close them all at once, hold the SHIFT key down and then press ALT F (for File) and then C (for Close All". If you have already saved all workbooks, they will simply all close simultaneously at this stage. 2. If you have not saved these workbooks already or have modified them since last saving them, you will now encounter a save dialogue box which is slightly different from the standard save dialogue. Your choices are: A. You will be on a "Yes" button to save one of your workbooks and if you press ENTER you can then type in a filename and press ENTER to save it. You will then be asked for a filename for the next workbook, etc, until all workbooks have been saved. B. If you TAB once from the above "Yes" button to a "Yes to All" button and press ENTER, you will be able to provide a filename for the first workbook and press ENTER to save it, followed by immediately being asked for a filename for your second workbook without having to go through the intermediate step of pressing ENTER on "Yes" for each workbook which needs saving. 3. Lastly, either shut down Excel with ALT f4 or continue working in Excel by either opening a new blank workbook with CONTROL N or an existing workbook with CONTROL O. ******** 22.1. Changing the Column Width and Row Height of Your Worksheets The default (standard) width of an Excel column is 8 characters. This can be changed to make it as wide as 255 characters. The default depth or hight of a row is 12.75 points (there are 72 points to an inch). This can be altered to make it as deep as 400 points. However, if you choose a font type and size which is larger than the default height of a row, the height of the row will automatically be adjusted. 22.2. Columns To change a column's width so that it can display the longest cell entry in it or the column heading if this is the longest entry: 1. With the cursor in row 1 of the column you wish to change, press ALT O (for format) and then C (for Column). 2. Now press ENTER on the "Width" button which you will be on and the current column width will be displayed. 3. You can type over this figure with the column width you require, e.g. type 20 to obtain a column which will take 20 characters of length in it. 4. Press ENTER to finish. 5. Alternatively, at step 2 above, you could have TABBED once to "AutoFit Selection" and pressed ENTER to get Excel to automatically change the size of the column to accommodate the longest entry in a column which has already been completed with data up to a maximum of 255 characters. You should have your cursor in the cell which contains the longest line when you do this. 6. If you keep on TABBING past "Width" and "Autofit selection" you will find some other features, e.g. "Hide" and if you press ENTER on this the whole column you are currently in will disappear from the worksheet, perhaps for security or confidentiality reasons, although it will still exist with its data and can be unhidden. Whilst in the column you have just widened, check the results of your work with your screenreader's special attributes hot key if it supports one, e.g. INSERT F with JAWS, ALT T with WE and Numpad 4 with HAL. 22.3. Rows To alter rows: 1. Place the cursor in the row you wish to change the hight of and press ALT O (for format) and then R (for Rows). 2. Now press ENTER on "Height" and follow the same steps as in 2 to 6 in the last sub-section. Of course, in this case you will be typing the number of points you want the height of the row to be, which can be as deep as 400 points, i.e. up to 5.5 inches deep. Otherwise, with your cursor in the deepest cell, choose "Autofit Selection" to let Excel choose the height according to the highest/deepest already completed cell in the row. 22.4. Workbook Exercise 2--Altering Column widths, using Formulae for Averaging and copying formulae Down a Column Open the fishandchips.xls workbook you created earlier and practise changing column widths and also how to copy or fill cells down a column by moving your cursor to cell E1 and creating a new column for the spreadsheet. 1. Launch Excel and you will automatically be in the first worksheet ready to type data in. However, as we wish to reload an already existing workbook, you should first close the current blank sheet by pressing ALT F (for File(, then C (for Close). 2. Retrieve "fishandchips" by pressing CONTROL O and typing its filename into the editfield and pressing ENTER. 3. Type in E1 the heading "Average Units Sold". 4. then in cell E2 enter the formula "=AVERAGE(B2:D2)" )" and press ENTER. You should obtain the average combined units of fish, chips and rolls sold for Monday as 100. 5. Next copy this formula down the rest of the column to E6 by placing the cursor in E2 (with the formula in it) and then highlighting this and all cells down to E6 by holding the SHIFT key down and ARROWING to E6. 6. Now finish the exercise by pressing the fill down shortcut of CONTROL D. View the results and the fact that some entries will have figures after a decimal point. remember, if you are to the left of a row total and need to know what that total is but do not want to have to go to it to find out, use your screenreader's read row total hot key, e.g. INSERT DELETE in JAWS. 7. Save your changed workbook to a slightly different filename than the original, so that you will now have two different workbooks under separate names. Do this by pressing ALT F (for File) and then A (for Save As) and type in the filename "fishandchips2" and press ENTER to save. Note 1: In step 6 you can also use the long way to do this via the ALT E (for Edit) menu and then press I (for Fill). In here you can ARROW through several ways of filling/copying data and formulae across columns and rows and through several sheets, etc. Some of these commands do not have a shortcut alternative like fill down does. Note 2: Whilst the fish and chips worksheet only has heading labels around the top and left edges, in reality many spreadsheets will also have label titles internally as well with data to their right or underneath them. ******** 23.1. Automatically Formatting a Worksheet or Pivot Table Report Excel has an "AutoFormat" feature, so that if you use this Excel will do the formatting of a worksheet with such as formulae, cell colours, fonts, borders, etc, already typed into it, instead of you having to do the formatting manually. In other words, it will resize columns and rows to the largest data in them, put suitable spaces or border lines around your cells, and even place things like eye-catching 3D effects into your spreadsheets if you wish. It uses a built-in combination of format features to do this. What you do is: 1. Create your worksheet with all required data and formulae or open one which has already been created. 2. Either highlight the whole of the worksheet data area or pivot table report or highlight the portion of the data only which you want to have formatted. 3. Press ALT O (for Format) and then A (for AutoFormat). 4. The default formatting style is "Simple" which provides an uncomplicated, straightforwardly formatted worksheet, e.g. with emboldened and centred headings, resized columns and rows, normal numbering style and numeric data aligned right as normal. If this is suitable for your needs, just press ENTER to complete the formatting. 5. If you would like a specific type of formatting, you can ARROW down the above formatting list from the Simple option to the one you would like, e.g. Accounting, 3D Effects, List, etc. 6. If you choose a format other than "Simple", you can then TAB to "Options" and press ENTER. With some formatting options but not all, this will reveal six formatting features which will be turned on, e.g. number, width/hight, alignment, etc. You can leave all of these on or press SPACEBAR on any of them to turn that particular formatting option off, when the formatting will be done with the remaining format options but not the turned off ones. 7. In reality, there is a snapshot image on the screen of what your worksheet would look like with each formatting example and option but, of course, your screenreader will be unable to give you feedback on this. 8. After effecting any format changes, save your worksheet with CONTROL S or ALT F, A. Warning: Unless you have dozens of columns and thousands of rows in your worksheet, do not highlight the whole sheet with CONTROL A because this makes Excel format every column and row in the sheet totalling hundreds of thousands in all which will certainly take several minutes and could take much longer on slower computers. ******** 24.1. Manually Formatting Cells You can yourself apply many different formatting choices to a whole spreadsheet or just to a selection of highlighted cells rather than using AutoFormat or after using AutoFormat, e.g. apply a coloured background to just the headings of your columns. To obtain an idea of what you might do, try some of the below numbered steps, each of which has been given its own sub-heading: 1. Press ALT O (for Format) and then E (for Cells). Alternatively, remember that you can use the Excel shortcut of CONTROL 1 to open this dialogue box. 2. Note that you come into a multipage set of property sheets with titles of 'Border", "Patterns", "Protection", "Number", "Alignment" and "Font". CONTROL TAB through these and have a good look at the many options by TABBING through them. 24.2. Selecting a Border Style 3. For example, the first option in the "Border" sheet is "Border Style" and the default type of border is "Thin Continuous", which provides a border with thin lines which do not break, as opposed to dotted lines, thick lines, broken lines of hyphens, etc. You can opt for another 13 different border types if you wish by ARROWING up or down this list and leaving focus on the one you would like. 4. Keep TABBING through the border sheet and note that by ARROWING down on some of the radio buttons you can change things like the colour of your border grid lines or leave them on "Auto" to let Excel choose a colour matching the rest of your worksheet colours; with "preset None" you can remove border lines from highlighted cells; with "Preset Outline" you can place the type of border you would like around highlighted cells only; and there are several more border options. When on a border (or any other option of this type), press SHIFT f1 to obtain context-sensitive help on that particular option (pressing ESCAPE closes this help). Now CONTROL TAB to the "Font" property sheet. 24.3. Selecting a Font Type and Size 5. In the "Font" sheet you can change the font type to bold, italic, etc, its size to make it smaller or larger, etc, as normal in Microsoft programs. 24.4. Selecting a Worksheet Background Pattern or Shading Colour 6. In the "Patterns" sheet you can choose a background pattern or shading colour for all or just some of your cells. 24.5. Opting to Protect or Hide Blocks of Information 7. In the "Protection" property sheet you can lock a selection of cells so that no one can modify them and you can hide highlighted cells and formulae so that they cannot be viewed by casual observers. You might also wish to hide rows or columns in order to print out only part of a worksheet or you may find it easier to enter or view data after first temporarily hiding some rows or columns. You can then use the "Unhide" option to make them viewable again later. 24.6. Selecting the Type of Numbering and Currency Symbol System to Use 8. In the "Number" sheet you can TAB to "Category" and view that "General" is the default used but you can ARROW down this list to select something else if you like, such as "Number" to have numbers given with decimal points or "Currency" so that decimal points and pound signs are displayed, etc. Note that when you elect to have decimal places rounded up, whilst the foreground display will indicate only the two decimal places, the figure held by Excel in its calculation database will be the longer figure typed in so that any calculations will be true to the accurate figure and not the rounded up figure, e.g. if 3.007 is rounded up to 3.01, it is 3.007 which any ongoing calculations using this cell will be done on. 24.7. Selecting the Way You Would Like Text and Values Aligned 9. In the "Alignment" sheet you can change the way data is aligned in a cell from its norm of "General", i.e. text being left aligned and figures being right aligned. So you could choose, for instance, centred, right aligned, centred across a selection of several cells, etc. In "Vertical" you can have data showing in your cells with the first line at the top of the cell, at the bottom of the cell or centred in the middle of the cell. This assumes a cell which has been made more than the default one line deep, of course. If you press SPACEBAR on "Wrap Text" it will wrap (automatically go to the next line) as soon as it reaches the right-hand border of the cell it is being typed in. You might want to enable wrapping if you are creating a spreadsheet consisting of mainly blocks of text in their own individual cells like a table of information. There are several shortcuts you could also use to change the alignment of a whole worksheet or just a range of highlighted cells: Press CONTROL L: To left align. Press CONTROL R: to right align. Press CONTROL E: to centre. Press CONTROL J: to justify. 10. After making any changes you would like for your current session, TAB to "OK" and press ENTER, then create and save your worksheets. 11. There are many other cell attribute changes in these property sheets so experiment with them and keep using SHIFT F1 to obtain a short description of what each option does. For more detailed information, use the excel online help system as described below in "Excel Help". Note 1: These attributes will only hold for the current workbook. If you save and close the workbook and then open want to create another with CONTROL N, the default formatting attributes will again apply. If you would like to use your new formatting attributes time and again, you could always save these as a template for reloading later (see "Creating and Retrieving Custom Templates" below). Note 2: Remember, Excel has many shortcuts to achieve the results shown above without you having to go into the Format menu, e.g. CONTROL 0 to hide a column, CONTROL SHIFT ) to unhide a column, CONTROL 9 to hide a row, CONTROL SHIFT ( to unhide a row, CONTROL & to generate an outline border, CONTROL _ to remove borders, etc. do not forget, either, that you can always highlight a series of cells and have a given attribute applied to them all at once, e.g. if you want 10 headings all to be emboldened from cell A1 to J1, just go to A1, press the END key followed by SHIFT and right ARROW and then press CONTROL B to embolden all 10 headings simultaneously. You may find that several of these shortcuts clash with some of your screenreader's own special hot keys, so, if this happens, either use the menu method of doing things or use your screenreader's bypass hot key before using the Excel shortcut, e.g. INSERT B with Window-Eyes, INSERT 3 with JAWS and CONTROL NumPad 7 with HAL. 24.8. Saving a Cell's Formatting Choices to a Style Sheet for Use in Other Excel Sessions If you have inserted several formatting characteristics into a particular single cell, such as text alignment, font type and size, foreground and background colours, etc, and you would like to save these attributes, you can do this to a "style" sheet and then retrieve it the next time you want to use these same formatting attributes in a cell. You would: 27.8.1. Creating and Saving a Cell Formatting Style 1. Either create the formatting you want or select a cell which already has your desired formatting preferences in it. 2. Press Alt O (for Format) and then S (For Style). 3. In the Editfield you will be in, type the name you would like to give to the style so that you can use this name to retrieve it the next time you want to use it, e.g. big text. 4. If you keep pressing the TAB key, you will come across several buttons checked on by default. These are for such as font, alignment, etc, and will ensure that all of these attributes are saved, unless you check any of them off by pressing the SPACEBAR on it, when all other attributes will save but not the checked off ones. 5. TAB to "Add" and press ENTER to save the new style and then TAB to "Close" or "OK" to finish. 24.8.2. Retrieving a Cell Formatting Style for Use You can quickly retrieve your above style and get it applied to the cell you are currently in or to a range of cells you will have pre-highlighted by: 1. Highlight your cell or cells to apply the style to. 2. Press ALT O (for Format) and then S (for Style). 3. You will fall in a list of the styles you have already created plus many other styles which Excel already has in-built. ARROW to your style name and press ENTER to apply it. Note that before you press ENTER you could have TABBED around several buttons to modify your style or get it deleted, etc, if you wanted to do this. 5. Now just complete your cell or cell range as normal with your required data. ******** 25.1. Editing a Single Cell You can use the F2 editing key to do this: 1. Move to a cell and type "John" in it. 2. Now, with the cursor still in that cell, press the F2 key and note that you will be at the right-hand side of the word John. 3. Press left ARROW as far as you can until you are on the J and then press the SPACEBAR to move the word to the right to where you want it. 4. If you have not changed the default number of characters which can fit across a cell, you would only have to press SPACEBAR twice to centre the word John, as the cell will only hold 8 characters by default. 5. If you wanted to correct a mistake in the word, you could just go over one of the letters in the word and then type in another letter, which would move the rest of the letters to the right, or you could press BACKSPACE to erase the letter to the left of the letter you are currently over or press DELETE to erase the letter to the right. 6. This same editing method can be used to correct or modify formulae and dates. 7. When you have finished, press ENTER to confirm the changes and leave editing mode. ******** 26.1. Clearing and Deleting Cells, Rows, Columns and Worksheets When you clear a column or row of data, the data, text, comments and formulae are removed but the row or column remains in place blank. In contrast, when you delete a row or column, the data is erased but the column or row is also closed up and removed. 26.2. Clearing 1. To clear a row or column, place your cursor in that row or column and highlight it with either SHIFT SPACEBAR or CONTROL SPACEBAR respectively. 2. Press ALT E (for Edit) and then A (for Clear). 3. If you press ENTER on "All" the whole row will be cleared including everything in all cells. 4. You can also ARROW to "Formats" to only have formatting in cells removed, "Clear" to clear the data in the selection but not formatting, comments, etc, or "Comments" to have only comments in cells removed. 26.3. Deleting 1. Highlight the cell, row, column or range you wish to remove. 2. Press ALT E, then D to remove the data in the cells and move any lower data up to close the gap or any data on the right to the left to fill the gap. To delete an unwanted worksheet from the workbook, with the sheet on screen, press ALT E (for Edit) and then L (for Delete Sheet) and then press ENTER on the "OK" button which is displayed. Remember, if you accidentally remove or delete the wrong column or row, you can undo your action by pressing CONTROL Z. ******** 27.1. Changing Worksheet Borders As previously mentioned, the standard border which Excel places around your worksheets is a thin continuous line. You can either remove this (but borders and lines do generally make data easier to read( or change it to make things look more professional or just to make certain areas of your grid stand out more. For instance: 27.2. Workbook Exercise 3--Creating Borders 27.3. Double Border around the Whole Worksheet 1. Open the "fishandchips" workbook. 2. Go to A1 and Highlight the whole sheet data area by pressing END then CONTROL SHIFT END. 3. Press ALT O (for Format), E (for Cells) and CONTROL TAB to "Borders". 4. TAB to "Border Style" and ARROW to Thick Double". 5. Next TAB to "Preset Outline" and press SPACEBAR. 6. Now SHIFT TAB to "OK" and press ENTER to finish. If your screenreader has a report borders hot key, such as ALT SHIFT B in JAWS, check the result. If you are in cell A1 you will be told the top and left borders are thick double and the inside borders are think grid lines, so to check that the double thick lines are all around the worksheet go to each corner cell and ask your screenreader for the information. 27.4. Single line Under a Row of Cells 1. Again with the "fishandchips" workbook, highlight the column heading cells A1 to D1. 2. Press ALT O, E, and CONTROL TAB to the "Borders" property sheet. 3. TAB to "Border Style" and then ARROW to "Medium continuous". 4. Now TAB to "Bottom Border" and press SPACEBAR. 5. Lastly, TAB to "OK" and press ENTER to finish. Your column headings will now all be underlined and separated from the rest of the data. 6. Save the workbook under the same filename by pressing CONTROL S. Note: in the Borders property sheet there is also a "Preset None" button which, if you press SPACEBAR on it, will remove borders from a worksheet or a highlighted range of cells. ******** 28.1. Naming Cells, Naming and Renaming and Hiding Worksheets and Workbooks In addition to naming/renaming worksheets and hiding ranges of cells, columns and rows, you can hide from others' view whole sheets or whole workbooks. 28.2. Naming a Worksheet The default names Excel gives to worksheets is in the order of "Sheet1", "Sheet2", etc. If you have several sheets in a workbook, you are likely to wish to give each sheet its own unique name, although this is not essential. To name a worksheet: 1. Clear the open spreadsheet grid currently on screen with ALT F, C. 2. Open the "fishandchips" workbook. 3. You should be in "Sheet1", so to give it your own name press ALT O (for Format). 4. Next press H (for Worksheet" follow by R (for Rename). 5. You will come into an editfield, so type your desired worksheet name in here, e.g. "Fish1", and press ENTER. 6. Press CONTROL PAGE down to move to "Sheet2" and, despite the fact that this sheet has nothing on it at present, give it a name for the sake of practising this. 7. With "Sheet2" on screen, again press ALT O, H, and then R, and this time enter the sheet name "Fish2" and press ENTER. 8. You can now move from worksheet to worksheet and observe the sheet names by pressing CONTROL PAGE up and PAGE down. 28.3. Naming Individual Cells or Blocks of Cells 28.3.1. Naming Cells With small spreadsheets the naming of cells is rarely necessary. However, you may find naming certain key cells or ranges of cells useful within a large and complicated worksheet to allow you to check where you are and to redefine other related cells. By naming a cell you are replacing the cell's reference co-ordinate name with another more obvious name, e.g. the cell name "C20" with such as "Total Deposits". This means that the contents of your Formula Bar will change to reflect this. Your Formula Bar would now read such as "TotalDeposits =SUM(C2:c19" instead of its default contents of "C20 =SUM(C2:C19). So, for instance, if you have a balance sheet of your bank account transactions and C20 contained the formula for your total deposits and D20 contained the formula for your total withdrawals, you could move to C20 and: 1. Press ALT I (for Insert) and then N (for Name), followed by D (for Define). You could also get to this point with the shortcut of CONTROL F3. 2. In the "Names in Workbook"editfield you come into type in the name you wish to give to the cell, e.g. "TotalDeposits" and press ENTER to finish. Note that, whilst you can supply a name of up to 255 characters, you cannot include spaces, so type your name as one word only. Also be aware that, if the current cell already contains text, then the "Names in Workbook" list will have had that text automatically inserted as the name you may wish to use but you can delete this and type another in if you wish. 3. In the above names list, if previous names have been allocated in this worksheet you can reallocate any of these to another cell or block of cells if you wish. 4. If you would now like to name your D20 cell something like "TotalWithdrawals" just repeat the above procedure. 28.3.2. Changing Cell References in Formulae to Names Having already allocated names instead of cell references to the appropriate cells you wish to get the formula substituted in with a textual name (as shown above), if you would like to substitute all or part of a standard formula in a cell (viewed in the Formula Bar), such as "C20-D20" with a textual name, such as "Deposits-withdrawals",you can do this by: 1. Do not highlight anything. With your cursor in the cell with the calculation formula in it which you wish to change to names rather than references, Press ALT I, then F, followed by A (for Apply). 2. In the list of cell reference names you come into, select (by pressing SPACEBAR on them) each cell name you wish to substitute. This could just be one part of the cell formula you are currently in or all of the elements of the formula. Note that this part can be tricky, as some screenreaders cannot "see" the already named cell references in this list. If you experience this, you should be at the top of the list, so just pressing SPACEBAR will select the first cell name and you should then hear its name spoken. Now ARROW down once to your next cell name and again press SPACEBAR to select the next cell name, etc. If you accidentally select an unwanted cell name, pressing SPACEBAR again should unselect it. 3. TAB to "OK" and press ENTER to achieve the substitution. In future when you land on this cell and then look at the Formula Bar to see what formula it contains, you will see the cell textual names instead of the cell numeric formula, e.g. "Deposits-Withdrawals" instead of "C20-D20", which may make the purpose of that cell/formula clearer to comprehend rather than seeing the cell references and formulae. Note: depending on the screenreader you are using, you may be able to hear the contents of the Formula Bar and these names or you may have to go into mouse or navigation/virtual cursor mode to do this, e.g. CONTROL F2 is the hot key to get the Formula Bar read out in JAWS and left SHIFT 7 in HAL. 28.3.3. Using Goto to Jump to Named Cells and Cell References You can use the Goto feature to jump directly to a cell by indicating its column and row reference co-ordinates or its cell name if you have replaced its reference with a cell name. Do this by: 1. With your cursor anywhere in the worksheet, press CONTROL G to open the Goto dialogue. 2. In the editfield you will now be in, either: A. Type the cell reference you want to go to, e.g. c7. B. Type the cell name you want to go to, e.g. "Total Deposits". 3. Press ENTER to be taken straight there. 28.4. Hiding Worksheets To hide a single sheet or several sheets, so that others cannot view them if they contain confidential or sensitive data: 1. Highlight the sheet(s) and then Press ALT O (for Format(. 2. Then press H (for Sheet) and ARROW to what you want to do, e.g. hide, unhide, rename the sheet(s), etc. 28.5. Hiding Workbooks 1. With the workbook open, press ALT W (for Window). 2. Then press H (for Hide). 3. To unhide a workbook, press ALT W, then u, and select the workbook you wish to unhide from the list. ******** 29.1. Creating, formatting and Totalling an Invoice to Include VAT 29.2. Workbook Exercise 4--Calculating VAT If you want to get more practise completing worksheets, try the below simple invoice example. After completing the cells, then format them and turn decimal point format to 2 places on in column E. 1. Open a new blank worksheet if an empty one is not already on screen with CONTROL N. 2. To turn on 2 decimal point format for column D, highlight the whole of column E with CONTROL SPACEBAR wen you have the cursor in that column, then press CONTROL SHIFT ! (which works in some but not all versions of Excel) or you can also do this via the Format menu, Cells, Number property sheet, by ARROWING in the list to "Number" and pressing ENTER. You could, of course, also elect to type your figures in column D as normal (but note that any .00 after integers will not appear) or, as another alternative, you could highlight column E afterwards and then invoke the two decimal places feature. Note that you could also have elected to select the "Currency" option in the above Number property sheet if you wanted two place decimals but this time with a currency sign. 3. In order to enter an invoice title which does not create underneath it an over wide column for the rest of the text, merge cells A1 and B1 together to take the invoice title. Do this by: A. Highlight cells A1 and B1 and then press ALT O (for format) and E (for Cells). B. CONTROL TAB to the "Alignment" sheet and then TAB down to "Merge Cells" and check it on by pressing SPACEBAR. C. TAB to "OK" and press ENTER. 4. Now type, in capital letters, "January Invoice" into the combined A1-B1 merged cell and press ENTER. This worksheet title is all that will appear in row 1. 5. In cell A2 type the heading "Customer" and press ENTER. 6. In A3 type "J Wilson. 7. In A4 type "D Smith". 8. In A5 type "F jones". 9. In B2 type the heading "Part No". 10. In B3 enter 100/C. 11. In B4 type "299/D. 12. In B5 enter "144/B. 13. In C2 type the heading "Quantity". 14. In C3 enter "5". 15. In C4 type "9". 16. In C5 enter "11". 17. In D2 type the heading "Unit Cost". 18. In D3 enter "15.00". 19. In D4 type "7.50. 20. In D5 type "21.00". 21. In E2 enter the heading "Total". 22. In E3 type "75.00" manually but, of course, you could have used the formula: =sum(C3:D3) 23. In E4 enter "67.50". 24. In E5 type "231". Now leave a blank row and type the totals side headings and figures. 25. In A7 type "Sub-Total". 26. In cell E7 type the formula: =75.00+67.50+231.00 and press ENTER. Excel will calculate the figures for you as 373.50. Note that you can use any standard calculation sign in this type of formula as well as the + signs, e.g. - (minus), / (divide) and * (multiply(. 27. In A8 enter "VAT". 28. In E8 type the formula to calculate VAT: =373.50*17.5/100. 29. In A9 enter "Total Cost". 30. In E9 type the formula: =373.50+65.3625. Excel should calculate the total invoiceable cost as ?438.8625 for you. However, if you have already turned two decimal point format on as suggested, this will have been rounded down to 438.86, although if you were to use this cell for any further calculations the true background figure of 438.8625 would, of course, be used in the calculation and not the rounded down figure to ensure a correct calculation. 31. Either effect any formatting you would like manually, e.g. widen such as column A to 10 characters, centre and embolden each column heading, etc, or use the AutoFormat feature by Pressing ALT O (for Format) and then A (for AutoFormat). 32. Now underline the final total in E9 by going to it and pressing CONTROL U and then ENTER. 33. Lastly, save the workbook in the normal way with CONTROL S if you have not already been making saves as you went along (you should have been to avoid possible loss of your work during its creation). Give it the filename "invoice1". Later we will reopen this invoice worksheet and effect more intuitive formulae in the calculation cells to achieve the same results but these new formulae will be automatically self-updating. ******** 30.1. Entering Dates Manually 1. You have to be careful in what formats you manually enter dates into cells. The recommended format is: day followed by dash, then the month signifier followed by a dash and lastly the last two digits of the year. For example: 19-feb-02 31-mar-02 23-11-03 However, remember that there is an Excel shortcut for entering the current date as held in your computer system memory, which is by pressing CONTROL ;. 2. If you would like to change the date format to something different which Excel will still work OK with, press CONTROL 1, CONTROL TAB to the "Number" property sheet, TAB to "Category" and then ARROW down to a list of format styles. ARROW down this list and when on the alternative date style of "3/14", just press ENTER to make this your default date style in future. Now when you use the Excel shortcut CONTROL ; your preferred choice of date will be generated and inserted into the current cell. 3. Another way of inserting a date is to type in the cell: =now() In fact, you will generate both the current date and the time in this way, although this does not work in Excel 2003. 4. If you would like to insert the time into a cell, the shortcut is CONTROL SHIFT ;. You can change time formats and styles in a very similar way to the way you can with dates, in the Number property sheet, as shown above. ******** 31.1. Using, Viewing and Printing Common formulae You have to input formula in the correct syntax form for them to work. Excel performs its calculations from left to right and to control the order of operations you may have to enclose distinct parts of a formula within round brackets (parentheses) to get the desired result. As you will already have realised, you have to start a formula or function by typing the = sign first and then the formula figures and/or text. For example, Excel as its default multiplies before it does addition, so the formula: =10+4*5 would not give a result of 70 as you might think but rather one of 30. However, you can alter the way this is done with the use of brackets, in the following way: =(10+4)*5 Which would produce the result of 70, as Excel would then know to calculate (sum in this case) the portion of the formula between the brackets first and then multiply this by the figure outside of the brackets next. 31.2. Formulae Example List On the left is the formula and on the right what it does: =B2*B3 multiplies the cell B2 contents by the contents of B3. =B1+B2 Adds contents of B1 to that of B2. =A1-A2 subtracts contents of A2 from that in A1. =C4/C5 Divides the contents of cell C4 by that of C5. =a5-(a2*a3) Would subtract the value of a2 multiplied by a 3 from the value of cell a 5, e.g. if A2 was 5, A3 was 10 and A 5 was 100, what would appear in the cell you have chosen to receive the result would be 50. Note: If you find you are sometimes getting the "#DIV/0" error value displaying because a formula in your worksheet is trying to divide the contents of one cell by another which has nothing in it or this might occur when you load a blank worksheet which contains only formulae, you can avoid this by using the "IF" function. For example, if the formula creating the error is: =A5/B5 you can avoid the error message by using the following IF statement instead: =IF(B5=0,"",A5/B5) where the two quotation marks represent an empty text string. 31.3. Workbook Exercise 5--Amending Data and Formulae 1. With Control O, retrieve the "Invoice1.xls" invoice and practise amending data and formulae on it. 2. Using the shortcut of CONTROL G, go to cell E3, press the DELETE key to delete its contents and then type in the formula: C3*D3 and press ENTER. Now ARROW up and view the same result but this time due to your formula rather than the straight figures which were in their before. 3. Next go to cell C3 and make an amendment there as follows: press the DELETE key to delete its contents and replace it with the figure "3". Have a look at the result of your formula in cell E3 now. It should have automatically recalculated from 75.00 to 45.00. 4. Now make a change in the type of formula in cell E7, since the formula in their no longer reflects the correct sum as it was dependent on the figures in E3 to E5 not changing. Delete the contents of E7 and enter instead the formula: =E3+e4+e5 and press ENTER. View the new and now correct figure of 343.50 in their now. 5. To clarify the currency used, you could go to cell E1 above the figures cells and simply type a ? or $ sign, etc, in their and centre it or you could: highlight the whole of column E with CONTROL SPACEBAR and then press ALT O, E, and then TAB to "Category" and ARROW to "Currency", followed by TABBING to "Symbol" and ARROWING to your chosen currency sign of pounds (Euros and dollars are in here as well). Now go to "OK" and press ENTER and view the pounds sign which has been entered in each currency column for you and would automatically appear if you extend column E further down by adding more figures. 6. Now make the VAT figure in cell E8 correct by using a different formula which will work whatever the sub-total is, as follows. Delete the contents of E8 and enter the formula: =E7*0.175 and view the updated result of 60.1125 or 60.11 if you have two decimal or currency formats turned on. 7. Lastly, delete the contents of cell E9 and enter: =e7+e8 to correct what is displayed in the total cost column to show ?403.6125. 8. Now save (with ALT F, A) under the filename "invoice2" this second, amended, copy of the invoice, so that you now have two of these and, if you wish, print the amended invoice as shown below. Note 1: You will have gathered that using cell co-ordinate formulae and functions is preferable in most cases to using formula which rely on you entering a fixed figure rather than a cell range, because if something else changes, they automatically update themselves. Note 2: Never type a currency sign, such as the dollar or pound sign, directly into a cell with figures in it. This is because Excel will see this as text and therefore ignore the figure in that cell in any calculations it does--in fact, with later versions of Excel, you cannot type currency signs in figure cells but you could in earlier versions. Additionally, if you enter a currency sign in its own cell above a column of figures, you should centre it, as it will be left aligned whilst the figures under it are right aligned, which would look amateurish if viewed by others or printed out. On the other hand, if you only intend to create spreadsheets for your own use on your PC and not for anyone else's viewing or printing, formatting is of less importance. 31.4. Printing a Highlighted Area of a Worksheet To ensure that you only print the portion of a sheet which you require and not extraneous data or blank cells outside of the data area: 1. In the "Invoice2.xls" worksheet, highlight the cell range A1 to E9 (it is sometimes easier to do this from a corner other than from the A1 corner, e.g. from E9 to A1 or from E1 downwards and then across to A9, etc). Or, alternatively, highlight it by being in A1 and then pressing END followed by CONTROL SHIFT END. Use your screenreader's report highlighted text if it has one which works in Excel, e.g. INSERT SHIFT Numpad 2 with JAWS and CONTROL SHIFT M with Window-Eyes. 2. Press ALT F (for File) and then T (for Print Area). 3. Press ENTER on "Set Print Area". 4. Now either print from the File menu or use the Windows shortcut of CONTROL P. 5. Press ENTER to print if you only want one copy or TAB through the options if you want to make changes (see Printing a Worksheet above for more details). 6. You should now clear the print area with ALT F, t, and then TAB to "Clear Print area" and press ENTER. 31.5. Printing Spreadsheets Showing Their Formulae If you would like to print out a sheet which shows the formulae in cells rather than the results of the calculations you can: 1. Press ALT T (for Tools) and then O (for Options). 2. Then CONTROL TAB to "View" if you are not already on it and TAB to "Formulas" and press SPACEBAR to check this on, then TAB to "OK" and press ENTER. 3. Lastly, press CONTROL P to print or use the File menu method as above if you prefer. Note 1: In 2 above there are several more property sheets besides "View", so CONTROL TAB through them and have a look at them and their options. You also have many other options you can TAB through and select in the View sheet as well. Experiment with some of them and use SHIFT F1 to get details about what they do. Note 2: Remember, the general Excel shortcut keystroke to view a formula in a cell you are in rather than the result of the formula's calculation is CONTROL `. Press CONTROL ` to return to normal view. ******** 32.1. Statistical functional and formula Operators You can create your own formulae or use Excel's pre-defined formulae, known as "functions". Excel has constant cell references, operators and functions. The result is displayed in a cell and the formula shows in the "Formula Bar". Excel's main calculation functions are: On the left are the function operators and on the right what they do. SUM Will add up a range of cells. AVERAGE Will provide an average value for a range of cells. COUNT Will show the total number of numeric only entries in a range of cells. COUNTA Will show the total number of all entries in a range of cells including cells with text only. MAX Will show the highest value in a range of cells. MAXA Will show the highest value in a range of cells, including true and false text and logical values. MIN Will show the lowest value in a range of cells. MINA Will show the lowest value in a range of cells, including true and false text and logical values. STDEV Will provide the standard deviation in a range of cells with figures, i.e. the spread of the data showing how widely values are dispersed from the mean average value. PRODUCT Will multiply all the numbers given and return the product (result of the accumulated multiplications). VAR Will estimate variants based on a sample of figures, in particular a sample of population. The syntax for all of the above is: Function(cell range). For example: 1. To find the average figure for a column cell range of figures from A1 to A20 and have it displayed in cell A21, place your cursor in cell A21 and use the formula: =AVERAGE(A1:A20) and press ENTER. The average figure will appear in A21 or in any other blank cell where you may have placed your cursor for that matter. 2. To have Excel count the number of cells in the range of cells B2 to E10 which contain numbers in them and place the result in the cell you currently have your cursor in, type the statistical operator: =COUNT(B2:E10) and press ENTER. 3. To generate the product of cells A2, b2 and C2 containing the figures 2, 3 and 10, you would use the formula: =PRODUCT(A2:C2) and press ENTER. The result of your multiplication would be 60. You can obtain and use a full list of Excel's functions and operators from the Insert menu by: 1. Highlight the cells you wish to perform the function on (although Excel will work these out itself in some circumstances if you wish). 2. Press ALT I (for Insert) and then F (for Function). 3. In the "Function Name" or "Select a Function" list you can ARROW down the various different possible statistical operators and leave focus on the one you require and press ENTER. If you are using Excel 2003, there is also a "Search for a Function" editfield where you can type in such as "sum", "find an average", etc, then press ENTER on "Go" to narrow the list of possible functions down to only those relevant to the type of thing you are wanting to do. 4. Then you will be brought into a multiple editfield dialogue box on the field which tells you which range of cells will be acted on. It will be your highlighted ones if you highlighted them; if you did not do this, it will be the range Excel thinks you require--it usually gets it right. 5. Press ENTER to finish and effect the calculation. Note: If your range includes a cell which itself has a formula in it, the result of that formula will also be taken account of in the current calculation, e.g. if you are summing (adding) the figures 10 in A1, 10 in A2 and 10 in A3 which have already been summed as 30 in A4 and you now sum all four of these cells to obtain a result in A5, that result will be 60. 32.2. Workbook 6--using Excel's Functions and Inserting Extra Columns and Rows into a Worksheet Create the following worksheet and then effect the calculations using functions and make the amendments suggested. I have condensed how I give the cell information to save time and space but an sure that by now you will be competent enough to be able to follow these instructions. The worksheet will initially cover four columns and 14 rows. 1. In A1 type The main heading in capitals "OUTSTANDING CAR HIRE FEES". 2. In B2, C2 and D2 type ? (pound signs) to indicate that the figures in the columns below represent money. 3. In A3 type the heading "Car Type" and in the next three cells to the right type: January; February; April. 4. In A4 type "Escorts" and in the next three cells to the right type: 1800; 2100; 2450. 5. In A5 type "Mondayos" and in the next three cells type: 850; 950; 1000. 6. In A6 type "Astras" and in the cells to the right: 500; 600; 650. 7. In A7 type "Metros" and to the right: 700; 800; 900. 8. In A8 type "Focuses" and to the right: 750; 800; 1000. 9. In A9 type "Volvos" and to the right: 900; 1000; 1050. 10. Leave two rows blank. 11. In A12 type "Totals". 12. In A13 type "Average". 13. In A14 type "Standard Deviation". Now take the following actions: 1. With the cursor in B12, press "ALT =" and observe that the AutoSum function will work out which cells are to be added together "B4 to B10 or B11 (its all the same) and offer you the appropriate formula, so just press ENTER to accept this. The result should be 5500. 2. Whilst you could do the same thing as above in cells C12 and D12 to sum those columns, instead, whilst in B12, press CONTROL C to copy the formula to the Clipboard and then move to C12 and then D12 and press CONTROL V in each to paste the same formula in to both cells. The results should be 6250 and 7050 respectively. Whilst in C12 use your screenreader's read Formula Bar hot key if it has one, e.g. CONTROL F2 in JAWS, ALT D in WE, left SHIFT Numpad 7 in HAL and Supernova, CONTROL ` to toggle between view formula or data, etc, or move to the Formula Bar in mouse mode to view the formula. Note that pasting the formula into C12 results in the cell range automatically changing from "=sum(B4:B11) to "=SUM(c4:C11). This is because these cells have relative references as opposed to absolute references, which will be explained later. 3. In B13 calculate the average value of outstanding payments with the formula: =AVERAGE(B4:B10) The amount should be 914.6667. 4. Copy the formula from B13 to C13 and D13 as before. Later I will show you an even quicker and easier way to copy data and formulae across cells. 5. Go to B14 and calculate the standard deviation with: =STDEV(B4:B10) The result should be 454.6061. 6. As before, again copy the formula from B14 into C14 and D14 or repeat the functional operator in the cells in the same way as you did in 5 above. 7. Alter the decimal point placement to allow for two decimal places only by highlighting the range B13 to D14 and then pressing the Excel general shortcut of CONTROL SHIFT ! or use the Format, Cell, Number menu method. 8. You now realise that you have omitted the month of March, so generate a blank column to fit this in by going to column D and then by pressing ALT I (for Insert) and then ARROWING down to "Column" and pressing ENTER. Your current D column will move one to the right and leave you with a new blank D column. In D2 type the ? sign, in D3 type "March" and complete the rest of the cells with some fictitious figures of your own and copy over the formulae in the Totals, Average and Standard deviation rows. Remember, if you are in a row well to the right of its left heading title and you need to know what that title is, use your screenreader's read row title hot key, e.g. ALT SHIFT R in JAWS and Numpad 3 in HAL. 9. If you wanted to add another row, you would follow the same procedure as above but in the Insert menu you select "Rows" instead of "Columns", of course. The row your cursor is in will move down one. 10. Lastly, You now decide to get rid of the gap between your figures and the totals, averages and standard deviations. Do this by highlighting cells A12 to E14 and then cut (move) the range to the Clipboard with CONTROL X. Move to cell A10 and paste the range in with CONTROL V as usual. 11. Remember, if you make a mistake in your highlighting, etc, you can always use the shortcut of CONTROL Z to redo your mistake to get back to where you were before you made it. 12. Now improve the formatting of the spreadsheet by manually widening columns, centring pound signs and column headings, emboldening headings, aligning the figures in the columns as you like, etc, or use the AutoFormat feature to achieve this. After highlighting the worksheet cell range to be AutoFormatted, AutoFormat is done with ALT O, A, and then either just press ENTER for a simple formatting or ARROW down the list of format options to select a fancy or specific type before pressing ENTER on "OK". 13. Now, if you wish, print the worksheet by either highlighting its range and pressing CONTROL P or use the method via the File menu, i.e. ALT F, T, etc, then ALT F, P. 14. Save the worksheet to the filename "carhire" as normal. ******** 33.1. Decimal Fractions and Percentages To display values in fractional or percentage terms you would proceed as follows. 33.2. Workbook Exercise 7--Fractions and Percentages 1. Reload the "carhire" workbook and create another column and row, as shown below. 2. In Cell F2 type a ? sign. 3. In F3 type the heading "Model Total". 4. In F4 type the formula "=SUM(b4:e4)" and press ENTER. This now shows the total outstanding fees for the Astra cars over the four month period. 5. Copy this formula down through rows F5 to F9. 6. In F10 generate a total for the whole outstanding debts on all cars for all four months with the formula: =SUM(B9:E9) and you should get the figure 22500. 7. Now delete the two rows with the side headings "Average" and "Standard Deviation" by highlighting the block and pressing the DELETE key. 8. In cell A11 type a new label of "Fraction". 9. In cell B11 type the formula: =B10/F10 10. repeat this in cells C11 to E11, as you will not be able to copy this type of formula over. 11. Have a look at the decimal fractions of each month's outstanding car hire fees. It should be 0.24in B11, 0.28 in C11, 0.16 in D11 and 0.31 in E11. 12. To change one of the fractional figures to a percentage, go to cell B11 and then press ALT O, E, CONTROL TAB to the "Number" sheet and then in "Category" ARROW down to "Percentage". Lastly, TAB to "Decimal Places" and ARROW down to "0" and then TAB to "OK" and press ENTER. You could, of course, have done this with all the fraction cells simultaneously if you had highlighted them all first. 13. Now view the altered figure in B11. It will read as a percentage now. 14. Do any formatting tidying up you would like and then save the changed carhire worksheet to a different filename, such as "carhire2" (with ALT F, A), so that you now have two car hire workbooks. ******** 34.1. Relative and Absolute Cell References A reference identifies a cell or range of cells on a worksheet and informs Excel where to look for the values or data you wish to use in a formula with references. You can use data contained in different areas of the worksheet in one formula or use the value from one cell in several formulae. It is also possible to refer to cells on other sheets in the same workbook, to other workbooks or to data in other programs. 34.2. The A1 Reference Style By default, Excel uses the A1 reference style. This refers to the column headings of A, B, C, etc, through to IV (256 columns in all) and row headings of 1, 2, 3, etc, through to 65,536 and requires the column heading to precede the row number, e.g. G3 to represent the cell at the intersection of column G and row 3. A range of cells is depicted by its top left-hand intersection co-ordinate through to its bottom right-hand co-ordinate, e.g. B2:H20. If you find that your cell references are not currently using the A1 reference style and are, in stead, using the R1C1 style, you can turn the R1C1 style off and the A1 reference style on via the Tools, Options, General property sheet. Just press SPACEBAR on "R1C1 Reference Style" to turn it off and change to the A1 reference style. 34.3. Examples of Cell and Range References To refer to the cell in Column A and row 10: type A10. To refer to the range of cells in column A and rows 10 through to 20: type A10:A20. To refer to the range of cells in row 15 and columns B through E: type B15:E15. to refer to all cells in row 6: type 6:6. To refer to all cells in column 5 through 10: type 5:10. To refer to all cells in column F: type F:F. To refer to all cells in column H through J: type H:J. To refer to the range of cells in columns A through E and rows 10 through 20: type A10:E20. 34.4. Relative and Absolute Cell References It is possible to use either "relative" or "absolute" cell references. Relative references are those which are relative to the position of the formula; whereas absolute references are cell references which always refer to cells in a fixed position. So, for example, with a relative reference, if you had a formula in cell C6 and it referred to data in cell B5 it would be looking one row up and one column to the left to find its reference data. If you now moved the formula in C6 to C7, then it would no longer look to B5 for its reference data but rather to B6 as this would also have moved over by one column, i.e. it is relative to the position of C6 and not constant or absolute. In contrast, with absolute references, if you want Excel to continue to look to B5 when you move the formula in C6, you would make the formula in C6 an absolute one by placing $ signs before its column and row headers, i.e. wherever you copied the formula in C6 to, it would continue to reference B5 for its values or data and not the cell one up and one to the left. To make a cell or range of cells absolute (fixed) you have to precede its heading with a $ (dollar) sign, e.g. $B$3. Copying formula which relies on the data in specific cells without making it absolute first will result in failed copying and error messages. 34.5. Making Formulae Absolute To make a formula absolute/constant: 1. Place your cursor in a cell which contains a cell range formula such as "SUM(A1:A9". 2. Press F2 to open the cell up to editing and then left ARROW to the beginning of the cell and then ARROW right until you get over the first letter A. 3. Next type the $ sign and then ARROW right until you are over the figure 1 and again type the $ sign. Do the same before the A and 9 of A9, followed by pressing ENTER to leave editing mode. 4. View the result in the Formula Bar. It will now show as "=SUM($A$1:$A$9". The hot keys of left SHIFT Numpad 7 in HAL, CONTROL F2 in JAWS and ALT D in WE read the Formula Bar for you without you having to go into editing or mouse mode. 5. Now when you move around a worksheet to copy and replicate the formula elsewhere your cell reference will not change and spoil the copying of the formula and its results. Note: The absolute reference A$1 would refer to a1 with only the row as absolute. 34.6. Workbook Exercise 8--Copying absolute formulae across a Worksheet To exemplify this: 1. Open the worksheet "carhire" which you created earlier. 2. go to the standard deviation figure/formula in cell B12. 3. Press F2 and edit the formula as above so that it reads: =stdev($B$4:$B$9) and press ENTER. You could, of course, elect to just delete the contents of the cell and retype it in with the $ signs if you prefer. 4. Do the same in columns C12, D12 and E12 and then highlight all four cells from B12 through E12. 5. Now copy all of them simultaneously to the Clipboard with CONTROL C. 6. Next Move to another part of the sheet, e.g. cell G30 and press CONTROL V to paste the formulae in from their across G30, H30, I30 and J30. 7. View the results, which should be the same as those in the original car hire worksheet range. This is because the copied formulae are still looking at the range of cells from B4 to E9 for their information. 8. By making cell formulae references absolute in this way, you can copy formulae to different areas of your current worksheet without the range of cells they refer to changing. Any changes/updating subsequently made in the original range, i.e. in the figures in the cells B4 through E9 in this example will result not only in the figures in the original total, sub-total and Standard deviations being updated in cells B10 to E12 but also those in the replicated formulae which were copied elsewhere. 9. Lastly, to show it does not work without making the cells absolute, close the workbook without saving your changes and reopen it. Go through the procedure again but this time do not edit the formula to include $ signs and view the resulting error messages. 10. Close the car hire workbook without saving these changes. ******** 35.1. How to Enter and Use Array Formulae An array formula can perform multiple calculations and then provide either a single or multiple result. Below is a simple single result example to demonstrate how an array formula uses the "if" operator to find specific strings in column A and then look up and perform an average calculation of the corresponding figures in column B. 35.2. Workbook Exercise 9--Using an Array formula 1. Open a new worksheet and complete these details: A. In row 1 enter the headings: in column 1 "MEDIA TYPE"; in column 2 "QUANTITY SOLD". B. In row 2 enter: Books; 100. C. In row 3 enter: CDS; 200. D. In row 4 enter: books; 200. E. In row 5 enter: cassettes; 150. F. In row 6 enter: books; 300. G. In row 7 enter: CDS; 300. H. In row 8 enter: Average Books; nothing in column B. 2. Now move to Cell B8 and type the array formula: =AVERAGE(IF(A2:A7="BOOKS",B2:B7)) 3. Then enter the formula into the cell by pressing CONTROL SHIFT ENTER. Just pressing ENTER will not work. 4. Observe the result, which should be the average figure of 200. In other words, Excel has found all instances of the text string "Books" in the range A2 to A7 and then averaged the corresponding figures in the range B2 to B7 to find the result and placed it in cell B8. Note: The formula "=AVERAGE(IF(A2:A7="BOOKS",B2:B7))" is a "nested" array formula because it has one formula within another, i.e. the "(IF" part with the outer parentheses and the second formula within the inner opening and closing parentheses. ******** 36.1. Using 3D References to Analyse Data Over Multiple Worksheets You can analyse data through many sheets in a workbook by using a 3D reference. In other words, if you have many worksheets in a workbook with a single cell or range of cells with data in them which you want to sum, multiply, use a function on, etc, you can do this by referring to the range of worksheets to be included followed by the range of cells to be acted on. The cells must be in the same place in the workbook on all worksheets. For example, with your cursor in the cell you want the result to appear in on the worksheet you want it on, to sum all of the figures in cell C3 in worksheets 5 to 40 you would use the formula: =SUM(SHEET5:SHEET40!c3) Therefore, if the figures in all of the 35 referenced sheets in C3 had been the number 10, your result would be 350. To average the range of cells from A1 to F10 in worksheets 3 to 10, you would use the function: =AVERAGE(sheet3:sheet10!A1:F10) You can use any of the functions of Excel in a 3D reference, e.g. MAX, MIN, COUNT, etc, but you should not try to use a 3D reference in an array formula. If you add or delete a sheet in the range of worksheets included in your 3D reference, Excel will recalculate the results of the new range of sheets/cells. ******** 37.1. Inserting Hyperlinks, Objects and AutoShapes into Your Worksheets There are several object insertion options in the Insert, Picture sub-menu which you can use to insert such as scanned pictures, clipart, autoshapes and wordart into your worksheets from. Not all of them are very screenreader-friendly but the below gives an idea of what can be done. 37.2. Inserting Hyperlinks into Worksheets from which to Launch Your E-Mail or Internet Client If you have Excel 2000, 2002 or 2003, you can quickly and simply insert hyperlinks into Excel cells by just typing such as an e- mail address into a cell and pressing ENTER. You may wish to place such hyperlinks into your worksheets to assist your clients to reach you easily under a column heading such as "Contact Me". For example: 1. Whilst in cell C3 type "jwjw@xxxxxxxxxx" and press ENTER. 2. Now move back to C3 and view the e-mail address you typed in. You may have to widen the column to see it all. 3. A. With some versions of Excel you can just press ENTER on the address and your e-mail client, e.g. MS Outlook, MS Outlook Express, Eudora, etc, will launch. 3.B. With other Excel versions you will have to get your e-mail client to launch by moving to the cell with the e-mail address hyperlink in it and then pressing SHIFT F10 and ARROWING up to "Open Hyperlink" and pressing ENTER. 4. You may either come into the "To:" field with the address which was in cell C3 automatically entered in there or you may be on the "Subject:" editfield. 5. If you are not already there, TAB to "Subject:" and the message body and complete these as normal. 6. Send the e-mail in the usual way, e.g. ALT S, ENTER and then CONTROL M with Outlook Express. 7. You will then be returned to your worksheet in cell C3. Similarly, to launch your Internet browser, such as Netscape Communicator or Internet Explorer, you would: 1. In your chosen cell, type an internet URL (address) and press ENTER, e.g. "http://web.onetel.com/~fromthekeyboard";. 2. Move back to the cell and press ENTER to launch your browser and take yourself online to that Website (which just happens to be my Website). 3. When you exit from the browser you will return to Excel in the cell you launched it from. 4. After creating a worksheet with e-mail and/or Website addresses in it, you just save the sheet in the normal way. 37.3. Inserting Pictures into Your Worksheets Directly from a Scanner or Camera With all versions of Excel from 97 onwards you can, provided you have a Scanner or camera attached to your PC and turned on before you start your computer, place a hard copy paper chart, photograph or other picture onto the scanner or in front of the camera and obtain a snap of it. It will then be inserted into your worksheet at the point of the cursor. 1. With your scanner or camera ready and your photo or other picture on the scanner or in front of the camera, press ALT I (for Insert) and then P (for Picture). 2. ARROW to "From Scanner or Camera" and press ENTER. 3. You should come into a list showing which camera or scanner interface driver will be used to do the scan or snap-shot. Just below this is an "Add Pictures to Clip Organiser" button which should be checked on if you would like your pictures to be saved in this Clip Organiser folder of pictures for possible future use. 4. Now TAB again to the "Custom Insert" button and press ENTER or use the shortcut of ALT C. 5. Your scanner or camera driver should load in and give you the same options as usual when scanning or takeing pictures, e.g. a scan or similar button. Press ENTER to start the scan or take the camera picture as normal. 6. After scanning or snapping the picture it will be inserted into the worksheet but, of course, you will have to be able to see this to appreciate it. If you go into your screenreader's mouse mode, you should be able to find the default name that Excel gives to picture images on a worksheet, e.g. "picture 1", "picture 2", etc, depending on how many pictures you have inserted. Note: If you choose one of the other options in the Insert, Pictures sub-menu, you will have to have such clipart and other images available on your computer to select from or load them from your MS Office installation CD. 37.4. Formatting a Picture or other Object After inserting an object like a photo, picture or other image object into your worksheet, if you wish, you can format it. If you cannot see what is happening to it wen you do the formatting, you will have to ask someone or simply accept that selecting and applying the various formatting choices you can make should work OK as long as you have highlighted the object successfully first. 1. Go to the object you wish to format in mouse mode and click it with your screenreader's left mouse simulation hot key, if you are not already there. Your screenreader may also have a find objects hot key to open up a list of objects on a worksheet to select one and take you there, e.g. CONTROL SHIFT O with JAWS. 2. Press ALT O (for Format) and then ARROW to and press ENTER on "Picture" to open up a Context Menu with several property sheets in it. 3. You can CONTROL TAB through these several sheets and make alterations to the size, appearance, angle of display, border line colour, etc, and you can even add alt text to a picture so that if you upload it to a Website a screenreader will be able to read the ALT (alternative) text on it and thereby tell you what it is or what it represents. In these sheets you can TAB to various options and ARROW up and down choices or spinboxes to make choices or increase or decrease the object's size. 4. When finished formatting, TAB to and press ENTER on "OK" to complete the procedure. 37.5. Inserting an AutoShape into Your Worksheet If you want to insert one of a number of autoshapes into your worksheet you can: 1. Firstly enable the Drawing Toolbar by pressing ALT V (for View) and then T (for Toolbars), followed by ARROWING to "Drawing" and pressing ENTER. 2. Place the cursor in the worksheet where you want the shape inserting. 3. Press ALT U and you will open up a list of autoshape choices to ARROW through downwards or to the right, each with its own sub-menu to right ARROW into and make a specific choice by pressing ENTER on a shape, line, type of arrow, etc. 3. So just ARROW to the one you would like inserting and press ENTER. ******** 38.1. Headers and Footers To obtain a header at the top of each sheet you print out: 1. with your worksheet on screen, press F (for File) and then U (for Page Setup). 2. CONTROL TAB to "Header/Footer". 3. In the list you are in or can TAB to you can ARROW down several supplied Footers to select one of these if you like, e.g. "Page 1", "Confidential . . .", "Page 1, Sheet 1", etc, and then TAB to "OK" and press ENTER to finish. 4. Otherwise, if you wish to create your own custom footer or Header, TAB to "Custom Header" or "Custom Footer" and press ENTER. 5. Now TAB once to the "Centre Section" editfield and type your desired custom header in, e.g. "The Locomotive and Coach Company". If you would like the heading to the left or right instead of centred, you have these options as well. You can keep TABBING in this list of buttons to also select other header attributes as well, such as Font, Date, Time, Insert Picture, etc. 6. TAB to "OK" and press ENTER. 7. Lastly, TAB to "Print" and press ENTER to get your workbook, Worksheet or highlighted cell range printed out with the header at the top of each page. 8. If you would like a footer instead of or as well as a header to the same as above but choose the "Custom Footer" this time. 9. Save your worksheet together with its header/footer as normal. Note: You can also view and choose built-in headers and footers and create custom headers and footers via the View, Header and Footer menu option. ******** 39.1. Filtering Out Specific Data or Records for Easier Viewing or Before Printing If you only want certain columns or rows of a worksheet to be displayed or to print out, for instance, all of a worksheet but not columns D and E, you should highlight the data in columns D and E and then press ALT D (for Data), F (for filter) and then press ENTER on "Autofilter". Now, when you print out this spreadsheet list of records, it will all be printed out but not columns D and E. The difference between excluding columns or rows in this way and what would happen if you simply deleted the columns before printing the remaining worksheet is that filtering results in a printed sheet which does not show missing (blank) columns or rows whereas straightforward printing would produce empty columns or rows. ******** 40.1. Sorting and Auto-Filling Data Sorting and AutoFilling can save you much time and tedious effort in manually and individually organising data. 40.2. Sorting Lists of data in Ascending or Descending Order If you would like your data to be placed in alphabetical order for ease of location, practice how it can be done as shown below. In reality, you will be taking advantage of Excel's ability to recognise a column of data as a list and therefore treat it as a kind of database of data. The Sort feature allows for three hierarchical levels of sorting, firstly on whatever column or row you choose as the first row to sort, then on the next column or row and then on the third column or row. There are three "Sort By" listboxes which you can ARROW down to leave focus on the column you want to have sorted, i.e. the first being the primary column to sort, the second the next column to sort, etc, and you can elect to have the first column cell or row included in the sort or get it/them excluded if they are column or row label headings which should remain where they are. Note, also, that there is an "Options" button and if you enter that dialogue box you can choose to do a "Normal" sort or ARROW down to more specialised sorts for such as days of the week and months of the year. You can also make a sort case sensitive in here and you can change the default sort expectation from down columns (top to bottom) to across rows (left to right). 40.3. Workbook Exercise 10--Sorting 1. Open the "Carhire" workbook with CONTROL O. 2. Sort column 1 (the list of car types) in ascending alphabetical order by Highlighting the block A4 to E9. 3. Then Press ALT D (for Data) and then S (for Sort). 4. In the dialogue you come into note that there are several "Ascending" radio buttons which you can down ARROW on to change this to "Descending" to obtain reverse alphabetical order if you like. Leave them on "Ascending" and TAB to "OK" and press ENTER to finish. 5. Save the changed workbook back to its original filename by pressing CONTROL S. Note 1: If you only highlighted column A4 to A9 and not the whole side label and figures columns, then the car model names would sort in alphabetical order but the figures to their right would not move, so the result would be incorrect. Highlighting the whole block A4 to E9 ensures that all related data moves with the car type label. Note 2: You could, of course, have done exactly the same after highlighting a block of figures and you would have got them relisted in numerical order. 40.4. Auto-Filling Dates, Days, Months and Values Down Columns or Across Rows The AutoFill feature of Excel has been described in earlier examples in this tutorial and its simple fill right with CONTROL R and fill down with CONTROL D have been used. These features are also found on the Edit, Fill menu. However, there is a more advanced AutoFill option here called AutoFill Series which can be used to fill down or across to automatically complete series of common data, such as days of the week, months of the year and dates. for example: 1. Go to Cell A1 and type "Monday" in a blank worksheet. 2. Highlight Cell A1 and the six cells below it. 3. Now press ALT E (for Edit), I (for Fill) and then S (for Series). 4. In the dialogue which opens up you SHIFT TAB to the "Type" list and ARROW down to "AutoFill". 5. Lastly, TAB to "OK" and press ENTER. You should find that below Monday in Cell A1 you have Tuesday in Cell A2, Wednesday in Cell A3, etc. If you had of highlighted 21 cells down the column, you would have got Monday to Sunday filled in three times over for three weeks. More Examples: 1. In the "Type" list mentioned at step 4 above, you will find other alternative types of fill options, such as "date". You would use "Date" if, for example, you had of typed "1/10/03 into Cell A1 and then highlighted down to say Cell A31 to get the dates from 1 October to 31 October automatically completed for you. This AutoFill feature may not be considered worth using for lists of just a few days or months but it is a great time saver for longer lists such as typing 1/1/03 into Cell A1 and then highlighting it and then highlighting all Cells down to Cell A365 to generate every day of the year for you instead of you having to type them in yourself. 2. Try another autoFill aspect. Go through the above procedure but this time type in Cell A1 1/1/03 and in Cell A2 1/2/03. The result will be that for each cell you highlight under A1 and A2, a series of incremental values will be inserted, namely in A3 you will generate 1/3/03, in A4 1/4/03, etc. Similarly, if you had entered 25 in A1 and 50 in A2and highlighted several cells below or to the right of these two, then the values 75, 100, 125, etc, would have been inserted for you. ******** 41.1. The Find and Find and Replace Features In a similar way to how you would use a Find and a find and replace feature in a word-processor, Ms Excel provides a similar ability. You can find instances of text, figures and formulae in a worksheet only or you can get them found and replaced with something else. 41.2. Find To use the Find feature: 1. With the "fishandchips" worksheet on screen, go to A1 and then press CONTROL F to enter the Find dialogue. 2. In the editfield which opens up type "140" and press ENTER. 3. The first instance of the figure 140 will be found and you should press ESCAPE to leave the find dialogue and view this. It will be in cell C3. 4. If you wish to find the next instance of the 140 figure, press SHIFT F4. The only other instance will be found at cell C5. 5. You can also use wild cards in find strings to find words or figures with things in common or those you cannot remember all of the characters for, e.g. "Wi*son" will find words such as Wilson, Williamson, etc, "127? will find figures starting with 127 and ending in any other single figure or letter such as 1270 and 127D. 41.3. Find and Replace To have all instances of the figure 140 found and replaced with the figure 200: 1. First highlight the area you want to have searched, e.g. if only column3 put your cursor in column 3 and then press CONTROL SPACEBAR, SHIFT SPACEBAR to select the current row, END then CONTROL SHIFT END to highlight the whole worksheet range from your cursor or just highlight the specific range you wish to have data searched for and replaced in. 2. Press CONTROL H and type in the search string which you want to find, e.g. the figure 140, the word Friday, etc. 3. TAB to the "Replace With" field and type what you want to have the first string replaced with, e.g. 200. 4. To have all instances of that word or figure found and replaced, press ALT A, then ESCAPE to leave Find/Replace. To find and replace the formula "SUM(C2:c6)" which appears in cell C7, type that string into the "Find" editfield and type "SUM(C2:C4)" in the "Replace With" field before pressing ALT A and then view the changed chips sales data in C7. ******** 42.1. Creating Charts with the Chart Wizard You can create and edit charts and graphs with the Chart Wizard. Sighted people often find charts much easier to glean information and trends from than columns and rows of raw figures but this may not be the case for most visually impaired users. Nonetheless, for those with a need to do this, you generate a chart from an already created worksheet and when you update the data in the worksheet the linked chart also simultaneously updates. Do this as explained in the next section. 42.2. Workbook Exercise 11--Creating a Chart 1. Open the workbook named "fishandchips". 2. If you would like to embed the chart in this same worksheet, i.e. sheet 1, just carry on as below; otherwise, if you would like the chart on another worksheet, press CONTROL PAGE down to move to worksheet 2 or any other before continuing. 3. Highlight the range of cells from A2 to B6 to obtain a chart showing the relative sales of fish only for the days Monday to Friday. 4. Press ALT I (for Insert) and then H (for Chart) and the Chart Wizard will open up. 5. The Chart Wizard has four distinct stages to go through: A. ARROW down the list you come into to "Pie" and press ENTER to create a pie chart. Note: a pie chart is one which shows values for relative sales, etc, such as percentages, whereas a line chart plots sales against an axis, a bar chart shows relative sales as a series of horizontal bars of differing length and column charts show values as a series of vertical bars. B. You will be on the line which confirms to you the data range to be used in the chart with $ signs in it to show that it has Ôëòûôêò<û?ðäþ²ó?þbødû4ûP÷pûªó¨ùHñVöÈðfò?ñ~ï\ñ?îî?î$çfîjÝ6íVÓÖê.ÌæîʨÝ:ÑÒhÞìÆï¸Àþh´dÌxúÜ?ý?ðrî|Ý. ÖÎÀÄ°?¿ðôL¿Ý~èÄtÊÀ±ÊÑR§¤×¦¥(Û ¬?ܹäÜjÈ\Ý´Ô ÞÚÚáôÛêäRÛ|ê?Ûîñ¬ÝûÆá ?ç?îÚö.èýªÐð¾?bÌB¬?BZ&®+|?,@*¾ ²'úø&ÚøÖ'Ìõò'2úÂ$²zöà ¾?& ?$dþ>'Ø,* ?, è& èèØD?Ê? ?¢$N?%ð!üdôJäþ#|ð-08@^=~:à"Î-î-Ü7¶ ê<Üûb9PõP,?õÌú: :ÿPȶÿØ,L? ¦ î Vò¢ 8þ??ü<?ÿdJ???´D ¬ ~ó&,?ܺ4LÊ5RÁ(|Ã? ¢ÎVì¨ÞôÏ?ðÞÁ¢?ÄÄ?Óf">è®,þö/îD*)>>:¬A ?:´ü?%þ x"ôÞzíF?÷?? z@"H 2ô?ÜÒ¸ê²8?!!ê$Ô%&¢'®&î#â(Ô?,?à.ªý-øó?%*ñ ÔõD rÿ|ú? $òÆHóÈôûìôDÞÔ VæÌÂú?àò\píÄ ?ë&¬ìnôïÒã¶ðLÖÊïzÐXìÓ¾çtÚøâ,âLÞ?æàÙpå?ÕNÞ´Ð`Ó`ËBÈ|Ƭ¿\ÃTºNÃZ·<ÇÒ´ÂÎÔ°Ê×?«úß ¨?æ?©ºì?°ªóºLû$Ãd ËêòÓXþ¨ß?ôîzçÀû¾ÙØÔÍ?ÈŦ÷<Ã,æÖǬÒFÔ`Á?æ¾µÄù?²ª6¹â²È~ Ý ÞðÌþlý`?ûÌÜýþ ÞúTÂûZî*?ô7ÜNA?ÚEd¾E$(Cæ,Aê4AÐ:?B\=NC¾<~@;?8?:´, ;^ :Ƽ5X?.vp'Þö"6 6"Ü Ô$Ⱥ(**öÿ?%.` 6¬?ÿ~°ý?¸?ÿBóÆ æè|Ú.ÿäÒÄð,Ò?Þ4ÚDËêܺ¶ý¬²ÒضN`ÈDRäÄÈÊJ¬)¸ð%° à?ö àÚ$ÖììЦÜêÿÒ²óøÍ"ãòÏÒPØÄç?»Îù¾¸È¤»?ÆÅØ6Ù*¨ôÞ´$\(úó^4ã°5ÎÓ¾,PÉÆ`ÆÐúÌ´ØÐØh»ö輪t÷ʪ?» ?Õjîð*úJbòr<ëzÖæç¨ûDí?ñ|ø?é(?æÈ4êÒjóFÈÿ¬&¦æ^ÿ²ý?,ÿ ÞâF¶ù öúúdöÀózø<ïùZïÈö¸óJó¨úÜñÜPôÎú" ÖVv?¶|2°ÿààÐý¼ ?öhóü?ñì ð^Ý®înÔ.îÒ?ð?Ó¤÷´Õøh×DÚĶàøòëÀîúîT 2úvðð?éÜäBäôónçöæ4îÀÜÖöÔ.ÿôÌHÉþNÊܦЦڬîãÒ?ìh% ô*(âûò"üjPN?é%öÖ2#L;üÍÔr×6ïFædÝöêÔî^×Ì æá?ï\ üJ 2Øö ÔûvöÂìòØ¢òÀó®?ô2 ìôjÌö?°û²jÞ 6!¸üxvôºú°Úö"?#æ&Â&4*r'?+ð"?*¦?( H&üð.#äÜìªÏrbÍJ úÕÔù²å|éèöPÝ´~Ú\6â?ð.ÿý~ù¾÷ðÆø.ù`ý¤í2?ä?áRäü¤éBüèîBõfóæîD÷öê2úTë|û?ðÊú°÷?ø°ûÂöpùª÷2ñzü¤å2¼Ú.èÓÖ<Ñ?÷ÂϬå?Í?ÔÌ`ÊÎòÇÜÔêÈ:à?È?îjÇ?üÊÊNôÖ° ¨ê® ú??îô%ÀÚÒ/tÉÐ0æ¿T'ôÁ?öÎzø|â¬Ý?÷ÈÌD èË\TÙr¾íÔHùÎNðvnï®"ö°,ÿ6÷ÀÊîÊ$ó2z`ýnäû*ýòÒÿZ4þv?øð äö²?öÈ÷¨ ú(4 ?Ð vàÌþ?!VþØ* P?þ?rûH$ýÖÖj`r`ÂVr xļ6ôîÊbÚ ?? @Àò8ªüÿV ý?^þ`4r \Öÿ?ºõñö? 2Lânù?bñX?ê"Êã?tÜÐþèÕèøzÒÞ÷ªÓø Ùðô¤àPî:èéïjé ÷ïä÷Tÿ'`ª1:2ü ¼'Ä3PþEÂ?P?îRNPád?RÛþ(ÂÝ?ç¬ýBô^ïNVç??åä?éæöñ$@üÀ%¤?#Ê!Ƹ"Ê/ ):<ø2Ð?,:¨<ä:;?4`@~+ZH?$?Jl tCb&7?+òê#?ª8Âên ´!¶ ¸,Bÿb:?ù CFúìADÈ4ðä"? h?~ì ¼l²$õô ìB¤çF>èXìjötñòê&ö6é4ûjð?¬ûÞ4´ ZZ :þ$"J&¾~"ÊÒ?ôJô^èþþ$Êô&Òê.!BâÐ?ÛòR×ÚöXÖ¤æZÙôؼà°Í?íøÅxÿÔÄ Ì*#XÚ?,îú-¾2'\"*Ä:f HÂú0Fðíæ7&è@"´ìØ<úÐîÌ?ÙÐfÏ>%ÄÒ?%®à\Zó&îr¾f$?ö*Æ+$ª%¦ªÌæ Z"vþ#fùÈôýÆ HüÊþêR+<ã4¶è?3&ø+F x ¸ð??ýÎÐùü¾õ@ú°ð(òPìnñÞê?ô°íFöôóþô?úpóFþÐôÄþÖøþRüôý?üxÿ6ù¦?ô¶Jñ¶ øð&vò¤ó?²ñöTðd÷$ò¸é ødÞÐþüØF?Úêüáôdéëªñ.å÷¨â¬öXâ.ñúâzê´ã<æÈã|åÌáºæ*ÜÈæ,ÓÊâ ÊÛ?Ä?Ò(Â8ÌÞ½É?³?ÉÂ¥\Î?ÔÖÄ?¤á?£rìô®Àóº»?ôVÊ?îÆÙ´äÐæÎÚ?îdÓ?ïþÏpê|ÑvàH×jÔ6ß?ÉèæDÂ?ìD¿vï?À¨ð ÆLòäÎ"õ0Ùî÷¦ãzù&ïòù ý?úH¨ül<î6úRþ® J¨>vê &R´¤x&,þê*¾,? ú("Ø ?(¼¾4rn>¤ûÀBxóî=?ðf/?ò?PöH úàý þ,ûÚÀþDdì.?¶îþÜtýðý¤?H 6¤Î pb?~!,d Ð2ü\N "2t$~># Ò¨f?ðRÚû:pð~þÞå^öþÞîÊÝêå"âÒÞ?ëäØôùèÔè ÕðÛÒ"Jæ?# ô?\øê?ØvýÀ¼÷¨Æñ>Fê&øìáò$ÚòHÔÊòXÑZï°ÑÀç?Õ?ÞîÜLÕlærÌÜî?ŶôÚÃù?ÉNýdÕ¬.â?òêî¸îªøïäûØð¶òJñ?ëðÆèÚìì?éHõäè?ì ?òØú,?? ª 4?Ì¢(äÒ t@ ¢êN>®þ8äôÌ?ë?0å02ãÞûØä$óè?éëÌàêìTÚ?îÔÖjð,ÕÎñÔVòØÔròPÚ¤ñ?ä´î@îöéFñ¶äíß,æ(Ù@ß?Ô$×?ÒÄÍ*ÒÖÆ:ÒźÑäÅdÏ.ÅØʾÂÆÅÊÀ¸Â?¾Ã:¹?ƱÍ?«<Ö^¬Úá²RïعØüèÃ?êÑ? ¤â>öò,?þÆ?ø²Fðò Då:ýlÙüð?Ñ?éBÑ?ã®×ÚÙ6áÆÍðê®ÇØóÍÞünÚjðç?Öñ®ÄùR?`^ ê??&öV.02,î8üðz$Fþn&Æûè%æúÎ%Xý(ê(,| /2?,? "ðt?f:)ö-~íÐ&6ê èë~®ð(û¦õÜôòø?òàúò®ü|òRÿòöXîàLç ²á?°ã¶>îÔ¶ûô Ftò ª|øpròØ"?ñ öòÎüZæ~??à B& ¶ Æ~pª2 È\ RÔ,ü¼Äðü?è?ö?åPõ@ç¦÷zìBú?ôtû>ÿÜühhz $Ò?#¸"?V,XÎ14?V2^+?+?9º Bj(HÊ JÆ$Dl4?b?VDø¦¶$%.T%X??bM?ÒVêD[ bZ¢(SF#8T*"1".D4.®<l+¬BF'ªBd"j>bä8(*3.Ú-?+Ê t, 0V2(0è `,H*|Þ+þ?/ÈþL1øz.pn'p ?TfÆ?& ?â 6Ø 8¸êê"D?/Þø;Ú¨F0?Kèü¾H÷AÌô¤7®÷Ö+öþ??þú <ú´$ôàÚö¸tÿP$ ®ð²LÆÜ ¾Î ª?0®ÄF` Â$'nî.^$p3r*,34/â/<1X,ø.F*ô&Â).N*F,¦ôð.çD0°ä+Fï òR?öt+Üõâ4|4ÂR+<6äCv|H² ?EÎ\<¨Æ.ª¬Úú.(ðXxdÄÄ?)? 2&à5ò2z ¦- "'?ú¶ ? ?Ný~ûpíØþjÞ?Óz Ïl?Ñìõ"Ùôäîã"ÔÜð È?þÆà þÆÞ,ÐÎÞözïV (?2ü h®Òüö?ò>¤îbû²òLíÔúZâ¤ØÜÂÿ?Û÷HÜ,éÊÝzÜXà*×å Ùìàß>ó¶çJø&òÜúªÿ?û? ú¸öô?nëÄàèÄ×úüf×^ñVàþáxï&ÒÞý?ÉòFÍxPÜ0ølñ²è?¸Ö,ÆÊÖ»¼ P»"òZÅ^Ö×Ú»fê°¨pøj£þÐxüÂ.÷ÔÖñ¬äôë¼éJèôè4æ0æ´æÞâ,êÄÞ?î?ÚbðÊ×6î?×âé?ÙÎç?Û<ëtÙ®ó?Ñ þ?Ç^`ÃH °Ê´ªÛò¸î ý?üB÷&?ïN6çVú"àFó?Û?êÜÚ¦Ü?Þ*Èbå`²í`¢?ô:?hüÐ?Tð¦Z?·öDɦv×? Øâ?XîzúÆùÜï?"æzúÞþüºÛPópÜÆæ¤ßÄ×bãØÇ?æ?¹éj®?ê¦<ë: ¬êÞ?úç\?L⬣êÚª¯´Ô?½RÑHÈ:ÐTÌШÉÌÏTÄâÎÔÁÆÍ\Ã<ÎöÄ?ÒDÃ?Û?¾¬çb¸?ôì²Ð²6 à¸êÅÚNЪºÖæÛ|ðæáÚ?êÎÇ|êè®ÁúÚ&ТÉìä2»vù¶Üì½Ì Ð? åRêõp|ÿâþ?^öÔø° Òðøhí?.ïþ?ñ^?ñ8ºñ0Øö ÈÜúê Hë@á:0ß² Òäþ"îPè`õ°ÕÚôxѪë¸Ý&ß`ñ Ö®ÔÐ.Ø`ø<ß?â~åüÌ?éæÂÂìÈàð×ÐôöçõÂö\ï8Då¨Û¸Õª ê×ÄRàöÿFê?ånñ$ÔÊóØÒ¬ñßBíÎïFéüçXþJæüö®æ¬ê?ç6ß¾èÆ×?ê`ÔRí`ÓÂðÓFô?Óüö~ÖZ÷?Üdô¬ãï¨éºéFîPç,ñÄérðÊñëbþrãZ VÞ?rߪ)Dçø1Zô´28+lHÒ(`6xý\;Dõä5ZõÜ%TüÎûªnìnxçf&$ì¦)<÷ )??$?~ZÄ#´$èB ä°¾ZðJ B F"`+l%X7?( @Â*êD^,´Dê,þAÈ*²=Æ$°7â?0JÎ)j&$öö?|ï(é2?âêÿdÜ?õÂÙ?îÖÝÈéîè?å÷þá?â ?é¬ù> ¤ìè0"f ¼ |4ðXð¤¤ø?íÈ òçn èæÖí(ôdø?ùxñ:þÒînÒðâPóøóÆ,ò,jô?ú¾ÿ¬.? öÿ¬0ûºù?øZýâóä?ôÆ ÆùRºû:ìó´ÆôbNý¦$ ̶àö(?ª1&û:®öÄ;Ì÷r1ZünÄpÒ²öb êô?ý 2\È?'JÀ0V48 ^4H\6ì; C>!zKÊ$,R$?VT¾W6²U?ÿQÌöI?öÔ9þ@! xLòvïZVú0ü(¼ò¨!4ì+êV&HëÈï? ,öJp¨úÆø®jö¨Òö¢$lü$%ôÐ"l?$¨à- º1Ä'|/2ò)ø9?%"<B&þ8Ê-4~:?0ÜF/´Kp-&F* ;²%ô3?#0'?*/Ä ¦6ðÞ8?4&?)ª1?:G??U°tV?8Jî^7$)?&¼9L6H?fQZ(¬SF<úNæS®D|h6?v?#¶}2~~ûÂwlìÞiväÆTLä4;ôéZ"ÎòØ?ýRl ü þúäXþ¾$?&)â?+Þ¸,h'?,F0)?5¨ 8âV9À b:?b:üÿ 7Ö?/Ðb&ð R Ôl¶¶HÚüD*Ò?æþ?ý:°û¢èø ÐôÔþ²ð?û°ï?ùêñ?ôzótíäñ²è?îêì ònìý²ï>Pó\äôdÌôÄþ¬ôXèõ¤ÞøzÐûNúúû´í?øåRò?â¼êärãæàÝ6æ\Úòã<ÙàÛ`ÛfßÂÖräÂÓbé¨Ó°íN×?ðúÞòé?óªò*õÖùÀöÿàøþ¤üH^v ? ̸öúøÞ: ?nÿ°2ôl?él&ãöôã®ôì~ü|ù$ ¼òdàò¢#?ý?%V?"î#?à1?f8ø Ü8@`3?&ªd2?&?ü*èþÄ&?(B$?,`#??æ:ô)êûæ.¸õF+ªýü?ø ?&æý¨6vò^9®ì, ìÜ&òªúhühçx ÒÚHÈÔêÀ×, 6äNõ²Ô ?ä?XºÌ * ?Öh?ÂþúÄ#`ñ'2éz%"åÄ ææìní(´õºéDý?ÛàØج JÝì>ã ç?ªç°æ?ù¢çðæÎì.Ùó¾Ó?ópÖ^ìß?ãÈêàT÷?â&?çêî´ö?èý*6ºt ª V°øü?ì4ûèâhö>ÝvñêÛ?åìÞvÕäãFËèË2ì(жñ\×ö?á öfìêñØòíõ°ê|÷¾ìJù6óbô(û0èúÿvÝÔþÜ?øÈâÖðRêÚêní:è´êPèãÐèFÙfç0Òhã,Ò?ÝVÙ×?á(ÒãöÏHÛHÐèÎtÒ0Ã?Ö´ºRÜæ¶Äâ?¸ÞèZ¾0î¨ÅæñbÍÊò?Õ@ñÀÝ0ïüä@íHê êÌì`ç¼ìÀã(ìðÞÔìLÙ?í¼Õ ëÖÜåÙØÜFÝîÐÚáÆèåRº¢çĸèB½êçàÁ°çlÃJèlúéæÃëðÄdìjÇ$î8Î ï?Ù?ðJäTòÊèõ2ç öhâºõ6Û ó(Ò¦ò¤ÊõvÇÌûÒÆþºÅ, æŸpÌf?ÚV?êtôô¾ûù?ò<û¼éþý|ä?Då:ì²þ(÷h÷¸tìZ ?âD,Þ?Là~ æ>~ì"ù¤ò?òàøÌíþ@ëêÿÀê?ý¶ê?øéôòÎä?îßÊìºÛÞì4ßþëÖépçøàx:Ûh ÖÞR Âê8:øBÿ?ÿ~ö(ý.ëò`Ý?ãðÏøØlƦÕÜà ÕTÉ Ð:ÔÐÄà?¹0êäµ^ò¼l÷ÌÇLùÞÒ¬ú?Ûý,ä<ÿ6íÒó¾Èó?ºò òbù?ò?îòå?ñÞ ïÛ¬èfÛ`ÝÞ$ÓôâRÎÄé?Î?ñ²Ñ0ùF×Æýhà?üdë¸öhôhðlú¢ìªþ?ë.ÿpìê÷Òî\ê6ñ ÞFòlØ?òxØÆò8ÜäñDãï@ììöóÂë|øÚîúôêøÚ÷õ?öXñÈïò*çøôßÚý¨Ûbü¶Ûòúà<äHêHÙðõÓ6?Ь ÓªìÜþ 2íL@ÿäù?Àðn&ê?*ç~øç?ôìú Üô?ÒûäÿbøþV?ýhÒûæRú¼ZøBBô6üèí?ôÈçÜïäìã?æþä,ÞªéÚÕâïäÑö?ÒNû?ÕrþØÚJþøãjû?îJø:öúö,ù?÷Ò÷¬ø0òfø¼é4õÖâ¸ï?à:êá,æ0àä?ÜXä ØDç$ÖÜëòÓòtÐ*ú?ÌÈÚÉ4¨È?èÊ®þ¾Ôú÷¢åòð<õÆëLü¶èBü°åRù?áLó?ÝênÚ?á?Ø?ÜÌ×ÒØØ?ÔøØ?ÒÛDÔ6àÜÕ?éÌÓ?ö?Ï??Í<"Ñ#ZÛ?+xë*+þþÀ Ì <%èö¶3â¾;(Ô$:®Ï,-ÔBÈÞ.ÿTíZìVýpâ? àà Úå?ðªJýî DzÂ0x² ì@\?ÞÐ÷° Jíì#Hè0æè¼ 2íúôÞò\ç?÷hä*ùÂê,÷h÷ðóz?ò?4ôâ?÷¾:ûpþþÞ÷Âÿ¤õ@*ö:÷râ÷j?øzúûô6dð´ ¶òüÖúøX8? Ô¢l :?ì ?(¼?&öÒ~Xl? ? @\Î??!j)? Ø-º¶.#P+ö+Æ#Ê2?b9L6>ÆÆ=Ì ¼7(0Êâ+?(?-81X5 8ô@¤<JL?>HTö<¨Y?8 _F4@e¼2@iÄ3¼g?5Â`6ÆX\3Tz- Rà&pN¶!®G,L?!N6ò(-?5ò%ÐC>%ÈO|,*U?9¬QlI*G Z<9i~*pqjÞn¢~bèÀQ¼Ê@1 æ$P)$?/ؾ18$F/*|'B0j?4¶À4? <0??'ôØTØ*âÿ´ý*Îú`P÷j °ôn¢ó?`ôR ø,ÿ¼þàõêðº6ðð:ò.öª&úÐüüØúìòÚø¤ëøøPé¼ûíöÿdôʲûÂh?ð Êÿö¶þ: ØüΨü ðv02r RNòr¶õ êö ç\¶ïR?üHâB¨0lÖ °$ p (ʶ*"¸(Jt#,v;2C( ü>l"1ÜÎêtdfÿ?~ý^6¸¶æ*(Æ6 P<b0<ÊÆ7üê1Ì .db-r,¢d&² *¨?D$t,àû*-Äýè'& Ô?4î)?/¶T,ìt$ 6dÔ¶?? ®&.¨)`")?( ):Ê+°D40HD¶4$=¢8v8&;Î;^;òDî9xNò78V?4²\¶."`Ì(Ø\À%RÊ%¨B@(?1N.4!î8nXFà$RnWZ ¬Q âB8??/äOD?Xx¸S¦B¾-?$Ö?/þ:N¢@f$¶@è/P;8883¤=B+Ä@?&¸?è& 9*à/,0)®)Î'?",+? 1"Z8º:¶ùX5û?),~òî ¢-&85b?6?ô2B+æ+?3D!¼7?¦7@d2bù)¦ò¬òòö² øüDàLù>Xøtûæÿ|´¢<?¢²?È ?(vÈ Î þJBöJ¢è<ÿ&Þ¨öÛPì¼ßlÞ>è,ÎXï?¦ñÂÂPðæÎ?ï(à¨ñ®íhõñî÷ªéö÷*Ýhö ÔâôÒó°Óâî>ÔÄçðÓ?àVÖ°Ü<Û Þ?Ý?äZÚÔë Ô?ï¼ÑjïxÔ`îzÜLîÎæïtîlï?ï¸íZëüêÖæ"ëÞåòï6ç°öÎæLüä?¾ãp?é< ¨ò?Îø°èù*<ùJöú¬ ?h¾?õêâÄá¬fݦ®ß<?ê0õú(åh¢Ùü?ØÄ?åd?ü (¼b"üü*?ù¦+úx&,ün¤üT êùnBõHþ¾ñVü?ò ù`ø,ôºÿ?ïähí^ï@ÿ*õ?ú?úÞôTù$í¾ï¶ã?â8Ú|ØþÒzÓ(ÑFÐzÖnËàÅòç.À|ê?¿?éBÄPèÀË0ç¤ÐÈåÊÏvä|ÌÂã?Ì?ãVÑRãèÖxáFÚ´ÛæÛ Ñ ÜTÄÛ?ºvÕH¸´ËZ½j¿üÅè²?Îl©×¦há©Jîò®ôú µÜ$½vþ¢Êñ?ÜâÞÀìpÎÊó(Äî<À Ý?¿É(À¹(ÃP³ É ¶BѪºdÖƼ?×Ö½ÖxÀ?ÕʾÙÈÀBã»8ï^·Ðø8»üÆÆÜöBÖÊéèäÆ×2î\Å î?·ôä²?Òæµ?»zÀ¦"ÍZ?L×ò?îÛö¥@Úh¶bÓ¬ÃøɬÉ?Á?ȽV½Ƹ6À¬Ä¢¡ÆÈ??æÊT?NÊ$?^ÇÚ?ÞÂJ?|½??t¸ Bµ&¦`´?ªþ´?¬$¶ «Æ·^¨nºÆ¤¾&¢¢ÁÖ \Ä?¡¢ÅB¥øÄ«?Âj²ü¿,¶6¿\µ?À?¯PÃr¥?Æ|?ØÉ6??ÌÖ?ÆÍR?ÆÌì¦"ÉV±ÄÚ»¢¿ÎÂâ¼ÐÁ ¼ú¸î½0Â0£?Ç??RÍ?Ô¶¡ºÛ@©ôá௸ä0´"ä0¹.âFÁ4áºÉþâ?ÎèÒÐ$ïÕõ>ÝP÷ìædöÔïÌô¦ö®ôùDöTõÄø@ï?ü¶íÂ|óØüºt)j j0P/"%?,Â5º?>|ôxD?éâAFåd3<éBõVHFîfjáªá?ÄîÚh?Æèú+Èò¦1?í0üç4'|à޾ضþÓæ ZÔ>?Ùöú<á¢í>é²ãÈð:áè÷ å`þ¦ì?öÿTÀvþ?´ö?ïäÀéL4åÎ\âöÿ&â?ÿå~zêÂÈðT>öFú??ü&Fÿh̺, 8vè," (¤ær¸ð&êÂ(Z""?ûöFø( Jø ?üNäl?jPR ?Üø??+j4Jf7TP2à#¢¨ê ~ú?ø bùv @?ÚÖý¶?ùhøâRü~þ <??ì?JÐ+@ì:äù\B õVAø^7¼ÿz$î Ö? d¦# ¢¤è$??2Dø<ñ:ï¢,?ððøòx Bõ¸6ø.òü2Únºô¨ ?¼îþ$,D? ¨ ¬ æ î²48$ÞZ0 ¼t":? $Ê$*p6.X.(.(ú"FN'd?*n,¬#Ü*^*&?.ò¾3?@: T=J ¶92 ?0f@$Frîä z& F ðä âh ú Ô¾&¦¸æ,¨ xüj ö÷H ?õ? ?ö, dúªlý?÷0ÄôØ?öâ-?û?ÜhLnÌQz% MF8h@¸Jl,P[þòe\¤fLöÆ\<õ2J?ÿÊ1ÆF*&J ?7DBZØD°'"Aþ;?:?MÈ3Y ?[¼+tUp)zJ*%r>´?3TZ+?&øÎ" °?Hè??"ä¸+Hº0ØÊ/Â*F!üèNòÞ.\?¤Ö¼²àn 0 ? þv ¬ <Ö fôýtû?LüpÒÿ´d ? ?P.Þ?¼úø?ðDªî~$?ó¾,&û?5¬0;6 è9~?1ì" %n0=ú@lä6üp#j Ìâ$nZP#Dp*L-t+6!?&?)~"¦+¬!Þ'Ú$ü?*´?0d z5Bn808Ä,3Ò:*°Dd ZJ¾LIà?z¨.è(d ?4?*=ÒT@ 'È=ú4 6ÄA¼)¨H~ÈF?Ð>¶Ô5²üd-Fÿ?#¤`n@Zä ?|>z (& D ºÚBþÖõVþðnýÒìØü?ê?øÄç4ðbäZé*áÈçß`è,Þ^æÞÝâÝ?ÞàÛ°Û?ÛÔÖ0Þ¬ÏläÂÈNí Ãàö?Àþþ?ÁhØÈ6ªÔ:ûvßlð|æÎä\ëÛ¨îbÕ íÕ2äÚ?×?âªÌØëÆüóèÃ?ù°Ç´û ÒøúDß*ù4é@ø2îêø*ð4ú°ï?ú?ëùå öàþñpÞXí?ÞÈèBÞPåzÞä"á.ælå<ìÖèdõÈê¬ÿ¬ìv ?ï*®ó\ú d?<,lÚÞôF%æç('¼á!zâr?è^öBòèà?ýØö^Üê pèX?÷´??ø²>ð!2í¼ ?ífíþ²é?êæzà?çðÞVðzÞÀü¨Ø?ÏÔ îÊ?6Ô >é?úÖþX÷? ®ô0?ðPþÆëÂ÷´è,÷Fé¨ùTí®ùÌò?ô?÷Øìû"èìýÒè?0ì¾îü?íÖíDï´Dô?ù¾ °úÚ ÆùØ Pú??þ( È, ´ÂFìú"úZ ¬ÿ?# ø).®3¨ì<?*@?$z:î0n-Ð@HØMº6Qt°HÊN8Ô¨'ð 4¼@$ò%Ø&ì(Ü-?'?1ì"Ê1`Z0`®-î&)¢Ô#V8!?ô#Ê +?ÿ?3?ï¾:¬âî<RÜ?5ÈÝ\#æå(ò?õ?ÿþèôDç¢ï.ÿîNâè?Ô$ºý²$"ó*xé¨8á?ÛDùÖÙÒñ?ÜTîlâÆì¼è>ì´ídíñFð$ó2óbô?ôÚôÔôìôõ õÔõ?õtö?öHö`÷`õVö$ôîñ¼ò&êtñdàØðêÖpðôÏîhÌÀçÌ0ÞîÎ?Ô¤ÔÈÍZÛËÜà?ËtääΦæZÕ.è?ÝêÒã~ì å|îÔáhîlÜðëv×ðçÕ?ã´Õ\ß ×$ÛÚÕ\×þÑÆÕ°Ï*׮ѸÙ8ÕÛ"ÖbÚHռزÖÎ×âÙ¾Ø?ÙòÚ@ÓâÜËÞxÅ"ߨÂ?àÖÀ2ãp¿*迨ïBÀþ÷äÂøþäÇDÒоÌÝ??ì(ú\D:¾ ù.RïªZç¤åÜ ëô¤ô?Üýñ?"êÀJîvør? Ö Dâ>þj&ù4?ùL¼ÿH???Êø\ òõ|ü&ÿÎN4æ,üT´ú4ÖZ>ð\ à!hÿ)RôØ+âëÆ)bèÔ#Pê:®ðÂ?ù?lÚÿ ?þ??ôvÚÞ?ùJôF¶óÚ ð÷"?ý?ûöÿ¢÷rüºúæó¼øérHâ`¦Þ¢Lß?ãôvêVæÜòêÜü´ØļÚDÊâ`Zî.¸ú&èÞ>ù,?êvß@ $Ù6ýjÙîJßRãé¨Þ,ôÂàVþpè¨vò? rû¾ Öò Ô¾.X Zø hÌò²D,Ðÿ"?ò:|ç8(á?DàÔä>øëÌìÊòè¶øvé¾útî?øöõ¦ó?ÿtî^öê¨Vêè?ìBäï2ø>òXðòé"ï?ätê0ä~å?åbáHå Þ?ã"ÝöâÜÒãRÚ"ä¼× ã®Õªá?Õàx×^ÜnÚüÕbÝÔÏ°ßÍJáúÌ^âP̪â?É?áÆß²ÂBÜj¿ÄÚð»\ÛÚ¸JÝ?¶?Þ>µÞ¶LÜBºÛ ÁÈÛzƨÞVǸârÄ?æºÁ?éXÂvì&Æzï?Ê?òJÍJõFÎîöÏ÷Ñ&ö&ÔNõTØâô?ÝÄôHãDõ?ç$÷téÈúxéÔÿ*èìä¾ àl ÄÜV°ÝÔ âȺæ|ôêd)?ðÄ4ù°<Âä>^`:Ö$ò/À7"I0V¦ T]J*^ÒWtÐJФ:À#È,)?%â)&Þ&?+*!2*f6?ê7Ʋ6?|2Ø°+Ö$Îþ!??%bþx- ÷?4?ð®60íp18ï?#h÷° ðRõ°0ãÆÝÒüåèÞ÷<ÒúN,ðJ è æâ?ü¢áï åtçTí¤ätø?å"?é$ ¨ïH`÷ò àÿ" ?¤ ¬? ?*ûX÷z!Vøâ#ìý¢ ¶<¸p?jÈüzdüî $L²`ÿ>?þ$4 JÚZÄ??ì? &ÎÊ.ìÂ1ÚÂ,h!h²à¦ Ò ôÖðÒÄ$zÚ´d&¶4+î$.H ð.*T,?H&.ûh\÷üì÷p²üB¬tþN ®òÊì?jíà?ñ?hö? û.ÎÿØúêÈóðÊïhvï þ>ò|õ÷¼ë ý´äJ~â (åv ìêþZñöööØûf\Ø&öàÐ ?ÔÈÈ° X ?J:¨ü\¼õ2ð&`î$ñÎhõLùpRüèÌÿdpÂLJ?Æ h:b ||*Æ?¨þ ?¾ä?JƤxvð$ ¾,Þzÿ¾ üΨù(n÷>ýøôüøRò>÷,ðø\ï?ûNð?Äò<ö` ú<4ÿÀZ?² û?Äñd%Fîf-úò?/lþî), $H(ìú/tîn.Úéj&0î^þùú ? 6"Àýø!Þl%h(#8!ªð,>¬2ªø0 ¨( @Æ"zj(Æt*úB'β\H&@Ü,Òú"0ÎõT.:÷^&¶ý¤?.À ? ¼ÿ´®ÒþÜxù¬ê÷tìù,Dýî?ÿvÿâÿÄùàþ²øþ\ú2þ2üâþøüÆþ?ý(ý>júºÀ÷®¼ö? ?øêýDÿ®õ?Nîü æìÔ Lñ?ù&¬°öÿ4ø0ïüçÀ ÎáP Þà¨<äf?êl¦ò2 (ü&ºìý2$÷ê?ôÔ²÷Âþ2ôú¾ê¨¦æ$êD,jô?/´n-rH& "??/RF8rÀ:fý"7Rûö-¼ý´NpÔ JþTèòjÀíX!øî$hö~#¦ô <¾ j(!là"Ú?"Ä æ"Â0%&þü(@ýè+lÿr+À'?º NÒfFîZþÞV¬ ¨ýÔòôl®ë8ã??Û??Õ ?Ñ?ûØÐrî8ÔºåÛæâ ã¨åâè"ìêêPôºé?ü>çl&åv@äJú?ä~ë°åÚÚTæÐÌ6åJÃHáä¾`ÚÀÂÑÊÅÊ`ÍPÆhÓBÈ´Õ:Ï?ÓpØάàÉöåÈÈ(èÎÎVèØÄç¼ß6ç?ãêæ¾ãàæFáçlÜâææÕäå<Ï ãÚÉhàÄÆJݸÆ?Û?ÉHÜàÍdß?Òtä?×?ê?Ýrðäîô"ê?÷äï?øöõVøü`÷¼õ?óâ¤ñhÌðöâñtõjùPÿ8ý~ýÜþZÿÄýd?ú"tö:ó&?ñ@¶ò|6ö|(û?(¶üÊþ²º2pæ²?j(ìèLù¦?ñr ¬ìúì ý?î¦ñØð?åÜï´ÝâêÎÝäå?Þ>îݲò?àî¸çâRð6Õ?øèÍ?ÿxÎÕøúÝò ?æ| Zî°:ö°Üþx¾ â (? ?Ð8Þü? ²÷â$löÞ"îùJ? ¨¾z¤LP|( jR\¶?Jx ئL?¨#ZL,P?7¢l@èÀ@¤$6v?$T%ÖZ-?3ôð4À!Ì1V3?(?D>¦QÐPWò?T°â6JøÛ?9ªß¼#8ìh ný¤þè ¾ýâÆ H¤lê+¬Ú5\D6~?+^nÜö ,õH?ïäý?òºõºûþîÖëvJêbíÖ dóxNü?þ¬?ùâ¨ö®Ìø< ⺠Nÿ¢Àør*ôºPñ¨zïÆö:îÈåÆíÚbî?ÔìïªÓòÔÕôÖÚHõ?â?ôúë&ñõ>ëBüä&vÞfÿÝÆøÔà:ìXè&ÜhðBÍâõ¼ÄD÷?Åõ Î?ð?Û¬êxæ~ãì¤Ûí?ÔhìvÐë¬ÐçþÔzÝdÛÏ,áÚÀ~ä?¸"åÒ¸*äÀÞâhÊæáÐÓ?áVÙ?âFÚ?å ØëNÔòªÏ?ù¦Ê²ÿÇFnÇV°Ì¬ÿÖÕ?øÖàäîlëNäó?ÛvõÞ×ñ$Û æ:åR×Øó¼É??Ã"?ÈèV×´Èê°?ýª èÆBþ~Ìø2rúZ´º¬ø@ó>'¾õ?'þô!Ü?¼Ä®~(,D<,Þü? X¸bJP?úØxõâònnñlfôö |ú à?XL?èù&Nör ÷Æ ?ûÀ2 J ?ýüZùTPõpñB?îÿpîÎö:ò8òºùdò??ö¨¬ý& ¦0<ýR?ølløPü?ÿp®ëxÜÛØØÔjü2Ö÷èÛ?ò âpï?çÀíí¾í,óðäø õ üôü ýª:úÜ`öõ6!2ù%f?$$º ø$Ü-NõÊ.?è®&Âá\2â®ûÈèxãÖòÞÒNý?Îv?ÖF ç júÀöÂûðâõ0Âó ÷ú?ÿ²| ¨÷??ð?¨ñ"Vúl Ð0føκð|äìzrìîv0ð>ûzñDóñ>îòîìì&ì?éîìèþì èúëêpê$î?è?óæ~ùÜâ?þà²ßìêßjÿøáäú:äèôæÜíææ?æ>æà<äHÜèàzÜZÛþàÚÒºè$ɬñÂ?ùÊÀÂþtÅJÎhþö×ìùá0ô\çïÐézìÚè4íàå(ñ8âX÷ßþ ÞØ"সäH ?é @ìt$ì@ ´é` æ ¢âî äá:,æ?3ð4ÿ®,$?#Þ¦1Òû?:?ïB<<ë6ÔðÎ(~ÿL ¤&<?4?d:^ 8À t/È2?#BdÚL? °Q®OL¾DÂ5z R%?NèþÌB$F3d ¸A °L|rSZ?VêTÒàKö²;&>)?B| ì?÷ ?ç$?ÞÄ(Þ?ä2LîòLø¼êlÒîÚ´ùÌت îè*?ÒH** ?¦ ¶ F? ÊÒN¼¢¾ ?Rþì÷`ö:Êúì àä@þ¶ b2ÿ?hü¾!BýØÈÖ6ò ü DúÂàþØÎ>& Ø(,Þ¶~&Xb.l ?4Ôæ6Îð2è¶)nü z÷¨Nójñd?òºröä Èû?ÆÔÿX²ü¨æû`pü¸ üL Øùb 8÷?Ôö@`ù?ýâüxùæþf÷ºþ?ø ýXýfú?\÷ÄõÚø÷Ü"?þz%ä&":T~Hì$4r(ðý%ìøBø÷°û@þ?úþ¸øîúÊXüÈ?ý\ $ý? ûDêö^äñÔ.ïÆdñ6Ì÷?ÿ??: ?0ÿ Fõ>î?2ì`ïxöÚ$ÿèýüæöάóÌ?ôÚûô÷¤òÆû ê?þbãÀþpÞ?ûhÚ õ?ÖÖî?Óè¤Ò"áòÔÚ?Ú°ÔXâxÓ?êì×àñìál÷?ï?û>ýÿ?P, f?xpþRþL÷Þô¦ò4è ñÆÚ ñÔϨïüÉöê?ʸâ?ѤØnÞÞÏìîÔˤÿÎÎ* ìØ6<è øù¦?ê :° #¸ 0$"JØðº0D ?NÄH.n èæ X? äÀ? ?îLf D®J!j,#tüÞ"?úP¤úä²ûÚ ?ür~ü¸þÌûØüîûVüJý¬ú?þ°÷îýÆõúúª÷röìýðñï?¶îÀ `ð´ðòòö?õ¬ì?øîæ¤ü?åÆþæÐêéH dí4Bñ2 ?õà?ùðú´üBøü?ñ¬ýºèºÿhàÀÿjÛ4ü¢Û¢õÈàzîèÒé8íê¢ì?ï®åþøÜÛªÕ èÕÜâÞ?&øì¦,Jü?-? B(?tF#Dê,?þ1?ôÔ/Lî?%Üï^ùúþpònüæP Êâ%°æ|#hñPÿΦ¶î?.b?p Ä??zÆ NB Ä!þº+t0x/.<*#r#&8Ô$øô6 ?f&à,ùþ0Àì^0ä¬(?ß"èÞè`á ò0æèìì?ælôTìPú?öJü?Þù? Øô¢fðúTï¨ò?vö´ólù@ã?øÀÙ@óÌÙ?êôß"á?æÞØhêæÒ*ëöÏ?é|ÐæºÓáî×2Û?Û¸Õ?ÝâѾÝ?ÐÜÎÑ$ÙNÔNÖÖ?Ô?Õ~Ô¸ÒLÕ$ÎÖÕzÈ4ÕTÂ@Óð¼æÐâ¹ÐĹÊÒ?»ôØ?½NàF¿Òå¶Âtç`Ê?ä?Ö¦Ý4ä?ÔÈîÖ˼òÎÅàîdÄPä?È´ÕÒ?ÆÌÞ®º^ëð´2ô?¶`÷¿¸ôâÌ?íªÜÐä@êÞ¦òÛäô?Üñ?â?èìëߺõVÙ&ýÚÜ°à¨?ë$ø¦ ¦ ? ª? ´.ìXÈî,¾@Ân¶üÜrú8ÿæû0÷ø?ò&úòøâ÷?ùÆýîLçìèèHÿ ò¤þèý6°Øð?Ìý??÷jløÒÜÖV Àè®? Ì ¸? Ôÿ¶RôTìî8~îä!?ðr'Ðó+ªøæ*òÿ¸& ¨üRèLF#:ø¬Döxt6¼\ 'RF.#/L,¸*p1|%ª0Ú#+ª'ú"B/ìÚ6.;:r;`Ì8êx4ªûÞ.T÷(?ö ?úøÈt ??º?j &?.Æ:"?=ZÂ4 F(Øähº>x´n̲¤x|à* ¬h(´0.ê3Âx7J&:¤Ú9ú66?2²<-ÀT'4Ô!è´¼Ò: "î äZ¢Òà?¤Ò ?L. ü8.þ!ùð?ö\÷æ ?ûþTôöðB Fñ Xö?lý?ýÔ¾ð? Fä& ¼ÚV0ÕRü?Óêí"Õ?ßî×äÓÊÙäÌüÙlË¢Ù~ÎxÚ8ÓÒÝ\ÖÆã"ÖÆê\Ó\ð¼Ðàò¼ÐBòÔHïÙtêÔÜàã4Ü?Û$×rÒêÐFÊ®ÌÈÅÈÉÞƲÄPÍL»ðÖ?¯á?¦¾éj¥@ð?¤ôL¼÷?Ì?÷?Úòõåðò?ììï¼ð?îÔð ðvì¸ôvå¨ûfß`\ÝD?à:ènñ°\øÄ"ìü8$èÿÆ$??%д'x¼*¾-ðr-,Tv)x!?%'N *? +°¶,HF0Fâ3?4þ!h/À,Æ(Ä1?$&.?%Æ"?+z4ö;Þú <¤ö¢3L÷d#hú?`ýlþ?ýHý,þ.ûÈ?ùÎÌø`Tø°ø²!Nø">ú?âþD>ò ?ù?fñ(@ñ?ø?óDÒèN näp 8ç*ÞîðóÐ÷Öë ÿbîö,úªb ?þ~x÷TÌí`ã?|ÚªPØZßhïÊí?ÝðVÐ?@Î|ÂÙRæî¼v ,ÔôÈ%?ì|&$êH¬ìPtòÔþ?ùäø<hùèÔüØ?ðJÈèè ?êæðèÐÿ$ÿd~þÀÈ!üüD(øó?&ÄîHÆíê¨ð?æõÄø?û´òhÜóFìø¤ÿ< ÿ2"ÿ8&Èÿ¾È÷B¼ð¬úê`?çRÿZè\úæìÔóôñ?íô2ê¢òbêÒîüìêè?ðÊá2ôPÝ ÷XßÐú?æÐý?íúÿñ®ÿRò?û¤óÈó¾õÖê.ø~ãÖù?ß øÜ߬ð äläæìv×.özÍàýâÇ?úÆ öÊNØÒÔü¢ÛÜ÷Nâ.óNæ@ï¨èízéÔíè@ñ¼äööàpú@ßdüfâèû?ë*û¼ø8üfÿÄVþ D?Tè?ÿàNû?þÖøünøÎø*ùÜô?úZñ:üððØý?ô?ÿ úä`ÿ?¨ìV Är?Ì l'ø¨.?0r^,4$ì?xDb:¦Ör¦>6ø°ðæ?`d|<¨?òÆ,Z!X*J²0x?4þ z5Ö 2\þê)ÒùD?ùxäýÆVú? ¦ó?óº"úÜê4ÿ¢à÷¬ô>tó?äóÀ<ô6û õ4øz÷@ù?û$ûô?úºøÐìödZú.&ðüì ?ø*õ*Øðx¤ê,ÿJãn÷&ÜôÖ¬óòÑ<ñZÑbé\Ô¦ÞzÙzÖXßLÓ¶åÔXì>×BòêÛ¨öpàèùBã?ü åöýXèÒü^íHù²ñZõüò"óFñ~ó6ínöxæRûÞ¤ä×?<×,?ÜF?å¸?ïäÿ ù¸ýx?ü¬?ý?xýø?Øò?¦ï²¦òÚ,ý.t *?üj2x lzèÎ!Bö´,zñä3?ôH5Üü*1*?Ö"À"°+¦?+¸?&? f¬0Î?ntÄ!Ðê&?¬)&X*èV'2ÊXä (þæ.ÿ"ùÚ6ò.í?Lêú?çîõæâXö@Þ>ùÜ?üÛ°ÿ®ØxÕlÑ4ü Îv÷VËîï2Ë&âZϾÏl×?¿lá?¶ 몳<òµ?ô»dñ|ÅJêjÑ$âÛ"Ûá$ÖàäâÓ¶æ?ÕrælÛ.åFäbä²íºãõPârø:á?÷BâÜô¬ä0óºå?óäBõÜàþöߤø²àRûBåÊéÌfëzê ë,6ñ6þü* ,H(J: H ¢¶?>²%H+ ê+â¦(b$@!L-?2Æ?0¼>+? ø$ÈHþ~ÄtÜz !b!L?². \þ?n?Xþ?v?Ö JøôÐ 0ê ÚêØRöbüÚõ¢ àðäêì?ûëóúë?ëîbäªð¨ßFõBà¾ú?å?ýºë$ý ñ|û^øÐøüô¶Øò?Rõ?ý4ûÈóî?ìÌçÄè?>ð\ü®? ¸ý? Âóxâí ïð ´ó¤º÷àPû@$"?+8P*@6#¨tp.ä =¬?<È "/" "Ò ºHð!ª<,X3Æ18( %ª/B2Ò L2Lî,@dØâÈðÞ" bp/¾?<Ö?J 8U?ÞSþTEÿj1?¢ RXFÜènf?Ö$? $!¤ÿVlöÜ@î?Réh?êvpðÒý~õlýRö?þòó?nð|ìÔ è pãÞú~ßâèÞ Ý´ßDÙfãlÖèç¦ÓFì>Ôºí\Ùzé`ßPálãÎÙ4çHÔ?ëRÏRë?Ë?á6Ë8ÓÒÍLÊJÑDÈjÔNÇÀÖ(Æô×hÉ?ØÐÐ?ÙZÕ ÛÔXÜ®ÑÝTÑ?ÜHÏPÛVɤÛâÃÀ޾¢álÃjàDÃ?Û?ÆÌÖÒ2Ô¶àXÔpçv×?â?Ü(Ù¶áHÒ?æRÎtì.Ì ôÍ?û¼ÐD?Ô?*Øöý?ÞDüçÒý*ë æÒòÝÙX?Ûn,äÎÜï?ÆûðXT 6îì8?â4??îf?n®?HÈ ªj?òJðV¬h öè(B0? Z3ø¨0Ä\)Ä$* 0`º8zJ9ìF2?>'|#>!¼??%N?2\?9*þ~5¶û¨*?þ²\?\ ?x?~ô 6Ân*48ºÿÞ¨úîXø®ù°HþôÞÒ®\ ì"?úª'?õ&)Dò®!xñÌ?ó?È÷ùýÔó?óºöÜ8úØ8üÜ!¢ÿ¸fl°üJ"0ýx)ØõN)VõJ"Rû¢èn|T Ú.¬RâèfÖ??P *64>fNت?¾Ð8?êLv$?:,^+" <%P B6?Ð??þ´æ?!ê Ð `Þ0?¶Lúÿ?òý þòü,ø?ûnô~÷$ô²ð6ø?éúþ¶äPúâ,ã¸üÌãÊð ä¢åäZß~ärÝ8æ?Ü éfÚ:ì^×,îòÔúíxÔ¾ëdÖHèvÙðãBÛ¦ÞHÚRÙ ×àÕÞÒîÔ6ÎþÔÖÈ¢ÔÌÃÔhÁPÔ ÂÎÕjÅÒØÔÆÝBÆêàèÅøâ¨Çîâ&ËæáÜÎ2áÑ?á´ÏÜâæÊ?ä?Åç°ÂìðÁüóøÁ`ý@ÃJPÇè ÄÎFâØdãö ëXBî?Xïn¨ò?|úÂ*VH2Höt?(?<Bhjàd ªö úÈê"t´#z"´N ÂrÌ?T²ZÞ(+Ò6b^>@Ø &>8;$À4¸)?°(?<Ü è2?Ì Æ²hþî@ù?DöÎ>ö`þø¬ñ`ù?æ?÷¤ã?ñFíPè@ÿlß~æÚJÜÐ(á06çîÆì(Û?ñ²ÒäõÖÓ?úæÖ^ÿÙúhÞàÿpèêø?óðXý?é¶éòî2ùÂ÷?å?þØXÙ4üåøô2òníNüRè*æ?ü¦å¶îRæÖàÚérÛøñ Ü8þrÚ¬6Ö×̾à`ðît ?üÊü" ñXë>\ìÀ¨ñ¢ìè÷làòüà?ÿ*ç ÿfñÞûrü¸ö® ðâÞêjÿ¾ç>ôêèæNóvØÞ?ËÆÊÅ>êÌ^Vá?ÔüÖX¶"?ÿ?<þ@BöÐö\ úòÔö,?6?\ n??ùÂ"õ4?ô? òötñÊúhß8ÿ?ØÎèÙÒ:àÌ ºêâl÷?ØÊüX? <ý?ùæÌþ~Jø"@l!|Þ"?Ì"6F"øû(ñ?êV ?ç?þréBñbîìè`ôbèxùòì?üLñ"ýÎò8û.òè÷Pð®ôÜíòäìÂï¸ï0îö¼írý?í?vìÆÚét$æ?ÿ*â?÷?ß ðàþèÐã®áÜè´Û´í´ÙòðÛRö¸ßÀú(â?ÿ¾âÐèâNäã>næò?ëþÿÌó6û ýF÷ 6ö*Hùr R>È ø ?Òª ^ú´Ê0Nxfª®"?&0n)Ê´-Ö?2R5B4RD1ì,/ÌT..V-|ö+ê2+?,+6Ú*?*@Ê)2*núJ)ºöF%&ø¸8þ?ê²Búì0ùÂ?>û.ðúæ?2áÌ?ÞRfÞðþà8ò®âì°å ééªævìÆåHï?æ|ð¬æ²ï²å°íüæ¢ì¬ëBïêî>ö²ëÐþXäÒ à²âÚêþBõ¬ùþä÷¶&ø?ûPøxó¼÷ªï?øpò,ý?÷^Zúªdúä:ûº²ÿð6~,? ?0 À?Æ?ü\äöF?õêVù?$ú?- ´3FL6?#®5?/°1,:Æ*L@Ø#B!$A?$ò<?,4T4z*ø6È'62p.(@8ø=¢>¤P:bÆ3æ:+rT#?#?*`-.P*à"? 6,*<8p°A?üÚC¼õ®=jñ¦1(ï\#ÞîZRðR ~òê¾ótýøòúþ\ð.4í Dë?Òëx îÚþòâónõ¶ëzùxè|þ¤éêÄìÌï\÷²óÚèÒúJØò®Êö.Ä´Å2õÒÌXáüÕäξÞ?Ãæ ¤ë?Ç?îÎdîZÒæë®Öúè?ÝjçRåè¢é¢ê?èlîBäÚñÐß°ó¤ÜòóÛPófÛúñFݾïjß¼ìâàréTâ?æHäÚä@å?ä¦ãàåhàDéìÝHïÎÜJ÷XÜ?ÿÝú?àF äçÎjñd úZ?ÿ0pÿn¼4úfZö ¾?â Ê.|?P~ ¸¦¼^PÚÄÞª?Î?º^úD:Ö ???nvÖò ?XDNà?øB ìðÀ¶ë¶Îê?ÌîØ6ö?îêýÎÞ?ÖØÆ6ÜòûHåRôzð?ïùøïÔùªôò?û¸ènåT Ìé¢òFrû¨Øh¢\V0þ^ PªÜô.¦üøª?õ.Hû? æþ?üâdû??ÿ@Äþõðìòê.ø²"ZÎ!Øòê(B ?3?ýÞ5¨ñ|.?ê ê° ¤î|ú¢õ¢ìÆü?ê¤(ôà*Ô? ª´?0¦>ªÌ Úì ¸û: ²ùzú´düÊ ,Bþ ¶÷??ïÀÔê?6ëö®ð ýÄøRõ>àð^¶ò¤þùZÔ0?ùDòä&í¾üêF ¶ëäÿºîÂõÊòVï8ö,ì?÷Öêö¶ëò:ï ìJóêä¾ô(Þ¨ò²Ù îÌØJçÛzÞ"ßèÕîãTÑæè?Òüì?×Êî¶Ü´í¼àêfäFä?çÌÜÀç0Õ<ãîÏþÛvÎðÔÞÏ,ÎøÑÆÆ8ÓèÀLÓ À?Ò>ÃÒÑ?ÅêÑÄVÓ`ÀèÕ̼üض¹RÜ*¸Dà`ºîäοFéÄÆëJÄìÃ>ë>Ä\ê$ÇüéÉê\ÊêÊÍ*éÓrç?×Ôå?Úå?Ühå,ÜäæVØdéVÓ¤ì®ÐæðÈаö¾Ñxý.ÓäVÖ? ?ÛN¨áîHèÆðÐ?ù?!B%??(Ô>,l%z/¶-¾0?2ô/ì4Ä.l6l.¢66/t4^1Î1|4R2À66z6â9?3<.j>&¦@~?@*=`v8^?3l!V.¶)H+?,ò.Æ'ò9däE öJ FHF9bº(?üNü??õN`ïü¢ë¶êd vëxþÆì?÷Àî°õfòüö?øù¸ ú¼ Âù?ú?þT? ¶ª"?$®,.²ó¤Þñz ?ú~ tþ|?ù¸Xù~ü¬8jüF2ôHóÜõT"ú?)Vò,,+?&Ø#2ª8p:üB2Â& V ð ^?ýHüüL$v#,ö~% ^2ZV5,ÿú+ÄùTè÷dúúBv?ùP.÷?¸ú*!Úr <X®Æd$8ÿ&<ö6"ò?ò2Øôúù?òTý&òªÿlõFþ¼ù:ùÿ¼ò"íV8éþøç?ü²é*ôâí¾í?óÔæfúÊßz>Ý??à"èår*ìú ÷ÂðB?é ?å?Ôã?ôãhæzã?Ý"æF×ÊêìÒòï?ÑVó¦Ò?òÕ*í?ÙæRàÆádäÆâ?ß\è\ÒðZÆ?÷ÚÄfý?Î?~Ýâäë@¤÷¤hÿ Vþ"þ?þ¸öHÂí?åB¢à"há°6è?(ò2øúÊ??÷²ôìJ0çjÿ´èô6ñ¸ç4þvâ< JçàLó?ÐòV?t!¦(??&Øùº¾òì?ð@?ò¬ó÷ní<ü?ðt´úÎ"PúÂÀꨰ? ÌJ |ºl? b?ªÂ\ .6?ªtýä À÷ð?ó²ÀòL?õÞ`û?B? Ð ?ÿÒ,ö^òíº@ê?¦ëÌðï?ô`ôPêN÷.è2øZë6÷¢ï4õ,ó ódõ¨ðBõ?íæò~êîðé,ñêñ\í?ì(òåh÷´à?ûîâÈü8é¦ú?ðöBù?ñ(ð ó°ýòø>ð?ýå¬üàZõ8á?êènáñîÝ4óêßXèJäpÖ?èÜÊÈì Ìò Õù¨Ý?ÔãB®é?ðÊ`øÒ?xÿ&\ľܦ÷,ôè¦ø?z??òî n?r?Òû?¦÷d¤ô?rórùõØòú.ìFrçvèh?ïäJúX2?Ð&Êâ*\Æ(?*( Ð-? 6è!$;d"4< ?;¤?9¢N4Ò+Î@#L6Ú?ò2þæ?!|Ö&P?%xûø¨óÒxñ?âö?P®4H ?R?BÒ$¸6*ü)Üû ®ò?è îÞØõÎØñÂ×4ïÚ`ë´ÜåäÝÌßÈÞÀÞ<ádà|ådàjê<ÝìîdÚ`òjÛèôrßì÷hãæüXæîÂè??ê?ìÆþºðÎö¾öLð\ú?ìøxë:ò?ì¼î ð?ð?öHõVþÐùZþº?z?r@ÿæ,üà?ý?p?÷4ñb |òøüÒâÎ?¨R>"Ìv%? Ê$lú!ô4® 4? ??è|f?? 0 ´. ?2 ?b Úz ² ,r ¾ùæ*õØô4 Îõø^÷þù6õÖó®îPóBçn÷òãû,çò÷üîÈì ÷Hàü4Û`ýà?üÀé?û?ñÎû¦ó?ý?ðjÿ´ëèè¬ÿnê~ÿRïüó®@õ^?óÂþæñöûòzø?òbõRñVôJîV÷Ôé?ÿ2ä??ß?<à¶\éfÚùî´ ü¼îN*Úÿ¤)*jJ ?H. Æ0ê ì'Ò?-6/?R*d? f*àn5Ì<82 L.?PT ¦Øÿ(?ØÎ 6ªT:!FÎ(,^-öö.$?,¬æ$xî*ÔnBîB\êâpTý?#öd&°ïÎ"Èë?tì6 ÒòhÆý?ön ?óÂÊùÌ$? zx°!jùp#<õ?Bö??ûjô^ç°òâ@ æç .ó¶ü?¨ð? ¸åFà`Hâ:ê?XóVô?ú?ê2þ(é:þíûÈðÊõxñÐï&ð?êjîÒæì?ä8èÜãZã\äÜÝ?åØôæRÓJènÑ:éTÒÜèÔNæJÕ&â$׶ÞÚþÝÐÛ?ßÊÙäáfÕRäÓ èÐÔèí"×òô>Ö?ú$ÓðûÒÑ®÷ÔHï?Ø"æÝ:ßÒÞ¸Û|ÚæÚ`ÐnÛ¾ÅÝÞ¿ààN¿Îæ.Á¬ìÌÃð(È®ð?Ϧð®ØòñößzõÎâúúâX>áh㬠0è*Tït¨öJþü?°L¢Ü Bêý? îüZÔú0ø¬÷$ó?õJòÈöDòTü?ðÆlîÜÚï¶$÷îNZ?JbØ?t?nÈ*¾"(*Z0? 1¸r*¼¶ä ? &?þ?ºöèÐùJ´ò¬L4?ö Ê ? (8ôý??þ´p ²? ??Â! `#d$ü?'Z#ôƪ4Z ÎÐþ,þòøò?ì² ¨çÞ¼ã?û<âðå|è8íàätù8ã(räB?ë?BøhÂ(lÆ?Ü?ÿ? <ü´ÿ?÷¸öDóô.ñÒö?ñzúófýJô4?ô& ?ôpõìBö^ â÷Ä¢ùúVûÖòºüÄïÐýnñÚþÔöÌÿvý?ð¢v\P¸?PJBÿÄöüð`û? ?ùdPöñ¨vëüÿâçrýîç?øRëhñ?ðæéö(å ú´äªý.çþ,ê?ú?ìÊòBîé ï8áÊíÞ éHߢâ*âbÚ8äNÔÚäÒÓJåÎØèæÞÞNéxáXêTà`èßväNà*âLã?ä?äPëbáhòTÚ?õôÓjóâÓ î>ÛîèÄä`æ^é<æîå¢æÈÜ6ææÒ~å?ÌöåËDèÍ?ëÑZîúÖðß?ñ(èô6ðª÷Bõöú÷?ü>÷?üþ÷Xü ú"ý¸þ\ÿ®¢?? Þ dXÎR\ΤÈ??BüPÂ!6$'&D+%ê+?$(?'ê ?.?7Ìþ=XBð?Bzb?z%9- 20T/<./d)&/ô#J.l -Ê.*ö.´0Z¦3ÔÌ5D¨3~-??&H4%?¢)Üø/?õ<.\ú #,nrø?FýH$F#âdt#4¢+?+6~%ý?ÆôRñNõnÔ° ¾H:#î ®(¼j"x$Dî2¦Ä:X*52?"ì?ÌNþ(?ý >?F 6$úl(øê"júÎâôx lór÷hºÿýº Úøjxúd ¸¾r*!N #æp.¬¦úÜ?òJô´ùæbÿ ÿüxú°hõø?î`úæªþNâbûävõòì<ë0úæàÎݤ æÄ0÷¶X (ê ¨h P ?òÄr d è ?ªxü|:ûvþÜý?ôÔìZÄè ûþçôêé&ïÚîðí<÷Òïܪò<?õ&?ú?À:@.ø6òºjñ8DôH¶÷ÂÿÐú®ûÿXþðªÖ ÌÚ$ýpZü& Î\ý ?óZúï?~órþû,ëø¤Ûº×ÿ Þö®ívìýFå@.â(èãÒý¸é?ñ|ñpåù°ÛÿèÕÞ*Õ?ØRùÞïôä?äNìxÜ?ðTØíz×äázØ?ÔòÙË¢Ú?ÇڲȨÙÍ?Û|ÒLà¾ÕpæxÕfìDÔ¼ñ²Õ?ö0Ú4û?ßÄäZvê? ?ð\Fõjvø< .üÄÿ@ õ\ ìà øæ6 Pç?4í:ÿö²öþêðôVð?.ôÈøÎ$ù FúÞÿH ° H¶ ~øê< ¼"?úÊõ²Dêx`äLdå?xë?î4òØÚ?õ´Ðàô?Ó¦òfßbòXë?õôï úhìnþ8å*ÿ¼ßBýzÞ?úÞß?ù>à?ûüÝvÿ~Û?¦Ü?þfãöÔî?ë´ûtãl?á?hæ¾óºï?Þ?ùJÏT8Îx.Ú?ëöþûêýðý?¦úÄúìöÌïþô|èü÷ÆãJîÜ?Õª0Ö?äÚ?úÜ ´d~ Hý'°ýÜ"ø? L xì$æJÜ@$8 8*>à)NJ"n?²???6Ä ð$?ê+t-|!,)Z,L#F2"ú1Lf-¢#D'()?"(,?"ª*à(%æ2¼?;J|?ø ^>D9öý0Jþ$ಠä:JünÊö?øæ¾þ?Àb ªúóö,î´øìnTî ð®ø¦ðôÀðôòø(õÎû¢ø?û|ú ùúÐø(øü?öøþ´öþVø´ù¼ùô?ø²î?ó?êöêìèXà êZÖàëÞÏ ëòÎðæþÓèàdÝÛêçÄÖ&ðÕ?ô`×æõ?Ü|õÞàÀóäá?ðtáìfãÊçèèåbë´çêðë?äfïÞ^ï?ÙÖëpÙhç?Ýåhâ¨åTãNçZßÄçÚ¤æ ×ÂåRØPçÙØëp×ÖñòÔv÷ÕRü ÚfâÖê ?í ^í¦¾ì?²íürïÌùHð\ùüï²øôïlö?ñºó°ô¢óf÷,øÚöH?òr?îl Pðòðù®F@ðò¾æ F&?`Vª:üè"õ tóJýXözðTû?çüÿè6ÒñPÿXz⨠l? z> P $¾Î¤ÜPH¸®èÀî<$ @+f-vl*ÔÄ"¦($ Úf`òꤼ!ì$2"¢Zº Ä,îàô.øº°è8?&?Ò/?°1dv-¦¬$tl? ?Ö>z>??ĦưhüN>õ?âîÖë¾Ìíì,õòøVøñòXò?û?, ?¢î?#h0*nl,Xì)$À#?.Äþìöæ´ïf4édZä0ãP ®æV?îô?øTç êà?ìâ? Jë?¼öDÜþ¤ Jút ønÂø$û??üFú¾ûÈó&ùò?öõVõ`÷¾ô²ôòºî¼ëÐêÈâ°ëÚ?îXÔºíxÓÐå0×^Ø?ÝÊ?ä&¿?êPº¸îÜ»Vð?Áï^È?ë?Î@çÞÔzãÛáºÞ&à*Þà²ÚÈàr×îáèÕ ãfÕ¬ãfÕ¬ã¼ÖDãÀÙ<ãÒÜ äöÝæçÝ\ì Û?ð?Ø"óòÔðótÑ ô?Ï ôÒÎÞõèÍ:÷ Î>ø¨ÒÐø¤Ü.ùlèòùòÐûHùÿBÿdR?ê ?äÔnrä?#ö&Ƥ'È\'zò$¼è! ð~À? '!1Ò% 74.f78`3j@¢.NEÀ,ÈE¤.*B¸1=L1;Ü)T>8?D? þF*ö?Bü. ¨V8 `R ?ÀR&Np+¢%¸?b @J4ýl È÷èùä >6?¨ýØ?ÿÈýê ðÎzä¾à?ÊæÈó¶ñÄè? ?í&ôýrÐüNºö΢ô?üõ|´ø?õûöíý"ï´ÿäôöûæJÖ`ü¨ò6ê¶xçX,ì8föbñÒøàø 0Ý|öæð Îø?* Lþ¢ð÷þTòÊ?îx Ví^´îVø¶ò¾ëù@ã&Èá¤<æ<(îRFø`þ¨ö÷ DóBpñ?¼òDù?ö¤ñ|ûêêbÿ¸æêÖçrÿ¢í¢únóìòìõ*êöÈâõ Þxð?ÞÄæNâ?Ûé°Ôjñ?ÔTùzÙþ^âÊý8ïÄøªýªñÔìÒ ôê?*ïÞüèölìÒþÜÜ~Ôâ®Õ?NÝ^?æ6ùïúîÔöþâ(ý´Ø°ÿ@Ô®û®×?ð¤á áîzÕ0ø¶Ò¼üÆÛü<íù²ÿöÀô² Úòt6ò¨þZòü÷êó?ôT÷tó üNô¸øöþû."~Bô^äÖúN øÞèò?>óÄ ¦÷~ Ôü¤?\ ?l fÌBØþ´<ý,?Nîv6? ?*ü â/Ú/ î'xÊðÔ"?!zV°?<Ü t^¦ 6èF ²n%ìâ%Æ?#ÎX#¢ 'ð$?,*?0>0z1V5l0*6 1?0Â6ð$D@¦0H^ rH?ÿ?>Âù-R÷?÷öjúªlÿD¸J x 4 jÖ |¼ðÜìÿ¾ ´°& Ìý?Îûz@ÿÔ.r¢$$¸ùêTðNÐíîxñÚýð÷èný~ÛZÿ.Û(ý.ãVø<î óº÷pîöúë¬õjêBë¨î¦áløÖÚdÕØpÑZÕpã? :ö?ýääòÄ `ìòÜêúý~íð|ò?ä&øtáÌüçÄþrð|ý?øúRþföz ôbô4øN÷²ë(üÔß ´Øbn×? ÜÛ* å?(ó(V¸ì°ýÄüöüvÊþ?ø?t$*?Î? Àúî~üÊPöÌ jòÚÎò°Bøÿ??ü:¦ý?8? DÞ®Z *è fâ ?. ¶bä¢ì? öþìRðrÿ¼á?ýÖ×Öù°Õàñ¾Û®å?çÚØÀô2Ðîþ"Î:ÀѲ?ØJø?߸ì"ä?á:ä¸ÙFà?ÖÚ\×pÓVÚºÍHÝÈÊÖÞôËÒÞ?мÝðÕÜÚZÚ¤ÜrÙþÝHÚ¶ÝPÝÔÚ?â¦ÕøèfÐïºÍFóèÎ\õ?Ófõ4Ú>óøß²îâLètà®á¾Ý:Ý(ÜþÜ´Ú?áô×?é¨Õó*×üÝøåÂþìh?ônÜúª?þþDÿüüèþöÿ¤þÒ¸ý?üðûì\ÿÆ(,^RüVvö &ôþ Îö°4ý?´hx`Æ 8<nTú?¤ñ??ï?ôîýó¢?ß?ØÖøþ¾Ü õë<ëÚ÷âäèý?ârý.ãÞ÷:æXï?ë,èöò?å?ûîåö,æl Bç? ?ìB¦õbüþDö|?õîlûjûrâïþbæÞøç"öÄ J h?øN#¬ó&Æò? dõVû¼ ¾Ú¶?FÒz#D Z'ª$¢ ¨¬.\ø8F â=²<?5¶&)$ØÈ $¸"~ò'ÊÞ(L"J%>($¼/l@4Èö2^p,2#"?ô#0 !?PL F(¢ z,ÎÒ,äR+Þ>(~"Îüf'vl-ä ,îF#Ô>@!?¶(Ø÷+pî?&6ênÐê`PïÚböd(þ6¬N?&N ¨??¸$#?®"L@zÞ ¢ò?êøúäjïƸâjüÎ×öùZÒ¼øÒÓ>õÛØíåÚåÊíâlò¸ã?òbè\ð°í¢íæñ.ë¨òJéÄî?èHé?é ç²ìLèòð2è?óäòß"ì¨Ý¬ã?ß?Û~á¤Ö.á¬ÕÜÝ|×@×hÚ?ÎÌÝTÇ°áöÄ´åÆÆ é(ÉBëÀʾìâÍVîÚÓÐðÈÙVô>Üü÷?ÛTú?Ú¨ú?Ú,ù>Û?ö?Ýöó?âDò&é®ñ?îòZò?óbö>öÌú|ù?ýâü"ýz?úD÷êTó$\ðÞ Xðr~ô¶üú ÂXÄÞ ^Bð,8?%?v-î?1@3B?5À ;pLB?þ&Fbÿ^AÆ1?("?Ì(?þ?'*î?hà-\T8rú 7bï?.?è6!éθòÄÈý?²ü\-ê7dì7T'>/:0#@E¬vE@¼:,(j?Î#p"P !þþ-òõÔ-Èò(#õJ@ûFþxÖï?\é®?êV(öðb- û?+â ü#â,Ö.LÔ78@40 ¶'F PÌ ü´?lÒ?ÐÈ:HZò??x?¦þÄþâ ?þfú8#?÷?#.÷v@ú?Äþ¼ô?6î¤*ñ?"ùB@!æ\ì¸?Ò.t?&Hbn høDðtÔëÜþÒìõªóÜêÿ?á ?Ú?Ø"Tܲ pçPü>õ¶î.ÿvåfnâ?ø ä?ë?ç¬Þ?ë@ÖDïFÔ¨ò¦×*õÝ?õ?á ò°ä8í:èâçìääí²ä¬çîå@ÜhæÐÏ?äÈTàtÇÚÚ°ÌÜÕjÔÀÒ?Ú Ò?Û¬Ó<ÖP×@ÍBܨĤáp¿®æ?¾ÚêÈÁ:îÞÇñRÏó|Ö?óüÛ8òßdï&à`ì,àpêRàøé*á?êãFëÆænë<ì?êÞñNéõ éxô,ë4ñ¢ïØìÌõÄèlüîænªéd\ñ<\ûÜ Dä? ? ? r VRý¦høªöº²öZ¾ùâRþj æ ðt ¢®?j¸? ê"| 4(ÜÊ)tv'? z"N*ðÖ/Ö°/öb*x?!R̺ÊvÚ@дþj üÖ¶ý´¶ÿ6RâúØþLû ü4úZ?ú??ýäý>^û*Rý~??0 f? ¦ê,l ?Ô¨ü¦,ùl*ø |ù ÿ?ûàõLürð?úððJöRõÈð?úæëìýRé*ýjéjö^ëBëLîxá?ñ?ÞLôãföêê¶÷¼ò?÷úõâñ??íØÊì"Bð\øb÷üì2ÿ â`Ül<ßÐ?èZû4òäõRö4ñnóíÈë8éøâäåúÛJäVØnåª×jé@زïÙb÷´Úpÿ?Þ,æã:é?üìÜöðh?öúý d°T þ|?ùj &önÄó:¬òúþòfó?ô?íB÷féûxæb äV?äp4çÞ$ìúèò(Úû?JºfÞ^ÔùbõÈóìó<ÖôÐ÷< (û BþH ?ÌvVþXúnÌõZ*ó8ºò*òóø>õàîöôbé?òìé¬î&ðøêªøpé0ÿ>ëÔÚïrýÒõ?÷ ü¬ò¬Äðâ?ñl~ó4 ,öÈÔùèýbÿ¸hûÎù?ý4øn÷.ø?ïdøÊè?ø.åÜøôä^ù?æÖù?èVújê?ûfì`þ&î´Jï,xðÜ *ó¶xøz¤?Ü þìºÔÔ Â?"Z #ì#@ ò!D?& îôìþ |Ì jäl? ÂNì°HV#æà,:ú2n3J¤/L(+bú( Î)¨@,?ú:.óÌ-:î)íït RòHûêôîHöHê ÷Ìî²÷Ìö0øRý"øúÿf÷ÔþüöûÌøÚõòýäï?êé ,æxtè"ó¶Øþ`ô&ï??ï?Ìõvý¾ÿ.í¤ ¾åörèâ,ñDjûjÎ>fÈ. \Tà¼Rú ÊöjRö² ùÐèÿà zÄîÿÂvÜèò|^?? ?$®(Üú&À,"ô(ªÜ-\.? p+$Ü%F62öh ær hÎbàú ¦ÀT$¶?)XN,¶À,°,úö*?)ò@(v(?À*?|-6â-ú*ø2&? ,ýüh÷?ô¼õ¾?øfý`ý´üB`ý Úþ$??ý^Ðùvð÷èý:ùhú ý÷??õ¸.÷Hû¢>þÒü?þ\ôvü4ê¼ùìß0÷Ä×8ôÊÓðLÔ?ê|×pã®Ú,ÜìÛ?×ôÚ ×DÙPÛîØöÞþÚ?ßß¾Ü?ãØJçÎÓðèfÑ^è.Ñþå&Ò4â6ÓtÝ|Ô?ØîÖ?ÔxÚ Ò>Ý`Ñ^ÝÌÑÆÚ?ÒÄÖNÓhÒ$Ô@ÎþÕËrÙ?É"ÞêÉââ&Ë?æÌÌ?è°ÎvéÐøé^Ï<ê?Ì êèÉ~éFÊXèÏçØÖvæ&ßÒæfå´ç?ç¸èXåxêfàRî*ÛõÚÖæýøÓ@ÂÓfØ"áH?ìºö8Âý â<r?¢ý?Üú¢¸ûºðÿ0 Dè :? ¶Fâæh|äæú<L?tûj öt"ò?jñbö,?RØl² 8²R Î0TÌF\"X)ú H-¼'´-Ô/?*?6?$Ä:$ ="ð<¾?7ôn,ľ f ?fr? $p$º ´(d>-¼úÔ..ø?*Dù"ìüÈÀ&¤¶2? f^(tæ*È$J( /ü"F8?j<üt8t-¼>L?<ÿ??øØ ?ôD@òÈ Úï0ììôjê? öéì fí?zõ,ÿ²dúJú¼?ÿÒ pÞ!Ð ?)?Z-öüÈ-Èøâ*(ø,$?ùÖFûTüº ØúöZ÷| ò6 `ìÔfçH ?ä? åýÚéþózò?ë¬ü åÔâãº0覾ò¬ý ¦õ¨ ?î?véH Êæð$æ0ýòæ?óéhëøëòæÈî?å¨ðÆärñ¶ãÆðÔã0îlå?êzåÖçá~æ4Ùòå¶ÐåɺâîÂTÞÜ¿Ù?ÁÜÔ2ÆÊÒ²É²ÓªÉ Ø®Ç,ß?Æ?çÇpï Èðõ.Ërú?ÎäüNÑ`ýTÒêûÐÒÄøÕäôØØ?ñÂÛ´ïäÛïvÙ$ïÕïLÏ:îÀÉðìØÆöì>ÇÂïhÉ$õÀËü^Î?ZÒ? 4Ø&jß²0ç(´îDèô°#@ù°'òüì'`P#? X4jB Ä ¶ è à?¦?V*¾Ôúº4f ¬RÀ ö À$J²)0ö/2ÆF/&r(î,ºè2ÜT6º H6PR3?ú?.êô°(tï"êé.låèÆã ÞåØðêü?ñØõJøìòDþÒó?÷ö"ú,ÐûÆÌüþ þ@ùªö4÷èhùjfúÚøÒþò88î>bìJxî( (ô?ìûÞþ?ý2Èü¤æ ?û>^ôܼïXïó?ö~ùTí?ré?þën ¤ó¦ÆýR ?l,2ºFl|º ?Äl6t ný?dÿ?6vÎýÆø¦þ8ôúôñõ?ñTð0óVí?öí4û`î?üïTÜñÌ&ô??õÒèô¶Ðó¾êôr@ø´ û?öûn<üÄbý 4þbFýì°ûü¢ú?ø?þ¸óÐýöíÎþ¤ê?dêJ ?ëhî êòúù?"??"ÊrÜ0?>Ö`üûxxöXtõ?÷0âù¤ºüÆþ?þøúTþðö|üÆó>úóNø|õ¸ö|úö\r÷?Òû( BZ ?h ?? ÚÐöÿ ?û0b÷?ô >ñ@ 0î8êj?å?ÿ<ánûfÞ¬ö°Ýbò²Þð¢à^ðèâ°ò&åöTçÚødéù?ê?õ¬éð(ç(ìÞäÈërä í(æíöè®ëtëúêâìºìlí~ðFíPôZì?öëj÷?ê|ø?ë°úôí ý>ñ`þlôÿ\öxö&LõnôúóÊüõ Nú¬ZÿÀ¦*T æ.0BÚ?nFhú?ä? ??t?àÞvbä<÷B zí@8ç Âç Äïl?û´ø?ðl ?ïVÚ÷¶ Ö è ¤¶¾D?¸ûȤ÷¶îô öñJ¶ïÒJñÖ²øD>º *î? ÿØ?ün?þFø¦¼è^ÝÚâál ¤ðÄÖöÀ?\à ~æ.ÜÐõtÐï`?ïöôîÂþ" ¨ Zò?$2X!@üôB`? Úý¬ Öù2Þüìîô,üTFÊê\?ðZ$äL x ~ð Æ? è Æ °(` ö. $2r²2<2Ôz08z-6L)úN%tÜ"b@"þÖ"²ýr#Òøn"Xôh|ðÈdí Îë ?ì¼Ðï¬ô( fùHÚûÎúèöê6ò?îD?ì?æë$ "ìú¾ìûZí8ø¤íÂö2íbõ¸ë¸ó8éòêå0ñXâñ¨ßàð ߤïèàÌììä~èrêÞãÔðêàX÷üàðü¨ãP?ç¼"ìÔþ ñÐûr÷ø¶ûpóýî"üJéÜúbæàùæ>ø0è(õìñ¾ðíhõFéZùæ¶ü?ãÖÿ6â??á>ná?⪰åpêô¬îxòòöZ÷XüûX@? nô,ÿ~((`ô0j Ú^ l?B?Âîø6²ó?¶ôôú¼ 2âÀþr÷Pü?ðTüÖîRðjÌï4?ì?Öìì?õ?ºÎH Üè` 0jàý??ÿ´l^¶tÞ>Ò&f2 ¢ù°?ð¢þ?ë0ø@ëÜñlînëÆò(æòöüä¶ú6éÞý6ð$föìHû®L(òÞræ¤è Ì* >(¢ >ÌFh²$²þ®(&÷*)Ôò&?ó®?øÿxÜÄ~?Næ?ZþBHûRRù@Lù hû\BÿLÊÞH?F?"ì?$ÈP%Üî"ÜrÆþ²ºý®Lþ? pÿê ÿÐ?þx¦ý ý:þ.ú&*öøðò?þ4ô ù?ú6ô(Îï?àìä¾ë°íîüÖñ,ùûöV°óXôÌ?úf?è?ê^ ~pF®?Äèªÿà¤þx|ü þPùÂý ÷.ü?÷¬÷?ûðÚÊç?Øâx¾ãÒê$HóJ?ü?ò? ¸?fD ¬HÒÿ?ûXú.ôjönï@ófëDò¤ç?ôTæð÷ºætøüäzõ°á ñ^âïÒæðí?æxîäÝÒðôÓPô2Ï ø?ÌpüÂÈ4Çb0ÊV?ÍþpÎ?÷&Ò0òÖÛ6í¸ãFç?áLáZÚôÝÄØ6Þ~Ü@à?Û4âøÓ2ä|Ïàæ²Ó~é´Ù¦êjÚ°ê?ØÎë²Øðî0Ø?ñ?Ó¶ðrÎhìÐÏ$è Ø¢ç¾à²ëøãìðªá?òßFð âPî?ë¦ïòô ò¸öüò òFóNñ"õúd÷Vtö Rñ^ Þë|4ë*ð??õÖÂöäúÈó^üØñÊVô?Âøàú?rú~vúüü: JÞÀT?RýH ?z®*¦?,È`¶#PúÞ(äôh'~ó?¬ôìÀ÷Æò üÄîäöõîR¤ Zæ¤>p¢ÿ² ðôZ.ï ?îè?òX?üxÂÀê? ^ÂØ ý ÿª0ý°ðóÒò? Îü6!²¶?²Ø"NX%V?".Þ!and their links is: 1. Press F1 or ALT H and then ENTER on "Microsoft Excel Help". You will fall on either a Search or Go button and if you TAB or ARROW forwards or backwards at this stage, you will encounter several primary links to both information which is part of your Excel help information on your hard disk and some which will take you online to various places on the Internet, mainly to resources on the Microsoft Website, e.g. Microsoft Office online, Training, what's new, etc. Press ENTER on any of these to experience what happens either online or offline. Note also the Online "Contents Settings" link, which permits you to make some changes to the way these help pages work and what they display. You can leave this stage of help by pressing ESCAPe or ALT F4. However, if you are a JAWS user, until Freedom Scientific create scripts to work with JAWS 4.5 and 5.0 which also work better with the Excel 2003 help system, you may find that if you do too much ARROWING around in these initial links, your computer and speech freeze on you, requiring a reboot. 2. If you TAB to the "Table of Contents" link and press ENTER you will open up the help books and topics as described in the introductory paragraph in this section and earlier descriptions in sub-sections within this main section of using help books and topics. You can still move back and forwards in some situations with ALT left and right ARROWS but this does not work everywhere. Leave help contents by pressing ALT F4. 3. After first entering help with F1 or ALT H, you fall on either a "Search For" editfield or "Go" button, which have basically replaced the Index and Answer Wizard tabs. So, if you would prefer to search for topics instead of using the help Contents books, type what you want to search for in this editfield, e.g. "printing" or "working with macros", and TAB to "Go" and press ENTER. You may have to press ENTER on the Go button twice to get this to work. You can then TAB through many other links where help details can be obtained from and eventually a list of printing topic links (or whatever you were searching for) which help has found on your search string. So, after typing "printing" into the search box and pressing ENTER on "Go", you will be able to TAB or down ARROW through many print topics which have been found and then, after leaving focus on one of these, press ENTER and then press TAB once or twice to move onto the help text and then ARROW down it to read it. Whilst in this second list of help links with the various printing topic links displayed, you can also press ENTER on several other links which will take you to and search for help information on the Web, e.g. a Microsoft Office online link, a learn more link, a get answers from other users link, a research link, etc. To go back to your search editfield and go button, press ALT right ARROW and press ESCAPE or ALT F4 to leave search help. 4. After using the search feature, you will find, after SHIFT TABBING backwards once, a "Search Scope" listbox where you can ARROW to such as training, templates, clipart and media, etc, and thereby narrow down the type of search you do to these kinds of topics. 5. Again, when using the search feature, there will be a "Can't Find It" link which will bring you into a help screen where you can read good searching methods information and tips for when doing help searches. 6. In fact, the above links to help books and topics and to the Internet and Knowledge base have brought together and amalgamated all of the elements of help in earlier versions of Excel under one large page of links with sub-pages flowing from it. You leave help in the usual way by pressing ALT F4. 47.6. The Excel Tips Website There is a Website dedicated to letting you find tips on how to use Excel at: www.exceltips.com It lets you search for tips, submit tips, discuss Excel, receive tips and Excel add-ins via e-mail and bookmark tips without registering. You can choose to get tips by Excel version and select from Tip of the Hour, Recommended Tips, Most Viewed Tips and Tip Categories. ******** 48.1. More Examples of commonly Use Formulae and Functions There are many types of Excel functions, including statistical functions, logical functions, date functions, string functions, Mathematical functions, Database functions, financial functions, look up functions, etc. Some of these, e.g. the stdev, if, AVERAGE, SUM, etc, have already been exemplified in earlier sections, but some more which may be of interest are given below. Have a look at these frequently used formulae and functions and get some practise using them. 48.2. Running Balances Create a worksheet similar to that described below and keep a running balance of your monthly banking or household income and expenditure. 48.3. Workbook Exercise 13--Running Bank Balances 1. Open a new worksheet and complete the cells as follows. 2. Go to Cell D1 and combine it with E1 and F1 with ALT O, E, CONTROL TAB to "alignment" and TAB to "Merge Cells", then press SPACEBAR and go to "OK" and press ENTER. 3. In A2 type the heading for the brought forward bank balance from last month of: "Balance BF". 4. Go to B2 and merge it with C2 as shown above and then type into it: Income/Amount". 5. In D2 type the single heading of: "Cheques". 6. In E2 make a heading for direct debit payments by typing: "Direct Dbt". 7. In F2 type: "Debit Card". 8. In A3 type the brought forward from last month balance of: 210. 9. In B3 type: "Salary". 10. In C3 type: 900. 11. In D3 type: 50. 12. In F3 type: 315. 13. In B4 type: "Child Benefit". 14. In C4 type: 47. 15. In D4 type: 15. 16. In E4 type: 40. 17. In F4 type: 15.70. 18. In B5 type: "Lodger". 19. In C5 type: 200. 20. In D5 type: 47. 21. In E5 type: 62.50. 22. In F5 type: 75. 23. Now in the cells D6, e6 and F6 respectively type: 10; 10; 88.24. 24. In the cells D7, E7 and F7 type: 25.50; 25; 5.55. 25. In cell F8 type: 79.60. 26. In F9 type: 110. 27. Now leave sufficient blank rows to accommodate a possible entry for every other day of the week in a 31-day month by going to cell B34 and typing: "Totals". 28. Next generate the totals starting in cell C34 by typing the formula to add the current month's income up of: =SUM(C3:C33) 29. Copy the C34 formula to the Clipboard and then paste it to cells D34, E34 and F34. When in any of these 34th row cells, if you have forgotten what the column headings are (they will be off the screen), use your screenreader's read column heading hot key, e.g. ALT SHIFT C or ALT 1 in JAWS, Numpad 3 in HAL or ALT CONTROL C in WE. 30. Leave a blank line and in cell A36 type: "Balance CF". 31. Lastly, generate the remaining monthly income figure to be carried forward to next month in cell B36 with the formula: =c34-(D34+e34+F34) 32. You could continue with next month's bank balance income and expenditure figures in this way under neath these entries or you could take a separate worksheet for each subsequent month. To do the latter, you must ensure that the worksheet is always created using the same cells with the carried forward balance in cell B36 so that you can reference it with a formula which will pass through worksheets as follows. 33. Press CONTROL PAGE down to open a new blank worksheet. 34. In the brought forward balance cell of A3 on the blank sheet type the formula: =sheet1!$b$36 35. Now view the resulting balance Bf figure which has been taken forward from sheet 1 to the balance CF cell in sheet2. 36. Convert the whole sheet to two decimal point format by highlighting it all and then pressing CONTROL SHIFT ! or by using ALT O, E, then ARROWING to "Number" in the "Number" property sheet. 37. Now do any manual formatting you personally prefer or take the easy way out by autoformatting with ALT O, A. 38. Lastly, Save your work or resave it to the filename you should have already given it whilst regularly saving as you created it, e.g. "Bank1". Of course, you are likely to want to create this worksheet as a skeleton with all of these headings/labels and formulae first, without any figures in them, and save it as a template with a filename like "Bank1". You could then retrieve the template at the beginning of every month on a blank worksheet in the "bank1" workbook and complete the figure columns on a daily basis as you go. The carried forward "Balance CF" figure will always be taken over for you each time you retrieve your template to a blank worksheet. Alternatively, if you would only like to create a running balance on a single worksheet which goes on day after day without splitting things into weeks or months, you could use the following method: 1. Using a Worksheet with three columns, in A1 type the heading: "Deposits". 2. In B1 type the heading: "Withdrawals". 3. In C1 type: "Balance". 4. In A2 type: 1000. 5. In B2 type: 625. 6. In C2 type the formula: =SUM(A2,-B2) 7. In A3 type: 1000. 8. In B3 type: 740. 9. In C3 type the formula: =SUM(C2,A3,-B3) 10. You now only have to add a new row for each entry to keep a running balance going for thousands of row entries but remember to copy the formula in cell C3 down the whole of column C. 48.4. calculating How Much You Will Pay on a Loan or Mortgage If you took out a loan of 10,000 pounds at an annual interest rate of 8 per cent and you have to clear the loan in 10 months with payments being made at the end of each month, the formula you would use to calculate what your monthly repayments would be is: =PMT(8%/12,10,10000) The resulting monthly repayment rate is 1,037.03 pounds. If you took out a mortgage of 100,000 pounds at 8 per cent over 25 years, the formula to ascertain how much your monthly repayments would be is: =PMT(8%/12,25*12,100000) The resulting monthly repayment figure should be 771.82 pounds. In practical terms, for the first of these examples, the details on your worksheet would look something like this: 1. In A1 type the heading (or some short form of it if you wish): "Percentage Interest Rate". 2. In B1 type: "Monthly Repayment Period". 3. In C1 type: "Amount of Loan". 4. In D1 type: "Monthly Repayment Required". 5. In A2 type: 8. 6. In B2 type: 10. 7. In C2 type "10000". 8. In D2 type the calculation formula: =PMT(A2%/12,B2,C2) 9. The monthly repayment figure of 1037.03 should appear in D2 for you. 10. Experiment with changing the values in A2, B2 and C2 to see what the repayment figure would be at different interest rates, over longer an shorter periods and for different amounts of loan. The Monthly repayment Required figure will update every time you change one of these values. If you took out the same loan but were required to start paying the loan at the beginning of each month, the result would be slightly different, as follows. The formula would be: =PMT(8%/12,10,10000,0,1) This would generate the slightly lower monthly repayment amount of 1,030.16 pounds. Of course, you would want to enter it into cell D2 as: =PMT(a2%/12,b2,C2,0,1) in the instance of this worksheet. Note: Remember, if you intend to copy the formula across columns or down rows and have different loan periods and/or interest rates applied in a table of comparative figures all referring to the principle loan sum of 10,000 pounds, you will need to make the cell containing the loan sum of 10,000 an absolute cell by referring to it in your formula as $C$2. 48.5. Calculating Your Long Term Savings Potential If you wanted to accrue a saved sum of 50,000 pounds over a saving period of 18 years by saving a known regular amount each month, at a fixed interest rate of 6 per cent, the formula to work this out would be: =PMT(6%/12,18*12,10,50000) Type this into a worksheet cell and you will be advised that you would be required to save 129.08 pounds a month to achieve your target savings of 50,000 pounds. In reality the calculation would look something like: 1. Type in cell A1 the percentage: 6. 2. In B1 type the period of years: 18. 3. In C1 type: 10. 4. In D1 type the final sum you would like to accrue: 50000. 5. In E1, where you wish to generate the result, type the formula: =PMT(A1%/12,B1*12,C1,D1) 6. Observe the result of 129.16 pounds, as expected. 7. Experiment by altering the percentage figure in A1, the period of years in B1 and the expected final saved sum in D1 and view the changed monthly savings requirement in E1. 48.6. Getting Advice About the Need to Re-Order Stock You can create a spreadsheet which automatically tells you if certain stock items have fallen below the number at which you should re-order. This short three columned example provides the general principle. 1. In A1 type the heading: "Minimum Stock". 2. In B1 type the heading: "Stock Carried Forward". 3. In C1 type the Heading: "Order Status". 4. In A2 type: 10. 5. In B2 type: 11. 6. In C2 type the function: =If(B2<A2,"Order Now","OK") 7. You will get a cell C2 instruction of "OK" because the stock numbers have not fallen below 10. In other words, if the "Order Now" instruction is not appropriate, the second instruction in your function is displayed. 8. Change the cell B2 contents to 9 or less and view the order instruction now displayed in cell C2. 48.7. Counting the incidence of Unique Entries in a Worksheet The "COUNTIF" command counts unique entries in a list and returns the result. For example, create the below small two columned worksheet and count the instances of invoices submitted by smith. 1. Working downwards, type the heading "Sales Rep" in cell A1 and in the cells below enter: Smith; jones; Smith; Davis; smith; jones; Williams; Wilson; Clark; Smith. 2. In column 2 type the heading in cell B1 "Invoice" and in the cells below type: 140.00; 122.00; 270.00; 159.50; 166.00; 210.00; 130.00; 200.00; 175.00; 135. 3. Now leave a blank line and go to cell A13 and type the formula: =countif(A2:A11,"smith") 4. The result of counting the number of invoices submitted by Smith should be four. 48.8. Generating Automatic Notification of Action to Take when a specified date is Reached You can combine a date with text in a cell or across cells so that, when that date is reached, you are advised of an action to take, such as to send out a customer's bill. For instance: 1. In Cell A1 type: "Billing Date". 2. In A2 type: 10-apr-2002. 3. In B1 type: "Action". 4. In B2 type the formula: ="Send Bill:"&TEXT(A2,"D-mmm-yyyy") 5. View the result in B2. It tells you to send a bill and confirms the date to do it on. 48.9. Changing the Case of Text To change the case of text in a cell, column, row or range of cells use the "Lower", "Upper" or "Proper" functions, as follows: 1. In A1 type: "John Wilson". 2. In B1 type the formula: =UPPER(A1) and press ENTER. To Change this name to all lower case (small) letters use the formula: =lower(A2) and to change it back to initially capitalised (title case) use: =proper(A2) Note: Your screenreader may not inform you of the case change. 48.10. Increasing or Decreasing a Number by a Percentage Experiment with this in the below two columned worksheet: 1. In cell A1 type the heading: "Number". 2. In B1 type the heading: "Percent Increase". 3. In A2 type: 23. 4. In B2 type: 3%. 5. In any other cell type the formula: =A2*(1+5%) 6. Observe the result which is to increase the number in A2 by 5 per cent and return the increased figure of 24.15. Try another example with the above sheet by, in any other cell, typing the formula: =A2*(1+B2) and observe that the figure in A2 has been increased by the per cent value in cell B2 and the increased figure is generated, which should be 23.69. This time reduce the figure in A2 by the per cent in B2 with: =A2*(1-B2) and the result should be 22.31. Note: When you use a number followed by a per cent sign the number is interpreted as one hundredth of its value, e.g. 5 would become 0.05. 48.11. Counting Numbers Greater than or Less than a Specified Number To exemplify this use the simple two column structure used in COUNTIF in 47.7. above. : 1. In that worksheet go to any cell outside of the data range and generate a count of how many figures in column B are over 140 with the formula: =countif(B2:B11,">140") and view the result, which should be 6. In a different cell do the same thing but this time generate a count of figures both equal to and less than 140 with the formula: =COUNTIF(B2:B11,"<=140") and view the result of 4. 48.12. Converting measurements The "CONVERT" function can convert many measurements, such as weight, time, distance, force, pressure, magnetism, temperature, liquid measurements, etc. However, the Analysis Toolpack add-in will have to be installed for you to do this. Install it in the same way as you did for the other add-ins in 42.2. above. Then try some of the below functions to convert measurements: 1. In cell A2 type the figure: "6". 2. We will do all measurement conversions using this figure. 3. With your cursor in any other cell but, obviously a different cell for each calculation, do the following conversions: A. To convert 6 teaspoons to tablespoons use: =CONVERT(A2,"TSP","TBS") and the result should be that 6 teaspoons is equal to 2 tablespoons. B. To convert 6 miles to kilometres use: =CONVERT(A2,"MI","KM") and the result should be 9.656064. C. To convert 6 kilometres to miles use: =CONVERT(A2,"KM","MI") and note the result of 3.728227. D. To convert 6 centimetres to inches use: =CONVERT(A2,"CM","IN") and the result is 2.362205. E. To convert 6 degrees Celsius to Fahrenheit use the formula: =CONVERT(a2,"C","F") and you should obtain the result of 42.8. Note: Sometimes the above calculation works (in example E) and sometimes it does not! The formula is correct, so perhaps there is a programming bug in some versions of the Analysis Toolpack. 48.13. Calculating the Difference Between Two Figures as a Percentage To do this you have to use the "ABS" function, as follows: 1. In A1 type the heading: "November". 2. In B1 type the heading: "December". 3. In A2 type: 2342. 3. In B2 type: 2500. 4. In any other cell you would like the result generating in type the formula: =(B2-A2)/ABS(A2) and note that the percentage change is 0.067464, which is the same as 6.75%. 48.14. Calculating the Difference Between Two Dates in Days, Months or Years You can work out the number of days between one day and another in the future by using the subtraction or networkdays operator: 1. in cell A1 type the date: 2/4/2002. 2. In A2 type the date: 14/4/2002. 3. In cell A3 or anywhere else you like type the formula: =A2-A1. 4. The number of days between the two dates should show as 12. 6. If you have the Analysis Toolkit installed, you can use the "NETWORKDAYS" operator and obtain the number of working days between two dates, i.e. without adding in weekends, with the formula: =NETWORKDAYS(A1,A2) and, using the same A1 and A2 dates as in the last example, you should receive a result of 21. Note: The format you are likely to obtain your number of days result in will be in the order of "12/01/00", being a date format. You need only take notice of the digits before the first / sign for your answer. To obtain the same type of calculation for months or years, use the functions, respectively, of: =MONTH(A2)-MONTH(A1) To calculate months within one year. Or to obtain months spanning years, use: =YEAR(A2)-YEAR(A1))*12+MONTH(A2)-MONTH(A2) 48.15. Adding Days to a Date to Forecast a Future Date To add days to a given date, with the date of 6/9/2002 in cell A1 and the number of days to add on in A2, use the formula: =A1+A2 You can add as many days as you like. It will move on to dates in other months and years. ******** 49.1. Sharing Workbooks with Others Over a Network You can share your workbook files with others on a company or other network and you can compare, track changes to workbooks and merge changes made by several authorised workbook sharers. Note that for sharing of a workbook to work, you must ensure that all copies of the workbook to be shared between different people are all kept in the same network folder. This means that each copy in the network folder must have a different filename, e.g. report1_1, report1_2, report1_3, etc. The viewing of highlighted and marked changes in shared workbooks will not prove to be easy for those who cannot see sufficiently to use the screen display. The changes are highlight in a way which your screenreader may not be able to "see" unless you turn your screenreader's ability to report attribute changes on and even then the changes will be difficult to observe. Nevertheless how it works is as follows. 49.2. Viewing Who is Simultaneously Sharing a Workbook and How Sharing is being Managed To see who is authorised to share a workbook with you and what the sharing settings are: 1. Press ALT T (for Tools) and then R (for Share). With Excel 2003, you press ALT T and then B. 2. You should be in the "Editing" property sheet, so TAB and press SPACEBAR to check on permission to share and also merge the workbook with the others who are also sharing the workbook as listed below. Therefore, when a workbook is amended and saved, the workbook will also be merged and saved to the other sharers' workbook copies as well, so that all sharers have an updated and merged copy. 3. If you TAB again, you will be able to ARROW down the list of current workbook sharers for the currently opened workbook. 4. If you press CONTROL TAB you will go to the"Advanced" sheet and be able to view the management settings for the shared workbook. This is not a very screenreader-friendly property sheet and, depending on your screenreder, you may or may not be able to make settings changes. You should be able to view the default settings in mouse mode. 5. After viewing and changing any settings to suit you and your network set-up, TAB to "OK" and press ENTER to save them. 49.3. Tracking Changes in a Shared Workbook In order to be able to view, revise and/or accept or reject changes to a shared workbook you can use the options for this in the Tools menu: 1. With your network shared workbook open, press ALT T (for Tools) and then T (for Track Changes). 3. You can now ARROW down two choices of "Highlight Changes" and Accept or Reject Changes". A. If you press ENTER on the former of these, you will open a dialogue box which has a "Highlight Changes Whilst Editing" checkbox which you can turn on with the SPACEBAR. If you check it on several other options will open up below it for such as checking for changes since you last saved or for all changes ever made, checking for everyone's changes including yours or for everyone's but not yours. Changes will be highlighted on screen by default. After viewing or making settings changes, press ENTER on "OK". All changes made to a workbook will be highlighted on screen, if you can see them, including altered cells, moved cells and cell ranges, inserted and removed rows and columns, etc. B. If you press ENTER on "Accept or Reject Changes", all tracked changes will be highlighted on screen and you will be able to view, modify, accept or reject them individually. 4. You may also have a "Compare Document" option in the above sub-menu which, if used, will compare an open workbook on screen with the original document and mark any differences in the open workbook on screen. 49.4. Comparing and Merging a Workbook To compare changes in a shared workbook made by various people working on it and to merge the changes to a single workbook file manually: 1. With your workbook on screen, press ALT T (for Tools) and then ARROW to and press ENTER on either "Merge Revisions" or "compare and Merge Workbooks", depending on the version of Excel you are using. 2. The changes will be merged into a workbook which you can specify to update that other workbook. ******** 50.1. Protecting Worksheets and Workbooks from Alterations by Others You can stop others from making unwanted changes to worksheets and workbooks with the protect feature: 1. With the workbook open, press ALT T (for Tools) and then P (for Protection). 2. ARROW down the various protection options, depending on the version of Excel you are running, e.g. Protect Sheet", "Protect Workbook", "Protect and Share Workbook", etc. For this example, press ENTER on "Protect Workbook" to protect a whole workbook instead of just a specific sheet within the workbook. This "Protect Workbook" option will then change to a "Unprotect Workbook" option. 3. In the dialogue you come into, you can type in a password so that only you or you and others allowed to know the password can remove the Workbook protection. Type a password in if you wish with up to 256 characters but do not forget it or you will never be able to amend your workbook again. 4. If you wish to keep the workbook structure protected against change, ensure that the "Structure" checkbox is checked on. 5. Finish by TABBING to "OK" and pressing ENTER. 6. Depending on your screenreader, however, when you next come to open this protected workbook, you may find that the screenreader cannot "see" anything in it. If this happens, you can use the "Unprotect Workbook" option in the Tools, Protection" list to temporarily unprotect it so that you can use it again and you can then protect it again when you have finished. ******** 51.1. Using Goal Seek to Adjust the Value of One Cell to Obtain a Specific Result for Another Cell If you already have a formula which provides a result by summing, multiplying, dividing, etc, one cell by another to obtain a result, you may sometimes wish to discover what figure would be necessary in one of those cells to obtain a given result in another cell using the same formula. For example, if you always previously needed to make a profit of 5,000 pounds a month by selling a fixed number of units at a given unit price, say 500 units at ?10, but you now only need to make a profit of ?4,000, you can get Excel to work out for you from your formula just how many units you now have to sell to achieve this new figure of ?4,000 or how much you can afford to drop the price by if selling the same number of units. For this example we are using very small and simple figures and a simple formula to ensure that the principle is easy to understand. 51.2. Workbook Exercise 14--Goal Seek For example: 1. Create your original small worksheet with its figures for doing the goal seek on as follows: A. In Cell A1 type "Price". B. In Cell B1 type "Units". C. In Cell C1 type "Total". D. In Cell A2 type "100". E. In Cell B2 type "50. F. In Cell C2 type the formula: =A2*B2 and press ENTER. The total in Cell C2 will show as 5000. 3. Now practice goal seeking by: A. With your cursor in the cell with the formula in, i.e. C2, press ALT T (for Tools) and then G (for Goal Seek). B. In the dialogue you come into, in the "Set Cell" editfield" C2 should be automatically inserted as the cell holding the formula. If your cursor had not have been in the cell holding the formula, you could have typed the cell reference of C2 in here to ensure that things worked OK. C. TAB on to "To Value" and in here type the new result you want, which in this example is 4000. D. TAB to the "By Changing Cell" field and in here type the cell reference which contains the value you want to adjust, which is A2. Note that the cell you refer to here must be a cell referenced by the formula. E. TAB to "OK" and press ENTER to finish. F. Excel is likely to give you a message to the effect that it has been successful in finding a solution and you will have an "OK" button to press ENTER on. 4. Now move to cell A2 and observe the changed figure of 100, which should now read as only 80. Cell C2 will have the new profit requirement of ?4,000 in it. Note: If you had of used Cell B2 as the "By Changing" cell, of course, you would have generated a result of 40 units having to be sold at ?100 each, giving the same resultant ?4,000 required profit. ******** 52.1. Creating Forms for Printing out or for Online Completion You can use Excel to create forms from Excel lists, other databases, for printing out and for online completion in Excel or on the Internet. Here we will be looking at Excel's own ability to create and organise forms from lists (columns) in its own worksheets. Forms may include columns/areas on the left with questions in them and areas opposite them to their right for completion by the recipient which are highlighted with such as border lines and/or shaded colour and you can incorporate controls such as option buttons, checkboxes and dropdown lists. You can merge cells and you can change the width and height of columns and rows. If you cannot see the result of a formatted form on screen, you will probably find it easier to create a form than to actually use one yourself with a screenreader. You could start by loading one of Excel's built-in forms or a business form downloaded from the Net but this may present more difficulties than starting from scratch and designing your own form, so that you know where things are, how they have been formatted and what is expected of the person filling the form in. You can create most types of forms with Excel, although it is most attuned to creating forms which require the collection of information for use in calculations, analysis and financial documents, e.g. for a mortgage table or customer price quotation. If you wanted to create a form with spectacular layouts and graphics, you are more likely to want to use Microsoft Word to do this; and if you wanted to create a form for use on the Internet, you are more likely to want to do this in MS Front Page. To create a form which incorporates links to relational information, you would use MS Access to create and design the form. Because creating forms and inserting object controls into them is not a very screenreader-friendly thing to do, you may benefit from having a sighted person sitting with you when you first experiment with the forms feature to advise you of what your inserted controls look like and what the overall display and layout/legibility of your forms is like. After a while you should get a feel for what you are doing and how to achieve what you specifically want. 52.2. Enabling the Forms Toolbar You will have to enable the Forms Toolbar to use the forms features. Do this by: 1. Press ALT V (for View) and then T (for Toolbars). 2. Then ARROW down to "Forms" and press ENTER. 52.3. Designing Your Own Form for Completion Online on Your Company Network You would create and design your own online form for use in Excel by: adding controls like checkboxes, listboxes and option buttons to your worksheet. Adding data validation rules. Adding data validation dropdown lists. Protecting areas of the form you do not want anyone to be able to change. Saving the form on your own computer as an Excel template. Making the form available to other people over your company network by saving it in a shared network folder. 52.4. Example of Inserting a Checkbox and Listbox Control into Your Form After creating your workbook as required with all of the necessary column and/or side headings, textual questions and prompts, formatting, foreground and background colours, grid lines, borders, font types, etc, you can insert the elements of your worksheet form which are to take the form of objects such as checkboxes and dropdown lists. You can also give some of these control objects text titles and format them. 52.4.1. Example 1--Checkboxes 1. If you wanted to insert a checkbox control starting in cell B2, you would firstly move to that cell. 2. Press the ALT key and then release it and then keep pressing CONTROL TAB to move through the various Toolbars at the top of the screen. In addition to the list of form controls you can select, you may find options for such as Font and Lines. You may also find other toolbars, if you have turned them on, such as for "Drawing" and "chart". 3. When on the form control list, which is likely to start with "Label", right ARROW through several more control objects such as Group Box", Option Button, Listbox", "Spinner button, etc. Press ENTER on "Checkbox". Note that in Excel 2003 several of the options in this list will be disabled and not available. This is because they pertained to earlier versions of Excel and are not used in later versions but could still be used if you had imported a workbook from an earlier version of Excel. 4. The next stage requires some of your mouse cursoring and routing skills. You will have to route the mouse pointer to the PC cursor with your screenreader's routing hot key if it has one, e.g. INSERT NumPad - with JAWS and Window-Eyes. After employing your screenreaders route mouse pointer to the PC cursor hot key, you just press your screenreader's simulate left click button to complete the pasting of the checkbox control into Cell B2 , e.g. NumPad SLASH with JAWS and Window-eyes and ENTER with HAL. 5. Now, without doing anything else first, open up the Context Menu by pressing SHIFT F10 and you will be presented with a list of commands you can now apply to the checkbox object in Cell B2. ARROW up or down this list and view the choices. 6. To give your checkbox a textual title, press ENTER on "Edit Text". An editfield will open up for you to type the title you would like to be attached to your checkbox for easy sighted identification purposes, e.g. "Tick Here". You then press ENTER. This editbox is not available with all controls. 7. Go back into the Context Menu with SHIFT F10 and press ENTER on the "format Control" option. You should be on the "Control" property sheet but if you are not, just press CONTROL TAB until you get their. Note that there are several other sheets in here which you can select conditions and options for your checkbox or any other control object from, depending on the type of control you are creating. 8. In the "Control" sheet TAB to "Unchecked" and leave it on this option if you want the checkbox you are inserting to be unchecked by default, otherwise ARROW down to "Checked" if you want this to be the normal state of the box on your form. If it is checked, the linked cell will return a value of "true"; if it is unchecked, the value returned will be "false", if the value is neither checked or unchecked (mixed), what willbe returned is "#N/A"; and if the linked cell is empty, Excel interprets the checkbox state as "false". If you want the checkbox to have 3-D shading to make it stand out more, you can TAB to this and press SPACEBAR to check it on. 9. to finish, TAB to "OK" and press ENTER and then press ESCAPE. 10. If you cannot easily now move around your spreadsheet and seem to be locked into the cell with the control object in it, just press CONTROL G (for Goto) and type another cell reference in and press ENTER. This will move the focus for you and you can then go back to the cell with the control object in it to view or spell-check, etc, although in normal mode your screenreader is unlikely to be able to "see" much if anything in these kinds of controls. 11. If you like, after going to your checkbox on screen, you can press F7 to spell-check the title as normal. 12. The default look of your just inserted checkbox is that it will be oblong and will cover almost two default sized cells downwards and two default sized cells across, i.e. it will spill over from C3 to D3 and downwards to C4 and D4. However, if you had of made cell C3 both deeper and wider before inserting the checkbox, then it could quite easily of been accommodated into one single cell. The checkbox will have a border of its own around it and within this border, to the left, will be a blank space if you had elected to have the checkbox unchecked or it will contain a tick mark if you had selected for it to be checked by default. To the right of this tick mark or blank will be the title you gave to the checkbox if you gave it one, all within the checkbox's own border lines. The checkbox has resize handles in each corner so that it can be dragged in any direction to alter its size and shape. Note: If you find the procedure at step 4 to be difficult or tedious and you have to do this often, you could go through it carefully once you have got used to it but this time with the macro recorder running and thereafter be able to achieve all of that stage by simply pressing a macro hot key (see section 53 for how to do this). There is also an "Assign Macro" option in the Context Menu which you can use at step 5. 52.4.2. Example 2--Listboxes 1. To insert a listbox, follow the above example 1 steps, selecting "Listbox" at step 3, and continue to step 7 but skip step 6 as this is not available with listboxes. 2. After going into the "Format Control" dialogue on the Context menu and then the "Control" property sheet, you will now have to TAB through and complete several editfields. In the "Input Range" field, type the cell range which your list will cover, e.g. if you want six list choices in the cell range C2 to C7, type "C2:C7" in here. Then TAB to "Cell Link" and in here type the cell you would like the result of the list selection to be displayed in, e.g. D2. Your list of items will automatically be numbered 1 to 6 by Excel, with the cell C2 choice being number 1, cell C3 choice being number 2, etc. 3. Now TAB to "OK" and press ENTER. 4. Now complete your six choice selection list by going to cell C2 and typing any item in here which you might want in your list, e.g. shoes. complete cell C3 with socks, C4 with trousers, C5 with jacket, C6 with gloves and C7 with hat. 5. Having now created your listbox and filled it in with a list of things to choose from, go to one of the cells with a list item in it and press your screenreader's left mouse click key to select it (you may have to do this in mouse mode) and then go to your linked cell (cell d2) to view what has happened. You should find that the number of the item in your list has been generated in here, so that any recipient of the data will know what list item was chosen, e.g. if you clicked on item 2 (socks) then a figure 2 will now be in the linked cell. The recipient of this data can now use it to make simple lists of requirements or even create a macro to process it or use a formula to perform calculations on it. 52.5. How to View and Verify That Your Control Object Has Been Inserted Your screenreader may have a hot key to list and then let you jump to and view objects in a worksheet or form. JAWS can do this by you pressing CONTROL SHIFT O. You can then ARROW down the objects, if there is more than one, and press ENTER on one of them to go to it. The fact that JAWS finds the checkbox object in your form on screen will verify that it is actually there and the cell range it covers. Pressing your screenreader's read cell contents hot key should further confirm that you have gone to the top leftmost cell containing the object, e.g. INSERT C with JAWS, ALT C with Window-Eyes and NumPad 3 with HAL. In the above objects list, you may also be told how many cells an object covers, e.g. if you have given the object a title which is wider than the column width and have not increased the width of the column already, you may be told that the control object covers C3:D4. If your screenreader does not have a view/find objects hot key, you should be able to view the control by going into mouse mode and ARROWING down from the top of the screen to find it. You may also be able to view the title of the object if you gave it one. 52.6. Printing a Form out for Manual Completion Having designed and saved a form, you can then print it out in the normal way. With the form on screen, just press CONTROL P or ALT F, P, go through the standard print options and then Press ENTER on the "Print" button. ******** 53.1. Basic Excel Macros A macro is the recording of several consecutive commands to be saved under one command name, which you can then run to automatically carry out all of those individual commands in the order they were recorded to easily and quickly achieve and automate a task which you carry out regularly. To some extent macros, styles and autotext are similar and overlap, although more complicated and advanced results can be achieved with macros and you can get into the realms of using the Visual Basic and Microsoft Script Editors. Whole books have been written on Office macros, so this is a basic example of how they work only. 53.2. Recording a Macro 1. To record a macro, you would press ALT T, M, R, to open up the macro editor. 2. You then type your preferred macro name into the editfield you come into by overtyping the default macro name already in there. Do not put spaces in the name you give to the macro. 3. If you TAB on to the "Description" editfield before carrying out step 4 below, you will find a standard description in there, such as macro recorded 20/11/02 by John Wilson, so replace this with your own more meaningful description if you like. 4. SHIFT TAB back to the "Shortcut Key" button and type in a key combination which you would like in future to use to invoke your macro which does not conflict with one of Windows or your screenreader's own shortcut key combinations. Use something a little obscure, like ALT CONTROL /. Having said this, this step is optional as you can always run your macro from the Tools menu instead of from a shortcut if you wish. 5. TAB on again to a "Store Macro In" combobox and "This Workbook" is likely to be chosen. You would normally want your macros storing with your Workbook if they are intended to generate actions specific to that workbook only but you can ARROW up to other choices, such as "New Workbook" and "Personal Macro Workbook". If you choose "Personal Macro Workbook" then you will be storing your Macro in an Excel folder which then makes your macro available for use on any workbook whenever you use Excel. So make your choice by ARROWING to it. 6. TAB to "OK" and press ENTER. 7. You will now be on the macro recording screen, so type any text or single commands you would like to have in your macro when you run it as a mini-program, e.g. text with any attributes you would like to give it, insert control objects like checkboxes, change column widths, type in already set-up Windows shortcut commands, press the keystrokes to take you through menu items and dialogue boxes and their steps to achieve a given goal, and just about anything that you could do with Excel on a single command basis, in the order you want them to appear when you run the macro and exactly as you would like them to appear on the screen in your workbook. 8. After typing all of the steps for the macro, stop recording it by pressing ALT T, M, R. 53.3. Running a Macro To run your macro and thus all of the commands recorded within it, in the order you recorded them: 1. If you elected to allocate a shortcut key combination to your macro as shown in step 3 above, you can run the macro by simply pressing that key combination, e.g. ALT CONTROL /. 2. If you prefer (or did not allocate a shortcut to the macro), you can either press ALT F8 or ALT T, M, M, to open up a list of all of your macros and ARROW down the list to the one you want and press ENTER on it to run it. You may have to get this combobox list of macros opened by pressing ALT down ARROW first and you may only be able to hear the individual macro names by pressing your screenreader's read current line hot key. Note: Macros of all types are often used by virus writers to create damaging viruses, using the Visual Basic programming language, so only send macros as attachments to e-mails after letting the recipient know you are doing so. Never open a macro attachment to an e-mail you yourself receive without first running a good quality, up-to-date virus-checker on it, such as McAfee or Norton. ******** 54.1. Using Lists in Excel as Database Forms You can create lists of information in Excel and then use them to perform basic database management and other database actions. You can search in these database lists and accomplish sub- totalling and sorting actions. The columns in the lists represent fields and the rows represent records. In this way you can, for instance, maintain and run searches on lists of customers and their addresses or phone numbers and you can keep lists holding such as invoice and car hire records. 54.2. Creating Data Forms for Storing and Manipulating Data A data form is a dialogue box which provides a convenient way to enter or display one complete row or record of information in a list at once. The columns on the worksheet which you base the data form on must have label headings at the top of each column, which Excel then uses as a means of creating fields on the form. 54.3. Workbook Exercise 15--Creating a Data form and Searching in It To create a basic data form of names and phone numbers: 1. Open a blank worksheet. 2. Type the following three Column label headings in Cells A1, B1 and C1 respectively: Surname; First Name; Phone No. 3. Now make up 12 surnames of your own choice and type them in column A from A2 to A13. 4. In B2 to B13 type in six Christian names of your own choice. 5. from C2 to C13 type in 12 fictitious phone numbers. 6. in future you could add entries to this worksheet in the same way as you have just done above or you could elect to use the data form dialogue box to do this. The data form option can now be used to view and navigate around these details and you can add to them, delete some of them, do single record or wildcard searches for one or more of them, etc, and any changes you make to any of the fields can be done directly from the keyboard without pressing F2 to get into editing mode. 7. So, to practise this, press ALT D (for Data) and then O (for Form). 8. You will come onto the first person's entry in your data list. To navigate amongst the records and fields you can: use down ARROW to move through each record surname by surname or phone number by phone number, depending on the field you are currently in. Pressing the Page up or Page down keys takes you through the records 10 at a time. Pressing the TAB or SHIFT TAB keys moves through fields a field at a time. Using the left and right ARROWS in a field moves through characters one at a time and you could correct any spelling mistakes by deleting a letter and replacing it if you wished. 8. If you TAB or SHIFT TAB to and press ENTER on the "New" button or press ALT W, you will be able to enter a new record for someone else. After entering the new record, press ENTER on "Close" or press ALT L. 9. When on any field of a record, you can press the DELETE key to remove that field only or you can press ALT D to delete the whole record with all of its fields. 10. To do searches for individual records or lists of related records you would TAB or SHIFT TAB to and press ENTER on the "Criteria" button or press ALT C. You will be presented with a list of the fields in your data form to TAB through and you can type someone's first name, surname, etc, into one of these and then press ALT P (for Previous) or ALT N (for Next) and press ENTER to find an instance of that person's name, then press ALT P or ALT L and ENTER again to find the previous or next instance of the same thing. You can use wildcards to find lists of records with similar characteristics, such as Wil?on to find the name Wilson, S*n to find the first names of Charon and Susan, etc. each time you press ALT C to open the Criteria search feature, you will have to hit BACKSPACE to remove the last search entry you typed to be able to type a new one in. Note: The maximum number of column fields you can display in a data form at once is 32. ******** 55.1. Automatically Generating Sub-Totals and Grand totals for your worksheets Excel can generate and insert both sub-totals and grand totals at the end of columns of figures for you if you prefer to do this rather than using manual methods. What you do is: 1. For this example, create a worksheet with three columns in it and several rows of figures. The columns must be given textual label headings. 2. With the cursor in one of the columns and having highlighted it, Press ALT D (for Data) and then B (for Subtotals). 3. The dialogue which opens up lets you fine tune how your totals will appear and what type of total you will get, e.g. a summed total, an averaged total, etc. 4. TAB through these options and note that you can ARROW down to have your sub- and grand totals appear below any of your three columns and that you can also ARROW up and down a list of different types of totalling, e.g. sum, average, count, etc. 5. After making your choices, TAB to "OK" and press ENTER to finish. 6. Sub-total and grand total headings will have been inserted below your columns and several formulae will have been inserted in strategic cells within the spreadsheet. 7. After, say, generating a grand total for summing, you can run the sub-totals feature again if you like and then obtain another sub-total, such as for averages, by unchecking the "Replace Current Subtotals" checkbox. ******** 56.1. Using the Excel 2003 Research Services Feature to Find Information The new Research feature of Excel/Office 2003 permits you to quickly and conveniently access information from your computer and from the Internet from within your Microsoft Office program. The results of your research searches can then be immediately inserted into your open document. This feature is available across the whole Office 2003 suite of programs, e.g. Excel, Word, Outlook, Access, etc. You can search for reference information from multiple sources simultaneously or from a specific source. Some of these research resources are free, e.g. the online Encarta encyclopedia, whereas others have a subscription fee. It must be stated, however, that screenreaders at the time of writing (beginning of December 2003) were not very good at dealing with the Research Pane and its contents. JAWS 4.5 permits you to view the dialogues and buttons in the Research Pane but it is not easy to get things working without going into mouse mode. JAWS 5.0 does better but you will still have to enter mouse mode to view the results of any search for information, which will be interspersed with some of your worksheet labels. Freedom Scientific are currently working on improved script files for Office 2003. Window-Eyes 4.5 will work with Excel 2003 but you will require the new Office 2003 set files. HAL before Version 5.03 will not work with Excel 2003 but Version 5.03 has maps for it, although at present they do not work very well in the Research Pane as far as viewing the result of your searches is concerned. You will have to experiment and decide for yourself if this research feature is worth the time and effort for you or if it is easier and quicker for you to use an alternative on your hard disk or on the Web dictionary, encyclopedia or general search engine to find what you want. To use the Research feature from within Excel 2003: 56.2. Searching for Thesaurus, Encyclopedia and Language Translation Resources 1. Press ALT T (for Tools) and then R (for Research). 2. You should come into the Research Pane, which takes over the standard Task Pane area. If it does not come into focus, you can press the F6 key to get there. Every time the Research Pane looses focus and you want to get back to it, use the F6 key and use the F6 key to also return to your worksheet. 3. When you first open the Research Pane, you will fall on an editfield where you can type a word or phrase in to have a search done on. For this example, type the word "magma" in here. Note that if you had of typed more than one word, such as blue whales, you would have only received information on the word blue rather than on the large sea creature, because to get information on both words, you have to put them in double quotes, e.g. "blue whales". 4. TAB once to the "Start Searching" button, which you can press ENTER on to do a default search of several online Web resources, such as the online English Encarter encyclopedia. 5. If, before you activate the above search button, you TAB forward, you can ARROW down several search resources to narrow the search down to a particular place on the Web or have all resources searched. They include resources like Encarta, Thesaurus, Elibrary, All Business and Financial Sites, etc. 6. After you have been taken onto the Web by Internet Explorer and it has found the type of information you want, you should be able to go into mouse mode and view the findings on screen but whether you can do this and to what degree things will be intelligible will, as already stated, depend on which screenreader you are using and how up to date it is. If you loose focus on the Research Pane at any time and come back to the worksheet grid, just press F6 to return to the Research Pane. 56.3. Searching for Online resources and downloads on the Microsoft Office Marketplace Site When you have the Research Pane open, you will be able to TAB to a "Get Services on Office Marketplace" link to press ENTER on to take you online with Internet Explorer. This will open up the Microsoft Office Marketplace home page, which you can then TAB and ARROW down, etc, to avail yourself of several available resources, such as free templates, clipart and the Elibary. Just follow the online instructions if you would like to download anything or join any of these services. 56.4. Changing Research Options and Enabling Parental Control over what can be accessed and Viewed by Children After enabling the Research Pane and moving to it, you can TAB to and open up a sub-dialogue called "Research Options". In here you can: 1. ARROW down a long list of information searching resources in different languages plus a "Translation" option. To use this latter option, you will have to have several languages installed on your PC. By default, on my English system the following options are checked on but you can press SPACEBAR on any of them to turn more on or some current ones off: Encarta English encyclopedia, English thesaurus and Elibrary. You may only be able to verify which are checked on in mouse mode. 2. You can then TAB to a "Add Services" button to register yourself for even more information services to search if you like. This will take you onto the Net. 3. TABBING another couple of times will enable you to activate a "Parental Control" button where you can press SPACEBAR to turn on a "Turn on Content Filtering to Make Services Block Offensive Results" checkbox. This will do just what it says and protect your children from offensive adult or expletive search findings by filtering them out. After pressing SPACEBAR on this checkbox, you can then type in a password so that only adults knowing this password can then obtain access to all search findings including those considered to be offensive. 4. After viewing and/or changing any of these search facilities and filters, TAB to one or two "OK" buttons and press ENTER. ******** 57.1. Appendix 1 Comprehensive List of Excel's Shortcut Keystrokes Whether the below hot keys do anything when you press them will, of course, depend on what type of worksheet environment you are in, whether or not you have highlighted any cells, etc. Press BACKSPACE: To delete the character to the left of the insertion point. Press DELETE: To delete the character to the right of the Insertion point. Press PRINT SCREEN key: to copy the image of the screen to the clipboard. Press ALT PRINT SCREEN key: to copy the image of the active window to the Clipboard. Press ALT SHIFT right ARROW: to group rows or columns. Press ALT SHIFT left ARROW: to ungroup rows or columns. Press CONTROL C: To copy text or objects. Press CONTROL X: to cut/move text or objects. Press CONTROL V: To paste text or objects. Press CONTROL P: To print a file. Press CONTROL S: To save a file/workbook. Press CONTROL DELETE : to delete text to the end of the line. Press CONTROL B: To embolden text. Press CONTROL I: to italicise text. Press CONTROL U: To underline text. Press CONTROL 0: To hide a column. Press CONTROL SHIFT =: To insert blank cells. Press CONTROL SHIFT 0: To unhide a column. Press CONTROL SHIFT ENTER: To enter a formula as an array formula. Press CONTROL SHIFT 1: To invoke two decimal places with commas for numbers. Press CONTROL SHIFT 2: To change the time format to hours and minutes. Press CONTROL SHIFT 3: To change date format to Day, Month and year. Press CONTROL SHIFT 4: To invoke two place decimals for currencies. Press CONTROL SHIFT 5: to invoke percentages. Press CONTROL SHIFT 6: to invoke two decimal exponential numbers. Press CONTROL SHIFT +: to insert blank cells. Press CONTROL 1: To display the format cells dialogue box. Press CONTROL 5: To create or remove a strike through. Press CONTROL 6: To rota hide objects, display objects and display place holders for objects. Press CONTROL 7 : To toggle the toolbar on and off. Press CONTROL 8: to display or hide outline symbols. Press CONTROL 9: To hide the current row or selection of rows. Press CONTROL SHIFT ~: To apply the general number format. Press ESCAPE: to cancel a cell entry. Press F1: To invoke Excel's online help system. Press F2: To be able to edit the contents of the current cell. Press F3: to paste a name. Press F4: to repeat the last action. Press F5: To open the Goto dialogue to go to a specific cell. Press F6: To move to the next pane. Press F7: To invoke the spell-checker for any text in your spreadsheet. Press F8: To turn on extending a selection by using the ARROW keys. Press F9: To calculate all sheets in all open workbooks. Press F11: To create a chart which uses the current range. Press F12 or SHIFT F12: to save a worksheet or workbook via the Save As dialogue box. Press SHIFT F6: To go to the previous pane. Press SHIFT F10: To open a Context Menu of commands. Press ALT F4: To exit Excel. Press CONTROL ALT F9: To calculate all open sheets in the active workbook. Press SHIFT F9: To calculate the active worksheet only. Press right, left, up or down ARROW keys: To move one cell at a time in any direction. Pressing TAB also moves you one cell at a time along a row from left to right and SHIFT TAB moves you backwards. Pressing ENTER after completing a cell moves you down a cell in the current column. Press PAGE up or down: To move through a spreadsheet upwards or downwards respectively by 25 lines each press. Press ALT =: To generate an auto sum. Press ALT ;: To highlight visible cells only in the current selection. Press ALT ': To display the style dialogue box. Press ALT f1: To insert a chart sheet. Press ALT F8: to display the macro dialogue box. Press ALT F11: to display the visual basic editor. Press F5 or CONTROL G: To specify a cell co-ordinate to go to, e.g. C6. You can also jump to a named cell with the Goto command if such a cell has been given a name. Press =: To then be able to type a formula into the current cell. Press CONTROL SPACEBAR: To highlight/select the whole of the column the cursor is currently in. Press SHIFT F1: To obtain What's This help. Press SHIFT SPACEBAR: To highlight/select the entire row the cursor is in. Press SHIFT BACKSPACE: To collapse the highlighted selection to the current cell minimised. Press SHIFT F2: to edit a comment in the current cell. Press SHIFT F3: to create names from row and column labels. Press SHIFT F11: To insert a new worksheet. Press ALT ENTER: to insert a carriage return and start a new line in the same cell. Press ALT 1: to go to the previous folder. Press ALT 6: To switch between list details, properties and preview views. Press ALT U: to select an autoshape from a menu on the drawing toolbar, then press CONTROL ENTER to select the one you want. Press CONTROL F: To bring up the find dialogue box. Press CONTROL H: To bring up the replace dialogue. Press CONTROL F3: to define a name. Press CONTROL F7: to move a document window. Press CONTROL K: To insert a hyperlink. Press CONTROL -: to delete the selection. Press SHIFT F3: to paste a function into a formula. Press SHIFT F4: To find the next instance of a search string. Press CONTROL with any of the ARROW keys: To move to the edge of data regions, e.g. CONTROL down ARROW when in the middle of row D of a page with several rows in it will jump you to the last cell in row D with data in it. Press CONTROL PAGE down: To switch to the next spreadsheet page including any with charts on them. Press CONTROL PAGE up: Opposite of the above. Press PAGE down: To move forward by a screen of data, i.e. jump forward by 25 lines. Press PAGE up: Opposite of the above. Press ALT PAGE down: To move right one screenful of information, which will normally mean jumping 11 rows to your right each time you do this. Press ALT PAGE up: Opposite of above. Press END and then right ARROW: To jump to the end of the current row. Press END and then down ARROW: To jump to the end of the current column. Press HOME: To jump to the beginning of the current row. Press CONTROL F11: to insert a Microsoft 4.0 macro sheet. Press CONTROL ;: To insert the date in the current cell. Press CONTROL SHIFT ;: to insert the time. Press CONTROL SHIFT PAGE down: To highlight the current and next sheet in the workbook. Press CONTROL SHIFT PAGE up: To select the current and previous sheet in a workbook. Press CONTROL ENTER: To fill the selected cell range with the current entry. Press CONTROL SHIFT F: to invoke the change font dialogue. Press SHIFT ENTER: To complete a cell entry and move up in the selection. Press CONTROL ': to copy the formula from the cell above. Press CONTROL SHIFT ": To copy the value from the cell above. Press CONTROL /: To highlight the entire array which the current cell is in. Press CONTROL 0: To hide the current column. Press CONTROL =: To calculate all sheets. Press CONTROL [: To highlight only cells directly referred to by formulae in the selection. Press CONTROL \: To highlight cells in a row which do not match the value in the active cell in that row. Press CONTROL ]: To highlight cells with formulae that refer directly to the active cell. Press CONTROL `: to toggle displaying formulae or values. Press CONTROL A: To highlight the whole worksheet. This also displays the formula pallet after you type the function name in a formula. Press SHIFT BACKSPACE: To select only the active cell when multiple cells are selected. Press CONTROL SHIFT SPACEBAR: To highlight all objects on a sheet once one object has already been selected. Press CONTROL d: To fill down. Press CONTROL N: to open a new workbook. Press CONTROL O: To open a workbook. Press CONTROL R: to fill right. Press CONTROL Delete: to delete to the end of the line. Press CONTROL F4: To close the current window. Press CONTROL F6: To move to the next open window. Press CONTROL F9: To minimise the workbook. Press CONTROL F10: to maximise or restore the workbook. Press CONTROL SHIFT SPACEBAR: To highlight all objects. Press CONTROL SHIFT !: To invoke two decimal place format. Press CONTROL SHIFT $: To invoke currency format. Negative numbers will either be enclosed in round brackets or preceded by a minus sign. Press CONTROL SHIFT %: To invoke the per cent format. Press CONTROL SHIFT &: to obtain an outline border around the highlighted cells of your worksheet. Press CONTROL SHIFT (: To unhide rows but you will have to highlight the whole of a column of rows with CONTROL SPACEBAR before you can do this. Press CONTROL SHIFT ): To unhide columns but you will have to highlight the whole of a row of columns with SHIFT SPACEBAR first. Press CONTROL SHIFT *: To highlight/select the current region, i.e. the data area enclosed by blank rows and blank columns. Press CONTROL SHIFT [: To set the top left corner of a region at the current cell. Press CONTROL SHIFT ]: to set the current cell as the bottom right corner of a region. Press CONTROL SHIFT _: To remove borders. Press CONTROL SHIFT A: to insert the argument names and parentheses for a function after you type the function name in a formula. Press CONTROL SHIFT {: To highlight all directory direct formula cells. Press CONTROL SHIFT |: To select cells in a column which do not match the value in the active cell in that column. Press CONTROL SHIFT }: To select cells within formulae which directly or indirectly refer to the active cell. Press CONTROL SHIFT ~: To obtain general number format. Press CONTROL SHIFT left ARROW: to select to the beginning of the data region. Press CONTROL SHIFT right ARROW: Opposite of the above. Press CONTROL SHIFT up ARROW: To highlight to the beginning of the data region. Press CONTROL SHIFT down ARROW: Opposite of the above. Press CONTROL ALT TAB: to insert a tab. Press CONTROL Z: To undo your last action. Press SHIFT up, down, left or right ARROW: To highlight/select cells as you go. The keystrokes you can use to move around within any selection you have made are: Press ENTER: To move from top to bottom within a selection. Press SHIFT ENTER : To move from bottom to top within a selection. Press TAB: To move from left to right within a selection. Press SHIFT TAB: Opposite of the above. Press CONTROL FULL STOP: To move clockwise to the next corner within the selection. Press ALT CONTROL right ARROW: To move to the right between non- adjacent selections. Press ALT CONTROL left ARROW: Opposite of the above. Press ARROW key: Pressing any ARROW key in any direction will remove the selection, i.e. unselect it. Note 1: You can use the standard Windows highlighting methods to highlight/select text in cells, i.e. combinations of CONTROL, SHIFT, the ARROW keys and the HOME and END keys, e.g. CONTROL SHIFT PAGE up to select one screen of information, SHIFT HOME to select from the beginning of the current row to the cell the cursor is currently in, CONTROL SHIFT END to select from the current cursor position to the end of the worksheet, etc. Note 2: The standard Windows shortcuts for copying to the Clipboard, pasting, etc, all work as normal. ******** 58.1. Appendix 2 Other Tutorials by this Author All of the below titles are available as plain text files as downloads from my Website at: http://web.onetel.com/~fromthekeyboard Tutorial titles and brief descriptions 1. "Accessing the Internet from the Keyboard", Volume 1, covering Web and e-mail protocols, Web Search engines, navigating the Internet with Internet Explorer 5.0/5.5/6.0, e-mailing with Outlook Express 5.0/5.5/6.0, Downloading files and programs from the Net, using a range of Internet search engines, Joining Internet newsgroups with Free Agent 1.92, configuration and hints and tips for screenreader users, and much more. 2. "Accessing the Internet from the Keyboard", Volume 2, covering hints and customisation, Download Managers, Online Auctions, Internet Chat Rooms, RealAudio, Internet Shopping and Internet Banking. 3. A selection of separate and individual manuals instructing visually impaired people how to use off-the-shelf print scanning/reading programs via screenreaders and the keyboard, including TextBridge Pro 98, TextBridge Pro 9 and Millennium, Omnipage Pro 10, 11 and 12, ReadIRIS Pro 6, TypeReader Pro 6 and Abby FineReader Pro 5, 6 and 7. Each scanner tutorial is an independent manual in its own right. For example, the titles of the principal two of these scanner tutorials are entitled: "Using OmniPage Pro 10, 11 and 12 from the Keyboard to Scan Print" and "Using FineReader Pro 5, 6 and 7 from the Keyboard to Scan Print". 4. "Audio Playing, Copying and Sound Editing From the Keyboard", Edition 1. This covers Easy CD Creator 4, Sound forge 4.5, Windows Media Player 6, Windows Recorder, Winamp 2.72, Freerip.mp3, RealPlayer 8 Basic, and much more. 5. "Audio Playing, Copying and Sound Editing From the Keyboard", Edition 2. This covers Winamp 5.0X, GoldWave audio editor 5.06, CDEX ripper 1.51, Basics of burning with Nero 5.5 and much more introductory and general sound-related information. 6. "Nero Burning-ROM Versions 4,5 and 5.5 from the Keyboard" (includes Nero INCD 3.3 and Nero Media Player). This covers burning of data and audio CDs and DVDs withe Nero Burning-ROM and the Nero Wizard, Saving and reopening compilation templates, Using Nero online help, burning/cloning whole hard disks and partitions to CD or DVD, converting MP3 files to other formats, a good deal of specific configuration and general information on CD and DVD burning drives and CD and DVD disks, using Windows Volume Control, and much more. 7. "Nero Burning-ROM 6 Ultra and Enterprise Editions from the Keyboard" (includes Nero INCD 4). This covers burning of data and audio CDs and DVDs withe Nero Burning-ROM and the Nero StartSmart interfaces, Saving and reopening compilation templates, Using Nero online help, burning/cloning whole hard disks and partitions or folders to CD or DVD, converting MP3 files to other formats, ripping sound files to MP3 or MP3 Pro files, a good deal of specific configuration and general information on CD and DVD burning drives and CD and DVD disks, using Windows Volume Control, and much more. 8. "Microsoft Excel 97, 2000, 2002 and 2003 from the Keyboard". This takes spreadsheet users from the beginner stages of Excel through much intermediate material and also covers a few more advanced features. It will give you the skills to use Excel for home accounting purposes, for keeping self-employed small business records and for use in the employment workplace. 9. "Microsoft Outlook 2000 and 2002/XP from the Keyboard". This is a tutorial instructing on how to use the richly-featured suite of programs which is a must for anyone seeking employment or wanting to do advanced e-mailing or calendar and other related tasks at home or at work. It covers all of the main features of MS Outlook and many other more technical topics. Covered is: E-mailing, Calendar, Journal, Tasks, Notes, Contacts, arranging appointments and meetings, searching, plus customising Outlook for visually impaired and blind users and appendices of Outlook general shortcuts and HAL, JAWS AND Window-Eyes hot keys and much more. 10. "Microsoft Word 97, 2000, 2002 and 2003 from the Keyboard". Available as a plain text file and instructs on how to use over 45 separate skills in these powerful leading word-processors for use at home or in the workplace to make you highly productive and efficient. ******** (The End.)