Copyright: Copyright © 1998-2001 Ability Plus Software (UK) Ltd.


What is Spreadsheet?
Spreadsheet

A spreadsheet is a tool that is used to organize, track and calculate financial and numerical information. You can use a spreadsheet to analyze figures, calculate totals or averages, or project business trends. In addition, you can use spreadsheets to experiment in “what if” scenarios, seeing what happens when you change the numbers.

You can use spreadsheets to solve complex problems (like calculating mortgage repayments), as well as simpler problems (like calculating miles to the gallon). Here are some more examples of how you can use a spreadsheet:

•	Create a yearly budget

•	Analyze quarterly sales figures

•	Set sales quotas

•	Analyze stock investments

•	Project loan repayment amounts

•	Compute interest payments at variable rates

•	Project income and profit figures

See:

Getting started with Spreadsheet

Entering information

Formatting information

Copying and moving information

Turning data into a chart


Starting Spreadsheet

To start Spreadsheet, follow these steps:

1.	Click the Start button on the Windows Taskbar

2.	Select Programs

3.	Select Ability Office

4.	Select Ability Spreadsheet

Spreadsheet will open with a new spreadsheet displayed, ready for entering data.

See also:

Parts of the spreadsheet

Moving around the spreadsheet

Spreadsheet definitions


The spreadsheet window

The area in which you work on a spreadsheet is called the spreadsheet window. The different areas of the spreadsheet window are shown on this diagram:

Clicking on a column letter on the column bar selects that column, while clicking on a row number on the row bar selects that row. You can select multiple rows or columns by dragging the cursor across several row or column bars, or you can select the entire sheet by clicking the button at the intersection of the row and column bars.

A spreadsheet is organized into rows and columns, like a table. The rows are numbered sequentially down the left edge of the spreadsheet window. The columns are labeled with letters across the top of the spreadsheet window. (The column letters run sequentially from A to Z, then from AA to AZ, then from BA to BZ, and so on, finally ending with IA to IV).

The maximum size for a spreadsheet is 65,536 rows and 256 columns.

Each Ability spreadsheet has 15 associated worksheets. These allow you to work with related information from different worksheets, which are then stored in the one spreadsheet file.

See also:

The toolbars and formatting bar

The formula bar

Worksheets


The toolbars and formatting bar

Across the top of the window are the various toolbars for spreadsheet. These contain the buttons and drop-down lists that enable you to format and manipulate the cells and figures quickly and easily, without needing to open the menus.

Note:	You can choose not to display the toolbar and/or the formatting bar. To do this, click on the Toolbars option from the View menu. Click on the Standard checkbox to turn the toolbar on or off, and on the Format checkbox to turn the formatting bar on or off. When the feature is shown, a check is put next to it; when it is not shown, the check is removed.

See also:

Coolbars mode and large buttons


The formula bar

The formula bar is where you enter text, numbers and formulas into cells and fields.

The formula bar shows the name of the current cell and what type of information it contains. You can click anywhere within the contents of the formula bar to edit the spreadsheet cell.

There are three buttons to assist in editing the cell contents:

•	Confirm button [image] or Enter key. Saves the contents of the formula bar into the spreadsheet.

•	Cancel button [image] or Esc key. Aborts the current edit and returns the cell to its previous state.

•	Functions button [image]. Calls on a list of over 200 built-in functions. For more information, see Function reference.


Coolbars mode and large buttons

You can choose how the toolbars will be displayed. Coolbars are normal toolbars but with the borders around the buttons removed.

Large Buttons are merely larger versions of normal toolbar buttons and with descriptive text added.

To switch to coolbars mode and/or large buttons, select Toolbars from the View menu. Click on the Coolbars Mode and/or Large Buttons checkbox to turn these on or off.

See also:

The toolbars and formatting bar


Moving around a spreadsheet

To move around a spreadsheet, you would usually use the mouse and then click to put the cursor where you want it. You can also use the scroll bars at the right and bottom of the screen to move either vertically or horizontally through the spreadsheet.

However, you can also move the cursor with the cursor movement keys, and there are times when this can be quicker. The following table shows all the ways you can move the cursor.

Key	Description
Arrow keys	The arrow keys move the cursor one cell at a time in the direction of the arrow (up, down, left, or right).
page up	Moves up one screen at a time.
page down	Moves down one screen at a time.
home	Moves to column A in the current row.
end	Moves to the last column that contains information in the current row, or to the last column of the whole spreadsheet if the current row contains no information.
tab	Moves one cell to the right.
shift + tab	Moves one cell to the left.
ctrl + home	Moves to the first cell (A1) in the spreadsheet.
ctrl + end	Moves to the intersection of the last column and the last row that contains information.
ctrl + page up	Moves one screen to the left.
ctrl + page down	Moves one screen to the right.
ctrl +	Moves to the next cell on the right that contains information.
ctrl +	Moves to the next cell on the left that contains information.
ctrl +	Moves to the next cell upwards that contains information.
ctrl +	Moves to the next cell downwards that contains information.
See also:

Moving to a particular cell

Worksheets


Going to a particular cell

If you are working on a small spreadsheet, it is easy to find the cell you require (for formatting, deleting, hiding, or whatever). However, if you have a very large spreadsheet, it can be quicker to ask Spreadsheet to find the cell (or range of cells) you want.

Select the Go To command from the Edit menu. The “Go To” dialog is displayed.

In the Category box, click on the location type. The categories include typical spreadsheet locations such as cells, named ranges, columns and rows. Next, enter the location you wish to go to (you can choose most of these from the drop-down box). Then click on the OK button and the spreadsheet will be redrawn, with the location you asked for selected and active.

See also:

Moving around a spreadsheet


Spreadsheet definitions

There are certain terms that are used in virtually all spreadsheet programs. It is worth becoming familiar with them, as it will make your spreadsheet work much simpler.

You can read about cells, cell addresses and cell ranges in the following sections.

See:

Cells

Cell addresses

Cell ranges


Cells

The spot where a row and column intersect is called a cell. A cell is the place in a spreadsheet where you enter text, numbers, or formulas. For example, in the following illustration, January appears in the cell C4, where column C and row 4 intersect:

The active cell is shown by a heavy cell outline - in this example, B2.

Cells can also be called fields (the word cell is part of the special spreadsheet vocabulary). You use fields throughout Ability – in Write, and Database – and they always work in similar ways and can call on the same function list. You can apply what you learn about fields (known here as cells) in Spreadsheet to the fields you work with in other applications.

See:

Cell addresses

Spreadsheet definitions


Cell addresses

Each cell in a spreadsheet has a unique identifier, called a cell address. The cell address tells you the exact location of a piece of information, just as a home address such as 12 Maple Avenue pinpoints a specific house.

A cell address consists of a column letter and a row number. For example, the cell located where column C and row 2 intersect has the cell address C2.

See:

Cell ranges

Absolute addresses

Spreadsheet definitions


Cell ranges

A range is simply a group of cells. For example, the cells A1, A2, A3, A4, A5 can be referred to individually when using them in a formula but it is quicker to use a range. The following formulas, which give the same result, demonstrate this:

=SUM(A1, A2, A3, A4, A5)

=SUM(A1..A5)

You can give a cell range a unique name and refer to it in formulas using the name, rather than specifying the first and last cells in the range. You can also treat fields in a database table as ranges so that, for example, you can sum all the records in an invoice database.

See also:

Cell addresses

Spreadsheet definitions


Creating a spreadsheet

To create a new spreadsheet, click on the New [image] button on the toolbar or select the New command from the File menu. A new blank spreadsheet will be opened in the Spreadsheet window.

When you first open a spreadsheet, it is given a default page set-up. This means that the paper size and margins are already set for you. For the majority of spreadsheets, these settings will probably be what you want, but you might want to change them, for instance to give a large left margin on a document that is going to be bound (see Page set-up).

See also:

Entering information


Opening an existing spreadsheet

To open an existing spreadsheet, first start Spreadsheet - see Starting Spreadsheet.

Next click on the Open button [image] on the toolbar or select Open from the File menu.

The "Open" dialog is displayed. When you have located the required spreadsheet document (you need to locate the appropriate folder in the Look In box and set the Files of Type to Ability Spreadsheet, if it is not so set already), click on the Open button to open it.


Worksheets

Worksheets are the pages, so to speak, of a particular spreadsheet and are where you enter data and perform calculations. There can be up to 255 worksheets associated with each spreadsheet. Whenever you create or open a spreadsheet you are immediately taken to the first worksheet in that spreadsheet − called Sheet1 by default, though you can rename the sheet if you like.

The worksheets are listed along the bottom of the spreadsheet window, with the current sheet selected.

If not all of the sheet names are visible, move the mouse pointer over the left edge of the bottom scroll bar (just to the right of the last visible sheet title). When the pointer turns to a cross, drag the scroll arrow till the required number of sheet names are visible. To select another sheet, just click on its name.

You can rename, delete, insert, copy and move worksheets, and also create links between sheets in the same spreadsheet or sheets in different spreadsheets.

Note:	The terms worksheet and sheet are used interchangeably in these help notes and have the same meaning. Sometimes the term workbook is used for a spreadsheet file and its associated worksheets, with Spreadsheet being reserved for the program (or application) itself.

See:

Renaming a worksheet

Deleting a worksheet

Inserting a worksheet

Copying and moving worksheets


Renaming a worksheet

Rename a worksheet by right-clicking on the sheet name and selecting Rename from the drop-down list. In the “Rename worksheet” dialog type the new name and then click on OK.

The new sheet name immediately replaces the old one along the bottom of the spreadsheet window.

See:

Worksheets


Deleting a worksheet

Delete a worksheet by right-clicking on the sheet name and selecting Delete from the drop-down list. You will be asked if you want to continue with the deletion − click on Yes.

The deleted sheet name immediately disappears from along the bottom of the spreadsheet window.

See also:

Inserting a worksheet

Worksheets


Inserting a worksheet

Insert a new worksheet directly to the left of an existing sheet name by right-clicking on the sheet name and selecting Insert from the drop-down list. In the “Insert worksheet” dialog you should enter the name of the new worksheet and then click on OK.

The new sheet name is immediately inserted at the right place along the bottom of the spreadsheet window.

See also:

Deleting a worksheet

Worksheets


Copying and moving worksheets

Copy or move a worksheet by right-clicking over the appropriate sheet name and selecting Copy/Move from the drop-down list. In the “Copy or Move Worksheets” dialog follow these steps:

1.	Select the sheet to be copied or moved in the Copy/Move selected sheets box (note that all the sheets are listed in order).

2.	In the To workbook box the current spreadsheet is displayed. (If you would like to copy or move a sheet to a different spreadsheet, make sure the spreadsheet is already open and then select it in the To workbook box.)

3.	All the sheets in the selected spreadsheet are listed in the Before sheet box. Select the sheet before which you want to copy or move the sheet selected in Step 1.

4.	Choose whether to copy (duplicate without deleting the original) or move (duplicate and delete the original) by selecting either the Copy or the Move button. Copy or move as many sheets as you want while you are in the dialog.

5.	Click on OK to close the dialog.

See also:

Worksheets

Deleting a worksheet

Inserting a worksheet


Calculating across worksheets

The basic syntax for a formula that will work across worksheets is:

=sheetname!cell

For example,

=Sheet1!A1

entered in any cell in any sheet in the spreadsheet will access the data in cell A1 of Sheet1.

The result can be mixed into a formula any way you like. For example, to take the contents of cell D4 from a sheet called Sheet5 and multiply it by 2, use the following formula:

=Sheet5!D4 * 2

To refer to a range from another sheet, just prefix the first part of the range with the sheet name:

=SUM(Sheet1!A1..A10)

will add up the contents of cell A1 through to A10 in Sheet1.


Entering information

You can put two main types of information into a spreadsheet: text and numbers. Ability will automatically sense which type of information you are entering and give it a default justification within the cell. When you enter text, Ability will left justify your entry; when you enter numbers, Ability will right justify them.

Here are some examples of the type of information you can enter and the way Ability deals with them.

