ECDL Spreadsheets for Screenreader Users

• HOME
• ABOUT ME
• TUTORIALS
• COURSES
• PROJECTS
• DONATE

Jaws and Windows Keystrokes and Keyboard Shortcuts for ECDL Module 4: Spreadsheets

The spreadsheet module covers all the skills and Windows keyboard shortcuts and Jaws keystrokes you will need to successfully complete the module.   When the tutorial was written my student was using Microsoft Office 2003, Microsoft Excel and Jaws 7.0   The majority of the keyboard shortcuts are Microsoft Office shortcuts so users of other screenreaders will not be disadvantaged.  To access this tutorial follow the Spreadsheet Link on the tabbed bar.

1.  Open a named spreadsheet file.

First hit the “Windows” key and Jaws will respond “Start Menu”, now hit “P” for programs and  then keep tapping “M” for Microsoft until you get to Microsoft Excel.  Now hit the “Enter” key and Microsoft Excel will open, giving a new document upon which you can enter data.  However we do not want this document, so close it by hitting “Ctrl + W”.

To open the file, hit “Ctrl + O”, to locate the directory where the named file is stored, hit “Alt + I” and then use the “up” and “down” arrows to find “My documents” or the directory that your tutor has allocated to you. Once you have located it, hit “Enter”.  To find the file keep hitting “Tab” until Jaws says “List view”, then either use the up and down arrows to locate the file or hit the first letter of the filename, keep hitting the first letter until Jaws responds with the filename and then hit “Enter”.

2.  Save the spreadsheet under a given name to your candidate disk.

As we are going to rename the file we need to use the “Save As” command.  To access this, tap the “Alt” key to highlight the “File” menu and use the “down arrow” to go to “Save As” and hit “Enter”.  Type in the filename “Your initials pay”, then do “Alt + I” and use the “up and down” arrow keys to select the drive where you wish to save the document.  If you need to create a new folder, which is a good idea so you can keep all your module 4 documents in one place then “tab” around until you hear “Back button unavailable” and then tap your right arrow key until you get to “New Folder” button and then hit “Enter” and type a name for your folder and hit “Enter”.  Finally “tab” around until you get to “Save” button and hit “Enter”.

3. Adjust the height of a row as appropriate so that entered data is fully visible.

Go to the row in question either by going to cell “A1” using “Ctrl + Home” and then using the “arrow keys” to locate the cell.  Select the contents of the row by doing “Shift + Spacebar”.  Now format the row by doing “Alt + O” for the “Format” menu and then down arrow to “Row”, hit “Enter” and down arrow again to “Autofit” and hit “Enter” again.  Finally hit any arrow key to un-select the row and avoid making any unwanted changes.

4.  Move to a named worksheet, enter and centre align a portion of text, so that it displays across a given cell range.

Move through the worksheets doing “Ctrl + Page Up” or back through the worksheets using “Ctrl + Page Down”, until you reach the desired worksheet.  Go to the first cell in the spreadsheet using “Ctrl + Home”, and then go to the cell where the typing is required using the command “Ctrl + G” and then type in the cell reference if you know it “eg: A2” and hit “Enter”. Then type in the required data and either hit “Enter” or tap one of the arrow keys to confirm your entry. 
We now need to centre the selection across a given range of cells.  Go to the first cell in the row and select the cells by holding down the “Shift” key and tapping “Right arrow” until you get to the last cell in the given range.  Now tap the “right mouse” button and go down to “Format cells” or press “F” to go straight there and hit “Enter”; now do “Ctrl + Tab” to go to the “Alignment” page and  hit “tab” again until you get to “Horizontal”, now use the down arrow to go to “Centre across selection” and hit “Enter” again.

5.  There is some data omitted from the spreadsheet, and there are some errors.  Make the necessary adjustments. 

