Workbook and Worksheet Basics

 

A workbook consists of worksheets that are divided into rows and columns forming a matrix of cells. You may enter data or formulae into cells. The active cell is highlighted with a rectangular border, you can move the active cell by clicking on another cell with the mouse or by using the cursor keys (arrows).

 

TABS

Tabs are similar to the tabs that stick up in a filing cabinet to help you identify and get to a folder. StatsDirect uses tabs towards the top of the main StatsDirect window to identify workbooks, if you click on one of these tabs then the workbook it identifies is selected as the active workbook. The tab of an active workbook shows bold back text on a while background whereas the tab of an inactive workbook has a grey background. When you hover over the tab of a workbook that has been saved to disk, the path and file name of the workbook will be displayed.

 

Workbooks also use tabs within themselves, this time towards the bottom of the workbook. Workbook tabs identify and enable you to select worksheets within workbooks. If you double click on the tab of a worksheet then you can re-name it.

 

SELECTING WORKSHEETS

Usually, you do most of your work in one worksheet at a time. This is called the active worksheet. When you have multiple worksheets in a workbook, you can use the mouse to click on a worksheet’s tab to make it the active sheet. The tab is highlighted and moves on top of the other tabs.

 

You can save time and effort by performing some tasks on several sheets at once. For example, if you want all three worksheets in your workbook to have the same title information, you can select all three worksheets and enter the titles on the active worksheet. The titles are automatically entered in the corresponding cells in the other selected worksheets as well.

 

To select multiple worksheets in the Workbook Designer, use one of the following key/mouse combinations, depending on whether you want to select adjacent or non-adjacent worksheets:

  • CTRL+Click on sheet tab Selects or deselects non-adjacent sheets. Any other selected worksheets remain selected.
  • SHIFT+Click on sheet tab Selects all adjacent worksheets between the active worksheet and the worksheet you clicked on. All other worksheets are deselected.

 

INSERTING WORKSHEETS

You can insert one or more sheets at a time and at any point in the sheet tab list. The number and position of the inserted sheets depends on number and position of the selected sheets in the workbook.

  1. Select the worksheet immediately to the right of where you want to insert the new worksheets.
  2. Select as many worksheets to the right of that worksheet as the number of worksheets you want to insert. For example, to insert two worksheets, select two worksheets.
  3. Select menu item Insert_Sheet.

 

DELETING WORKSHEETS

You can delete one or more worksheets from the sheet index list by selecting the sheet(s) you want to delete and then selecting the menu item Edit_Delete Sheet.

 

RENAMING WORKSHEETS AND WORKBOOKS

StatsDirect provides a default name for each worksheet (Sheet 1, 2, etc.). You can change these names to more meaningfully describe the sheets’ contents. For example, the sheet names in the following illustration are more descriptive than the worksheets’ default names. To name a worksheet either double click on the tab of that worksheet or select that worksheet and then select the 'Name' box from via the menu item Format_Sheet Settings. The whole workbook can be renamed by right clicking on its upper tab and using the naming dialog.

 

SETTING DISPLAY OPTIONS FOR WORKSHEETS

You can display or hide different parts of worksheets. You may hide the column and row headings and the gridlines. You may display the worksheet at regular size, shrunk down, or enlarged. You may also choose how many rows and columns you want to display. The default number of rows and columns displayed is the maximum (65,536 rows by 256 columns). You may display fewer. Rows and columns beyond the display limits you specify will not be seen, but they can hold data and formulas. Finally, you may specify how you want to display formulas. Note that StatsDirect does not permit you to hide zero values.

 

When formulas are displayed, the worksheet automatically doubles the width of columns to accommodate the wider text. When the Formulas option is later unselected, column widths return to their original settings.

 

To change display options for worksheets:

  1. Select the worksheet(s) whose display settings you want to change.
  2. Select the menu item Format_Sheet Settings and click the View tab.
  3. Select the worksheet and data viewing options you want by selecting or unselecting the options.

 

NAVIGATING THROUGH WORKSHEETS