Enter this text	Spreadsheet displays	Type of information
Hello world!		Text
123		Number
$123,000.00		Currency
12/12/98		Date
In this example, Currency and Date are actually numbers with a format applied. There are other types of number formats and you can change the way Ability displays the result. See Formatting numbers using the toolbar.

Any information you type will be put into the cell that has the dark border around it. This is the active cell. To activate a cell, click on it, or move the dark border to it by using the arrow keys.

See:

Entering text

Entering values

Entering dates


Entering text

When you start typing text, you will notice that it does not immediately appear in the active cell. Instead, it is shown in the formula bar. This means that you can make changes to it more easily, as it is slightly larger and easier to see.

When you have finished typing the text, do one of the following:

•	Click on the Confirm button [image] on the formula bar. The text will be inserted into the active cell.

•	Press one of the arrow keys. Your text will be entered and the active cell will move according to which arrow key you pressed. This saves having to use a keystroke if you’re entering a long column or row of information.

•	Press the Enter key - the text will be entered and the active cell will move down one row.

•	Click the Cancel button [image] on the formula bar to abandon the text entry.

Ability will display the word “Text” in the Cell Type box on the formula bar.

The following section shows you how to enter text that is wider than the cell.

See:

Entering a long text item


Entering a long text item

When you create a new spreadsheet, each column is 80 points wide by default.

A point in this case is 1/96 of an inch or 0.026cm and is not the same as the typographical measure (where a point is 1/72 of an inch and used to measure font size).

If the text you want in the cell is longer than the default width, you can enter the text and let it overflow into the next cell. If there is no information in the cell to the right, the whole text is displayed, otherwise Ability ‘clips’ the text at the cell boundary.

An alternative is to allow the text to be wrapped within the cell (see Cell alignment).

See also:

Entering text


Entering values

You enter values in the same way as you enter text: point to the cell where you want the value to appear, then type it in. Finish the entry in the same way as you would finish a text item (see Entering text).

Ability will display the word “Number” in the Cell Type box on the formula bar.

Note:	Values are automatically put to the right of cells – this is to make sure that columns of figures line up neatly. You can change this alignment if you prefer (see Cell alignment).

See:

Formatting a number

Formatting numbers using the toolbar

Displaying numbers as text


Entering currency values

You can include a currency symbol when entering numbers. Ability will understand that you want a currency format. All these are valid currency entries:

$12
$123,456
$123,456.00

Note:	The currency symbol you use must match the setting in the Windows Control Panel. For example, this will be the dollar symbol ($) if you are in the US and the pound symbol (£) in the UK. If not, Ability will treat the cell content as text and you won’t be able to perform arithmetical calculations on it.

See also:

Formatting a currency


Entering a time

Use a colon to separate hours from minutes. Here are some examples of entries that Ability will recognize as time values:

23:45
11:45 AM
11:45 PM

For a description of how Ability deals with dates and time, see Date and time functions.


Entering dates

You should use the shorthand method to enter dates (if you prefer the long display for dates, change the format – see Formatting a date).

These are all OK to enter:

3-6-64
12/2/1995
01.01.95
September 12, 1995

But these will be treated as text only:

3rd June 1964
September 12th, 1995

If you don’t use the shorthand method, Ability could treat your entry as text and you won’t be able to change the format or perform date arithmetic.

You can enter the year as two or four digits. Two digit years below 30 will default to the 21st century (see Year 2000). For example:

Enter	Date stored as
1/1/20	1/1/2020
1/1/29	1/1/2029
1/1/30	1/1/1930
For a description of how Ability deals with dates and time, see Date and time functions.


Entering numbers you don't want to calculate

Whenever you enter a number, Spreadsheet assumes that it is a value and that you might want to use it in a calculation.

If you enter a number that is obviously not meant to be used in calculations, such as a phone number like 6684123, Spreadsheet can't automatically tell that it is supposed to be text. So, to enter numbers like phone or invoice numbers, you need to tell Spreadsheet not to calculate them.

To do this, type a single quote (‘) before you enter the actual number. This is necessary if you want a formula expression, such as =26 - 11, to appear as text rather than returning the result of a calculation. In this case, type '=26 - 11.

The single quote doesn’t show up in the spreadsheet. It simply indicates that the number should be treated as text rather than as a value.


Formatting the spreadsheet

Whether you enter text or a number into a cell, you can specify how you want the cell contents to appear. You can format a single cell or range of cells in the same way.

Most formatting options are available through the “Format” dialog box and some can also be implemented using the Format toolbar.

Access the “Format” dialog box by selecting Cells from the Format menu (alternatively, right-click and select Format Cells from the drop-down menu).

The Format box contains six main sections, all of which have their own page. These can be accessed by clicking on the appropriate tab along the top of the box. In addition, there is a button at the bottom right of the box that gives you access to the Styles page for designing, naming and saving format combinations.

The formatting options available through the “Format” dialog box are:

•	Number (see Formatting a number)

•	Alignment (see Cell alignment)

•	Font, font style, size and color (see Cell font and style)

•	Borders (see Adding Borders)

•	Background colors (see Selecting the background color)

•	Locking and hiding cells (see Locking and hiding cells)

•	Styles: formats saved for future use (see Styles)

Some of the above can also be implemented through the Format toolbar. For instance, if you are formatting numbers, take a look at the formats you can apply by just clicking a button on the toolbar (see Formatting numbers using the toolbar.)

Note:	When you format a cell and then copy it, the formatting is also copied and is therefore pasted to a new cell.


Cell font and style

Use any or all of the following methods to select cell font and style using the toolbars:

•	Click on the arrow to the right of the Font box [image] to choose a font.

•	Click on the arrow to the right of the Size box [image] to choose a size (or type a size directly into the Size box).

•	Click on the Bold, Italic, Underline and Strikethrough buttons [image] to apply styling.

The Bold, Italic, Underline and Strikethrough buttons have two states: up and down. If the active cell shows a button down, the effect has been selected; up and the effect is turned off.

You can also choose fonts and font styles by using the menus.

See:

Choose fonts using the menus


Using the menus to select cell font and style

To choose a font, select Cells from the Format menu, which opens the “Format” dialog box, and then select the Font tab. (The “Format” dialog box can also be accessed by right-clicking your mouse and then selecting Format Cells).

•	Choose a font for the text in the Font box, and select a style in the Font Style box.

•	Choose a size for the text in the Size box.

•	Choose to strikeout and/or underline text in the Effects box: click on the checkbox next to the effect(s) you require.

•	Select a text color in the Color box.

A sample of the options you have chosen is given in the Sample box. When you have finished, click on the OK button and the text will be formatted to your specification.

See also:

Using the toolbar to select cell font and style


Cell alignment

By default, Ability will align text to the left and numbers to the right of a cell. Both will be aligned to the bottom of the cell and each cell is formatted to sit on a single line (i.e. cell wrapping is off).

You can override the default alignment by using either the formatting bar or the Alignment command.

Using the formatting bar you can:

•	Click on the Left, Right or Center buttons [image] to specify horizontal alignment.

•	Click on the Top, Middle or Bottom buttons [image] to choose vertical alignment. Depending on the size of the cell, this might not make much difference to the appearance of the text.

•	Click on the Wrap button [image] to wrap the text within the cell. This switches the cell from a single line to as many lines as it needs to fit all the text within the cell.

Alternatively, you can use the Alignment tab in the “Format” dialog box (select Cells in the Format menu or right-click and select Format Cells):

To set the horizontal alignment of the text, click on the button next to Left, Center or Right.

To set the vertical alignment of the text, click on the button next to Top, Center or Bottom.

If you want text that is too long for the cell to wrap round onto a line below within the cell (rather than writing on into adjacent cells), click on the checkbox next to Wrap Text. The cell will look something like this:

A sample of the options you have chosen is given in the Preview box. When you have finished, click on the OK button and the text will be formatted to your specification.

See also:

Formatting the spreadsheet


Formatting numbers using the toolbar

Various formats can be applied to cells. However, the most important in Spreadsheet are the number formats. These will be effective if the cell contains any form of numeric value (including plain numbers, dates, formulas, currencies).

The quickest way to apply number formats is to use the number format buttons on the toolbar, as follows:

 - Add currency format to cell.

 - Add percentage format to cell.

 - Format thousands with comma separator.

 - Increase number of decimal places by one.

 - Decrease number of decimal places by one - the numbers will be rounded to the nearest decimal place.

These buttons can be used together - for example, a cell may contain the number 32. Click on the Currency button, then twice on the Increment precision button, to display $32.00.

For more detailed number formatting, select the Number tab in the “Format” dialog box (select Cells in the Format menu or right-click and select Format Cells).

See:

Formatting a number using the menus


Formatting a number

To format a number:

1.	Select Cells in the Format menu (or right-click and select Format Cells).

2.	Select the Number tab in the “Format” dialog box.

3.	Select (for example) Number from the type list. The “Number” group box will appear.

4.	In the Decimal Places box, specify the precision of the number. You can have a minimum of none and a maximum of fourteen. (You can also use the increment precision button on the formatting toolbar − see Formatting numbers using the toolbar).

5.	Auto Precision automatically sets the precision of the number at the maximum of fifteen i.e. any number that has greater than fifteen digits (or fourteen decimal places in scientific form) will be rounded to fifteen significant digits.

6.	Select the Thousands With Commas checkbox for comma formatted numbers, i.e. whether to have 1,345,622 or 1345622. (You can also use the commas button on the formatting toolbar − see Formatting numbers using the toolbar).

7.	Select the Negatives In Brackets checkbox to enclose negative numbers in brackets, i.e. whether to have (123456) or -123456. (Surrounding negative numbers with brackets is a common practice in accountancy.)

8.	Select the Negatives In Red checkbox to display negative numbers in a red font.

9.	In the Trail/Lead String option you can choose to have either trailing or leading characters. Enter your text in the text box or choose from one of the existing group. You can create your own number formats, including alternative currency formats. Here are some examples:

2 lb.
2.2 Kg
1.2 $bn
FFr 23.56
DM 23.56

Note: Trailing and Leading text is managed in the Options dialog. You can add and delete entries here. See General options for more details.

A sample of the options you have chosen is given in the Preview box. When all is set, click on the OK button and the number will be formatted to your specification.

You can use a variety of number types, all of which are displayed in the list at the left. To format the appropriate type of number (e.g. Number, Currency, Fraction, etc.) click on it in the list. The settings displayed to the right of the list change according to your selection. The following sections give details on how to modify these settings for each type of number.

Note that the General option applies no special number formats.

See:

Number

Currency

Date

Time

Percentage

Fraction

Scientific

Boolean


Formatting a currency

To format a number as currency:

1.	Select the Cells command from the Format menu (or right-click and select Format Cells).

2.	Select the Number tab in the “Format” dialog box.

3.	Select Currency from the number list. The “Currency” group box will appear.

4.	The currency symbol will automatically be attached to the number. The rest of the currency options are the same as for the Number format - see Formatting a number for details on how to set these.

You can also use the Currency button [image] to add a currency symbol directly.

Note: 	The currency symbol used by Ability is taken from the Regional settings in the Windows Control Panel.


Formatting a time

To format a time:

1.	Select the Cells command from the Format menu (or right-click and select Format Cells).

2.	Select the Number tab in the “Format” dialog box.

3.	Select Time from the numbers list. The “Time” options box will be displayed.

4.	Click on the checkbox next to Seconds to display seconds as well as hours and minutes.

5.	Click on the checkbox next to 0..12 to use the 12-hour clock and thereby display a trailing AM/PM. Leave the checkbox blank to use the 24-hour clock.

See also:

Formatting a date


Formatting a date

To format a date:

1.	Select the Cells command from the Format menu (or right-click and select Format Cells).

2.	Select the Number tab in the “Format” dialog box.

3.	Select Date from the numbers list. The “Date” group box will display.

4.	In the Month group box, specify how you would like the month represented:

Digits:	1
Short Name:	Jan
Long Name:	January

5.	Choose the separator you wish to appear between the different elements of the date in the Separator group box (e.g. 15/7/97 or 15-7-97).

6.	In the Year group box, choose between None, Short Form (e.g. 97), or Long Form (e.g. 1997).

7.	In the Order group box, choose whether you would like the day or the month to come first.