Go to the cell requiring amendments quickly by doing “Ctrl + F” for “Find” and then type in the data to be changed and hit “Enter”; next “tab” to “close” and hit “Enter”.  The data to be changed is now highlighted, simply type in your new data and either hit “Enter” when you are finished or hit any of the arrow keys.  If you have a lot of data in a cell that simply needs a minor correction you can edit that data by hitting “F2” using the “Ctrl + Left or Right Arrow” keys to move through a word at a time, then use “Ctrl + Shift + Left or Right Arrow” to select the word to be corrected and type in the correct data followed by “Enter”.

6.  Enter an appropriate function in a given cell reference to calculate a column total. 

Go to the cell reference using the command “Ctrl + G” to bring up a GOTO dialogue box, type in the cell reference eg “B3” and hit “Enter”, you will now be in the cell where the formula is to be entered.  The quickest and easiest way of adding a column total automatically, is to do the Windows shortcut “Alt + =” this automatically selects the cell range with figures in and then sums them together.  To accept the default range of figures, simply hit “Enter”.

7.  Replicate a formula in a given range of cells.

Go to the cell which contains the formula you arrived at earlier.  Select it by holding down the “shift” key and then tapping either the right or down arrow key depending on which direction your cell range is in.  To replicate the formula downwards do the windows keystroke “Ctrl + D”, to replicate it to the right do “Ctrl + R”.

8.  Enter a formula manually in a specified cell reference eg “cell H9” to multiply cell G9 by an explicit non neighbouring cell reference eg “B4”. This formula should then be replicated in a given the cell range eg “H10:H14”.

The question in itself is a bit of a mouthful, however once we understand what is going on, things are fairly straightforward.  Go to cell H9 by doing the command “Ctrl + G” and typing in the cell reference followed by “Enter”.
We now have to tell the spreadsheet that we want to do a formula, so hit the “=” key and then type in the required formula in this case “G9*B4” however because the “B4” bit is an explicit reference and we want to refer only to this cell when we replicate the formula we have to put a dollar sign before the row number and column letter, so the formula ends up as “=G9*$B$4”.  Just so you are all clear on where to find the mathematical operators, the dollar sign is generally “Shift + 4”, the multiply sign is “Shift + 8” and the divide sign is a “/” generally found to the right of the “full stop” which Americans amusingly describe as a “Period”.
OK so now you have entered your formula and hit the “Enter” key we are ready to replicate the formula, in this case we are replicating downwards so go back to the cell which contains the formula.  Select it by holding down the “shift” key and then tap the down arrow key to select the range of cells “H10 to H14”; to replicate the formula downwards do the windows keystroke “Ctrl + D”. Now tap any arrow key to come off the replicated cells.

9.  Find the Minimum from a labelled column of figures using the Insert Function feature and enter the answer in a given cell reference.

To locate a given cell, do “Ctrl + G” and type in the cell reference followed by “Enter”.  As we are going to enter a formula, start by hitting the “=” sign and then do “Alt + I” to access the “Insert” menu and then tap the down arrow until you reach “Function” and then hit “Enter”.  An “Insert function” dialogue box will appear, tap “tab” until you reach the “Select a Function” list box and then tap “M” followed by the “up or down arrow” until you reach “MIN”.  If the “MIN” function is not in the list do “Shift +tab” to get to “Select a category” combo box and tap the “down arrow” until you reach “All” and then hit “Enter”.  Now tab back to the “Select a Function” list box and tap “M” again and then the “up or down arrow” until you reach “MIN” followed by “Enter”.  A “Function Arguments” dialogue box will appear, to accept the default range of cells just hit “Enter” and the minimum value of the column figures will be calculated and appear in the cell where you typed your formula.

10.  Format the contents of a given range of cells with a double underline border effect.

Select the range of cells to be formatted using the “Shift + Arrow keys”  then hit the “right mouse button” key.  Now use the “up arrow” to select “Format Cells” a “Format cells” dialogue box will appear, use “Ctrl + Tab” to move between the tabbed pages until you get to “Border”, then tap the “tab” key again until you reach the “Border Style” box, now use the arrow keys to locate “Thick Double” radio button. Tap “tab” again until you reach Presets “None” button, now tap the “Right Arrow” key to reach “Outline” button and hit “Enter”.  Finally hit “tab” repeatedly until you reach the “OK” button and hit “Enter”.

