include_once("common_lab_header.php");
Excerpt for LibreOffice / OpenOffice (LO/OO) Calc from the keyboard by , available in its entirety at Smashwords

Smashwords Edition

LibreOffice / OpenOffice (LO/OO) Calc from the keyboard

By Visimpscot (http://tinyurl.com/visimpscot) Published by Pointsize Press at Smashwords 2018 Published under a Creative Commons Attribution-ShareAlike 3.0 Unported License. To view a copy of this license, visit http://creativecommons.org/licenses/by-sa/3.0/.

Other titles by Visimpscot:

  • MS Word from the keyboard

  • LibreOffice / OpenOffice Writer from the keyboard

  • MS Excel from the keyboard

  • NVDA digital skills



Contents

Introduction

Different keyboards

For tutors

LibreOffice / OpenOffice Calc some history

Target audience group

Conventions

Suggested approaches for effective learning with this guide

The aims of this guide

Keyboard access in general

Using LO/OO with screen reader programs

Working with the LO / OO user interface without mouse

Activating menu bar, toolbars, windows, and document

Calling a menu command

Executing an icon command

Special hints for toolbars

Selection from a combo box

Selection in tables

Size and Position of Windows and Dialogs

Docking and Undocking Windows and Toolbars

Selecting objects

Controlling the Dividing Lines

Controlling the Data Source View

Controlling the Help

Controlling the Text Import dialog (CSV file import)

What is a spreadsheet and how are they structured?

Pen-picture of the LO/OO Calc screen

LO/OO Calc general shortcuts

Customising LO/OO Calc for visually impaired users

LO/OO Calc’s default Workbook formats and conventions

How to Launch LO/OO Calc

Getting a feel for how to move around a worksheet

Entering data into a Worksheet

Workbook exercise creating and saving a Worksheet with headings and formulae for totals

AutoSum

Selecting / selecting cells

LO/OO Calc’s additional selection mode

Saving and opening files

The library folders

Recently opened files

Printing Workbooks, Worksheets and Cell Ranges

Portrait Printing

Landscape Printing

Cut, Copy and Paste with the Clipboard

Spell- and formula-checking your Worksheets

Spell-checking the text in worksheets

Creating a multi page Workbook

To insert a new sheet

To delete the current sheet

To rename the current sheet

Changing the Column Width and Row Height of Your Worksheets

Columns

Rows

Manually formatting cells

Selecting a border style

Selecting a font type and size

Selecting a cell or worksheet background pattern or colour

Selecting the type of number format and currency symbol to use

Selecting the way you want text and values aligned

Editing a single cell

Clearing and deleting cells, rows, columns and worksheets

Clearing

Deleting

Naming Cells

Naming individual cells or blocks of cells

Using the current date

Using, Viewing and Printing Common formulae

Example formulae

Workbook exercise amending data and formulae

Sorting data

Sorting lists of data in ascending or descending order

Workbook exercise sorting

The Find and Find and Replace Features

Find

Find and replace

Autofilter

Importing data from other programs

AutoCorrect

Add a text entry to the AutoCorrect list

LO/OO Calc macros

Password protecting ODS format spreadsheets

Customising LO/OO Calc

Creating your own keyboard shortcuts or changing existing shortcuts

LibreOffice/OpenOffice help system

OpenOffice help keyboard shortcuts

Navigating the main help pages

Comprehensive list of LO/OO Calc’s keyboard shortcuts









Introduction

This guide was inspired in 2016 by one on using Microsoft Excel from the keyboard that was written by John Wilson in 2006, after ten years it was in serious need of updating. There are guides and tutorials to using Open Office Calc but very few of them pay much attention to using LO/OO Calc while relying on the keyboard, rather than a mouse or some other pointing device.

OpenOffice can be downloaded from http://www.openoffice.org, LibreOffice can be downloaded from http://www.libreoffice.org/. LibreOffice is also included in many of the popular Linux distributions such as Linux Mint (www.linuxmint.com)


The guide has been written by Visimpscot (http://tinyurl.com/visimpscot), we are based in the East of Scotland but have also worked in Asia, East Africa and the Pacific. Concentrating on delivering quality IT training, we specalise in helping the ‘not for profit sector’ and users with sensory, physical or cognitive impairments. You can contact us by emailing visimpscot@live.co.uk .


If you spot any errors that have slipped through editing and proof reading, please let us know.


This guide is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License. To view a copy of this license, visit http://creativecommons.org/licenses/by-sa/3.0/. Basically it means that you can freely use or adapt this material so long as Visimpscot is acknowledged and that any resulting product uses the same license. If you do use or adapt this guide please let us know because shared ideas are better ideas. If you want to produce translations into other languages that would be great.


There are times when it is suggested that certain tasks are best carried out with sighted assistance, it is not that it is impossible to do them without but because it really will be easier.


The guide is free but if you do use it and want to make a donation please go to the NVDA screen reader project website (http://www.nvaccess.org/) and make a donation to support them.

Different keyboards

This guide assumes that you are using a standard layout desktop or laptop qwerty keyboard. Some small keyboards on notepad computers or large keyboards for users with visual or motor control impairments may not have all of the keys mentioned. There may be ways around this problem by remapping the keyboard but this not a task for the inexperienced.

For tutors

There are not that many tutorials, guides and books on using LibreOffice/OpenOffice programs to cover the features this guide does not, or only gives short treatment to. One web based resource that provides a large number of well structured free tutorials is the Goodwill Community Foundation (http://www.gcflearnfree.org/topics). There are no LO/OO tutorials but they do have tutorials on using MS Office (back to MS Office 2003, when the program interfaces still used menus, like LO/OO) that can be adapted for use with LO/OO.

LibreOffice / OpenOffice Calc some history

For a long time Microsoft has dominated the market for spreadsheet software with the Excel component of the MS Office suite of programs. OpenOffice Calc was closely modeled on MS Excel but this changed from MS Office 2007 onwards when the Excel program interface underwent major changes, in terms of basic functionality the two programs are still similar. For various reasons Open Office has split into OpenOffice and LibreOffice, the two products share much of the same underlying program code and this guide can be used with either.

Why use LO/OO Calc

  • Both OpenOffice and LibreOffice are free. Microsoft Office is not a free program, to use it you should buy a license and Microsoft use a variety of annoying methods such as requiring product activation to remind you of this.

  • LO/OO Calc can read and write files in Microsoft file formats, so you can exchange information with people who use MS Excel.

  • LibreOffice and OpenOffice are cross platform and work on both Linux and MS Windows computer operating systems

  • LibreOffice and OpenOffice are ‘open source’ programs while MS Office is closed source and owned by Microsoft, this has little practical effect for most users but there are good ethical reasons why people prefer open source over closed source software. You can find out more about this at https://en.wikipedia.org/wiki/Open-source_software.

Target audience group

This guide was written for visually impaired computer users and their tutors. Using LO/OO Calc with keyboard access methods and no mouse or other pointing device, is the basis of this work. The guide assumes a basic understanding of your computer operating system and your particular screen reader’s general hot keys.

The guide was not written to be used with any particular screen reader program but is intended for users of LO/OO Calc 2007 or later. It is not uncommon for screen reading programs to have extra spreadsheet commands specifically for use with MS Excel You should also study the guides manuals which come with your screen reader and get to know the main hot keys for use in spreadsheet programs.

Conventions

Many people refer to the key in the bottom row of keys, usually between ‘FN’ and ‘ALT’ as the Windows key because it has an MS Windows logo on it. Libre Office / Open Office will run on both MS Windows and Linux computers and many Linux users have made a positive decision to switch from MS Windows. In this guide the ‘Windows key’ is called the ‘logo key’

The terms “hotkey” and “keyboard shortcut” are used interchangeably in this guide.

All keystrokes are formatted like this. In this guide and all individual and combinations of keys you actually have to press during a procedure are in capital letters so that they stand out to anyone reading this tutorial visually, e.g. to bring up the Open dialog box press CONTROL + O, terms have the following meanings:

  • ALT + F AND 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, release both and then press the letter C key followed by the ENTER key.


If, in a menu, your screen reader 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 screen reader announces a row of three dots or says something like dialog, you will open up a dialog box to work in if you press ENTER on it.


Some of the keyboard access methods make extensive use of the TAB key and the up, down, left and right arrow keys and at times ‘tabbing’ and ‘arrowing’ are used as verbs, apologies to English language purists.

Suggested approaches for effective learning with this guide

It is up to the individual how they gain information and work through this guide, but a few suggestions might assist the learner who is relatively new to computers. I suggest that you read through the whole of a section before attempting to practice it to obtain an overview of what is being done.

There are a number of approaches can be taken to make reading the guide and simultaneously carrying out the instructions more fluid and easier to follow. You could try one of these methods:


Ideally, if you have two computers (physical or physical and virtual), 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 listen to the directions on one computer whilst practicing them on the other.


If you only have the one computer, you could launch your word-processor and load the tutorial into it for reading and then launch LO/OO Calc to practice the lessons. You would have to keep cycling between each running program by pressing ALT + TAB.


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 get your local library or resource centre (if there is one) to produce a Braille version for you to work from if you are a Braillist.

The aims of this guide

This is not a comprehensive guide to using LO/OO Calc, hopefully that after working through it learners will be equipped to follow other tutorials or courses on LO/OO Calc with little adaptation needed.

The guide aims to take a learner new to spreadsheets through the beginner*s level concepts and practical stages of LO/OO Calc or to help an experienced computer user who is unused to relying on keyboard commands.


If the guide does not meet your particular needs the Creative Commons License means that you are free to adapt it, so long as the source is acknowledged and any derivative work uses the same license.


Unless you are an experienced computer and screen reader user work through the guide systematically, 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 practiced earlier sections. Experienced computer users may not need to spend much time on basic spreadsheet concepts but mastering navigating through a spreadsheet is particularly important.


A visually impaired person may decide to create spreadsheets for your own use that avoid fancy formatting and blank rows and columns. However, if sighted people are going to view them, you may then wish to change the layout, formatting and spacing to give a more professional.

Keyboard access in general

This is not specific to LO/OO Calc but should work in any well designed program.

Most computer programs have some ‘hotkeys’ / ‘keyboard shortcuts’ to access commands quickly and easily, an example would be CONTROL N for create a new file.


In tandem with ‘hotkeys’, most visual program interfaces can be operated (or should be able to if the program designers have done their job properly) with keystrokes.


Only one control can ‘have focus’… be the active control at a time. In a group of controls, such as a program menu pressing the TAB key will move the focus forward through the controls in a set order, SHIFT TAB will move the focus backwards through the controls.


Pressing the up or down ARROW keys generally has the same effect as TAB and SHIFT TAB while pressing the left or right ARROW keys will move the focus into a submenu.


For button controls, when they have focus, pressing ENTER is the equivalent of clicking on the button with a computer mouse


For check box controls, when they have focus, pressing SPACEBAR will check or uncheck the control

For tabbed controls, when they have focus, pressing CONTROL PAGE down will move to the next tab, pressing CONTROL PAGE up will move to the previous tab.


For radio button controls, when they have focus, pressing SPACEBAR will check or clear the control

Using LO/OO with screen reader programs

From version 4 of both Libre Office and Open Office, accessibility is standard. Earlier versions of Libre Office and Open Office may require adaptations. Information on how to do this is available at http://accessibilitycentral.net.

Working with the LO / OO user interface without mouse

Activating menu bar, toolbars, windows, and document

Repeatedly pressing F6 switches the focus (changes the active control) and circles through the following objects:

  • menu bar,

  • every toolbar from top to bottom and from left to right,

  • every free window from left to right,

  • document



If the focus is on a menu bar or a toolbar, and you press CONTROL + TAB, you switch through the previously mentioned objects just as with F6, but without switching through to the document.

Press SHIFT + F6 or SHIFT + CONTROL + TAB to switch through objects in the opposite direction. Press CONTROL + F6 to switch to the document. Press F10 to switch to the menu bar and back. ESCAPE closes an open submenu, a toolbar, or the current free window.

Calling a menu command

Press ALT or F6 or F10 to select the first menu (the File menu). With right ARROW , the next menu to the right is selected; with left ARROW , the previous menu.

Arrow down opens a selected menu. Any additional arrow down and up arrow move the selection through the menu commands. With right ARROW you open any existing submenus. Press ENTER to execute the selected menu command.

Executing an icon command

Press F6 repeatedly until the first icon on the toolbar is selected. Use the right and left arrows to select an icon on a horizontal toolbar. Similarly, use the up and down arrows to select an icon on a vertical toolbar. The HOME key selects the first icon on a toolbar and the END key, the last.

Press ENTER to execute the selected icon. If the selected icon normally demands a consecutive mouse action, such as inserting a rectangle, then pressing the ENTER key is not sufficient: in these cases press CONTROL + ENTER.

Pressing CONTROL + ENTER on an icon for creating a draw object. A draw object will be placed into the middle of the view, with a predefined size.


Press CONTROL + ENTER on the Selection tool to select the first draw object in the document. If you want to edit, size, or move the selected draw object, first use CONTROL + F6 to set the focus into the document. If a toolbar is longer than can be displayed on screen, it shows an icon at the right or lower edge. Select the toolbar and press PAGE UP or PAGE DOWN to display the remaining icons.

Special hints for toolbars

Press the down arrow or right ARROW to open the selected toolbar. This is equivalent to a mouse click. In the toolbar use the right ARROW and left ARROW keys. The HOME and END keys select the first and last icon in the toolbar, respectively.

Close the toolbar with ESCAPE. It is not possible to move the toolbar without a mouse.

Selection from a combo box

Select the combo box. Press ENTER. Use the down arrow or PAGE DOWN key to scroll down the combo box entries, or the up arrow or Page Up key to scroll upwards. The HOME key takes you to the first entry and the END key takes you to the last entry. Press ENTER to execute the selected entry.

Selection in tables

In several windows, dialogs, and in the table control field, there are tables to select data, for instance, in the right part of the Data Source View. The following keys are used for selections in these tables:

  • SPACEBAR: switches from selection of the current row and cancellation of any selection, but not if the current cell is in edit mode.

  • CONTROL + SPACEBAR: switches between selection of the current row and cancellation of this selection

  • SHIFT + SPACEBAR: selection of the current column

  • ALT + Up ARROW or ALT + Down ARROW: moves the window separator between table and form, for instance in the bibliography database.


In a table control or in the data source view, the TAB key moves to the next column. To move to the next control, press CONTROL + TAB. To move to the previous control, press SHIFT + CONTROL + TAB.

Size and Position of Windows and Dialogs

  1. 1.First press ALT + SPACEBAR. A system menu opens with menu commands like Move, Resize and Close.

  2. 2.Choose a command (down ARROW, then ENTER). 3.Now you can use the arrow keys to move or resize the dialog or window. 4.Press ENTER to accept the change. Press ESCAPE to cancel the changes.

Docking and Undocking Windows and Toolbars

1. Press F6 until the window or toolbar is selected.

2. Press CONTROL + SHIFT + F10.

Selecting objects

Press SHIFT + F4 to select the first object in the current document. When an object is selected, press TAB to select the next object, or press ESCAPE to go back to the text.

Controlling the Dividing Lines

Documents of OpenOffice.org Calc, OpenOffice.org Draw, and OpenOffice.org Impress can be split horizontally and vertically into separate views. Each view can show other parts of the document. Using the mouse, you can drag a dividing line from the scrollbar into the document.

  • SHIFT + CONTROL + F6: shows the dividing lines at default positions and focus a line.

  • ARROW keys: moves the current dividing line a big step in the arrow direction.

  • SHIFT + ARROW keys: moves the current dividing line a small step in the arrow direction.

  • DELETE: deletes the current dividing line

  • SHIFT + DELETE: deletes both dividing lines

  • ENTER: fixes the current position of the dividing lines ESCAPE: resets the current dividing line to its default position

Controlling the Data Source View

F4: opens and closes the data source view. F6: switches between document and toolbars. + (plus key): expands the selected entry in the data source explorer. - (minus key): collapses the selected entry in the data source explorer. CONTROL + SHIFT + E: switches between data source explorer and table.

Controlling the Help

Press SHIFT + F1 to display the Extended Tips for the currently selected command, icon or control.

Navigating the main help pages In the main help pages, use TAB to jump to the next hyperlink or SHIFT + TAB to jump to the previous link. Press ENTER to execute the selected hyperlink. Press BACKSPACE above the ENTER key to return to the previous help page.



Controlling the Text Import dialog (CSV file import)

Ruler

  • Left or Right ARROW : go one position to the left or to the right

  • CONTROL + Left ARROW or CONTROL + Right ARROW : jump to the previous or to the next split

  • CONTROL + SHIFT + Left ARROW or CONTROL + SHIFT + Right ARROW : move a split one position to the left or to the right

  • HOME or END: jump to the first or the last possible position

  • CONTROL + HOME or CONTROL + END jump to the first or the last split

  • SHIFT + CONTROL + HOME or SHIFT + CONTROL + END: move split to the first or to the last position SPACEBAR key: insert or remove a split

  • INSERT key: insert a split (leave existing splits unchanged)

  • DELETE key: delete a split SHIFT + DELETE: delete all splits

  • Up ARROW or Down ARROW: scroll table down or up one row

  • PAGE UP or PAGE DOWN’: scroll table down or up one page ESCAPE* key (during mouse drag): cancel drag, move split to old position

Preview

  • Left ARROW or Right ARROW : select left or right column and clear other selections

  • CONTROL + Left ARROW or CONTROL + Right ARROW : move focus to the left or to the right column (does not change selection)

  • SHIFT + Left ARROW or SHIFT + Right ARROW : expand or shrink the selected range

  • CONTROL + SHIFT + Left ARROW or CONTROL + SHIFT + Right ARROW : expand or shrink the selected range (does not change other selections)

  • HOME or END: select the first or the last column (use SHIFT or CONTROL as with cursor keys)

  • SPACEBAR key: select the current column and deselect all of the other columns

  • CONTROL + SPACEBAR key: select or deselect the current column

  • SHIFT + SPACEBAR key: select the range from the last selected column to the current column

  • CONTROL + SHIFT + SPACEBAR key: select the range from the last selected column to the current column (does not change other selections)

  • CONTROL + A: select all columns SHIFT + F10: open a context menu CONTROL + 1 … CONTROL + 7: set the 1st … 7th column type for the selected columns

  • Up ARROW or Down ARROW: scroll table down or up one row

  • PAGE UP or PAGE DOWN: scroll table down or up one page

  • CONTROL + HOME or CONTROL + END: scroll to the top or bottom of a table

What is a spreadsheet and how are they structured?

Experienced users may choose to skim through this section.

LO/OO Calc is a spreadsheet program. It provides a grid of vertical columns and horizontal rows of “cells” into which you can type numbers, words or formulae to do calculations.


The text you type into a cell is called a “label” and the figures you type in are called “values”.


One or many pages of a spreadsheet are known as a file, although LO/OO Calc has its own name for such a file, calling it a “workbook”. So, if a workbook had 100 pages (known as worksheets) within it, the workbook would be like 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 with an extension of “.XLS”, e.g. book1.xls, invoice3.xls, smith44salesledger.xls .


Each worksheet in a workbook has columns running down the sheet which are labeled 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. So, by knowing the letter at the top of a column and the 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 coordinate of B2, a cell which is five in from the left and 124 rows down could be found at the cell coordinate of E124, etc.


The default name for the first page in a workbook is “Sheet1”, the second “Sheet2” etc. but you can give change these names.


A “cell”, is a kind of box, such a cell can vary in size, horizontally (width) and vertically (depth) 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.


Worksheets can also include a “chart”, which is a graphical representation of the data on a worksheet.


You would, create or read a spreadsheet like reading a bus or train timetable. It is simply a grid of small (but expandable) boxes, each of which has a known place on the grid that is given by the column and row letter / number.

The hierarchy of LO/OO Calc’s structural levels, from the largest down, is: workbook, worksheet and then cell.

Pen-picture of the LO/OO Calc screen

A single LO/OO Calc screen of information will typically contain:

  1. The ‘Title Bar’ containing the name of the worksheet which is on screen, e.g. “Untitled” and then the words ‘Libre Office Calc’ or ‘Open Office Calc’ if you have not yet replaced this default file name with a name of your own.

  2. Below the Title bar are the program menus. It is possible to use some keyboard commands with the program menus.

  3. Next down are the tool bars, that are mostly buttons to access features that can also be reached in other ways.

  4. Under this comes the “Formula Bar” where a formula or the contents of the cell with current focus can sometimes be viewed. Look at this in mouse or virtual cursor mode unless your screen reader has a read Formula Bar hot key.

  5. All the main part of the screen down to the last two lines contains the worksheet grid of cells i.e. with columns alphabeticised at the top A, B, C, etc, and rows numbered at the left 1, 2, 3, etc. Only a fraction of a total worksheets potential size can be displayed on one screen even, with the screen maximised (ALT + SPACEBAR + X in MS Windows).

  6. On the bottom line of the displayed screen are found the worksheet tabs which can be used to move from worksheet, to worksheet but you can achieve this from the keyboard by pressing CONTROL + PAGE up or down. Have a look at the stationary screen in your screen reader*s mouse or virtual mode. Note that if you arrow right or down the worksheet will move (scroll) to the left or down and reveal more columns and more rows.

  7. Finally, on the last line, is the status bar, normally displaying the current work sheet number and the total number of worksheets… for example Sheet 1 / 3, if LO/OO Calc is displaying worksheet one of three. It will also display different information at other times, such as the autosum total of any cells you have selected.

LO/OO Calc general shortcuts

For a complete list of these shortcuts, is included in Part four of the guide.



Customising LO/OO Calc for visually impaired users

If you use a screen reader and cannot make any reasonable use of your computer monitor screen, you should set up copy of LO/OO Calc like this:

  1. Ensure that the formatting and standard tabs are on displayed (they are usually on by default)*:

  2. Turn toggle “Grid Lines” to on (they are usually on by default)

  3. Always work with your worksheet windows maximised by pressing ALT + SPACEBAR and then X (MS Windows and Linux) if they are not already maximised.

  4. Keep the navigation pane of the open and save file dialogs on (it is on by default, so just do not turn it off) and the preview pane off (MS Windows).

  5. Do not bother with the “Freeze” or “Split” options in the Window menu unless you can see well enough to do so. They are useful for sighted people and of little or no use to people who cannot see the screen or who only see a small magnified portion of the screen.

LO/OO Calc’s default Workbook formats and conventions

When you create a new workbook LO/OO Calc will use these default settings:

  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 ‘Liberation Sans’ , 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 height of a cell will be 12 points, i.e. deep enough for 1 line of text/figures only.

  7. Cell borders will be of continuous thin lines.

  8. The cell foreground color will be automatic, i.e. normally black.

  9. The background cell pattern/color will be set to off, meaning no background cell patterns will be used and the background will be left the color of the paper if printed.

  10. No cells will be hidden or protected.



It is possible to change the default settings but this is a task best left to an experienced and sighted person.

How to Launch LO/OO Calc

How you launch LO/OO Calc will depend on which computer operating system you use, in all versions of MS Windows except Windows 8 pressing the Logo key will launch the ‘Start Menu’, this also works in some varieties of Linux.

With a little research on your chosen operating system it should be possible to create a desktop shortcut or other quick way of launching LO/OO Calc.

Getting a feel for how to move around a worksheet

Launch LO/OO Calc. 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 LO/OO Calc general movement and command keyboard shortcuts and hotkeys 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. For example:

  1. Use your up, down, left and right ARROW keys to move from one blank cell to another and notice how your screen reader announces the combined cell co-ordinates derived from those headings. 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 want to go directly to a cell some way into a worksheet,

  • Press F5 to open the LO/OO Calc ‘Navigator’ . When it is opened the Navigator displays the coordinates of the cell that you are currently in

  • You start with the ‘Column’ edit field as the active control, press DELETE to clear the contents and type in the column letter that you want to jump to, e.g. V,

  • Press TAB to shift the active control to the ‘Row’ edit field and type in the row number that you want to jump to, e.g. 20, this will overwrite whatever is in the edit field already.

  • Press ENTER to jump to the cell whose column and row coordinates you just typed in

  • Press F5 to close the Navigator.

Check your position by arrowing up once and then back down once to get the coordinate of your current cell announced to you.

  1. Now move back to cell A1 with CONTROL + HOME. Next press CONTROL + END and note 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? Experiment and find out.

  2. Move to cell B4 and then press the PAGE down key and observe that you are moved a lot of rows further down in the same column (the exact amount will depend on the page setup . 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.

  3. Go back to A1 with CONTROL + HOME, then press CONTROL + right ARROW* and note you are taken directly to the last column in row 1, which is AMJ 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, C1 in this case.

  4. 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,526. 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, if your cursor was within the area of the worksheet data when you started.

  5. If you would like to go to the next worksheet to enter data into, press CONTROL + PAGE down. Pressing CONTROL + PAGE up will take you back to your first sheet and any data you may have entered into it. This command moves through the worksheets that already exist it does not create new worksheets.

  6. Move to one of the empty cells and enter a number, press ENTER or TAB. You can delete or modify the contents of a cell by pressing the DELETE key when you are in it in it or by pressing F2 and backspacing it out, with this you can also edit the data to produce a slightly different figure, etc.

  7. Complete a few rows and columns and keep on experimenting in this way, using the LO/OO Calc general and your NVDA 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.

Entering data into a Worksheet

You type figures or text into a cell in the same way as you would into any other edit field. Create the following small worksheet of fish, chips and bread rolls sold for the week for a chip shop to practice on by following the instructions.





Workbook exercise creating and saving a Worksheet with headings and formulae for totals

  1. Launch LO/OO Calc and press CONTROL + HOME to go to cell A1 if you are not already there.

  2. In cell 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.

  4. In cell C1 type the heading “Chips” and press the right ARROW.

  5. In cell D1 type the heading “Rolls” and now press the down ARROW key once followed by the HOME key to move to cell A2, immediately below your A1 heading of “Days”. Note that your screen reader may speak the column heading when you get back to column A.

  6. In cell A2 type “Monday” and press either TAB or right ARROW.

  7. In cell B2 type “100” and press right ARROW*.

  8. In cell C2 type “150” and press right ARROW or TAB.

  9. In cell D2 type “50” and press down ARROW followed by HOME to go to cell A3.

  10. In cell A3 type “Tuesday” and press right ARROW.

  11. In cell B3 type “120” and press right ARROW.

  12. In cell C3 type “140” and press right ARROW.

  13. In cell D3 type “50” and press down ARROW followed by HOME.

  14. In cell A4 type “Wednesday” and press right ARROW.

  15. In cell B4 type “177” and press right ARROW.

  16. In cell C4 type “190” and press right ARROW.

  17. In cell D4 type “90” and press down ARROW followed by HOME.

  18. In cell A5 type “Thursday” and press right ARROW.

  19. In cell B5 type “120” and press right ARROW.

  20. In cell C5 type “140” and press right ARROW.

  21. In cell D5 type “85” and press down ARROW followed by HOME.

  22. In cell A6 type “Friday” and press right ARROW.

  23. In cell B6 type “200” and press right ARROW.

  24. In cell C6 type “170” and press right ARROW.

  25. In cell D6 type “105” and press down ARROW followed by HOME.

  26. In cell A7 type “Totals” and press right ARROW.

  27. In cell B7 press * = (equals sign) and then in the edit field type in the formula: SUM(b2:b6) and press ENTER*.

Note that LO/OO Calc 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.

  1. Now, while in the fish total cell (B7), 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 (the cell reference b2:b6 will be update automatically) . 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 you copied is kept on the Clipboard until you overwrite it with something else or turn your computer off). Check that the formulae have been copied and the totals automatically calculated for you. The chips total should be 790 and the rolls total 380.

Note: the shortcuts for cut, copy and paste work in MS Windows and most varieties of Linux.

  1. Save this simple worksheet (it then becomes a single sheet workbook) to a file that can be worked on latter in this tutorial To save the workbook, press CONTROL + S (for Save) and then type a filename into the edit field you come into, e.g. fishandchips, and press ENTER. The file will save to the hard disk and automatically be given an “.,” file extension by LO/OO Calc. Saving is explained in more detail in a later section.

  2. When you want to create another Workbook, you just press CONTROL + N (for New Workbook).



Note 1: Be aware that the default way LO/OO Calc 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, 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 LO/OO Calc 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.

AutoSum

In step 26 of the exercise you typed a formula into a cell to add up a column of figures in a specified range, i.e. cells B2 to B6. Calc has a way to sum down a column. Instead of pressing = and then typing the formula as you did in step 26 above, you can:

  • Press SHIFT + F6, press TAB twice

  • Press ENTER twice and Calc will sum (total) the column of cells above the cursor.


If you have not got the fish and chips workbook open, press CONTROL + O (for Open) and type the filename of “fishandchips” into the edit field which you are now in and press ENTER to bring it to screen (open file starts in the last used folder) .


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 see what happens.

Selecting / selecting cells

You need to select or select existing text, cells or formulae before carrying out certain actions on them, such as deleting them, moving or copying them, emboldening them or changing the font etc. “Selecting” (also known as selecting) is where you mark a cell or block of cells 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 do this by selecting with the SHIFT key, as follows:

  1. Move to the first cell you want to select to select 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 select the whole column your cursor is currently in, press CONTROL + SPACEBAR*.

  3. To select the whole row the cursor is in, you press SHIFT + SPACEBAR.

  4. To select the block of cells 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. To select the whole of a Worksheet, you press CONTROL + A, but this will select 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 select a portion of a sheet from the cursor position down to the end of the data in the sheet, press CONTROL + SHIFT + END.

  7. To select cells from the cursor position to the end of the row, press CONTROL + SHIFT + right ARROW.

  8. To remove the selecting from a block, press any of the arrow keys once in any direction.



Note: Sometimes selecting fails when working from the top left of a block of cells right and downwards. You may get better results working backwards from bottom right to top left.

After selecting 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.


You can try some of these things on the fishandchips.ods 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, i.e. put things back to how they were before the change.

LO/OO Calc’s additional selection mode

You can use standard selecting procedures with the SHIFT key or CONTROL + A for select all. LO/OO Calc also has an ‘Additional Selection Mode’ that is toggled on or off by pressing F8. When it is on, you will be able to arrow around a worksheet and select cells, columns, rows and ranges of cells, etc, without having to hold down the SHIFT key. When Additional Selection Mode is on:

  • Press the right ARROW key to select the next cell to the right

  • down ARROW will select the next cell down

  • HOME selects all cells from the current cell to the first cell in the in the row

  • CONTROL + END selects from the cell the cursor is currently in to the last cell with any data in that row, etc.

To remove this type of selection / selecting you have to press F8 and any arrow key.

Whatever way you choose to do it, as soon as you have the cell, row, column or group of cells selected you can, for example:

  1. Press the DEL key to delete it (ENTER to confirm).

  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. This will not cope with multiple selections.

  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 CONTROL + 1 press CONTROL + PAGE UP or CONTROL + PAGE DOWN to navigate to the ‘Font Effects’ page of controls, tab through the controls to select any font type, style and size you want to use and press ENTER apply it to the selected / selected cells.

  8. Press CONTROL + P to print the entire spreadsheet, current workbook or the currently selected / selected range of cells (selecting what the print command applies to is done in the print dialog).

Saving and opening files

The procedures for saving and opening files is similar but not identical in MS Windows and different varieties of Linux and keyboard access shortcuts are not consistent either. File handling is a fundamental skill and tutors should ensure that their learners master it. The differences between operating systems means that this is not covered in detail here.

Many people, if they have a visual impairment or not take a while to grasp the concept of files and folders, besides the intricacies of drive letters, drive mappings and expanded or unexpanded tree views. These concepts can be introduced in different ways but one of the best is to keep things as simple as possible, some learners may never need to know any more, others can build on their existing knowledge when they need to.

The library folders

MS Windows and many varieties of Linux provide you with a set of library folders “Documents”, “Music”, “Pictures” and “Videos” on the hard disk of the computer. Unless there is a very good reason not to (perhaps you are using a public access computer in a library or cyber cafe), using them will make your life a lot easier. If possible all of your LO/OO Calc work should be saved in the “Documents” folder.

It may not be possible to follow this advice if you are using a shared public access computer, in this case you might have to use a removable flash drive instead.

Recently opened files

Pressing ALT + F (opens the ‘File’ menu) arrow down to ‘Recent Documents’ and press right ARROW this will take you to a list of files that LO/OO Calc has opened recently, that you can arrow through, if you have opened the file that you want recently it should be in this list, arrow to it and press ENTER to open it again.

Printing Workbooks, Worksheets and Cell Ranges

If you have no need to print your work you may want to just skim through this section.

You can print a whole workbook with all of its sheets and charts, a single worksheet, a range of worksheets from a workbook or a range of cells within a worksheet.

Portrait Printing

The default page orientation Calc prints is portrait, i.e. with the paper in your printer the same way as you would print a letter.

To print your worksheets:

  1. Go to the first cell in the worksheet (top left cell) and then select the whole range of your worksheet by pressing CONTROL SHIFT END.

  2. Press CONTROL P to open the print dialog box. The layout and details of the print dialog box will vary depending on which computer operating system you use.

  3. Press ENTER to start printing to the default printer.

  4. If, before pressing ENTER to print, you want to customise how the print job is done, or just see the options that are available TAB through the other print options.

Landscape Printing

It may be 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 side first.

To print in landscape format:

  1. Press ALT + O (for the ‘Format’ menu), arrow down to ‘Page’ and press ENTER

  2. Tab to ‘Orientation’ if this is set to ‘Orientation Landscape’ pressing the down ARROW’ will change it to Orientation Landscape (up ARROW* will reset it to ‘Orientation Portrait’)

  3. Tab to the ‘OK’ button and press ENTER



Now pressing CONTROL P and ENTER. LO/OO Calc will print one copy of the workbook using the default printer, in landscape orientation.

Note If you have selected a block of cells before Pressing CONTROL P, you will be able to print out only that range of selected cells not the whole worksheet or workbook, by tabbing to the appropriate option.

Cut, Copy and Paste with the Clipboard

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 select data before copying or cutting (moving) it to the Clipboard, as explained in Part One.

The 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 the computer off, when the clipboard is emptied.

After you have selected something:

Press CONTROL + C to copy to the Clipboard Press CONTROL X to cut to the Clipboard

Press CONTROL V to paste from the Clipboard to the cursor position

Spell- and formula-checking your Worksheets

How much information you get while spell-checking will depend on which screen reader you are using.

Spell-checking the text in worksheets

To spell-check a worksheet:

  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. In the spell checker use the TAB key or SHIFT TAB to move through the controls. You will start off in the list of suggested word replacements which you are offered for something the spell checker reckons is wrong. If there is more than one suggested replacement, you can arrow down and up through them, put the focus on the one you want and press ENTER to replace the mis-spelt word.

  3. The spell-checker then moves onto the next word it thinks is wrongly spelled.

  4. If LO/OO Calc 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.





In the spell-checker dialog box you can use several shortcuts:

  • Use ALT C to change a word to the first suggestion in the replacement list.

  • Use ALT I to ignore the spell-checker*s suggestion that a word may be spelled wrongly this time.

  • Use ALT G to ignore this and every future occurrence of a word the spell-checker reckons is spelt wrongly. Use this if you know that it is spelled correctly. The spell checker does not know every word in the English language and will regularly mark acronyms and proper nouns as incorrect spellings and often has American English set as the default dictionary (although this can be changed).

  • Use ALT L to change this and every future instance of this same wrongly spelled word in the worksheet.

  • Use ALT A to add the word to LO/OO Calc’s custom dictionary, useful if you know that it is spelled correctly but LO/OO Calc does not agree. Be careful though, you should definitely not add anything to the custom dictionary if it is not your computer.

Creating a multi page Workbook

If you use more than one worksheet in your workbook, you can move through the pages (worksheets) of a workbook by pressing CONTROL PAGE down. By default a new OpenOffice workbook has three worksheets, LibreOffice has only one.

To insert a new sheet

  • Press ALT + I (for the ‘Insert’ menu), arrow down to ‘Sheet’ and press ENTER

  • Pressing ENTER will generate a new sheet, with a default sheet name

You can change the default sheet name by tabbing through to the ‘Name’ control and entering your own choice of name before tabbing to the ‘OK’ button and pressing ENTER.

To delete the current sheet

  1. Press ALT + E (for the ‘Edit’ menu), arrow to ‘Sheet’ and press the right ARROW

  2. Arrow down to ‘Delete’, press ENTER and press ENTER again to confirm

To rename the current sheet

  1. Press ALT + O (for the ‘Format’ menu), arrow to ‘Sheet’ and press the right ARROW

  2. Arrow down to ‘Rename’, press ENTER

  3. Press BACKSPACE to clear the current sheet name, type in your new one and press ENTER.



Note: You cannot have two sheets in a workbook with the same name.

Changing the Column Width and Row Height of Your Worksheets

The default (standard) width of an LO/OO Calc column is 2.27cm, this can be changed. The default depth or height of a row is 0.45cm . This can be altered to make it deeper. 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.

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 W, or arrow down to ‘Column’, press right ARROW, to width and press ENTER, the current column width will be displayed.

  2. You can type over this figure with the column width you require, e.g. type 4 to obtain a column 4cm wide.

  3. Press ENTER to finish.

  4. Alternatively, in step 1 after pressing right ARROW, you could have arrowed down to ‘Optimal Width’ and pressed ENTER to get LO/OO Calc to automatically change the width of the column to accommodate the longest entry which has already been completed with data.

Rows

To alter rows:

  1. Place the cursor in the row you wish to change the height of and press ALT + O, arrow down to ‘Row’, press right ARROW to ‘Height’. Now follow the same steps as in 2 to 4 in the column section. In this case you will be typing the number you want the height of the row to be.



Workbook Exercise 2–altering column widths, using formulae for averaging and copying formulae down a column

Open the fishandchips.odf workbook you created earlier, practice changing column widths and also how to copy cells down a column by moving your cursor to cell E1 and creating a new column for the spreadsheet.

  1. Retrieve “fishandchips” by pressing CONTROL O and typing its filename into the edit field and pressing ENTER.

  2. Type in E1 the heading “Average Units Sold”.

  3. 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.

  4. Next copy this formula down the rest of the column to E6 by placing the cursor in E2 (with the formula in it) and selecting E2 and all cells down to E6 by holding the SHIFT key down and arrowing to E6.

  5. Now finish the exercise by pressing ALT + E (for the ‘Edit’ menu), arrowing down to ‘Fill’, pressing right ARROW to ‘Down’ and pressing ENTER. View the results and the fact that some entries will have figures after a decimal point.

  6. Save your changed workbook to a slightly different filename than the original, so that you will now have two different workbooks under separate names. Press ALT F (for file) and then A (for Save As) and type in the filename “fishandchips2” and press ENTER to save.

Manually formatting cells

You can apply many different formatting choices to a whole spreadsheet or to a selection of selected cells e.g. apply a coloured background to all cells or just the headings of your columns. To get an idea of what can be done, try some of these numbered steps, each of which has its own sub-heading:

Press ALT + O (for the ‘Format’ menu), arrow down to ‘Cells’ and press ENTER. You can also use the LO/OO Calc shortcut of CONTROL + 1 to open this dialog box.


You now have a multipage set of property sheets with titles of ‘Borders’, ‘Cell Protection’, ‘Numbers’, ‘Alignment’, ‘Background’, and ‘Font’. CONTROL + TAB through these and have a look at the options by tabbing through them.

Selecting a border style

For example, the first option in the ‘Borders’ sheet is ‘Border Style’ and the default type of border is a 0.05 point continuous line, which will give a border with thin lines which do not break, as opposed to dotted lines, thick lines, broken lines of hyphens, etc. You can choose from another 11 different border types by arrowing up or down this list and leaving focus on the one you want to use.

Keep tabbing through the border sheet controls, by arrowing down you can change things like the colour of your border grid lines or leave them on ‘Auto’ and let LO/OO Calc choose a colour matching the rest of your worksheet colours,. Now CONTROL + TAB* to the ‘Font’ property sheet.

Selecting a font type and size

In the ‘Font’ sheet you can change the font type to bold, italic, etc, its size to make it smaller or larger, etc.

Selecting a cell or worksheet background pattern or colour

In the ‘Fill’ sheet you can choose a background pattern or colour for your selected cells.

Selecting the type of number format and currency symbol to use

In the ‘Numbers’ sheet you can tab to ‘Category’, ‘General’ is the default format for all cells but you can arrow down this list and change it to something else if you need to, such as ‘Number’ that has numbers given with decimal points or ‘Currency’ so that two decimal places and a currency sign is displayed, etc. By default it will use the currency symbol appropriate to the language set in the MS Windows control panel, although this can be changed by tabbing through to the symbol control and arrowing down to what you want (be warned, though the list of available currency symbols is very long).

Selecting the way you want text and values


Continue reading this ebook at Smashwords.
Download this book for your ebook reader.
(Pages 1-35 show above.)