Open topic with navigation
Cells can contain two types of information: constant values and formulas. Constant values are numbers (including dates and times), logical values, error values, and text. Formulas are groups of constant values, cell references, names, functions, and operators that result in a new value when calculated or evaluated.
ENTERING CONSTANT VALUES
Numbers. Numeric entries can contain numeric characters (e.g., 1, 2, 3, 4, 5, 6, 7, 8, 9, and 0) and the special characters (e.g., +, -, (, ), /, $, %, ., E, and e).
The worksheet numeric entries as fractions. If the fraction contains a leading integer (e.g., 1 1/3) it can be entered directly. If there is no leading integer, the fraction should be preceded by a zero (e.g., 0 2/3).
Numbers larger than the cell in which they are entered are converted to scientific notation unless a specific format is applied.
Use the Format_Column_Auto-Fit Width menu function to automatically set the column width to the correct size for all data in the column.
Dates and Times. Dates and times are automatically recognized by the worksheet. They are entered in the cell as values and automatically formatted. The following date and time formats are automatically recognized:
|9:55 PM||h:mm AM/PM|
|9:55:33 PM||h:mm:ss AM/PM|
|3/15/94 21:55||m/d/yy h:mm *|
* That these formats are locale-dependent. For example, in the United Kingdom, 15/3/94 would be recognized as the date format d/m/yy. Your locale is your country setting in the Windows control panel.
Text. Text is any set of characters that the worksheet does not recognize as a number, date, or time. Text that is wider than a cell ordinarily spills over into the cell immediately to the right. You can specify that text should wrap within the cell by enabling word wrap in your alignment format settings.
Logical and Error Values. Logical and error values are not normally entered directly in cells; they are usually the result of a formula. However, entering these values can be useful for testing formulas. The logical values that can be entered are TRUE and FALSE. The error values that can be entered are #N/A, #VALUE!, #REF!, NULL!, #DIV/0!, #NUM!, and #NAME?.
ENTERING MULTI-LINE DATA
A single cell can contain as many as nine lines or 16 kilobytes of data. When entering the data, new lines of data are specified within the cell by entering carriage return/line feeds. When entering data, press F2 when editing a cell. The Cell Text dialog box is displayed in which you can enter the cell data. To enter a line feed, press RETURN. Click the OK button to accept the entry and return to normal worksheet editing.
A formula is a string containing numbers, operators, worksheet functions, cell references, and names. A formula can contain as many as 1024 characters. When entering a formula you must start with an equal sign (=). The worksheet then recognizes this entry as a formula.
Numbers in formulas can be followed by a percent sign (%). Numbers with trailing percent signs are treated as percentages (e.g., 100% is evaluated as 1).
If text is encountered when a number is expected, the text is converted to a number. For example, the formula 1 + "3" returns 4, because "3" is converted to a number. If the text cannot be converted to a valid number (e.g., 1 + "Text"), #VALUE! Is returned. Likewise, if a number is encountered when text is expected, the number is converted to text. The formula "The number is "&3 converts to the text string "The number is 3".
The value TRUE always converts to 1; FALSE converts to 0. If a number is encountered when a logical value is expected, a zero is converted to FALSE. All other numbers are converted to TRUE. If text is encountered when a logical value is expected, "TRUE" is converted to TRUE; "FALSE" is converted to FALSE. All other text returns #VALUE!.
Dates and times are recognized and converted to their serial values. For example, "10/10/94" - "10/1/94" equals 9.
If you want to display formulas as text rather than the values they produce, use the menu function Format_Sheet Settings to set the Show Formulas property of the worksheet.
USING FORMULA OPERATORS
When creating formulas, the worksheet provides a set of operators for specifying the type of calculation or evaluation to be performed on the formula data. The following table lists the formula operators:
|Operator type||Operator||Operator Description|
|>=||Greater than or equal to|
|<=||Less then or equal to|
|<>||Not equal to|
|Reference||:, .., .||Range - produces a reference that includes all the cells|
|between the two references (e.g., A1:A5 includes cells A1 and|
|A5 and all cells in between).|
|,||Union - produces one reference that includes the two|
|references (e.g., A1:A10,C1:C10).|
When combining operators in a formula, the worksheet uses a specific order of precedence to calculate the formula. The following table lists the order of precedence for formula operators.
|:, .., .||Range|
|-||Negation (single operand)|
|* and /||Multiplication and Division|
|+ and -||Addition and Subtraction|
|= < > <= >= <>||Comparison|
Operators of like precedence are evaluated left to right. Parentheses should be used when it is necessary to change the order of evaluation. The following example illustrates how the result of a formula can be altered by adding parentheses to change the order of precedence.
As illustrated in the previous table, the multiplication operator (*) has higher precedence than the addition operator (+). It is evaluated first unless parentheses are used to force the addition to take place first.
UNDERSTANDING CELL REFERENCES
A reference identifies a cell by referring to the row and column coordinates of the cell. References are based on the row and column headings. For example, A1 refers to the cell at the intersection of row 1 and column A. References can be used in formulas to access data from a worksheet.
A range of cells is specified by placing a colon (:) between two cell references. For example, the reference A1:C3 refers to the range anchored by cells A1 and C3. The range includes all cells in columns A, B, and C of rows 1, 2, and 3.
Absolute and Relative References
There are two types of cell references: relative and absolute.
References can be part absolute and part relative. These are called mixed references. The following table lists the reference types.
|A1||Relative reference pointing to cell A1.|
|$A$1||Absolute reference pointing to cell A1.|
|$A1||Absolute column reference, relative row reference pointing to cell A1.|
|A$1||Relative column reference, absolute row reference pointing to cell A1.|
The reference operators can be used to specify multiple ranges in the same reference. For example, A1:C1,A10:C10 specifies the three cells A1, B1, and C1 and the three cells A10, B10, and C10. The formula =SUM(A1:C1,A10:C10) adds the values in all six cells.
References to Other Worksheets
You can reference cells in other worksheets in the same or different workbooks.
To reference a worksheet in the same workbook, use the following syntax:
To reference a worksheet in a different workbook, use the following syntax:
Workbooks are referenced by the value of their Title property, and must be loaded for the reference to work.
References to Multiple Worksheets
You can also refer to multiple worksheets, or ranges in multiple worksheets. The following example references cell A1 in Sheet1 and cell A1 in Sheet2.
Important. Worksheets must be referenced in index order. For example, the reference to the sheet indexed 1 must come before the reference to the sheet indexed 2. Remember that the worksheet index is usually different that the sheet name that appears on the sheet tab. Worksheets are indexed from left to right, beginning with 1.
The following syntax references the range A1 to B2 in Sheet1 and the range A1 to B2 in Sheet2.
You can also reference multiple worksheet ranges in different workbooks by referencing the workbook name at the beginning of the syntax.
References can point to cells in other workbooks. This type of reference is called an external reference. An external reference is created by placing a workbook name in brackets, followed by the worksheet name and an exclamation point, and finally a cell reference. The following table shows examples of external references.
|[Sales]Sheet1!A1||Relative reference pointing to cell A1 in the first|
|worksheet of a workbook titled Sales.|
|[FY91]Sheet2!$A$1||Absolute reference pointing to cell A1 in the second|
|worksheet of a workbook titled FY91.|
|[Q1]Sheet1:Sheet2!$A1||Absolute column reference, relative row reference|
|pointing to cell A1 in the first and second worksheets|
|in a workbook titled Q1.|
|[Store1]Sheet1:Sheet4!A1:F1||Relative row and column reference, pointing to the|
|range A1 to F1 in a workbook titled Store1.|
Automatically Entering Cell References
Cell references can be automatically entered as you enter a formula.
To automatically enter a cell reference:
When you enter a cell reference in this manner, the workbook assumes it is a relative reference.
UNDERSTANDING WORKSHEET ERRORS
When a formula cannot be properly calculated, an error is returned in the cell. The following table lists the errors that can be generated.
|#ARRAY_FORMULA!||The workbook read an Excel file that contained an array formula.|
|Since this feature is not supported in the StatsDirect workbook, this|
|error value is placed in the cell that used to contain the array formula.|
|#DIV/0!||Divide by zero. May be caused by a reference to a blank cell or|
|a cell containing zero.|
|#N/A||No value is available. May be caused by inappropriate values in|
|the formula or a reference to a cell containing the #N/A value.|
|#NAME?||Name is not recognized. May be caused by a user defined name|
|that is not defined.|
|#NULL!||Null intersection. An intersection of two ranges was defined that|
|does not intersect.|
|#NUM!||Number problem. May be caused by inappropriate numbers in|
|functions, an iteration that cannot solve for a value, or a formula|
|that results in a number too large or too small to represent.|
|#REF!||Reference error. May be caused by referring to a cell that was|
|#VALUE!||Wrong argument type. May be caused by entering text where a|
|number was expected, or supplying a range to an operator or|
|function that was expecting a single value.|
Most worksheet functions are composed of keywords and arguments. Every worksheet function contains a keyword, but not all functions require arguments.
The keyword identifies the function and tells the worksheet what type of calculation or evaluation is performed. Each function keyword is unique.
Arguments provide the data for the function to calculate or evaluate. The arguments for a function immediately follow the function keyword and are enclosed in parentheses.
When entering functions in a worksheet, all functions are preceded by an equal sign (=). The leading equal sign tells the worksheet that the following information is to be evaluated or calculated.
The function keyword follows the equal sign. It can be entered in lowercase or uppercase characters. After the function is entered, the worksheet records the function keyword in uppercase characters, regardless of how it was entered.
If a function requires multiple arguments, the arguments are separated by commas. Some functions contain optional arguments. If you omit an optional argument, a default value is assumed for the argument.
Functions that do not require arguments still require a set of parentheses following the function keyword.
A function can be used as an argument for another function. When a function is used in this manner, you are nesting functions. The nested function must return the appropriate type of data for the function in which it is nested. You must also provide the necessary arguments for the nested function.
In the following example, the AVE RAGE function is used as an argument for the SUM function. In this case, AVE RAGE is nested in SUM.
=SUM(5.23, 6.82, AVERAGE(2.45, 5.62, 7.74), 8.95, 9.01)
The arguments for a function can be:
Each argument requires a specific type of data. Refer to Worksheet Functions, to determine the type of data required for the function you are entering.
For most arguments, you can substitute a cell or range reference for the data required by an argument. For example, if an argument requires a number, you can substitute a reference to a cell that contains a number. The number in the referenced cell is used in the calculation of the function. The data in the referenced cell must be appropriate for the argument for which it is used.
If the worksheet function you enter contains syntax errors, the workbook does not allow the function to be entered. You must correct the errors before proceeding with other tasks.
The workbook calculates cells in natural order. In natural order calculation, formulas are calculated in such a way that all dependencies are calculated before their dependents. This ensures that the formula results are always correct.
When a worksheet is edited, the workbook first adjusts formula references so they point to the correct cells. Then, the workbook determines the natural order of the formulas.
When a change is made to a cell, the formulas are recalculated to keep all worksheets in the workbook current, ensuring that data is always valid.
Setting Automatic Recalculation
Normally, automatic recalculation is enabled, which means the worksheet is recalculated each time a cell is changed and system processing is idle. For moderately sized worksheets, recalculation operations happen in a fraction of a second. But for large worksheets or situations where many cells are changed by code, this reorganization and recalculation process can slow system processing.
In these situations, you may disable automatic recalculation while your code operates on the worksheet. After the operation is completed, you may enable automatic recalculation to update the worksheet. Automatic recalculation can be disabled using the Format_Workbook Options menu function.
Setting Minimal Recalculation
Minimal recalculation means that, when the workbook needs to recalculate the formulas on the worksheet, it only recalculates cells that refer to cells that have changed. Cells and references to cells that have not changed are left alone. This will often cause a dramatic improvement in recalculation speed. By default, minimal recalculation is on.
Whether or not minimal recalculation happens is determined by the setting of the AutoRecalc property. When AutoRecalc is set to True, minimal recalc will be called whenever the worksheet changes. If AutoRecalc is off, minimal recalc will be called when you press F9.
The CALL, COLUMNS, INDEX, INDIRECT, NOW, OFFSET, RAND, ROWS, and TODAY worksheet functions must be re-evaluated during every recalc to ensure accurate results. Formulas that use these functions are re-evaluated during every recalc operation regardless of whether minimal recalc is on or off.
You may encounter an unusual instance where minimal recalculation slows worksheet processing speed. In these cases, you may want to turn minimal recalculation off.
Use the Format_Workbook Options menu function to set the MinimalRecalc property.
Solving Circular References
In some circumstances, a formula refers to its own cell, either directly or indirectly. This is called a circular reference. To solve a formula that contains a circular reference, iteration must be used. Iteration is the process of repeatedly calculating a worksheet until a specific condition is met.
The workbook supports iteration that you can configure using the menu Format_Workbook Options. The iteration properties allow you to specify the maximum number of iterations and the maximum change between iterations. The iteration continues until one of those two conditions is met.
The following example involves a circular reference:
Suppose a small business has 10,000 shares of stock owned by four shareholders. A fifth shareholder enters. In return for his investment, you give him 10 percent of the company. How many more shares will the company have to issue to give the new investor 10% of the company?
Old shares = 10,000
Total shares = old shares + new shares
New shares = total shares * 10%
Old shares = 10, 000
Total shares = 11,111
New shares = 1,111
LIMITING DATA ENTRY
Denying Access to Cells
To deny access to one, a few, or all of the cells in a worksheet, lock the cells and enable worksheet protection. Locked cells may be selected, but they cannot be changed, moved, resized, or deleted. You may protect the entire worksheet or just certain cells in the worksheet.
Protecting the worksheet is a two-step process: First you choose which cells you want to be locked. Then you enable worksheet protection. Locking the cells has no effect until you enable the protection. By default, worksheet cells are locked and protection is disabled.
To protect the worksheet:
Select the cells you want to lock or unlock.
Use the menu item Format_Cells and click the Protection tab.
Check the Locked check box to lock the selected cells. Uncheck the Locked check box to unlock the selected cells. Click OK.
Use the menu item Format_Sheet Settings. A check by the Protection menu item means that protection is enabled and locked cells cannot be entered and changed.
Working With Locked Cells
By default, when a user selects a locked cell and tries to enter data, the worksheet will beep and display the message "Locked cells cannot be modified." You can turn off the error message so that the worksheet just beeps in this situation. To turn off the error message, uncheck the Show "Locked Cells" Error Message in the Edit tab under the Format_Sheet Settings menu function.
Also, when a locked cell is selected, the ENTER, SHIFT-ENTER, TAB, and SHIFT-TAB keys advance the selection to the next unlocked cell.
Denying Access to Row and Column Headings
You may keep the user from changing row and column heading text in a worksheet.
To deny access to row and column headings:
Select the worksheet(s) you want to restrict access to headings in.
Choose Format_Sheet Settings and click the Edit tab.
Uncheck the Edit Headings box and click OK.
Restricting Cell Data to Certain Values
You can restrict the user to entering only specific values in a cell by specifying a validation rule for the cell. A validation rule consists of a formula to test, and text to display if the validation fails. If the formula returns True, the value is entered. If the formula returns a text string, the string is displayed and the value is not entered. If the formula returns False, the value is not entered and the validation text is displayed in an error dialog box.
For example, you can limit the range of values a user can enter in a cell by creating a rule that fails if the user enters a number under 100 and displays the message "Enter a value greater than 100."
You can use relative references in validation rules. These references are considered to be relative to the active cell. This allows a validation rule to be properly applied to an entire range.
Note that the workbook's validation rules are incompatible with Microsoft Excel’s validation rules.
To create a validation rule:
The Validation Formula
The validation formula must also be a worksheet formula that evaluates to True or False. Following are a number of examples of validation formulas.
SUM(A6:A7) > A5
AND(A6>1, A6 <100)
OR(ISLOGICAL (A7), A7=1,A7=0)
Denying Entry of Formulas in a Worksheet
You may prevent users from entering formulas in a worksheet while still allowing them to enter constant values.
To deny users the ability to enter formulas:
Restricting the Use of Certain Keys
You may enable and disable a variety of navigation and data entry keys for particular worksheets.
To disable the use of certain keys:
Select the worksheets to which you want to apply the changes.
COPYING, MOVING, AND PASTING SELECTIONS
You can copy, move, and paste selections interactively. Selections in a worksheet contain many attributes such as:
Formulas. Establish the value in a cell. Formulas are displayed in the formula bar when the cell is active.
Values. What displays in the cell.
Formats. How the cells and values are shown, such as red text applied to a selection.
Note. The workbook maintains its own internal clipboard and also supports text on the Windows clipboard. The internal clipboard is more flexible than the Windows clipboard. The internal clipboard retains formulas and allows cell references to be adjusted when cells are pasted. The Windows clipboard only holds text and formatting; cell references are not maintained by the Windows clipboard.
Using Dragging to Move, Copy, and Paste Selections
If you are moving or copying a selection within the same worksheet, dragging or dragging-and-dropping are the most simple techniques to use.
Copying a Selection Using Dragging
You can copy a selection to the right or down by dragging the copy handle of a selection. The copy handle is the small knob in the lower right corner of a selection. When you copy data using the copy handle, the pointer changes to a small crosshair.
You can also copy a selected range in a worksheet. If an autofill list is defined for the data in the range, the data is copied using the autofill list.
Moving or Copying a Selection Using Drag-and-Drop
The workbook's drag-and-drop functionality is different from this type of functionality in other applications.
To move or copy a selection using drag-and-drop:
Select the cells that contain the data you want to move or copy.
Position the pointer on the border of the selection.
When the pointer changes to an arrow,
- to move the selection, hold down the mouse button and drag-and-drop the selection to the new location.
- to copy the selection, hold down the CTRL key and drag-and-drop the selection to the new location.
When the pointer is at the appropriate location, release the mouse button. The original selection is not moved if you hold down the CTRL key. However, the original selection is moved to the new location if do not hold down the CTRL key.
Using Drag-and-Drop with Other Applications
You can easily drag text and Windows metafile pictures onto a worksheet from other Windows applications, such as Excel. The mouse pointer changes to indicate that the data cannot be placed in the selected location. For example, if you select data and drag it to the Desktop, the pointer indicates that the data cannot be dropped onto the desktop.
To drag information from another application and drop it into a worksheet:
Arrange the application windows so that the source and destination documents are open and visible. You must be able to see the information you want to drag and the StatsDirect worksheet you want to place it on.
Select the information you want to move or copy.
To move the information, point to the selected information, and then hold down the mouse button. When the pointer appears, drag the pointer to the new location, and then release the mouse button. The pointer changes to indicate a move.
To copy the information, hold down the CTRL key, point to the selected information, and then hold down the mouse button. When the pointer appears, drag the pointer to the new location, and then release both the mouse button and the CTRL key. The pointer changes to indicate a copy.
To drag a selection from a worksheet to another application:
Arrange the application windows so that the source and destination documents are open and visible. You must be able to see the data on the StatsDirect worksheet.
Select the information you want to move or copy.
To move the selection, point to it, and then hold down the mouse button.
When the pointer appears, drag the pointer to the new location, and then release the mouse button. The pointer changes to indicate a move.
To copy the selection, hold down the CTRL key, point to the selection, and then hold down the mouse button. When the pointer appears, drag the pointer to the new location, and then release both the mouse button and the CTRL key. The pointer changes to indicate a copy.
Using Menu Commands to Move, Copy, and Paste Selections
If you are moving or copying a selection for a longer distance, such as another workbook, worksheet, or application you might want to use menu commands. Also, in some instances you might not want to paste all cell attributes to the other location. The StatsDirect workbook allows you to choose whether to paste formulas, values, or formats. Furthermore, the workbook allows you to copy formatting from a cell or range and apply that formatting to another cell or range.
To move or copy a selection using menu commands:
Select the cells that you want to cut.
Select Edit_Cut to move a selection. Alternatively, select Edit_Copy to copy a selection.
Select the upper left cell where you want to move or paste the selection and select Edit_Paste. This pastes all cell attributes.
To paste individual cell attributes:
Select the upper left cell of where you want to paste the selection and select Edit_Paste Special.
Press OK when you have selected the attributes to paste.
Finding and Replacing Data
The StatsDirect workbook enables you to find and replace data within a worksheet in formulas and values. You may invoke the Find dialog and a separate Replace dialog within a worksheet.
You may search by row or by column; you may search for values or formulas or both; and you may specify search options like matching case.
To find data in a worksheet:
Choose Edit_Find or issue the Ctrl F keyboard shortcut. The Find dialog will appear.
Enter the data you want to search for in the Find What dialog box.
Choose any special search options you want, then click the Find Next button.
The workbook will select the first cell that contains the search string you chose. If it finds no instances of the string, it will display the message "Cannot find matching data."
If you want to replace the data, click Replace.
Click Close when you finish.
To replace data in a worksheet using the Workbook Designer:
Choose Edit_Replace or issue the Ctrl H keyboard shortcut. The Replace dialog will appear.
Enter the data you want StatsDirect to search for in the Find What dialog box.
Enter the data you want to replace that data with in the Replace With box. Choose any special search options you want, then click Find Next.
StatsDirect will select the first cell that contains the search string you chose. If it finds no instances of the string, it will display the message "Cannot find matching data."
Click Replace if you want to replace the data, or click the Find Next button to search for the next instance of the search string.
Click Close when you finish.
Inserting Cells, Rows, and Columns
To insert cells:
Select a range of cells in the size and place of the new cells that you want to insert.
Select Insert_Cells to display the Insert dialog box.
Select an Insert option button to specify a direction that you want the surrounding rows, columns, or cells to shift.
To insert rows:
Select the same number of columns directly below where you want to add the new row or rows.
To insert columns:
Select the same number of columns directly to the right of where you want to add the new column or columns.
When new cells are inserted, cell references in formulas are adjusted so the formulas
Clearing and Deleting Cells, Rows, and Columns
Deleting cells removes the cells and shifts the surrounding data to fill the space. Clearing cells leaves the cells but deletes the data.
To delete cells:
To clear cells:
Copyright © 2000-2016 StatsDirect Limited, all rights reserved. Download a free trial here.
Copyright © 2000-2016 StatsDirect Limited, all rights reserved. Download a free trial here.