You can also choose to display the time alongside the date: click on the checkbox next to Add Time and then choose a time format.

A sample of the options you have chosen is given in the Preview box. When you have finished, click on the OK button and the date will be formatted to your specification.

See also:

Entering dates

Formatting a time


Formatting a percentage

To format a number to be displayed as a percentage:

1.	Select the Cells command from the Format menu (or right-click and select Format Cells).

2.	Select the Number tab in the “Format” dialog box.

3.	Select Percent from the numbers list. The “Percent” group box will be displayed.

4.	The percentage symbol will be attached to the number. The rest of the percentage options are the same as for the Number format - see Formatting a number for details on how to set these.

Note: you can also use the Percentage button [image] on the formatting toolbar to apply percentage formatting.


Formatting a fraction

To format a real number as a fraction:

1.	Select the Cells command from the Format menu (or right-click and select Format Cells).

2.	Select the Number tab in the “Format” dialog box.

3.	Select Fraction from the numbers list. The “Fraction” group box will be displayed.

4.	In the Display As box, you can select Auto (the nearest representation of the number as a fraction) or round the fraction to the nearest 100th, 10th, 16th, 8th or 4th.

You can also choose to have the integer part of fractional numbers displayed with commas, negatives in brackets or in red, as with other numbers - see Formatting a number.

A sample of the options you have chosen is given in the Preview box. When you have finished, click on the OK button and the fraction will be formatted to your specification.


Formatting a scientific number

To format a number to display in scientific notation:

1.	Select the Cells command from the Format menu (or right-click and select Format Cells).

2.	Select the Number tab in the “Format” dialog box.

3.	Select Scientific from the numbers list and then set the decimal places as described in Formatting a number.

Ability defaults to Scientific format if the number is too big (or small) to display with significant precision in the width of the current cell.

Note: scientific format coverts the original number into a base number, between 1 and 10, multiplied by a power of 10. This is useful for working with very large or very small numbers. For example, 1 meter is defined as the distance light travels in 0.000000003335640952 seconds. Entering such a value is hard on the eye. Selecting scientific format gives you a simplified display of 3.34E-09 (which is 3.34 x 10-9).


Formatting a boolean value

To format a number as boolean:

1.	Select the Cells command from the Format menu (or right-click and select Format Cells).

2.	Select the Number tab in the “Format” dialog box.

3.	Select Boolean to display either TRUE, for all numbers that are non-zero, or FALSE, for cells containing zero.

Note that there are no options to choose from here – just selecting Boolean will ensure the above result.


Clearing a format

You can clear a format from a cell (or range of cells) without deleting the contents.

1.	Select the cell (or range) and then right-click your mouse.

2.	Select Clear and then select the Format option from the drop-down menu.

Alternatively, select the Clear command from the Edit menu and then select Format.


Selecting the background color

To change the background color of a cell, or range of cells, first select the cell, or range, and then do the following:

1.	Select the Cells command from the Format menu (or right-click and select Format Cells).

2.	Select the Color tab in the “Format” dialog box. The “Color” dialog is displayed.

3.	Select a color from the Basic colors box.

4.	Click OK to apply the color to the selected cell or range.

In addition to the basic colors you can “design” your own custom color:

1.	Enter the “Color” dialog box as above.

2.	Click on the Define Custom Colors button. This expands the right side of the box to display a color chart.

3.	Use your mouse to pick a color from the chart. Once you’ve done this you can refine your choice in the color bar at the side of the chart. The result will be previewed in the Color/Solid box below.

4.	Click on Add Custom Color to add the color to the Custom colors section at the left of the box.

5.	Click on OK to apply the custom color.

In future you can enter the box and choose any of the custom colors you have designed and saved.

Note that there are boxes for Hue, Saturation and Luminosity, as well as Red, Green and Blue. The values in these boxes change with the color selections you make. You can type in values directly that will find and display the matching color.

Also, note that a background color can be applied to the whole of a worksheet in a spreadsheet. To do this follow these steps:

1.	Select Sheets from the Format menu.

2.	Select Background from the drop-down list.

3.	The “Color” dialog box is displayed.

4.	Follow the same procedures as above.

The applied color will affect all of the current worksheet, rather than just selected cells.


Adding borders

There are two ways - a simple way using a button and a more complex way using the menus - of adding borders to a cell or a range of cells in your spreadsheet.

To add a border to a cell or a range of cells using the button:

1.	Select the cell or range of cells around which you wish to put the border.

2.	Click on the arrow attached to the Format Border button [image] on the formatting bar. A small “Borders” box is opened.

3.	Click on the borders you would like to be placed around the selection: All Borders puts a border around the whole selection; Left, Right, Top and Bottom can be used to place individual borders; Clear Borders clears all borders from the selection.

Note that in using the Format Border button the borders are placed around the boundary of the selection. If you want to put borders around individual cells within a larger selection, then you need to use the menu, as shown below (although you could, of course, select the cells one by one and repeat the above procedure).

To add borders to a cell or range of cells using the menu:

1.	Select a cell or range of cells.

2.	Select Cells from the Format menu (or right-click and select Format Cells).

3.	In the “Format” dialog box, select the Border tab to open up the “Border” box.

4.	The general procedure for creating a border is first to choose a border color, followed by a line-style, and then to click on the appropriate border or borders to apply the selected color and line-style. Of course, you may want to give different borders different colors and/or line-styles. In this case, you have to repeat the process in each case. The following sections explain how to select borders, line-styles and colors.

5.	In the Borders group box at the left, you can specify which edges of the selection you want to put borders around. Outline puts a box around the whole of the selected area; Left, Right, Top and Bottom act on each of the cells. If you switched on all of these at once, you would get a selection surrounded by a border, with all the cells within the selection also surrounded by borders. This allows you to have, for example, a thick-lined box of one color that contains broken-lined cells of another color.

6.	You can also have double borders, once again choosing these individually or as a whole in the Borders box at the left.

7.	Choose a color for the border in the Color box. Clicking on the arrow displays a range of colors. Note that all of the line-styles take on the color that is selected, until you choose another.

8.	In the Style box at the bottom, you can choose the line-style you would like a border to have. The easiest way to set your borders is to pick a line-style first and then click on each of the border edges in turn. Often you’ll want a different outline style, so click on the outline box and then click on a different line-style to finish. For example, to set a border style that looks like this:

go through the following steps:

(i)	Click the Border button on the toolbar.

(ii)	Select a thin line style.

(iii)	Click on Left, Right, Top and Bottom border boxes.

(iv)	Click on the Outline box.

(v)	Select a heavier line style.

(vi)	Click OK to finish.

9.	Remove a border by clicking on it in the Border box. This will cancel the line-style. Alternatively, select the blank line-style in the Style box and then click on the appropriate border.

10.	Any line-styles, colors or borders you set will be displayed in the Preview box.

11.	Once you are satisfied with your settings, click on OK.

The “Format” dialog box will close and your settings will be applied to the currently selected cells in the spreadsheet.


Protecting a spreadsheet

To “protect” a spreadsheet, that is to say, to prevent unauthorized changes to the contents and formats of locked cells and to ensure that the contents of hidden cells are not displayed, follow these steps:

1.	First, lock and hide the relevant cells (see Locking and hiding cells).

2.	Next, select the Protect command from the Tools menu. The “Protect” dialog box is displayed.

3.	Type in a password of your choosing. You will not see the letters; they are represented by asterisks to prevent anyone seeing your password accidentally.

4.	Click on the OK button. You will be asked to re-enter your password.

5.	The box will close and any locked or hidden cells will be unalterable until the Protect command is cancelled.

6.	Unprotect the spreadsheet by selecting Unprotect from the Tools menu and entering the same password in the box. Locked and hidden cells will now be accessible again.

Note: You can protect a spreadsheet without entering a password. To do this, just click on the OK button of the “Protect” dialog box without entering a password. Obviously this is not a safe method, although it is an easy way to stop yourself altering cells you don’t want changed.

Write your password down in a safe place. If you forget your password, you will never be able to unprotect your spreadsheet!


Locking and hiding cells

You can lock and hide cells to help protect information you don’t want to be altered or seen. For this to be effective you also need to protect your spreadsheet (see Protecting a spreadsheet). When you protect a spreadsheet, it is only the locked and hidden cells that are affected.

•	Locking a cell prevents the cell from being changed when the spreadsheet is protected.

•	Hiding a cell means that its value will not be displayed, either in the cell itself or on the status bar, when the spreadsheet is protected.

Since it is possible to alter a cell that is hidden, even though it is protected and the changes would not be visible on screen, it is a good idea to lock any hidden cells.

These features can be useful, for instance, if someone else uses your spreadsheet and you do not want them to accidentally alter values or formulas in certain fields or to view sensitive information.

To lock or hide cells:

1.	Select the cell or range of cells you want to lock or hide.

2.	Click on either or both of the Lock and Hide buttons [image] on the formatting bar.

Alternatively, select Cells from the Format menu and then click on the Cell Attributes tab in the “Format” dialog box.

1.	Click on the checkbox next to Lock to lock the cells, and on the one next to Hide to hide the cells. You can choose both to lock and hide cells.

2.	Click on the OK button to close the “Format” dialog box.

3.	The cells will be redrawn with hatching over them (forward slashes for Hide, backward slashes for Lock) to help you remember which cells have been locked or hidden, or both, while you are designing your spreadsheet.

Once you have locked or hidden cells, you should protect the spreadsheet. The Lock and Hide commands are then activated (see Protecting a spreadsheet) and the hatching disappears.

You can unlock and unhide cells again by selecting them and clicking on the Lock and Hide buttons on the formatting bar or by going into the Cell Attributes page of the “Format” dialog box to cancel the selections. First, though, you need to unprotect your spreadsheet.

Note:	Once a document is protected you cannot lock and hide cells, and so the Lock and Hide buttons and their equivalents in the “Format” dialog box are made inactive.


Changing column width

Letting text overflow into the adjacent cell is fine as long as you don’t put information into that adjacent cell. However, in the body of most spreadsheets you’ll want columns of information side by side, and if you do put information into the adjacent cell the end of the long text is ‘clipped’.

To avoid this, you can alter the widths of the columns so that the texts will fit in them without leaking over into adjacent cells. You can change the width of a single column or several columns at once.

To change the width of a single column, position the mouse pointer over the right edge of the column bar. The mouse pointer will change to a movement indicator:

Drag the mouse (keep the mouse button pressed while moving) to resize the column.

To change several columns at once, first select them and resize any of the selected cells.

Alternatively, you can select the Column Width command from the Format menu. The “Column Width” dialog box is displayed.

Do one of the following:

•	In the Column Width box, type the width you require in points. A point is 1/96 of an inch, or 0.026cm, so type 96 if you want the column to be 1 inch.

•	You can ask Spreadsheet to find the best width, given the texts you have entered. To do this, click on the Best Fit button. Spreadsheet will then look at the longest text in the chosen column and adjust the width so that the text will be displayed in its entirety. If you have highlighted several columns, Spreadsheet will find the appropriate best width for each of the columns.

•	If you want to go back to the default width of 80 points, click on the checkbox next to Standard.

When you have altered the column width to suit, click on the OK button and the spreadsheet will be redrawn to your specification.

Note:	You can tell Spreadsheet to keep the width of a column, and instead of writing across into adjacent cells, move extra text down onto a new line within the same cell. To do this, select the cell in question and choose the Alignment command from the Format menu. Then click on the checkbox next to Wrap Text in the “Alignment” dialog box.

See also:

Changing row height


Changing row height

Ability will automatically adjust the height of a row to match the largest font you’ve used, so you won’t normally need to change row height.

To change the height of a single row, position the mouse pointer over the top edge of the row bar. The mouse pointer will change to a movement indicator. Drag the mouse (keep the mouse button pressed while moving) to resize the row.

To change several rows at once, first select them and resize any of the selected rows.

Alternatively, you can select the Row Height command from the Format menu. The “Row Height” dialog box is displayed.

Do one of the following:

•	In the Row Height box, type the height you require in points. A point is 1/96 of an inch, or 0.026cm, so type 48 if you want the row to be a half-inch tall.

