11 Entering and editing data in cells

This chapter, and a number of subsequent chapters, show you how to use the spreadsheet facilities of Fireworkz for entering, editing, analysing and managing data.

This chapter explains how to enter and edit data, both text and numbers, to build up your worksheet.

It includes sections on:

Cell types: number and text cells

Fireworkz worksheets are made up out of cells, which may contain text or data. To make it easier for Fireworkz to handle information, each cell is assigned a type, either text or number (numeric). The type of the cell governs the way the cell handles the data it contains. If you follow the instructions in the section below, you should not have to manipulate cell types directly. But on occasions you may find that the default decisions Fireworkz makes about cell types are not what you want.

When a cell is created, its type is first set according to the cell type which has been chosen in the current style used in the worksheet. This will usually result in numeric cells, but may be different in some templates. The Sheet template creates number cells by default.

You can assign a type to an empty cell easily by selecting it and applying a style which affects the type of the cell. You can also change the type of cells which contain text or data, although this has a major impact on their behaviour, by using the commands on the Make sub-menu.

Usually you will want to apply a type to a whole column or row. For example, you might want to apply the Text style to the leftmost column in the worksheet, to ensure that all labels come out as text.

Dealing with cell types

If you place the caret in a cell and start typing, Fireworkz will mark the cell as a text cell and interpret its contents as text. It will do this regardless of the information which you enter, so if you type numbers straight on to the worksheet you will not be able to add them up unless you mark the cells as number cells.

Text cells

Text and Number cells behave differently in several important ways.

Behaviour of text cells

Number cells

Cells which contain the data for your calculations must be marked as number cells.

With number cells:

You will usually find that you need a mixture of both types of cell within your worksheet. Text cells are useful for entering headings for worksheets, and for columns holding data such as people's names, while obviously you will need Number cells to hold data and calculations. New cells in documents based on spreadsheet templates that use the BaseSheet style will default to being Number cells.

Cell type problem areas

There are a few occasions when Fireworkz may not make a sensible decision about the contents of a cell.

The most notable of these is numeric or alphanumeric information which you wish to store as text rather than numbers. Telephone numbers and British postcodes which you wish to enter in a database are good examples. Typing a telephone number is likely to have its leading zero stripped off, and Fireworkz may attempt to recognise postcodes as cell references, because of their letter/number format. If you are entering such details in cells, the easiest ways to avoid this problem are:

Changing cell types

Once you have created a cell, you can change its type using the commands on the Make sub-menu, accessed from the Edit menu. Follow the procedure outlined below.

Changing cell type

To change the type of a cell:

  1. Select the cell or group of cells whose type you want to change.
  2. Display the Make sub-menu from the Edit menu.
  3. Choose Make number to convert text cells to number cells, or Make text to convert number cells to text cells.

Moving from cell to cell

Whether you are entering text or numeric data, it will appear in the current cell. Usually when you start typing the the caret will appear in the current cell. If the current cell is a number cell, and Edit in cell is not enabled, then the caret will appear in the formula editing line at the top of the window. Whatever you type in the formula line will then be placed in the current cell.

The current cell is highlighted with a different cell background and border. The precise appearance of the current cell is governed by the Current cell style in the style list. If you want to change this, see the earlier chapter on Styles for details on changing styles. The corresponding column and row headings are also highlighted.

Combine the process of moving to another cell with confirming the contents of the current one using Autoentry

You can move around the worksheet from cell to cell in several ways:

Autoentry

When you enter numbers or formulae on the formula line, you ca combine the act of clicking the Tick button and moving to an adjacent cell for entry by pressing one of the following keys.

Entering text and data in number cells

To make or edit number cells, you type into the formula line at the top of the window. Tick tool button When you have finished typing or editing, you click the Tick button and the result appears in the cell.

When you are typing in the formula line, you will see that the pointer changes to a caret while it is pointing to the formula line and to a crosshair pointer when it points at cells in the worksheet. Also note that some buttons in the toolbar are greyed out.

Entering numbers

Much of your work with Fireworkz will consist of entering numbers which you will then manipulate and analyse with formulae.