11.  Format a given column range of cells as currency with two decimal places.

As you work out which columns or rows will need to be formatted as currency with 2 decimal places, you will find the Jaws keystroke “Ctrl + Shift + C” very useful, this has the effect of listing all headings and column contents regardless of where you are in a spreadsheet.

If you know the heading of the column to be formatted do “Ctrl + F” and type in the heading name followed by “Enter”, you will hear Jaws echo the name you just typed, hit the “Esc” key and you will now be at the top of the desired column.  Select the cells containing data with “Ctrl + Shift + Down Arrow”, now tap the “right mouse button” key and then either use the “down arrow” to go to “Format Cells” or tap “F”.  A format cells dialogue box will appear,  do “Ctrl + tab” to go to the “Number” tabbed page, now hit “Tab” to go to the “Category” list box, and then use the “down arrow” to go to “Currency”, hit “tab” again to go to  “Decimal Places” and make sure this is set to 2 (the default is 2 places but it is best to check).  Now “tab” to the “OK” button and hit “Enter”.  Repeat this procedure for all the other columns. 

12.  Format figures in a given  row cell range to show no decimal places.

As you work out which rows will need to be formatted with no decimal places, you will find the Jaws keystroke “Ctrl + Shift + R” very useful, this has the effect of listing all row headings and contents regardless of where you are in a spreadsheet.  If you know the heading of the column to be formatted do “Ctrl + F” and type in the heading name followed by “Enter”, you will hear Jaws echo the name you just typed, hit the “Esc” key and you will now be at the top of the desired column.  Select the cells containing data with “Ctrl + Shift + Right Arrow”, now tap the “right mouse button” key and then either use the “down arrow” to go to “Format Cells” or tap “F”, A format cells dialogue box will appear,  do “Ctrl + tab” to go to the “Number” tabbed page, now hit “Tab” to go to the “Category” list box, and then use the “down arrow” to go to “Number”, hit “tab” again to go to  “Decimal Places” and make sure this is set to 0.  Now “tab” to the “OK” button and hit “Enter”.  Repeat this procedure for all the other rows.

13.  Delete an image from a specified worksheet.

Move through the worksheets using the windows keystroke “Ctrl + Page Up” or “Ctrl + Page Down”.  Once you have reached the correct worksheet do the Windows keystroke “Ctrl +G” a “Go to” dialogue box will open, hit “tab” until you reach the “Special” button and hit “Enter”. A “Goto Special” dialogue box will open, go through the options available using the up and down arrow keys until you reach “Objects” and hit “Enter”, The graphic will then be selected. To check if the graphic has been labelled correctly you can either do the Jaws keystroke “Insert + G” or hit the “right mouse button” and then “up arrow” to “Format Picture” followed by “Enter”, a “Format Picture” dialogue box will open, do “Ctrl + tab” to go to the “Web” page tab and then hit “tab” again until you reach  “Alternative text”; if the graphic has been labelled correctly its name will be highlighted in the alternative text box.  To delete the image simply hit the “Delete” key.  Alternatively you can cut the image using “Ctrl + X”, paste it back again using “Ctrl + V” or copy it using “Ctrl + C”.  If you wish to go back at any point do “Ctrl + Z”.

14.  Add a header with the text centered “Updated by (your name)”.

Go to the “Header and Footer” menu by doing the command “Alt + View” to access the view menu, then use the down arrow to select “Header and Footer”, and a “page setup” dialogue box will open.  Tap “tab” until you get to the “custom header” button and hit “Enter”. Tap “tab” again until you reach the “Centre section” dialogue box and type in “Updated by (your name)”, now tap “tab” until you reach the “OK” button and hit “Enter”. You will be returned to the page setup box, hit “Enter” again to close it.  It is a wise person who remembers to keep save your work by doing “Ctrl + S” after each step has been completed.