You can navigate within individual worksheets using mouse actions or keyboard commands.

  • Left-Click moves the active cell to the pointer position.
  • Right-Click in the worksheet brings up descriptive statistics for the column clicked on.
  • Wheel-click or middle click invokes or cancels scrolling mode, which allows you to scroll by moving the mouse over the worksheet.
  • Left-Click in Row or Column Headings selects entire row or column.
  • Left-Click in Top Left Corner selects entire sheet.
  • Left Double-Click in Top Left Corner, Row Headings, Column Headings, or Worksheet tabs displays a dialog box that allows you to enter a label for the top left corner or the column or row heading, or a new name for the worksheet that was double clicked.
  • Left-Click and Drag selects a range. If other ranges are selected, the previously selected ranges are unselected.
  • CTRL + Left-Click and Drag selects a range. If other ranges are selected they remain selected.
  • SHIFT + Left-Click and Drag Extends the current selection.
  • CTRL + SHIFT Click on Row Headings, Column Headings, or Top Left Corner selects the row headings, column headings, or top left corner of the sheet.
  • Drag a Selection's Copy Handle copies the selection into the newly selected area.
  • Drag a Selection’s Border moves the selection to a new location.
  • ENTER accepts the current entry. When a range is selected, and if the Enter Moves Down box is checked in Format_Sheet Settings, enter accepts the current entry and moves active cell vertically to next cell in selection.
  • SHIFT + ENTER accepts the current entry. When a range is selected, and if the Enter Moves Down box is checked in Format_Sheet Settings, enter accepts the current entry and moves active cell vertically to previous cell in selection.
  • TAB accepts the current entry and moves the active cell horizontally to right.
  • SHIFT +TAB accepts the current entry and moves the active cell horizontally to left.
  • F9 recalculates workbook.
  • DEL may clear current selection depending on the setting of the Allow Delete in Format_Sheet Settings.
  • Escape cancels current data entry or editing operation.
  • Up Arrow moves active cell up one row.
  • Down Arrow moves active cell down one row.
  • Left Arrow moves active cell left one column.
  • Right Arrow moves active cell right one column.
  • CTRL Up/Down/Left/Right moves to the next range of cells containing data. If there is no additional data in the direction in which you are moving, moves to the edge of the worksheet.
  • Page Up moves up one screen.
  • Page Down moves down one screen.
  • CTRL Page Up activates the previous worksheet in the current workbook.
  • CTRL Page Down activates the next worksheet in the current workbook.
  • Home goes to first column of current row.
  • End goes to last column of current row that contains data.
  • CTRL Home goes to row 1 column 1.
  • CTRL End goes to last row and column that contains data.
  • Scroll lock causes the view window to scroll without changing current selection with all movement keys except Home, End, CTRL Home, and CTRL End.
  • SHIFT plus any movement key extends the current selection.
  • SELECTING CELLS, ROWS AND COLUMNS
    Many operations require one or more cells to be selected. There are three kinds of selections: a single cell, a range of cells, and multiple ranges of cells (non-adjacent). The worksheet cursor is always located on a cell. The cell on which the worksheet cursor is located is called the active cell. The active cell is also a selection or part of a selection. Any data you enter is always placed in the active cell.
  • To select a range of cells, click and hold the left mouse button and drag through the range you want to select. When a range is selected, it becomes highlighted.
  • To select multiple ranges, press the CTRL key while selecting a range with the mouse. Any previously selected ranges remain selected.
  • Once a range is selected, you can move the active cell within the range using the ENTER, SHIFT + ENTER, TAB, and SHIFT + TAB keys. When you use these keys to move the active cell, the range remains selected.
  • Entire rows and columns can be selected in the worksheet using the mouse. To select a row or column, position the pointer on the header of the row or column you want to select. When you click the header, the row or column is selected.
  • You may prevent users of your worksheet from selecting cells. You may also choose to display the worksheet in row mode, which specifies that an entire row be selected when a user selects a cell in that row. Row mode is a nice feature in applications where each row represents a particular record: it makes it obvious which row the user is currently working on and facilitates copying and pasting entire rows. To set selection options use the menu function Format_Sheet Settings and go to the Selection tab.