Numbers which appear in the spreadsheet because you have typed them in are called constants. Other numbers which might appear on your page are the displayed results of formulae.

Number formats

Fireworkz always stores numbers in a plain format. Other information about the number, such as currency symbols, percentage symbols and so on, is applied to the number as a style.

When you enter a number on the formula line, you must not enter any characters other than the numerals and, if relevant, the decimal point. Formatting information is applied to the number when it is displayed on the page as part of its style.

Entering dates

Dates are effectively a special kind of number. You must enter dates in the following format, in day, month, year order:

15.2.1993

or

31.12.93

The critical part is that the date must have two full stops (but no spaces) separating its three parts.

See the section on formatting dates and times.

Entering strings

Text in number cells can be entered as a string. Strings must be enclosed between double quotes. They can contain spaces, but not control characters.

"As long as a piece of string"

If you need to include a double quote mark within a string, you must enter it twice:

"She said ""Hello!"""

It is much easier to enter text strings as plain text in a text cell. When you need to use the text in a formula, you can enter its cell reference; this automatically creates a string out of the contents of the cell.

To enter text in a cell, place the caret within the cell and type the text. If the text was not entered via the formula line, it will be interpreted as text automatically.

Entering cell references

The ability to cross-reference data, and to use the result of one calculation as the basis for another, is fundamental to spreadsheets. By using cell references your data can stay live throughout a chain of calculations, so that if you change the original data for the first calculation, all further calculations referring to it are automatically updated without any further effort from you.

You can type a cell reference into a number cell. When evaluating the formula, Fireworkz uses the value of the referenced cell, which may itself be the result of a formula. If you were working with a large worksheet and wanted to create a summary section, this is one way to do it. If the value of the referenced cell changes, the value in the cell containing the reference is updated too.

You should always make a reference to a cell rather than typing its value into a second cell as a constant. If the value changes (perhaps because you discovered an error earlier in your worksheet) the reference will be updated, and the new figure automatically passed on to further calculations which need it.

To enter a cell reference:

  1. Make sure that the caret is in the formula line, not on the worksheet. You cannot enter cell references into text cells.
  2. Point at the body of the worksheet. You will see that the cursor turns into a crosshair pointer.
  3. When you are pointing at the cell you want to reference, click on it.
  4. The cell reference will be entered on the formula line. It will appear in a different format to numbers which you type in directly: it will have a light grey background. As long as the reference has a light grey background, it is selected and can be deleted in one operation, because Fireworkz treats it as a single character.

Editing cell references

When you type another character, enter a function or operator, or enter another cell reference, the grey highlight disappears. The cell reference is no longer subject to special treatment.

You can now edit the elements of the cell reference individually, for example, replacing the row or column part of it.

Confirming data entry

When you enter data via the formula line, you must confirm it before it is displayed on your worksheet. There are several ways to do this:

Editing number cell contents

The available options for editing data you have typed in the formula line are rather similar to those in a word processor.

If you need to replace some of your entry:

  1. Click on the cell you want to change.
  2. Click in the formula line or press F2 to start editing.
  3. Click the caret into position or move the caret with the left or right arrow keys.
  4. Use Delete or Backspace to remove the characters you don't want, and enter the new characters.
  5. Tick tool button Click the Tick button on the toolbar (or press Return↵) to enter the new formula.

Clearing cells

To clear cells, totally removing their contents but leaving their style and formatting information intact:

  1. Select the cells you want to clear.
  2. Choose Make Blank from the Edit – Make menu.
  3. The cells will be cleared, but remain in the worksheet so that you can enter new data.

You can also simply select the information on the formula line and type over it.

Copying data from cell to cell

You can use the Copy, Cut and Paste commands to move data around your spreadsheet.

To copy data:

  1. Select the data cells whose contents you want to copy.
  2. Copy tool button Click the Copy tool button on the toolbar (or choose Copy from the Edit menu, or press Ctrl-C).
  3. Click at the starting position of where you want to paste the data, i.e. the cell which will now be the top left of the range.
  4. Paste tool button Click the Paste tool button on the toolbar (or choose Paste from the Edit menu, or press Ctrl-V).
  5. A copy of the data will appear in the new cells.