•	You can use Spreadsheet to find the best height, given the texts you have entered. To do this, click on the Best Fit button. Spreadsheet will then look at the highest text in the chosen row, and adjust the height so that the text will be displayed in its entirety. If you have highlighted several rows, Spreadsheet will find the appropriate best height for each of the rows.

•	Select the Standard checkbox to return to the default height.

When you have altered the row height to suit, click on the OK button and the spreadsheet will be redrawn to your specification.

See also:

Changing column width


Copying and moving information

In a spreadsheet you can copy or move information:

•	From one cell to another

•	From one range of cells to another range of cells

When you copy information, Spreadsheet creates duplicate information in the destination cells you specify and the information also remains in the original cells.

Any formulas in the copy range are propagated in the destination range. For example, a reference to the cell A1 in a formula in cell A2 will become a reference to cell B1 if the formula is copied to cell B2. You can override propagation (see Absolute addresses).

When you move information, Spreadsheet removes it from the original cells and displays it at the destination cells instead. When you move formulas, any cell references within the range are adjusted so they work in their new location. References made to cells outside the range are preserved.

In either case, if there is already information in the destination cells, Spreadsheet replaces it with the new information.

The most effective way of copying and moving information is to use drag and drop (see Drag and drop).

You can also use cut and paste in a number of ways, as explained in the following sections.

Note:	As Spreadsheet is a Windows program, anything you copy or cut is placed in the Clipboard. This means that you can paste it into another spreadsheet or another type of document entirely, such as a database.

See:

Copy and moving from one cell to another

Copy and moving from one range to another

Copying one cell to a range

Copying a range of cells to a single destination cell

Copying one range to another range


Copying and moving from one cell to another cell

To copy or move a single cell’s contents to a new location:

1.	Click on the “source cell” - the cell you want to copy.

2.	Select the Copy [image] button or the Copy command from the Edit menu. If you wish to move the information, use the Cut [image] button instead.

3.	Click on the “destination cell”.

4.	Select the Paste [image] button or the Paste command from the Edit menu.

See also:

Drag and drop

Copying and moving information


Copying and moving from one range to another range

You can also copy or move the contents of a range of cells from one place to another.

1.	Select the range of source cells.

2.	Select Copy [image] from either the toolbar or the Edit menu, or by right clicking your mouse. If you wish to move the information, select Cut [image] instead of Copy.

3.	Select the range of destination cells, and then select Paste [image] from the toolbar or Edit menu, or by right clicking your mouse.

Provided the copy range can fit exactly into the paste range (i.e. the paste range is an exact multiple of the copy range), you can copy information to a much larger area than the copy range. To do this, select the paste range before pasting. The paste range will contain an exact multiple of the copy range. This is extremely important when building spreadsheets. For example, you can make a formula work for one column and then copy it, in one operation, to many columns.

See also:

Drag and drop

Copying and moving information


Copying one cell to many other cells

You can copy one cell and paste it into any number of destination cells. In the example below, a formula has been entered in cell C4, =C3*$A4, to convert January’s figure from £ to $:

You can copy the formula to make it work for February, March and April:

1.	Click on cell C4 to make it the active cell.

2.	Click the Copy button [image] on the toolbar.

3.	Select cells D4 through to F4 to mark the destination range.

4.	Click the Paste button [image] on the toolbar.

Note the use of the absolute reference in the formula – as you copy it to February, March and April, the reference to C3 changes to D3, E3, F3 respectively, but they all still refer to A4 to get the exchange rate (see Absolute addresses for more details).

The above operations can also be used to move the contents of one cell to many other cells, if Cut is used instead of Copy.

See also:

Drag and drop

Copying and moving information


Copying many cells to one

This saves you from having to select an area the same size as the copy range when pasting: just point to the top left-most cell in the destination range and Ability will fill in the rest. In the following example, a new line for DM has been added:

You can copy the formulas in row 4 and make them work for row 5:

1.	Select cells C4 through to F4.

2.	Click the Copy button [image] on the toolbar.

3.	Click on cell C5 to mark the top left corner of the destination range.

4.	Click the Paste button [image] on the toolbar to fill row 5 with formulas.

The above operations can also be used to move the contents of many cells to one cell, if Cut is used instead of Copy.

See also:

Drag and drop

Copying and moving information


Copying many cells to many other cells

The number of cells in the copy range must divide exactly into the paste range. In the following example, cells C4 and C5 have the correct formulas, which need copying to D4 through to F4 and D5 through to F5.

1.	Select cells C4 through to C5.

2.	Click the Copy button [image] on the toolbar.

3.	Select cells D4 through to F5 to mark the destination range.

4.	Click the Paste button [image] on the toolbar to copy the formulas.

The above operations can also be used to move the contents of many cells to many other cells, if Cut is used instead of Copy.

See also:

Drag and drop

Copying and moving information


Drag and drop

"Drag and drop" is a technique, using your mouse, for moving, copying, filling in and deleting spreadsheet data. "Drag and drop" in Ability is supplementary to the more traditional copy, cut, paste, delete and fill commands, although easier to use once learnt.

The following sections show you how to drag and move, copy, fill or delete.

See:

Drag and copy

Drag and move

Drag and fill

Drag and delete


Drag and move

To move the contents of a cell (or selected range):

1.	Click on the cell whose contents you want to move.

2.	Point anywhere along the cell's border.

3.	When the mouse pointer changes to an arrow, hold down the left mouse button.

4.	Drag the cell outline to the target cell. Release the mouse button.

The contents of the cell will be moved to the target cell. This has the same effect as cut and paste.

See also:

Drag and drop

Copying and moving information


Drag and copy

To copy the contents of a cell (or selected range):

1.	Click on the cell whose contents you want to copy.

2.	Point anywhere along the cell's border.

3.	When the mouse pointer changes to an arrow, hold down the left mouse button and the Ctrl key.

4.	Drag the cell outline, while keeping the Ctrl key depressed, to the target cell. Release the mouse button and Ctrl key.

The contents of the cell will be copied to the target cell. This has the same effect as copy and paste.

See also:

Drag and drop

Copying and moving information


Drag and fill

You can drag and fill the contents of a single cell, or range of cells, to another range of cells. Certain types of ordered data, such as numbers, days and months, are incremented automatically. Follow these steps:

1.	Click into the source cell.

2.	Move the mouse pointer over the cell handle in the bottom right corner of the cell:

3.	When the mouse pointer turns to a cross, hold down the left mouse button and drag the cell outline till it covers the area you want to fill.

The contents of the source cell will fill the selected cells. Note that only cells adjacent to the source cell -either up, down, left or right - can be filled. To fill an area, rather than just a column or row range, follow steps 1 to 3 above then drag the range you've just created to fill the remaining area.

If the source cell contains a number or a text string with a number at its end, the selected cells will be filled incrementally. Here are some examples:

Source cell	Target range after drag and fill
1998	1999, 2000, 2001
Product1	Product2, Product3, Product4
Jan	Feb, Mar, Apr
Monday	Tuesday, Wednesday, Thursday
The last two examples show how the built-in ordered lists can be used (see Custom lists for more information).

Sometimes, when the source cell contains a number or member of a custom list, you may not want a range to be filled with incremental values. In these cases, you should first select the required range and then select the Fill command from the Edit menu or click on the Fill button on the toolbar. In the "Fill” dialog make sure you leave the Series box unselected and then click on OK. The contents of the source cell will be copied exactly (without incrementation) to each of the cells in the selected range.

See also:

Drag and drop

Copying and moving information


Filling cells automatically

You can automatically fill a whole range of cells with values. You might use this, for example, when you set up a spreadsheet in which you want a column for every year from 1984 to the present. Column A would be 1984, column B would be 1985, and so on.

There are two types of fill: Normal and Series. In normal fill, the same value is put in each selected cell. In series fill, a different value (calculated in a specified increment from the starting value) is put in each cell.

To automatically fill in a range with a particular value, using Normal Fill, follow these steps:

1.	First enter the value in a cell.

2.	Next, select the range you want to fill (click and drag the mouse across and/or down the spreadsheet to select a range, making sure the first cell in the selected range is the cell containing the value).

3.	Click on the Fill button [image] or select the Fill command from the Edit menu. The “Fill” dialog is displayed.

4.	Click on OK (note that the fact that the Series box is left unselected indicates that this is a normal fill).

5.	The range will be filled with the value of the active cell. With normal fill, the fill is performed in one direction at a time. If the range spreads over an area greater than a single row or column, then you need to repeat the operation, specifying a different fill direction, to fill the whole area. For instance, you might first fill an area down and then right. (See below for details)

To automatically fill in a range with an incremented value, using Series Fill, follow these steps:

1.	First select the range you want to fill.

2.	Next, select the Fill command from the Edit menu or click on the Fill button on the toolbar.

3.	In the “Fill” dialog select the Series checkbox by clicking on it. This activates the Series box.

4.	In the Start box enter the value from which you want to start.

5.	In the Step box enter the step-size by which you want the value to be incremented. (Note that Ability recognizes various incremental lists, including numbers, text that ends with a number, and the lists, such as days of the week, contained in Custom Lists (under Options from the Tools menu)). If you leave the Step box empty, Spreadsheet will automatically use an increment of 1.

6.	The range will be filled incrementally according to the direction selected in the Direction box. If the range stretches over rows and columns the fill will proceed in the same direction throughout the range. (See below for details)

7.	When all is set, click on the OK button. The selected cells will be filled.

Direction of Fill

With either fill type, if the selected range spreads over an area greater than a single row or column, then you can choose in which direction you would like the fill to work.

Left	Fills the cells to the left of the active cell with its value
Right	Fills the cells to the right of the active cell with its value
Up	Fills the cells above the active cell with its value
Down	Fills the cells below the active cell with its value
•	Series Fill automatically fills the whole of an area, according to the selected direction.

•	Normal Fill fills in only one direction at a time. So, for instance, to normal fill a selected area with a particular value, you might select Down first and then select Fill again and select Right.

Note:	Fill will work with formulas as well as values. Filling with a formula works in the same way as copying a formula: each formula is propagated throughout the range. The formula should be entered in the active cell, as for normal fill, before selecting the range to be filled (see Copying a formula for more details).

Example 1

To fill the area A1..D10 with a particular value, say the number 1, do the following:

Enter 1 in A1 and then select the range A1..D10. Select Fill from the Edit menu or click on it on the toolbar. The "Fill" dialog will be displayed. Leave the Down button switched on and then select OK. The first column in the range will be filled. Next, select Fill again and this time switch on the Right option by clicking on it. The rest of the range will be filled.

Example 2

To fill the range A1..D10 with an incremented series of invoice numbers, where the first one is ABC45, the step is 2 and the range is to be filled along the rows, do the following:

Select the range to be filled. Select Fill from the Edit menu or by clicking on the toolbar. The "Fill" dialog will be displayed. Click on the Series checkbox to activate it. In the Start box put ABC45. In the Step box put 2. Select the Right option. Select OK. The range will be filled along the rows in increments of 2, from ABC45 to ABC123.


Inserting and deleting cells, rows and columns

The following sections show you how to delete and insert cells, rows and columns in your spreadsheet.

See:

Drag and delete - using the mouse to delete cells

Erasing the contents of a cell   - removing contents, formulas and formats

Inserting cells. rows and columns  - creating new cells, rows and columns

Deleting cells, rows and columns  - deleting and removing cells, rows and columns


Drag and delete

To delete the contents of a range of cells:

1.	Select the range.

2.	Move the mouse pointer over the cell handle:

3.	When the pointer changes, press on the left-hand mouse button and drag back across the range. The range will be grayed out as you drag.

4.	Release the mouse button when the range is totally gray. The contents of the range will be deleted.

See also:

Drag and drop

Copying and moving information


Erasing the contents of a cell

You can erase the contents of a cell. To do this, activate the cell you wish to delete (usually by clicking on it), and then press the del key. This will delete everything in the cell, including contents and format.

If you wish to delete particular features of the cell while leaving others intact, select the Clear command from the Edit menu. The “Clear” dialog will appear.

In this dialog, you are given five choices:

