Working with Data

A form sheet contains cells, which values can be specified explicitly, get on setting up an attribute or table area from source or calculate by formula.

Data Entry and Editing Cell Contents

To enter or edit data in a cell, select a cell on a report sheet and enter necessary data.

Copying and Pasting Data

Deleting Data

To delete cell or cell range contents, select the required cell or cell range and press the DELETE key.

When data range filtering is used on a data entry form sheet or in table area, data deletion is applied only to visible rows/columns.

Using Formulas

A formula starts with the = sign and can include constants, statements, and cell references.

A constant is a ready (non-calculated) value. Examples of constants: date 10.10.2015, number 210 and text "Quarterly profit". Expression or its value are not constants.

If a formula does not contain references to other cells (for example, a formula looks as =30+70+110), value in such cell is changed only after manual changing of the formula.

Statements are operations that should be executed with formula operands. There is a standard order of calculation execution but it can be changed by means of brackets.

Arithmetic and comparison statements can be used to create formulas.

Arithmetic statements are used to execute arithmetic operations: addition, subtraction, multiplication. Operations are executed with numbers. The following arithmetic statements are used:

Sign	Description
+	Addition.
-	Subtraction.
*	Multiplication.
/	Division.

Comparison statements are used to compare two values. The comparison result is a logical value: True or False.

Sign	Description
=	Equal.
<	Less.
>	Greater.
<=	Less or equal.
>=	Greater or equal.
<>	Greater or less (not equal).

Special features of using references to cells of report sheet in a formula:

Single cell. A sheet cell address is specified, for example, B8.
Column. To use all cells of the same column in a formula, specify column name via a colon, for example, B:B. To use all cells of several columns in a formula, specify their names via a colon, for example, A:C.
Row. To use all cells of the same row in a formula, specify row number via a colon, for example, 5:5. To use all cells of several rows in a formula, specify their numbers via a colon, for example, 1:5.
Cell range. To use a cell range in a formula, first specify address of the upper left cell, where the range starts, next, after a colon, specify address of the bottom right cell where the range ends, for example:

C7:D15

Where:

C7. The address of the upper left cell.
D15. The address of the lower right cell.

Combined cell range. To describe an area that contains several not intersecting ranges (including single cells), specify addresses of rectangular areas and individual cells, separating them with a semicolon (;). For example:

C7:D15;A1:B4;B8

Where:

C7:D15 and A1:B4. Cell ranges.
B8. Single cell.

Reference to cells of other sheet. To use a cell (or a cell range) located on another sheet, specify sheet name in quotes and with an exclamation mark before the range.

A link to a sheet is inserted following these steps:

The sheet name is inserted into cursor position when the user moves to another sheet. If a term was selected, sheet name is inserted into selected term position. No objects are selected on the active sheet.
Select the required range on the sheet - references to a range are inserted into cursor position. The range is selected with a dashed frame.
Changing the active sheet adjusts sheet name in the output term, the reference to range is not adjusted, it corresponds to selection on each of the activated sheets. Changing borders of the range selected on the active sheet results in adjusting the reference to this range in the output term.
When the active sheets changes, the cursor remains within the cell editor or in the formula bar, depending on its position before this event.
If the formula is entered with a cell editor, and another sheet is activated or a term is inserted on entering the formula, when the user goes back to the current sheet, the editor is restored, and the cursor is placed after the added term.
After finishing to enter formula, the sheet, to which the formula was entered, becomes active.

Formula editing stops when another sheet is activated if:

The cursor is positioned after any term.
The cursor is within a term.
Several characters that are not a term are selected: a cell address (A0) and a sheet name with the exclamation mark (“Sheet1”!).

When other sheets are activated during formula entry, the current sheet is highlighted. References to other sheets in formula or on a sheet are not highlighted in color. Examples of references:

="Sheet1"!A3

Value of the A3 cell from the sheet 1.

=Sum("Sheet2"!A:A)

Sum of values of the A column from the sheet 2.

=Sum("Sheet3"!C31;A31;E31)

Sum of combined range: value of the C31 cell is taken from the sheet 3, values of the A31 and E31 cells are taken from the current sheet.

=Sum("Sheet2"!B31:D31)

