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, if it is there. 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 th