All	Erases everything from the cell.
Values	Erases the value from the cell, leaving formulas and formats intact.
Formulas	Erases only a formula from the cell - the result of the formula will be left intact.
Contents	Erases only the contents of the cell, that is values and formulas; anything else you put in this cell will take on the existing formatting.
Format	Erases only the formatting of the cell – the contents will be left intact.
You can also access Clear by right-clicking your mouse, although the options to clear formulas and values are not available in this way.

Select the option you require, and click on the OK button.


Inserting cells, rows and columns

When you are building a spreadsheet, you may discover that you need to insert new cells, columns or rows.

To insert cells, select the cells where you want the new cells to go. Next, select Cells from the Insert menu (or by right clicking your mouse). The "Insert Cells" dialog will appear. Choose whether you want the selected cells and their contents to be shifted down or right. Click on OK to insert the new cells.

To insert a row, click on the row bar to select where you want the inserted row to appear. The whole of the row is selected, rather than a range of cells. Select Cells from the Insert menu (or by right clicking your mouse). The new row will be inserted, and the selected row and all rows beneath the selected row will be shifted one row down. Note that the "Insert Cells" dialog does not appear when inserting rows.

To insert a column, click on the column bar to select where you want the inserted column to appear. The whole of the column is selected, rather than a range of cells. Select Cells from the Insert menu (or by right clicking your mouse). The new column will be inserted, and the selected column and all columns to the right of the selected column will be shifted one column to the right. Note that the "Insert Cells" dialog does not appear when inserting columns.

It is possible to insert several rows or columns at once by selecting the required number of row or column bars.

Note:	Spreadsheet readjusts formulas and range references to compensate for inserted rows and columns.

See also:

Deleting cells, rows and columns


Deleting cells, rows and columns

Note:	When you delete cells, rows or columns, any information they contain is also deleted.

To delete a cell (or cells), select the cell(s) you want to delete. Next, select Delete from the Edit menu (or by right clicking your mouse). The "Delete Cells" dialog will appear. Choose whether you want the deleted cells to be replaced with cells from the same column(s) or row(s). Click on OK to delete the selected cells and shift cells from the same column (up) or row (left).

To delete a row, click on the row bar to select the row you want to delete. The whole of the row is selected, rather than a range. Select Delete from the Edit menu (or by right clicking your mouse). The selected row will be deleted, and all the rows beneath the deleted row will be shifted one row up. Note that the "Delete Cells" dialog does not appear when deleting rows.

To delete a column, click on the column bar to select the column you want to delete. The whole of the column is selected, rather than a range. Select Delete from the Edit menu (or by right clicking your mouse). The selected column will be deleted, and all columns to the right of the deleted column will be shifted one column to the left. Note that the "Delete Cells" dialog does not appear when deleting columns.

It is possible to delete several rows or columns at once by selecting the required number of row or column bars.

Note:	Spreadsheet readjusts formulas and range references to compensate for deleted rows and columns.

See also:

Inserting cells, rows and columns


Comments

It is possible to attach comments to cells in a spreadsheet. When you need to remind yourself what sort of information the cell contains e.g. a formula whose purpose you have forgotten, it is easy to bring up the comment box with your previous note.

To create a comment box, do the following:

1.	Click on the cell to which you want to add the comment.

2.	Right-click your mouse and select Insert Comment (alternatively, select Insert Comment from the Insert menu).

3.	A box is displayed next to the selected cell. Type your comment directly into the comment box.

4.	To close the comment box, click anywhere outside it.

5.	A red mark will appear in the top right-hand corner of any cell that has an attached comment. While you are working in a spreadsheet, you can display the comment boxes attached to the cells by running your mouse pointer over this mark. The box will remain visible till you move the mouse away (see Editing comments for details on how to edit a comment).

Note that a comment box will accept only text. If you attempt, for instance, to insert a function into a comment box, it will be inserted into the cell itself. Likewise, any attempt to change the font characteristics of the comment text will affect the cell contents instead.

See:

Editing comments

Deleting comments


Editing comments

You may decide that you would like to change the text in a comment box. To do this, follow these steps:

1.	Click on the appropriate cell.

2.	Right-click and select Edit Comment from the drop-down menu (alternatively, select Edit Comment from the Insert menu).

3.	The comment box is displayed with its text. The box will remain visible on screen till you click outside it.

4.	Click in the box and edit the text as you would with any other text (with the proviso that you can’t apply any font effect).

5.	Once you are satisfied, click outside the box to close it.

You can edit the comment at any time and as many times as you like.

See:

Comments

Deleting comments


Deleting comments

You can delete a comment box attached to a cell by following these steps:

1.	Click on the appropriate cell.

2.	Right-click and select Delete Comment from the drop-down menu.

The comments box will be deleted, this being indicated by the disappearance of the red mark in the top right-hand corner of the cell.

See:

Comments


Merging and splitting cells

You can merge a range of cells to form one cell and split an already merged cell back into its component cells. In this way it is possible to have a large cell and its contents stand out among otherwise normal cells.

Merge a selected range of cells as follows:

1.	Select the cells you want to merge.

2.	Select Merge Cells from the Format menu.

The cells will be merged to form one big cell. Note that the individual cell borders disappear and that when you click anywhere within the merged cell the whole cell is selected, as you would expect.

If you would like to convert a merged cell back into its component cells, follow these steps:

1.	Select the merged cell you wish to split.

2.	Select Split Cells from the Format menu.

The cell will be split into normal cells.

Note: The merge and split options are useful as aids to designing your spreadsheet’s appearance. Once this is settled, you can then enter the required data. However, if a range of cells that are to be merged already contain data, it is only the data in the top left-hand cell that is displayed in the new merged cell. To retain all of the data in the cells that comprise the range, you should use copy or cut and then paste into the top left cell of the range before merging. When you split a merge cell the data is displayed in the top left cell after the split has been performed.


Styles

The styles option allows you to design and save for future use formats and combinations of formats that you may want to apply to certain cells in your spreadsheets. Styles can save you a lot of time, especially when the combinations of formats are complicated.

Design a new style as follows:

1.	Open up the “Format” dialog box by either selecting Cells from the Format menu or right-clicking and selecting Format Cells from the drop-down menu.

2.	In the box, click on the Styles button at the bottom left corner to open up the Styles Design box.

3.	You can now use any, some or all of the six format options (accessible through the six tabs along the top of the box) to help design the new style. Set the required formats and then click in the top line of the Styles Design box.

4.	Type the name of the style. This will activate the Add button, which you should now click on to place the new style in the box below, in which styles are listed and saved for future use. (Note that the add button will only be activated if the new style has a different name from those already listed.)

5.	Once this is done you can do one of three things: continue to design new styles, as described above, and add them to the list; exit the dialog box altogether without applying a style; or apply a style there and then.

6.	Apply a style by selecting it from the list and clicking on Apply. When you click on OK the style will be applied to the currently selected cell or range of cells. (If you choose not to apply a style, clicking on OK will close the box without affecting the spreadsheet.)

You can go back to the “Format” dialog box at any time to design new styles or apply old ones. Also, you can delete a style by selecting it and clicking on the Remove button.


Using formulas

This is the real heart of the spreadsheet. You can use formulas to add, subtract, multiply, and divide the numbers in your spreadsheet. For example, you can:

•	Add up a column of numbers

•	Automatically compute averages

•	Solve mathematical problems like 3456.87 x (345.2498 + 1224.1 + 45.3)

•	Use any of Ability's built-in functions

With formulas, you can design spreadsheets that help you to explore “what if” possibilities. What happens to your profits if expenses creep up 9%? What if the payments on a new car stretch over 48 months instead of over 24? This “what if” analysis is a vital component of business and financial planning.

The following sections show you how to work with formulas in Ability. You will also need to refer to the sections under Function reference.

See:

What is a formula?

How to enter a formula

Clearing formulas

Automatic totals


What is a formula?

A formula is like a set of instructions for calculating or retrieving values. Here are some examples of formulas:

=341 * .065	Multiplies 341 by .065.
=A9 - C14	Subtracts the value in C14 from the value in A9.
=(A3 + A4)/.125	Adds the values in A3 and A4, then divides by .125.
=SALES - B5	Subtracts the value in B5 from the value in a field named SALES (you’ll read about naming fields later in this chapter).
=TOTAL (D8..D11)	Using one of Ability’s built-in functions, (TOTAL), adds the values in fields D8 to D11.
=Sheet1!A1 * 2	Multiplies the contents of cell A1, in the worksheet called Sheet1, by 2.
See also:

Arithmetic operators

How to enter a formula

Function reference


Arithmetical operators

You can use formulas to add, subtract, multiply and divide numbers. You should use the following standard arithmetic operators in your formulas:

/	division
*	multiplication
+	addition
-	subtraction
Note:	These arithmetic operators are listed in the natural order of priority, from highest (calculated first) to lowest (calculated last). You can use parentheses to group information and to control the order of calculation.

In addition, you can use these logical operators:

%	The percentage sign simply divides a number by 100. For example, 25% means 0.25.
( )	Parentheses group parts of a formula and also control the order in which calculations are performed. For example, A2 * (B3 + C4) tells Spreadsheet to add B3 and C4 first, and then multiply the result by A2. Without the brackets, Ability would multiply A2 by B3 before adding C4.
^	The caret raises a number to a power (exponentiation). For example, 3^2 raises 3 to the power of 2, or squares it. You can also raise numbers to fractional and negative values. For example, 3^0.5 or 3^(1/2) finds the square root of 3 and 10^-2 gives the result of 1/(10*10), which is 0.01.
Ability will automatically strip excess brackets from a formula to display it in its simplest form.

See:

Relational operators

Logical operators

Priority of evaluation of operators

What is a formula?


How to enter a formula

You build a formula by making reference to other cells. For instance, to display the total of two cell values in a third cell, you would put the formula (location of cell 1) + (location of cell 2) in the third cell. When the contents of cell 1 or cell 2 change, cell 3 is updated to reflect the change.

Formulas are entered in the active cell just as you enter text or values. However, you always start a formula with an equals sign (=).

Note:	You can begin a formula with other operators if you like, for example “+” or "-".

Now you’re ready to build the formula. Lets use an example of adding cell A1 and A2 together and putting the result in cell A3. First, click on A3 and type “=”. Next, click on cell A1, type “+” and then click on cell A2. Note that the formula bar will read:

=A1+A2

You can type the entire formula directly if you want. Often though, you’ll find it easier to refer to a cell in a formula by simply clicking on it.

When you have finished the formula, click on the Confirm button [image] on the status bar, or press enter, and the results of the calculation will be inserted into the active cell.

If you make mistakes while you’re building a formula and you want to start over again, press esc or click on the Cancel button [image] on the formula bar. Spreadsheet erases the formula and you can start again by entering an equals sign.

If you want cells to show their formulas instead of the results, open the View menu and click on the Formulas command. When formulas are showing, this command has a check next to it. To show results again, click on the command to remove the check.

See also:

Calculating across worksheets

Copying a formula

Absolute addresses


Copying a formula

You can often save time by copying a formula from one cell to another.

To do this, activate the cell whose formula you wish to copy. Then select Copy either from the toolbar [image] or the Edit menu, or by right clicking your mouse.

Next, click on the cell to which you wish to copy the formula, and select Paste either from the toolbar [image] or the Edit menu, or by right clicking your mouse. The formula is copied into the destination cell.

However, you may notice that the formula has been altered slightly. This is because Spreadsheet automatically adjusts the cell addresses in the formula so that they work logically in the new location.

To give an example, let’s say you entered the formula B8*C8 in cell D8. If you then copied it to cell D9, Spreadsheet would turn it into B9*C9, and in cell D10 it would become B10*C10.

If you do not want the cell addresses to be adjusted in this way, you need to specify them as absolute addresses.

See:

Absolute addresses


Absolute addresses

As described in Copying a formula, Spreadsheet normally adjusts the cell addresses so that the formula works in the new locations. Occasionally, though, you might not want the cell addresses to change. For example, when you copy a formula like B8*C8 to a new location in a spreadsheet and still want the formula to multiply the value in B8 by the value in C8.