15.  Add a footer with a centered automatic date and automatic time at the right.

Go to the “Header and Footer” menu by doing the command “Alt + View” to access the view menu, then use the down arrow to select “Header and Footer”, and a “page setup” dialogue box will open.  Tap “tab” until you get to the “custom footer” button and hit “Enter”.  Now it starts to get a bit complicated as we have to access a series of graphical buttons and then mess about cutting and pasting the results into the correct sections.. Tap “tab” until you reach the “Date” button and then hit “Enter”, this has the effect of pasting an automatic date script in the right section, we want it in the centre section so first select it using the command “Ctrl + Shift + Left arrow”, cut it using “Ctrl + X” then do “Shift + tab” to reach the “Centre section” dialogue box and paste in the date using “Ctrl + V”.  Now keep tapping “tab” until you reach the “time” button and hit “Enter”, this will have the effect of adding a “time script” to the right section of the footer;  Now you can tab to the “OK” button and hit “Enter”, you will be returned to the “Page setup” dialogue box, hit “tab” again until you get to the “OK” button and hit “Enter”.

16.  A new row needs to be added immediately below row 4.

Go to the first cell in the spreadsheet using “Ctrl + Home”, and then tap the down arrow until you reach row 5, insert a row using the command “Alt + I”, then down arrow to “Rows” and hit “Enter”, this will have the effect of inserting a blank row above row 5.

17.  Copy a designated range of cells from one worksheet to another and save the file at this point.

Go to the range of cells that needs copied, select the cells either a cell at a time by holding down the shift key and tapping an arrow key.  Alternatively do “Ctrl + Shift + Arrow key” to select a whole data row or column.
Copy the cells using “Ctrl + C”, then move to the desired worksheet by doing “Ctrl + Page Up” or “Ctrl + Page Down”.  Move to an unoccupied region of the sheet and paste in the data by doing “Ctrl + V”.

18.  You are now required to apply various formatting actions to the other worksheets.  Apply the formatting from a named cell to a range of other cells.

Go to the cell where the formatting is to be copied from and then hit the “Ctrl + C” key to copy the contents of the cell.  Then highlight the range of cells that require the formatting to be applied by holding down the “Shift” key and tapping  one of the arrow keys.  We now have to do a “Paste Special” to do this do “Alt + E” to acces the “Edit” menu and then tap the down arrow to go to “Paste Special”, a “Paste Special” dialogue box will open.  Tap the “down” arrow to go to “Formats” radio button and hit “Enter”, the formatting will then be copied across.

19.  Without adjusting the font size or margins, apply a setting so that the contents of the worksheet would print on 1 page. Do not print at this point.

Access the “File” menu by tapping the “Alt” key then tap the “down arrow” to go to “Page Setup” and a page setup dialogue box will open.  Tap “tab” a couple of times until you reach “Scaling” and the  “Adjust to” radio button, now tap your “down arrow” which takes you to the “Fit to” radio button and the  “pages” combo box where you can specify how many pages you want the sheet to fit on, the default is 1 so leave things as they are and keep tabbing until you reach the  “OK” button and then hit “Enter”.

20.  Apply settings so that the gridlines and the row and column headings would be displayed if printed. Do not print at this point.

Access the “File” menu by tapping the “Alt” key then tap the “down arrow” to go to “Page Setup” and a page setup dialogue box will open.  Tap “Ctrl + tab” a couple of times until you reach the “Sheet” page tab, now tap “tab” until you reach the “Gridlines” checkbox and put a check in the box using the spacebar.  Now tap “tab” again until you reach “row and column headings” checkbox and check the box using the “spacebar”.  Finally tab round to the “OK” button and hit “Enter”.

21.  On a named worksheet enter a formula in a named cell which will count over a given cell range.

