English
Deutsch
CzechDocumentation
Working with Worksheets and Workbooks
Sierra Chart has very advanced built-in spreadsheets. Sierra Chart worksheets are Excel compatible spreadsheets that can be used for custom studies, systems and alerts by using the Worksheet Study/System/Alert Study or can contain detailed continuously updating quotes. See the Getting Quotes documentation page. Worksheets are contained within workbooks. A workbook is a single window in Sierra Chart that can be saved to a file and opened. They are separate from chartbooks which are collections of chart windows. The worksheets in a workbook can be selected with the sheet tabs at the bottom of the workbook window.
The workbooks and worksheets in Sierra Chart are Excel compatible.
Workbook and Worksheet Menu Commands
Creating and Opening Workbooks
Activating and Viewing Worksheets
Cell and Range References and External References
Workbook and Worksheet Menu Commands
The program menus contain commands for workbooks and worksheets. Information for these menu commands can be found in the File Menu, Edit Menu and Worksheet Menu documentation pages.
Creating and Opening Workbooks
To create a new workbook select the New Workbook command from the File menu.
To open an existing workbook: Select Open Workbook from the File menu. The Open window will display. Select a workbook file. Click the Open button. The Files of Type drop down list box in the Open window lists the different types of workbook files you can open. Select the type of file you want to open from the list if you want to open a type other than the default.
New and existing workbooks for custom studies on a chart are automatically created or opened by the Worksheet Study/System/Alert study.
Worksheet Cells
Worksheets contain rows and columns of cells. Worksheet cells can contain formulas, numbers, dates, times, text, logical values (TRUE, FALSE), or error values.
The active cell is the one where the worksheet cursor is located.
Activating and Viewing Worksheets
To view a worksheet, left click with the pointer the worksheet tab at the bottom of the workbook.
If the worksheet is visible, then it can be made active if it is not already by left clicking with the pointer anywhere on it. If the worksheet is not active, the scroll bars will not be visible.
Entering Data
To select a cell: Activate the worksheet if it is not active by left clicking anywhere on it with your pointer. Use the keyboard navigation keys to move to a cell or select a cell by pointing to it with the pointer and left clicking.
To enter data into a worksheet, select the cell and type directly into it or type into the edit bar at the top of the worksheet. Unlike with formulas and functions, dates, times, and text should not be enclosed quotation marks when entered directly into cells.
Filling Cells
If you have a value or formula in a cell and you wish to fill other cells with this same data, then highlight the cell or cells that you wish to copy. Copy the content by select Worksheet >> Copy. Select the range of cells that you want to be filled in with the contents of the originaly selected cell, and paste the conents by selecting Worksheet >> Paste. If you are copying a formula, then the cell references will be automatically adjusted unless they are absolute references.
It is also possible to use the Worksheet >> Fill commands.
Formulas
Formulas can be entered into worksheet cells to perform calculations.
A formula starts with an = sign and can contain Worksheet Functions, numbers, user defined names, cell references, text strings (text needs to be in quotation marks), dates and times, logical values (TRUE (1),FALSE (0) ), and Operators. There is full Excel compatibility and virtually any formula that can be used in Excel can be used in Sierra Chart worksheets.
When entering numbers and formulas, keep in mind to use the proper delimiters in your numbers and formulas, according to your Regional Setting. For more information, see the Regional Setting section.
Examples:
= A2 + 10 If A2 has the value 5, then this formula returns 15.
= SUM(B2:B10) This formula uses the SUM function. The sum of cells B2 through B10 is returned.
= C10 Returns the value in cell C10.
If text is found in a formula when a number is expected, then the text is converted to a number. If a number is found in a formula when text is expected, then the number is converted to text.
Dates and/or times when entered into cells are stored as Serial DateTime Values, which are numeric values. Arithmetic can be used on these numeric values.
Operators
| + | Addition |
| - | Subtraction |
| / | Division |
| * | Multiplication |
| % | Percentage |
| ^ | Exponent |
| & | Concatenation |
| = | Equal to |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal to |
| <= | Less than or equal to |
| <> | Not equal to |
Use parentheses, ( ), around expressions to change order of precedence.
Regional Setting
Depending upon your Regional Setting, set under Global Settings >> Workbook Settings, the format of displayed numbers and the format of formulas may be different. The regional setting will primarily affect delimiters. In English the typical delimiter is a ',', however your region may use something different. Select Global Settings >> Workbook Settings and select the proper region you want to use and make sure that your formulas use the proper characters coresponding to your regional setting. If you change the regional setting, you will need to restart Sierra Chart for the changes to take effect.
For example the SUM function would be implemented as follows
English: =SUM(A1, B2, C3). English(United States) Regional Setting
German: =SUM(A1; B2; C3). German(Germany) Regional Setting
Cell and Range Selection
Click and hold the left pointer button and drag through a range of cells to select them. Or press the SHIFT key and use the keyboard navigation keys to select a range of cells.
A single cell is also considered a range or a selection. It is range or selection of just one cell.
To select entire column(s) or row(s), left click with the pointer the row or column headers. Headers are along the left and top of the worksheet.
Use the ENTER, SHIFT + ENTER, TAB, and SHIFT + TAB keys to move the active cell in a range selection.
CTRL + Left Click with pointer on a worksheet tab allows multiple selection of worksheets.
Cell Formatting
To format cells, select a cell, a range of cells, column(s), or row(s), then select one of the commands on the Worksheet menu that formats cells.
Cell and Range References and External References
A reference refers to the contents of a cell or a range of cells. References are used in formulas. The reference specifies a cell by referring to the row and column position of the cell. The reference uses the row and column headings in the worksheet. Example: A1 refers to the cell at row 1 and column A. To reference a range of cells, use a colon (:) between the cell reference at the top left of the range and the cell reference at the bottom right of the range.
Examples:
B1 refers to cell at column B and row 1.
A1:B4 refers to the range that includes all cells in columns A and B of rows 1, 2, 3, and 4.
A1:A10 refers to the range that includes all cells in column A of rows 1 to 10.
There are relative and absolute cell references. Relative references refer to a cell using offsets that the cell being referenced is to the cell that contains the reference. When the cell with the reference is copied, the reference is adjusted to refer to a new cell with the same offsets as the reference that was copied has to the cell or range it references. Absolute references refer to a cell at an exact location. When the cell with the reference is copied, the reference will not change. Absolute references are specified with a $ in front of the row and/or column that is to be absolute.
References can be part absolute and part relative.
Examples:
B1 Relative reference to cell B1. It if is in cell A1 and copied to A2, then it will refer to B2 since the reference refers to one cell to the right in the same row.
$C$4 Absolute reference cell C4.
$D1 Absolute column reference and relative row reference to cell D1.
A$2 Relative column reference and absolute row reference to cell A2.
Multiple references can be separated by a comma to refer to separate cells and ranges in the same reference. Example: A1:C1, A10:C10, F2
To enter a reference in a formula automatically, go to the point in the formula where the reference is to be entered. With the pointing device (mouse), select the cell or range you want to reference. The reference is automatically placed in the formula as a relative reference.
Cells can be referenced in other worksheets and workbooks. To reference worksheet in the same workbook, use the following syntax. Remember that references are used in formulas and formulas begin with =.
Syntax: SheetName!CellReference
Example: Sheet1!A1
To reference a worksheet in a different workbook, use the following syntax. Workbooks must be loaded for the reference to work.
Syntax: [WorkBookTitle]SheetName!CellReference. Keep in mind that the WorkBookTitle may or may not have a file extension depending upon whether it's been saved. You need to use the exact WorkBookTitle. If there is a file extension shown, then use the extension. If there is no extension shown, then do not use the extension. However, you really should save a workbook before referring to it since the extension is normally part of the name.
Examples:
[Book1]Sheet1!A1:B2
[MyWorkbookName.scwbf]Sheet1!A1:B3
Keyboard Commands
| Enter | Accepts the entry when in edit mode. |
| Shift + Enter | Accepts the entry when in edit mode and moves active cell up. |
| Tab | Accepts the entry when in edit mode and moves the active cell to the right. |
| Shift +Tab | Accepts the entry when in edit mode and moves the active cell to the left. |
| Del | Deletes the selection. |
| Escape | Cancel edit mode. |
| Up Arrow | Moves up one row. |
| Down Arrow | Moves down one row. |
| Left Arrow | Moves left one column. |
| Right Arrow | Moves right one column. |
| Ctrl+Up/Down/Left/Right | Moves by more than one column. |
| Page Up | Moves up one screen. |
| Page Down | Moves down one screen. |
| Ctrl+Page Up | Moves left one screen. |
| Ctrl+Page Down | Moves right one screen. |
| Home | Moves to first column on current row. |
| End | Moves to last column with data on current row. |
| Ctrl+Home | Moves to A1. |
| Ctrl+End | Moves to last row and column with data. |
| Scroll Lock | Moves worksheet without changing current selection. |
| Shift+Up/Down/Left/Right | Extends the current selection. |
Worksheet Errors
| #DIV/0! | Division by 0. |
| #N/A | No value is available. |
| #NAME? | Unrecognized name. |
| #NULL! | Null intersection. |
| #NUM! | Number problem. |
| #REF! | Invalid reference. |
| #VALUE! | Incorrect argument type. |
Copying and Moving Ranges
A selection can be copied to adjacent cells with the copy handle. The adjacent cells that are copied to can be a larger range than the one copying from. The copy handle is at the lower right corner of a selection. Left click the handle with the mouse pointer and drag it to highlight the cells you want to copy the selection to. Release the pointer button. Note: In the newer Workbook/Worksheet component in Sierra Chart, this feature is not currently available.
To copy a range: Select a range. Place the pointer on the border of the selection to copy. Press and hold the Ctrl key. Left click the pointer and hold it to drag the selection. Release the pointer button at the location where you want to copy the selection to.
To move a range: Select a range. Place the pointer on the border of the selection to move. Left click the pointer and hold it to drag the selection. Release the pointer button at the location where you want to move the selection to.
Rename a Sheet
Double click on the worksheet tab at the bottom of the workbook window to display the Sheet Name window.