In this case you don’t want the formula to adjust the field addresses for the new location. In other words, you want B8 and C8 to be absolute field addresses in the formula.

To include absolute field addresses (as opposed to adjustable addresses) in a formula, precede the field address with a dollar sign ($). The dollar sign tells Spreadsheet to copy the formula exactly, and to leave the field addresses unchanged.

You can use a dollar sign to make:

•	absolute row addresses, e.g. B$8

•	absolute column addresses, e.g. $B8

•	absolute row and column addresses, e.g. $B$8

See also:

What is a formula?


Clearing formulas

Once you have entered a formula, you might decide that you no longer want the cell to contain the formula, but simply to display its result. Consequently the current contents of the cell will be protected from change (when, for instance, one of the other cells referred to in the formula changes).

To turn a current formula result into a static cell value, select the cell whose formula you wish to clear. Then choose the Clear command from the Edit menu and select Formulas. This indicates to Spreadsheet that you want to clear the formula from the cell but without deleting the result. Click on the OK button.

You can clear the formulas in a range by first selecting the range and then repeating the above operation.

This procedure is irreversible (i.e. you cannot later turn the values back into the formulas which created them), so use it with care.

Note:	Clearing formulas also clears any links, turning them into static values.

See also:

How to enter a formula


Automatic totals

Auto-sum is a quick method of summing values in a column, row or range.

To use auto-sum, do one of the following:

•	Click into a cell below the column or to the right of the row that you want to sum and select the Sum button [image]. Ability will show you the range about to be summed - accept this with the Confirm button, or press the Enter key. The SUM function will be pasted in the cell.

•	Select a range of cells and click the Sum button - Ability will fill in row and/or column totals in the appropriate place.

Example 1 - totaling a column of figures

Say the range A1 though A10 contains numbers you want to total. Click into cell A11 (or A12 if you prefer to leave a gap) and click the Sum button. Ability will outline the range and wait for you to press Enter or click the Confirm button.

Example 2 - totaling a column of figures

As an alternative to Example 1, again say the range A1 though A10 contains numbers you want to total. This time select A1 through A10. Click the Sum button - Ability will put the total in cell A11. If instead you select cells A1 through A12, the total will be placed in A12 (that is to say the total will always be placed in the last, or last+1, cell of the selected range).

Example 3 - including row and column totals in one go

Suppose the range A1 through D5 contains values and you want row and column totals and a grand total. First select the cells A1 through D5. Next, click the Sum button. All the totals will appear below and to the right of the range with a grand total in cell E6.

See also:

Calculating current selection


Calculating current selection

Sometimes it is useful to see the results of calculations as you work in a spreadsheet but without having these results inserted into the spreadsheet itself. To this end Ability provides a status bar box called Autocalc, in which calculations can be performed automatically on any range of values that is currently selected.

To activate Autocalc right-click on the right most box on the status bar. A menu will appear with functions for Count, Sum, Average, Max and Min. Click on the function you require. This function will be active until you choose another one. Select the range of cells to which you want to apply the function. The result of the calculation will appear in the Autocalc box. To apply another function right click on the box again and click on another function to make it the active one. To switch off Autocalc click on None.

For example, to perform the full set of automatic calculations on values in the range A1..D10, you should first select the range and then activate Autocalc. Selecting the available functions one by one will tell you how many non-empty cells there are in the range and the sum, average, maximum and minimum of the values. To apply the currently selected function to another range, just select that range.


Turning recalculation on and off

Spreadsheet has an auto-calculation feature. This feature is turned on by default, so Spreadsheet automatically calculates the results of all formulas after each entry you make that affects a formula.

To turn off auto-calculation, select the Auto Calculate command from the Tools menu: the check next to the command in the menu will be removed. Select the Auto Calculate command again to turn it back on.

When auto-calculation is disabled, you need to select the Calculate Now command from the Tools menu to calculate the results of formulas. Manual calculation of this sort is useful if you have many formulas, or are putting in a lot of data, and you do not want to wait for Spreadsheet to recalculate after every entry that affects a formula.

Auto Calculate and Calculate Now operate on the current spreadsheet as a whole and not just on a selected cell or range.


Linking

You can link a field in a Spreadsheet to another field in:

•	the same spreadsheet

•	a different spreadsheet

•	a Write document or Database

Linking is a way to tie information in one field to information contained in another. When you link fields, if you change the value in one field, the value in the other field automatically changes too.

Links can be either one-way or two-way:

•	A one-way link, at its simplest, is just a cell reference; for example, =A1 entered in cell A5 creates a one way link. You can make changes to A1 that will be reflected in A5, but not the other way round.

•	A two-way link is similar but the data can be changed at either end. A logical consequence of this is that two-way links cannot be used in building a formula (unlike one-way links).

One-way and two-way links can work between documents: a spreadsheet can have a one-way or two-way link to another spreadsheet or a Write document (see Linking through fields).

See:

One-way links in a spreadsheet

Two-way links in a spreadsheet


One-way links

The easiest way to set up a one-way link is to type it directly into the destination cell. For example, if A1 contains 123, type the formula

=A1

in any other cell, say E10. The contents of E10 will always be the same as A1. This is identical to a standard spreadsheet formula, so =A1 + 10 is also valid.

As an alternative, you can use a paste special operation to achieve the same result. For example:

1.	Click in cell A1

2.	Select Copy by right-clicking your mouse

3.	Click in cell E10, which is to contain the linking expression

4.	Select Paste Special by right-clicking your mouse

5.	Select Paste Link from the "Paste Special" dialog

See also:

One-way links between documents

Two-way or hot links


Two-way or hot links

The easiest way to create a two-way link is to type it directly. For example, if A1 contains 123, type the formula

! A1

in any other cell, say E10. The contents of E10 will always be the same as A1 and you can edit data at either end of the link. For example, entering 456 in E10 or A1 will cause both E10 and A1 to display the same result - 456.

As an alternative, you can use a paste special operation to achieve the same result. For example:

1.	Click in cell A1

2.	Select Copy by right-clicking your mouse

3.	Click in cell E10, which is to contain the linking expression

4.	Select Paste Special by right-clicking your mouse

5.	Select Paste Hot Link from the "Paste Special" dialog

To convert a one-way link into a two-way link, select the Edit Source button [image] or select the Attributes command from the Format menu, and then check Edit Formula Source. Selecting the Edit Source button again will convert the two-way link back into a one-way link.

See also:

Two-way links between documents

One-way links


Naming cells and ranges

Cells and ranges of cells can be assigned names. After a cell is named, you can use the name instead of the cell address in formulas and other references to that cell.

For example, supplies might be one expense category and utilities another. By giving names to the grand total cells for both supplies and utilities, you can build a formula that looks like this:

=SUPPLIES+UTILITIES

In addition, you might find it easier to remember SUPPLIES than a precise cell address. So, if you want to use the Go To command from the Edit menu to move the cursor to that cell, you can simply enter the name SUPPLIES rather than a cell address.

See:

Assigning names to cells and ranges


Assigning names to cells and ranges

To name a cell, activate it (usually by clicking on it), and then select the Range Name command from the Insert menu. The “Range Name” dialog is displayed.

The cell you have selected is displayed in the Range box. Type in a name for the cell in the Name box. The existing cell and range names are listed below in the Ranges box. Click on the Add button and the cell name will be added to this list. Notice how the addresses of named cells and ranges are given in square brackets after the names. Select Close to save the new name.

If you want to name a range, the steps are the same as for a cell, only make sure you select the range before selecting the Range Name command (although it is possible to type in the range directly).

The name you have chosen will appear in the Cell Index box on the formula bar.

To refer to named cells or ranges in formulas, just use the name wherever you would otherwise use the cell address. For example, if you name the range A1 to A10 as MYLIST, these two formulae are equivalent:

=TOTAL(MYLIST)
=TOTAL(A1..A10)

See also:

Changing or removing names


Changing or removing names

You can easily change or remove a name assigned to a cell or range. Click on the cell to activate it or select the range, then choose the Range Name command from the Insert menu. In the “Range Name” dialog, use the Delete and Delete All buttons to delete as required. You can select and delete the named cells and ranges at will.

To rename a cell or range, click on it in the Ranges box in the Range Name dialog, and then click on the Name box. Delete the name by selecting it and then either using Del on the keyboard or right-clicking on your mouse and selecting Cut. Type in the new name and select the Add button. The new cell or range name will appear in the Ranges box. Note that the old name, which is now inactive, is still there. To erase this, select it and click on the Delete button.

See also:

Assigning names to cells and ranges


Sorting

You can sort the fields of your spreadsheet. Sorting is a way of re-arranging items in a specific order, according to the contents of a key row or column within the selection.

To sort rows and columns in a spreadsheet, first shade the range to be sorted. Then select the Sort command from the Tools menu. The “Sort” dialog box is displayed.

In the Sort box, you can choose between sorting by Rows and sorting by Columns.

In the three boxes below the Sort box, you can specify up to three sort levels. For each sort you should specify which column to sort by when sorting rows, or which row to sort by when sorting columns.

For each sort you can also choose between Ascending order (where normal ascending numerical or alphabetical order is applied) and Descending order (where normal descending order is applied). Normal sort order can be overridden by selecting a Custom List from the Order box. This enables you to sort columns or rows that contain, for example, days of the week or months, or any lists that you may have added to Custom List yourself.

If you apply more than one sort, columns or rows with duplicates in the first sort level are sorted according to the second and third sort levels. If you have not specified second and third sort levels, rows or columns with duplicates in the first sort are left in the order in which they are found.

To sort on more than three levels, use the Sort command several times using the least significant keys first.

Note that when a sort is applied using Normal order, the following criteria apply:

•	numbers are sorted according to their numerical order, words according to their alphabetical order;

•	numbers come before letters (3 before a; 3a before a3);

•	a lower case letter comes before its upper case equivalent (if you want letters to be ordered without regard for case, then check the Ignore Case box by clicking on it).


Searching

You can search a spreadsheet for a cell that contains a particular text string, value, or result of a formula. To begin a search, click the Find button [image] on the toolbar or select the Find command from the Edit menu. The “Find” dialog box is displayed.

In the Find what box, type the text, value or result of a formula you wish to find, or paste it in from the clipboard.

In the Where box select Values if you only want to find text or a value; select Formulas if you only want to find the result of a formula; select Both if you want to find either text/value or a result of a formula. For instance, if you enter 53 in the Find what box, and if cell A10 contains the value 53, while cell A11 contains 53 as the result of a formula, say =21 + 33, then setting Values will find A10 but not A11, setting Formulas will find A11 but not A10, and setting Both will find both A10 and A11.

In the Direction and Scope boxes, choose where you would like to look for the item:

All	Search from the beginning of the spreadsheet or selection
Down	Search from the active cell to the end of the spreadsheet or selection
Up	Search from active cell to the beginning of the spreadsheet or selection
Whole document	Search the whole document
Selection	Search only the selected cells. Use this option to search a specific row or column by first selecting a row or column, or to search a selected area
Select the Match Case checkbox to find only those occurrences with the combination of uppercase and lowercase letters specified in the Find what box.

Select the Match whole words only checkbox to find occurrences that are words by themselves, and not part of a larger word.

When everything is set, click on the Find Next button and Ability will find the next occurrence of the text, value or result of formula, as specified. If no matches are found, a message will tell you that the search was unsuccessful.

See also:

Finding next occurrence


Finding next occurrence

You can repeat the last performed search by selecting the Find Next command from the Edit menu. Since Ability remembers what you last searched for, you do not have to go back into the Find dialog to set up a search.

See:

Searching


Finding a formula

If you want to find a formula, select Formulas from the View menu first. The spreadsheet will display formulas rather than their results. You can then use Find to search for formulas directly. Note that when formulas are displayed they are treated as values for the purposes of Find, so the Where box should be set to Values.


Replacing

To replace a particular text string, value or result of formula with another one, click on the Replace button [image] on the toolbar or select the Replace command from the Edit menu. The “Replace” dialog box is displayed.

This is very similar to the “Find” dialog box. However, it has some additional sections.

In the Find what box, type the text, value or result of a formula you wish to find, or paste it in from the clipboard.