Go to the named cell with the command “Ctrl + G” type in the cell reference and hit “Enter”.  Start by telling the spreadsheet a formula is coming, do this by hitting the “=” key and then access the “Insert” menu  doing “Alt + I”, followed by “down  arrow” to “Function” followed by “Enter”.  An “Insert Function” dialogue box will open now  “tab” round to the “Select a Function” list box and tap the down arrow until you reach “Count”, then hit “Enter”.  A “Function Arguments” box will appear with a default range of cells; tap the “right arrow” and then do the Jaws keystroke “Insert + Up arrow” to echo the range of cells already there.  Alter the range of cells to the one given in the question and hit “Enter”.

22.  In a named worksheet, enter a function in a given cell reference that states “Yes” if the number in another named cell is more than 30 and otherwise states “No”.

Go to the named cell with the command “Ctrl + G” type in the cell reference and hit “Enter”.  Start by telling the spreadsheet a formula is coming, do this by hitting the “=” key and then access the “Insert” menu  doing “Alt + I”, followed by “down  arrow” to “Function” followed by “Enter”.  An “Insert Function” dialogue box will open now  “tab” round to the “Select a Function” list box and tap the down arrow until you reach “IF”, then hit “Enter”.  A “Function Arguments” box will appear, the first box is the “Logical test”, here you will have to type something like “D8>30”, then hit “tab” to go to the “Value if true” box, type “yes” and then hit “tab” again, you are now taken to the “Value if false” box, type in “no” and then hit “Enter”. Voila you should have a value of either yes or no in your named cell.

23.  Using a named worksheet, use Freeze Panes to prevent row 5 from scrolling. Sort  a specified cell range by Name in ascending order.

Go down to the row below the one that requires frozen and insert a blank row using the command “Alt + I” for “Insert” and then down arrow to “Rows” and hit “Enter”.  Select the whole blank row using the command “Shift + Spacebar” and then do “Alt + W” to access the “Window” menu and then down arrow to “Freeze Panes”, followed by “Enter”.   To sort a given range of cells go to the first cell in the range and then either hold down “Shift” and tap the “down arrow” or to select a column range do “Ctrl + Shift + Down Arrow”, and then keeping “Shift” held down, tap the “right arrow” to select the rest of the data range.  Now do “Alt + D” to access the “Data” menu and “down arrow" to “Sort”.  A “Sort” dialogue box will open and you will be in the “Sort by Column” list box, tap the down arrow to select the column required and then tap “tab” to choose “Ascending” if you wish to change the selection use up or down arrows and then hit “Enter”.

24.  Copy a given cell range from a named worksheet and place into another named sheet in cell A1 (Widen columns as appropriate).

Go to the first named cell in the range with the command “Ctrl + G” type in the cell reference and hit “Enter”.  Select the cells either a cell at a time by holding down the shift key and tapping an arrow key. 
Alternatively do “Ctrl + Shift + Arrow key” to select a whole data row or column.
Copy the cells using “Ctrl + C”, then move to the desired worksheet by doing “Ctrl + Page Up” or “Ctrl + Page Down”.  Once you arrive at the desired worksheet do “Ctrl + Home” to go to cell A1 and paste in the data by doing “Ctrl + V”. Now make all the columns wide enough by going to the “Format” menu with “Alt + O” and then down arrow to “Column” followed by “Enter” and then down arrow to “Autofit Selection”, followed by “Enter”.  Finally tap any of the arrow keys to deselect the modified cells.

25.  Rename a particular worksheet to a specified name.

Go to the specified worksheet using “Ctrl + Page Up or Page Down”
Go to the “Format” menu with “Alt + O” and then down arrow to “Sheet” followed by “Enter” and then down arrow again to “Rename”, followed by “Enter”. Type in the new name and hit “Enter”.

26.  In a specified worksheet create a 2D column chart (accepting default column chart) from a given cell range.  Place the chart as an object in a named worksheet.

Go to the start of the desired cells by doing “Ctrl + G” type in the first cell in the range and hit “Enter”.  Now select the data cells using “Shift+Arrow keys”.  Once the data cells are selected, we can start the chart by doing “Alt + I” to get to the “Insert” menu, go down to “Chart” and hit “Enter”.  A chart wizard will open, the first “List Box” that you will come to is “Chart Type”.  Use the up and down arrows here to choose the type of chart you want, in this case we require a Column chart so we do not have to do much arrow work.