Sum of value range from the sheet 2.

The selected cell or range is highlighted using a dotted color frame. When entering statements or signs separating function parameters, a cell is fixed and highlighted using solid color frame. The next mouse click specifies the address of another cell. Use the mouse to move, increase or decrease a highlighted cell or range, the formula address automatically changes. To extend or reduce a range, point the cursor to cell corner and after it looks as a double-headed arrow drag the range border in the required direction.

Input or edited cell (range) address and the appropriate cell (range) borders have the same color.

Two reference styles can be used when creating references:

A1. When the A1 style is used, columns are marked with letters and rows are marked with numbers.
R1C1. When the R1C1 style is used, rows and columns are marked with numbers, and cell references in formulas are set by means of shifting from the current cell. For example, the cell R[-1]C[2] refers to the cell one row to the top and two columns to the right of the current cell.

It is available to use mixed styles in formulas as it does not affect calculation results.

The example:

=$A$0+R[0]C[-1]

On copying this link the value of a cell located in the row where the link has been copied to and the column on the left from the cell where the link has been copied to is added to the value of A0 cell.

NOTE. References to columns and rows are always created using A1 style. During export the links with R1C1 style are transformed into A1 style.

On creating formulas the following reference types can be used:

Relative reference in a formula, for example, A1, is based on the position of the cell that contains the formula, and the cell, to which the formula refers. On changing position of the cell that contains the formula, the reference is changed too. On copying or filling a formula along rows and columns the reference is automatically corrected.
Absolute reference of a cell in a formula, for example, $A$1, always refers to the cell located on a certain sheet. On changing position of the cell that contains the formula, the absolute reference does not change. On copying or filling the formula along rows and columns the absolute reference is not corrected.
Mixed reference contains either an absolute column and a relative row, or an absolute row and a relative column. On changing position of the cell that contains the formula, the relative reference is changed and the absolute reference is not changed. On copying or filling the formula along rows and columns the relative reference is automatically corrected and the absolute reference is not corrected.

Depending on reference style the way of indicating absolute and relative references differs:

Style A1. When the A1 style is used to specify absolute references, the & character is used. To fix the reference in cell or formula bar:

Enter the $ character before the reference part that should be fixed.
Press F4: single-clicking fixes the cell, double-clicking fixes the row, triple-clicking fixes the column, and four times clicking makes the reference relative again. The cell, next to which the cursor is located, is fixed. Select the entire formula to fix all contained references.

=$A1

When this reference is copied, the A column is always fixed. Relative part of the reference, that is. The row number will change according to the row, according to which the reference will be copied (=$A2, =$A3, and so on).

Style R1C1. When the R1C1 style is used to specify absolute references, specify the row/column number after R or C. Relative numbers of rows and columns are indicated in square brackets and correspond to shift relative to the current cell. To fix the reference in cell or formula bar:

Specify row/column numbers in square brackets.
Press F4: single-clicking fixes the cell, double-clicking fixes the row, triple-clicking fixes the column, and four times clicking makes the reference relative again. The cell, next to which the cursor is located, is fixed. Select the entire formula to fix all contained references.

When this reference is copied, the relative number does not change, but in fact it corresponds to another row or column.

=R[-3]C0

When copied, this reference has the same appearance, but on calculation the reference corresponds to the cell positioned in the first column three rows above the formula cell.

Features of reference use

The absolute reference =$A$0 created by means of the A1 style is similar to the =R0C0 reference created by means of the R1C1 style.
The =RC1 reference created by means of the R1C1 style is similar to the cell reference located in the RC column and in the first row created by means of the A1 style.

Operations with Formulas

A formula can be created in the following ways:

Directly in the cell. To do this, execute the following operations in the cell to edit:

Enter the equality sign. Skip this step if the cell already contains this sign.
Create a formula using numbers, statements, links to cells and functions.
Press the ENTER key to save and calculate the formula.

In formula bar.
Using function wizard. To do this, click the Insert Function button in the formula bar or press SHIFT+F3.
By copying the cell with the created formula. Execute the following operations:

Copy the cell with formula.
Enable the Paste Special option. Select the Cell Formulas checkbox.
Click the OK button.

To cancel entering formula, press the ESC key.