To move data:

  1. Select the data cells whose contents you want to move.
  2. Cut tool button Click the Cut tool button on the toolbar (or choose Cut from the Edit menu, or press Ctrl-X).
  3. Click at the starting position of where you want to paste the data, i.e. the cell which will now be the top left of the range.
  4. Paste tool button Click the Paste tool button on the toolbar (or choose Paste from the Edit menu, or press Ctrl-V).
  5. The data will be removed from the old cells and will appear in the new cells.

Filling data

If you want to copy an item of data across a row or down a column, you can use the Fill buttons to make this easier. You might want to do this if, for example, you were working on your accounts and needed to enter a fixed payment into every month.

To copy data by filling:

  1. Enter the data into the first cell in the row or column.
  2. Select that cell and the rest of the cells in the row or column where you want the data to appear.
  3. Fill down tool button If you are filling down a column, click the Fill down button on the toolbar.
    If you are filling across a row, click the Fill right button on the toolbar.
  4. Your data will be copied into the selected cells.

For information on the way pasting and filling affect cell references in formulae, see Cell references.

Formatting numbers

The concept of formatting is applied to two different facets of cells and their contents in Fireworkz. You may be familiar with the idea of formatting, from work with other programs, and understand it to mean the choice of font, text style and alignment applied to an area of text, and the way it is laid out on the page.

This all applies in Fireworkz. In addition, numbers can also be formatted to display in different ways. There are many ways to express the same number. For example the number 50 can be expressed in all of the following ways:

505.00e+01
50.005000%
£50.00L

The appropriate format will usually depend on the context; if you are performing financial calculations, it will be useful to have numbers expressed in a currency format such as a leading pound sign, and perhaps negative numbers in brackets. For mathematical work, you may want to see a greater degree of precision in the form of extra decimal places, or even to express numbers in a scientific format.

Choosing a number format and applying it to a cell

Fireworkz contains a number of standard number formats which are easily applied to any numbers in spreadsheets. Because number formats are an aspect of style, number formats are manipulated through the Style editor.

Formatting a number

To change the display format of a number, you need to apply a style to the cell containing it.

  1. Select the cell or range of cells to which you wish to apply the format.
  2. Style tool button Click the Style tool button on the toolbar. A dialogue box will appear listing the available styles.
  3. Click on the style you want to use to select it from the list.
  4. Click the Apply button. The numbers in the selected cells will be redisplayed in the new style.

More about number formats and styles based on them

The Number formats and styles chapter contains a full reference to the wide range of number formats built into Fireworkz and ways in which you can develop custom styles based upon them.

Cell references

Once you have entered your data into number cells, you can build formulae which operate upon the data to produce results.

You don't need to type in your data all over again to include it in the formulae; instead, you enter a reference to the cell containing the data. When the formula is calculated, Fireworkz looks for the number stored in that cell, and uses that in the calculation. This allows you to build up chains of calculations, each operating on the result of the previous calculation; if you change some of the original data, all the calculations will be automatically updated to reflect the new starting data.

Single cell references

References to single cells are always given in the format column_letterrow_number, for example A1, D13, and so on. You can use either upper-case or lower-case letters to create cell references. Even if you have altered the column or row heading format, cell references must still be entered in this standard form.

To enter a cell reference:

Ranges

Some functions require a contiguous group of cells to work on, which may extend over one or more rows and columns. Such ranges are described by giving the references of the top left and bottom right cells included in the range; for example B1:D5.

There are two ways to enter ranges:

Unlike many other spreadsheets which require you to enter ranges as two cell references separated by a colon, Fireworkz does not require use of the colon, so could accept B1D5 as equivalent to the above example range. All examples in the User Guide use the colon separator for clarity.

Absolute references

Usually when you copy a formula using Fill down or Fill right Fireworkz updates the cell references within the formula as it moves down the column or across the row. If you do not want this to happen, but want to refer to a particular cell explicitly, and always to refer to that cell, in copies of a formula, you must use an absolute reference. You may also make half of the reference absolute, so that different cells within the same column or row are always referenced.