When you tab again you will be in the “Chart Sub-Type” box, use the “right arrow” to go to the type of sub chart you require in this case 2D Column and hit “Enter”.
 
You will now come to “Step 2” of the chart wizard where it will give you the data regions to be included in the chart, you should not have to alter anything here so simply hit “Enter” again.

You are now in “Step 3” of the chart wizard where you can alter things like the title and legends.
On the “Titles” page, “tab” to “Chart Title” and if required you can enter a chart title, I am going to assume you don't need one so do “Ctrl + tab” to get to “Legends” page and tab to the “show legend” check box, here you can check or un-check it using the “spacebar” followed by “Enter”.
There are all manner of things you can do to your chart, all of which are accessed by doing “Ctrl + tab”.  You can also use the Jaws keystroke “Insert + B” to give a full feedback of everything on the screen.

You are now on to “Step 4” where you can choose to place your chart either on the same sheet or a new one.  The default is to place the chart as an object onto the same sheet as your data so simply hit “Enter”.

Your chart is probably already selected ready for you to cut and paste it. You will have to go to a region in your spreadsheet which is not occupied by data before you paste the chart.  I would suggest going to “column A” and then working out which is the last occupied cell and then pasting the chart into that cell. 

If you need to select the chart you can either do so by doing “Ctrl + G” to access the “Go To” menu.  Tab round to the “Special” button, hit “Enter” and then use the “down arrow” to go to “Objects” and hit “Enter”, the chart is now selected.  Alternitively you can do "Ctrl + shift + O"  a list box will appear allowing you to select the required chart but in our case there is usually only one chart so press enter. You can cut the chart with “Ctrl + X”.

27.  Change one of chart bars to yellow and the background of the chart to white.  Please save at this point.

First select the chart, you can do so by doing “Ctrl + G” to access the “Go To” menu.  Tab round to the “Special” button, hit “Enter” and then use the “down arrow” to go to “Objects” and hit “Enter”, the chart is now selected. Alternitively you can do "Ctrl + shift + O"  a list box will appear allowing you to select the required chart but in our case there is usually only one chart so press enter
However you still have to get down to the chart editing level to make any changes.  To do this hit the “Right mouse button” and use “Down arrow” to go to “Edit Chart Object” followed by “Enter”.  Now move through the chart elements by tapping the right arrow  or by repeatedly doing “Ctrl + tab” and then “right arrow” key until Jaws echoes the element that you wish to alter.  Now tap the “Right Mouse button” or “Shift +F10” and do “Down Arrow” to “Format Data Point” and hit “Enter”. Keep tabbing until you get to “Area” and then tab again until it says “Black” now tap the “Right Arrow” until you get to the required colour and hit “Enter” again.  Finally tab around to the “OK” button and hit “Enter”.  Finally hit “Escape” a couple of times to ensure you do not make any inadvertent changes to  your chart.  To save do “Ctrl + S”.

28.  Save the spreadsheet file as a template under a specified name on your candidate disk. 

Ensure any open spreadsheet files are saved prior to closing the application.
To access the “File” menu hit the “Alt” key and then tap the “down arrow” to go to “Save As” followed by “Enter”.  You are now in the “Filename” box, so type in the name of the file you wish to save.  Now “tab” again and you will reach the “Save as Type”list box, tap the down arrow until you reach  “Template” and hit “Enter”.  You now need to specify the directory in which you wish to save your file, to get to the “Look In” spin box do “Alt + I” and then use Up and down arrows to locate your directory or subdirectory.  If you are saving on a USB pen then locate “My Computer”, hit  “Enter” and then tap “tab” until Jaws echoes “List View”, tap “R” for “Removable Drive” and hit “Enter”, now tab round to “Save As” Button and hit “Enter”.  Congratulations you have now finished! Exit all files and applications by doing “Alt + F4” a few times until you reach the desktop.