In the Where box select Values if you only want to find and replace text or values; select Formulas if you only want to find and replace the result of a formula; select Both if you want to find and replace either text/values or the result of a formula. For instance, if you enter 53 in the Find what box, and if cell A10 contains the value 53, while cell A11 contains 53 as the result of a formula, say =21 + 33, then setting Values will find A10 but not A11, setting Formulas will find A11 but not A10, and setting Both will find both A10 and A11.

In the Replace with box, type the text, value or formula you want to replace the found item (or paste it in from the clipboard). Note that you can replace a value or text with a formula, or an existing formula with another, and vice versa.

In the Direction and Scope boxes, choose where you would like to look for and replace the text, value or result of a formula:

All	Search from the beginning of the spreadsheet or selection
Down	Search from the active cell to the end of the spreadsheet or selection
Up	Search from active cell to the beginning of the spreadsheet or selection
Whole document	Search the whole document
Selection	Search only the selected cells. Use this option to search a specific row or column by first selecting a row or column, or to search a selected area
Select the Match Case checkbox to find only those occurrences with the combination of uppercase and lowercase letters specified in the Find what box.

Select the Match whole words only checkbox to find occurrences that are words by themselves, and not part of a larger word.

When everything is set, click on the Find Next button and Ability will find the next occurrence of the text, value or result of formula as specified. If no matches are found, a message will tell you that the search was unsuccessful.

Since Find Next finds values one by one you can choose to replace these as you go along by clicking on the Replace button. Ability will then automatically replace the present occurrence and proceed to search for the next one. If you choose not to replace a particular occurrence, click on Find Next instead. Alternatively, click on the Replace All button to replace all occurrences of the find item with the replace item.

See also:

Replace next occurrence


Replace next occurrence

You can repeat the last performed replacement by selecting the Replace Next command from the Edit menu. Since Ability remembers what you last entered in the Replace dialog, you do not have to go back into the dialog to set up a new search and replace.

See:

Replacing


Transposing ranges

You can transpose selected cells in a spreadsheet. Transposing is a way of rotating the cells in a spreadsheet, from horizontal to vertical, with the top row becoming the first column; the second row, the second column; and so on.

Transposing cells in a spreadsheet can be useful when you are including data in a document. For instance, what appears in rows of a spreadsheet might be more appropriately displayed as columns in a report.

To transpose an area of the spreadsheet, select the range of cells you wish to transpose. Then select Cut from the toolbar or the Edit menu, or by right clicking your mouse. You can use the Copy command instead if you wish to keep the original.

Select the range in which you wish to put the transposed values, and then select the Paste Special command from the Edit menu. In the “Paste Special” dialog, click on the checkbox next to Transpose. Click on the Paste button and the spreadsheet will be redrawn with the data transposed.


Transforming ranges

You can transform selected cells in a spreadsheet. Transforming is a way of adding, subtracting, multiplying or dividing two ranges.

To transform an area of the spreadsheet - the target range- first type in and select the range of cells you wish to use to effect the transformation. Then select Copy from the toolbar (or the Edit menu, or by right clicking your mouse).

Next, select the target range and then select the Paste Special command from the Edit menu (or by right clicking your mouse). In the Operation group box, click on the Add, Subtract, Multiply or Divide button. Click on the Paste button and the spreadsheet will be redrawn with the data transformed.

Example 1

The target range contains 1, 2, 3, 4, 5, which you want to multiply by 2. Enter 2 into a spreadsheet cell and Copy this cell. Select the target range and then select Paste Special. Select Multiply and then click on the Paste button. Each of the target cells will be multiplied by 2 to give 2, 4, 6, 8, 10.

Example 2

The target range contains 25, 20, 15, 10, 5, which you want to divide by 5, 4, 3, 2, 1, the transformation range. Type in and select the transformation range. Copy this range and then select the target range. Select Paste Special and click on Divide. Click on the Paste button. The values in the target range will be divided by the corresponding values in the transformation range to give 5, 5, 5, 5, 5.


Page set-up

Whenever you open a new file in Spreadsheet, it is given a default set of margins. To change these defaults for the current spreadsheet, select the Page Setup command from the File menu. The “Page Setup” dialog box is displayed.

Here’s how the different Margin and settings affect your page:

As the diagram above shows, all the measurements are from the edge of the page.

The header and footer sit within the top and bottom margins respectively. So if your top margin is too small, you won't see all your header text. To control the margins for header and footer, select Header & Footer from the Format menu. Enter a value in the From edge box in the header and footer tabs.

If you want to set new default margins for all subsequent spreadsheets then select Options from the Tools menu and click on the Page Margins, Header and Footer tabs. The new default settings will not affect existing spreadsheets or the current one if its settings have already been altered.

Note:	To change the units of measurement used, select the Options command from the Tools menu and click on General. You have a choice between S.I. and Imperial units.


Headers and footers

You can add information, such as a spreadsheet title and page number, that will appear at the top and bottom of every page when you print the spreadsheet. Text that appears at the top of every page is called a header; text at the bottom is called a footer.

To print the header, you need to leave enough space between the top of the spreadsheet and the top of the page so that the header text can fit into it, bearing in mind the fonts you have used. The same applies to footers. The defaults Ability uses will fit most header and footer definitions. You can fine-tune these dimensions in the "Header and Footer" dialog.

See:

Adding a header and footer


Adding a header and footer

To add a header and/or footer, select the Header & Footer command from the Format menu. The “Header & Footer” dialog appears. To create a header click, on the Header button in the dialog; to create a footer, click on the Footer button.

The “Header & Footer” dialog contains three boxes to set up the header and footer information. You can type any text you like into these boxes. Text in the Left box will start printing at the left margin, text in the Right box will end at the right margin, and text in the Center box will appear in the middle.

Use the Font button to set the font for each individual header or footer area.

You can have Ability insert information automatically. Position the cursor in the box where you want the text to appear and then:

Click on this button...	To...	Using this code...
Page Number	Insert the current page number at print time.	&P
File	Insert the name of the spreadsheet file.	&F
Time	Insert the time the spreadsheet was printed.	&T
Date	Insert the date the spreadsheet was printed.	&D
When you select one of these buttons, Ability inserts a code in the dialog box instead of the actual text (since the text changes depending on the current time and date and the number of pages and name of the spreadsheet).

You can delete the codes (or add them by typing them directly) in the same way as you can edit any other text.

If you want to change the default margin between the top and bottom edges of the paper and the header and footer respectively, then click on the From edge box, delete the default setting and type in the new margin in each case as required. This will change the settings for the current spreadsheet.

If you want to set a new default for all spreadsheets then select Options from the Tools menu. Click on the Headers or Footers button in the "Options" dialog. Change the default From Edge setting as required. Note that this changes the setting for all subsequent spreadsheets but not for existing spreadsheets or the current one if it has already been altered in the Headers & Footers dialog under the Format menu.

The First Page box tells Spreadsheet from which page it should begin printing the headers and footers. The default is 1, which you should replace as required.

If you want to create a footer as well as a header, then click on the Footer button and create the footer in the same way as you created the header, before selecting OK to save the new settings.


Freezing sections of a spreadsheet

As you fill more and more rows of a spreadsheet, the top rows move up and out of view. Therefore, if you've put titles in a row, they move up out of view too. Similarly if you've put titles in a column, they move out of view as you fill more and more columns to the right.

You can use the Freeze command to freeze particular rows, columns and ranges. When you freeze a spreadsheet, the frozen part remains visible on the screen as the rest of the spreadsheet moves out of view.

To freeze a row or several rows:

1.	Select the rows using the row bars to the left of the row.

2.	Select the Freeze command from the Window menu.

The frozen rows will move to the top of the screen and stay visible no matter where you are in the spreadsheet. You can freeze columns in the same way by first selecting the columns you want to freeze.

To freeze rows and columns in one go, first select the intersection of the rows and columns to be frozen. For example, suppose you want to freeze the first three rows and columns A and B, follow these steps:

1.	Select the range A1..B3.

2.	Select the Freeze command from the Window menu.

Note that you can exclude rows and/or columns from those that are displayed by excluding them from the selection. In the above example, if the range A2..B3 were selected instead, row 1 would not be visible.

Also note that the effects of freeze will go through to print time as well - you can use it to make sure each page has the same row and/or column repeated at the top or left side.

To turn off the freeze, select the Unfreeze command from the Window menu.

See also:

Splitting a spreadsheet


Splitting a spreadsheet

It is possible to split the spreadsheet screen into vertical or horizontal sections. These sections are different views of the same spreadsheet and not separate spreadsheets. The advantage of this is that when a spreadsheet contains a large amount of data, you can view data that would normally be off-screen in one section, while working with the data of your choice in another section.

To split the screen horizontally, depress and hold down the left mouse button over the bar attached to the vertical scrollbar. A horizontal axis will be displayed. Move this to where you want the screen to be split and release the mouse button.

To split the screen vertically, depress and hold down the left mouse button over the bar attached to the horizontal scrollbar. A vertical axis will be displayed. Move this to where you want the screen to be split and release the mouse button.

Both horizontal and vertical splits can be applied at the same time to the one spreadsheet.

Scroll through the views as required, till you have located the data you want to remain visible on screen while you are working. You can choose to work in any or all of the sections, since anything you add, edit or delete in a particular section applies to the whole spreadsheet.

To clear a horizontal or vertical split, just double-click on one or both of the axes as appropriate.

You can also use the Split command from the Window menu to split the screen.

See also:

Freezing sections of a spreadsheet


Printing

You can print the whole of the document you are working on, or only certain pages. You can also print to a file rather than to paper (perhaps for taking to a professional print bureau).

The following sections explain print set-up, print preview, and how to print a spreadsheet.

See:

Printing the spreadsheet

Print preview

Print set-up


Print preview

Before starting to print, you might like to see how your document is going to look on paper. To do this, click on the Print Preview button [image] on the toolbar or select it from the File menu.

The display switches to print preview mode, in which the document is shown at a reduced size to give you an overall view.


Print set-up

To choose which printer, paper size and orientation you want to use and which bin you want the paper to feed from, select the Print Setup command from the File menu. The “Print Setup” dialog box is displayed for you to select the relevant settings.

In addition, there are scaling and print-to-fit options available. These help you to control the appearance of your spreadsheets when they are printed. Set up the scaling options in the Scaling section as follows:

•	Select the Adjust to button to reduce or enlarge the printed spreadsheet by percentage of the normal size. Set the percentage in the % normal size box by either typing it directly or using the scroller.

•	Select the Fit to button to make the printed spreadsheet fit onto a specified number of pages. Enter the required pages, by width and height, in the accompanying boxes (or use the scrollers). Typically, if you want to fit the spreadsheet onto 1 page, leave the boxes set at 1 for both width and height.

Click on OK once you have selected your print settings.


Printing the spreadsheet

To print the spreadsheet, click on the Print button [image] on the toolbar or select the Print command from the File menu. The “Print” dialog box is displayed.

The active printer is displayed at the top of the dialog box. To change this, click on the arrow at the right of the Name box and scroll through till you find the printer you want.

In the Print range group box, you can specify how much of the spreadsheet you want to print:

All	Prints the entire spreadsheet.

Pages	Prints the range of pages you specify in the from and to boxes.

Selection	Prints the selected cells only.

Check the Print To file box to create a new file on disk, instead of sending the document to the printer. You will be prompted to give the file a name.

Type the number of copies you require in the Number of copies box and leave the Collate box checked if you want the pages to be printed in sequence (rather than all page 1s, then all page 2s, and so on).

If you want to select further properties click on the Properties button. The properties are printer dependent and therefore vary from printer to printer.

When all is set, click on the OK button and the printing process will commence. There will be a pause while the information is sent to the printer, and then the physical printing will begin. Ability will divide your spreadsheet into pages, left to right and top to bottom. If you find, for example, that page 2 and 4 print out with a single column, and you really wanted to fit the entire spreadsheet onto two pages, either reduce the width of one or more columns or reduce the left and right margins (and then check with print preview).

Note:	If you don’t want the grid to print out, select the Grid command from the View menu to turn the grid off prior to printing.


Spell-checker and thesaurus