Absolute references are expressed by prefixing the row and column address with dollar signs. For example:

$A$1  Always refers to the same cell, A1

$A1   Always refers to column A but the row number will be updated when the formula is copied

A$1   Always refers to row 1 but the column is updated when the formula is copied.

Entering absolute references

You can set up absolute references in the same way as ordinary, relative references, by clicking on cells when you are working on the formula line.

Note that absolute references only make a difference when copying cells from one part of the worksheet to another using Paste, Fill right or Fill down. There is no difference at all between absolute and relative references when inserting or deleting rows and columns in the worksheet. In this case if there is a reference to a cell which moves the reference will always change to reflect the new position.

External references

You need not restrict your references to cells in the current worksheet. You can incorporate a reference to a cell in another worksheet; this is called an external reference. You might want to do this if, for example, you had a worksheet for each months accounts and wanted to transfer the totals to the annual return.

To make an external reference:

  1. Open the source worksheet containing the data you want to reference.
  2. Select the cell in your worksheet where you want the reference to appear.
  3. Click in the formula line.
  4. Click on the cell in the external worksheet. The file and path names will be enclosed in square brackets.

For example:

[May]E55

will place the value of cell E55 from the file May into the current cell in your worksheet.

Ideally you should ensure that all files between which a set of external references are made are stored in the same directory. This makes the management process much easier, because Fireworkz need only store the leafname in the cell reference, and it is much easier for you to copy sets of linked documents if they are in the same directory.

Managing external references

When you create an external reference by including a reference and its filename in a worksheet, you inextricably link the two documents for as long as the reference remains in the file.

Whenever you open the document containing the reference (the dependent document), the referenced document (the supporting document) will also be opened. In the following case, opening worksheet A also opens B, C and D.

If you are creating dependencies between several documents (e.g. referring to each of 12 monthly worksheets in an annual roundup), and the documents themselves are each very large, you may want to consider the memory implications of this, particularly if you are running several programs in addition to Fireworkz.

If you open a supporting document, the dependent document is not automatically opened. If you edit the supporting document without opening the dependent document, however, references may not be updated and the link may become confused, for example if you insert extra rows or columns into the supporting document thus changing all the cell positions.

If all the documents are open, references are updated automatically, so if a referenced cell is moved within the supporting document, the reference to it in the dependent document is automatically updated, and the dependent document is modified, so you will be prompted to save it.

External references and directories

Fireworkz tries wherever possible to use only the leafname (the actual name of the file) rather than the full pathname when providing external references. You may refer to files in other directories, but if you do you will need to take care when you copy the file to another disc that you take all the supporting documents with it, and you may need to manually update the filenames in the external references. Keeping all the files in the same directory enables Fireworkz to use only the leafname, and so you can copy the files to other discs or directories very easily.

Using names for external references

External references can become unwieldy and hard to use. It is always much simpler to use the Names capability of Fireworkz to set up external references. A name is defined for a particular reference, which may or may not be an external reference. When you want to use the external reference, simply enter the name you have defined — you can do this automatically from the names option in the Function selector — and choose the name you want from the list.

Sorting your data

Some functions require that data is sorted into ascending or descending order before it is processed. You may also want to sort a list of names into alphabetical order.

The Sort command provides the easiest means of doing this. It will sort rows by ascending or descending numerical or alphabetical order.

If you want to sort data that is organised along rows, not down columns, you will need to use the TRANSPOSE() spreadsheet function to swap the data around.

To sort your data:

  1. Select the rows you want to sort. Make sure you select all the columns which you want to move when sorting.
  2. Sort tool button Click the Sort tool button on the toolbar (or choose Sort from the Extra menu). The Sort dialogue box will appear.
  3. Enter the name of the column containing the data which you wish to sort by.
  4. If you wish to sort the data further, enter the names of other columns.
  5. If you wish to sort in descending order (i.e. highest number first) select that option.
  6. Click Sort. Your selected range will be sorted.