The Spell-checker and Thesaurus are both available from the Tools menu. They operate in Spreadsheet in the same way as they do in Write. See Spell-checker and thesaurus in Write for more details.

To change the way spell-checker and thesaurus work, select the Options command from the Tools menu and then select Spelling & Thesaurus.

Click on the checkbox next to Always suggest to ensure that the spell-checker always suggests alternative spellings for words it does not recognize.

Click on the checkbox next to Use only main dictionary if you do not want the spell-checker to take account of any words that you might have previously added to the dictionary yourself. You will usually want to leave this box unchecked. The main dictionary is the original dictionary that is supplied with Ability.

The User Dictionary contains any words you might have added and is kept separate from the main dictionary. This can be edited by clicking on the button to the right of the box and then opening up the file from the Open dialog that appears.

Language is the form of English used in the dictionary i.e. American or British English.

You can choose to have automatic spell checking by clicking on the Auto recognize spell errors box. This works as you type and immediately tells you if the dictionary does not recognize a word, by showing the word in the color you have chosen from the Spell error text color box.

See:

Autospell


Autospell

Autospell is a quick way of automatically checking a spreadsheet document for spelling mistakes, as you work, and correcting these on the spot.

To activate Autospell right click on the box second from the right on the status bar. Clicking on Auto Recognize will cause Ability to display in red any words that it does not recognize. To add an unrecognized word to the User Dictionary or to examine suggested corrections, you should first select the cell containing the word and then right click on the Autospell box again. The Add option adds the word to the dictionary. If Ability can find any suggestions, these will be displayed at the top of the Autospell menu. Click on one of these if you want it to replace the word in red. Auto Recognize will stay switched on until you switch it off.

You can also check for spelling mistakes by using the Spelling option. This goes through your document searching for errors and brings up a dialog whenever an error is found. Here you are able to change or ignore the word or add it to the dictionary. Spelling operates only when you select it and is closed down when you exit the dialog.


Default settings

Whenever a new spreadsheet is created, certain defaults are applied. For example, the font might be 9pt Arial and the background color white. Although you can always change these for individual spreadsheets, it's useful to be able to set them for all new spreadsheets as well.

Select the Options command from the Tools menu. The “Options” dialog box is displayed. In most cases, each tab of this dialog works in a similar way to the equivalent command from the Format menu - but as well as changing the current spreadsheet, the global defaults for all new spreadsheets are set.

You can read about the available options and how to set them in the following sections.

•	General − spreadsheet size, units of measurement, trailing/leading formats (see General options)

•	Font − fonts and styles (see Using the menus to select cell font and style)

•	Color (see Selecting the background color)

•	Header & Footer (see Adding a header and footer)

•	Page Margins (see Page setup)

•	Spelling & Thesaurus (see Spell checker and thesaurus)

•	Custom Lists (see Custom lists)


General options

To modify the General page of the Options tab, select the Options command from the Tools menu and then select General.

Set any of the following as defaults for all new spreadsheets (as well as the current one):

•	Spreadsheet size (the maximum setting is 256 columns and 65536 rows)

•	Column width and row height

•	The list of leading and trailing characters e.g. currency symbols

•	Zoom, view grid and view page breaks

•	Unit of measurement, Autocalc and Undo

See also:

Other default settings


Custom lists

To modify the Custom Lists page of the Options tab, select the Options command from the Tools menu and then select Custom Lists.

Ordered lists are, typically, days of the week or months, and are automatically filled in on drag-copy of a cell containing a member item. For example, enter "Jan" in cell A1 and use drag-copy to fill A2 to A12 with the remaining months.

•	In the Custom Lists box, select an ordered list for editing or removal. Use NEW LIST to create new custom lists.

•	The List Entries box displays individual items from the current ordered list. You can edit the items in this list, or add new ones (use Ctrl-Enter to create new line). To save the modifications, click the Modify button.

•	To create a new list, make sure NEW LIST is selected in custom lists, add some items in List Entries and click the Add button.

•	To remove a list select it in the Custom Lists box and click on the Remove button.

See also:

Other default settings


Chart

Chart lets you plot a series of numbers from a spreadsheet. There is a wide range of chart styles to choose from; multiple series can be plotted and 3D views created.

The resulting chart can be saved in a separate window, attached to the spreadsheet, or inserted into the middle of a spreadsheet. A single spreadsheet can contain, or have attached, many different charts.

You can insert charts into Write documents and, since Spreadsheet can display information from database tables, chart information from databases.

See:

Creating a chart

Editing a chart

Deleting a chart

Printing a chart


Creating a chart

To see how to create a chart, look at the following example. The data shows the output of raw materials over four months and includes both row and column titles.

To create a chart from this data:

1.	Select the range A1..E4.

2.	Select Chart from the Insert menu.

3.	Choose On This View to insert the chart into the current spreadsheet or As New View to create a new window that is attached to the spreadsheet (see Attached charts for more details).

4.	Follow the steps in the chart wizard.

The chart wizard will prompt you for the following:

•	Data − in this case, A1..E4 - does the series run down with the data points across, or vice versa? (see Data range )

•	Style − choose from bar chart, pie, x/y plot, and so on. (see Style)

•	Titles – what title text to display

•	Legend – for multiple series, display a legend or not.

•	Series - Series attributes).

Once created, you can modify any of these options using the Chart toolbar and Chart dialog, and further modify the way the axes are drawn and how the background of the chart is drawn.


Style

To change the style of chart, whether you want to display a pie, bar, or other type of chart, right-click over a chart and select the Style command from the menu.

•	Category. Select a chart category - for a brief description of each chart type, see Gallery of chart styles.

•	Style. Pick from the styles offered for a particular category.

•	Border and Shadow. Choose from a range of border styles – none, single line, with or without shadow effects. Note that the border applies to the chart and not the background, which can be set separately.

•	Fill style – color and gradient fill for the body of the chart.

•	Font – used for drawing the values on the axis.


Gallery of chart styles

The different chart styles are arranged under category

Column	Plots a column for each data point in a series.
Bar	Plots a horizontal bar for each data point in a series.
Line	Plots a point for each data point in a series and connects the series with a line.
Pie	Plots the percentage values for the data points in a series, using a pie chart. If there is more than one series, only the first series is charted.
XY (Scatter)	Plots a set of points using a column (or row) of values as the X-values and the values from another column (or row) as the Y-values. See More on scatter charts.
Area	Joins the data points in a series with a line and shades the area under this line.
Stock	Plots the extent of the range of values in a series, where the values plotted are the highest, the lowest and the closing value and opening values. See More on stock charts.
Strata	Cumulative bar charts
Radar	Plots a point for each data point in a series along an edge terminating at a pole, where each data point in the series has its own edge.
Bubble	Plots points similar to XY (Scatter) charts. Bubble charts take a third column that plots the size (by area or radius). To create a Bubble chart, you'll need three columns of data.
Vector	Plots points similar to XY (Scatter) charts. Takes two further, relative, points and plots a line showing scale and direction. To create a vector chart, you’ll need four columns of data.
See:

Style for details on how to change chart style.


Scatter charts: example

The following is an example of a scatter chart.

You wish to chart the performance over 2 hours of two backup systems you have been testing, where the results are as they appear in this table:

Time (Mins)	Backup 1 (%)	Backup 2 (%)
0	0	0
30	23	15
60	59	34
90	84	59
120	98	74
Scatter can be used to plot the backup performance for both systems, measured against the Y-axis, against the time, measured against the X-axis.

See also:

Gallery of chart styles


Hi-Low charts: example
Hi-Low-Open-Close charts: example

Hi-Low-Open-Close charts are very similar to Candle charts. Both take four points for a data series a high, low, opening and closing value. The difference is that a Candle chart displays the open and close values as a rectangle and the High-Low-Open-Close as a step.

The following is an example of a Candle chart.

Assume a record of the temperature in central London, between 12 a.m. and 3 p.m., on July 31st, over a period of 5 years can be tabulated as follows:

Date	High	Low	Open	Close
31/7/93	29	26	28	26
31/7/94	33	30	31	32
31/7/95	29	26	27	29
31/7/96	35	31	34	33
31/7/97	33	24	30	30
The vertical lines in the chart show the extent of the High-Low difference; the horizontal bars show the extent of the Open-Close difference:

Note that if the Close value is less than the Open value, the Open-Close difference is shown in dark blue. If they are equal, a line is drawn.

Hi-Low-Close charts are especially useful for charting the highs and lows of daily share prices, as well as showing their closing value.

Note that in the above example, Chart assumes that the date column contains data, since dates are ultimately represented as numbers by Ability. Therefore make sure you switch First Column (or First Row where appropriate) to Labels. See Vertical and horizontal series for more details.

See also:

Gallery of chart styles


Data range

To change the data range Chart is to plot, or to alter the way the series will run, right-click over a chart and select Data Range from the menu. Set any of the following:

•	Data. This is the range, including series and data point titles. Usually this will be done by first selecting the data before entering the chart wizard. Suppose instead that you need to exclude certain series. For example, in the sheet below, you want only to plot the results for Cobalt. In Data, separate the ranges using commas as follows:

A1..E1, A3..E3

In a similar way, you can plot discontinuous data by entering a series of values like:

B2, D3, E3, E4

•	Series run in. Treat each data column (when set to Columns) or data row (when set to Rows) as a series of values of a variable. See Vertical and horizontal series for more details.

•	First Column contains. In the example below, the first column of the data range (A1..E4) obviously contains labels rather than data. If the first column had contained numbers that you wanted treated as labels rather than as data, this option would need to be set to Labels.

•	First Row contains. Determine whether the first row contains labels or data in the same way as for First Column.


Vertical and horizontal series

Using the following example data:

with Series Run In set at Rows, there are three series, namely Iron, Cobalt and Copper, each of which has a set of associated measurements or data points. The data points are the measurements for April, May, June and July. The different series are assigned different colors, which are maintained for each data point that is plotted throughout the chart. The data points are plotted measured against the scale along the left-hand axis (the Y-axis):

To plot the chart with the months as series and the values for the metals as the data points, switch the Series Run In setting to Columns. There will now be four series, namely April, May, June and July, each with three associated data points:

See also:

Data range and series display


Editing a chart

Use the Chart toolbar to edit your chart. The toolbar becomes visible whenever you select a chart (i.e. click on it).

Use the toolbar to arrange the elements (legends, titles, chart area) within the chart. Select the element you want to resize or reposition from the chart drop-down box and drag and drop the element anywhere on the chart or resize using the handles.

You can go to any of the pages on the Chart dialog directly – right-click over the chart and select any of the menu options to open the dialog at the appropriate page.

See also:

Deleting a chart

Resizing a chart


Attached charts

When creating a chart, you are given the choice whether to insert the chart into the current spreadsheet window (On This View) or into another window that is connected to the current spreadsheet (As New View) - see Creating a chart.

If you choose On This View, the chart will be embedded in the current spreadsheet and will remain visible in the spreadsheet as you work. You can resize and move an embedded chart (see Resizing and moving an embedded chart).

If you choose As New View, the chart will be occupy a new worksheet.


Resizing and moving an embedded chart

Once a chart is embedded in a spreadsheet you can edit, resize or move it.

Move the chart by holding down the left-hand mouse button when the cross cursor is showing over the body of the chart. A chart outline will be displayed. Drag this to where you want the chart to appear and release the mouse button. The chart will be displayed in its new position.

Resize the chart by clicking on it, pointing at one of the handles and holding down the left-hand mouse button when the cursor changes to a double-headed arrow. An up-down arrow is displayed along the top and bottom borders; a left-right arrow along the left and right borders; and a diagonal arrow at the corners. You can use these to increase or decrease the height, width, or height and width together of the chart. Drag the border till it is the size you require and then release the mouse button. The chart will be displayed with its new dimensions.


Deleting a chart from a spreadsheet

To delete an embedded chart from a spreadsheet, click on the chart once to select it and then press the Del key.

To delete a separate chart sheet, right-click over the chart name (on the worksheet selector at the bottom of the screen) and select Delete.


Printing a chart

Printing is simply a case of printing the worksheet containing the chart. Select the Print button [image] from the toolbar or the Print command from the File menu.