Help Topics Commands Index Custom Apps Save & Print

A spreadsheet allows one to display and compute data stored in the cells of a grid, and relate rows and columns by formulas. Basics Cells are referenced according to their location by column (vertical arrangement is alphabetized by letter) then row (horizontal arrangement is numbered). For example, the cell highlighted at left is "C3" (column C, row 3). Select a single cell by clicking on it. To select multiple cells at a time: (1) click and drag over a region with the mouse, (2) hold down the Shift key while you click, or (3) hold down the Control key while you click to select non-adjacent cells.

Formulas

Formulas in a spreadsheet allow cells to be related so that the entry of one cell can be calculated from a value or values in different cells.

Getting Started

Some general tips on using formulas are listed below:

• To enter a formula, type an equals sign (=) and then the formula you wish to use. Press Enter.
• Single-click a cell to view the formula used to compute it. Double-click a cell to edit the formula.
• Cells can be used in calculations by referencing their cell name. Any cell referenced in a formula must contain a numerical value or the formula will produce an error. For example:
=B1^2+5 If cell B1 has a value of 2, the cell containing this formula will get the value of the square of cell B1 plus 5 (i.e., 2^2+5=9).
• Place a dollar sign (\$) in front of the column or row reference to fix the reference of that particular column or row. The row and/or column that is fixed will not change if that cell is copied and pasted or filled down a particular column. For example:
=\$B1^2+5 Fixes the column reference (column B) and allows the row reference (row 1, 2, 3, etc.) to vary.

=B\$1^2+5 Fixes the row reference (row 1) and allows the column reference (column B, C, D, etc.) to vary.

=\$B\$1^2+5 Fixes both the column (column B) and row (row 1) references.

Fill Down, Column Formula

Both the Fill Down and Column Formula entries of the Edit menu allow you to apply formulas to entire regions of cells at a time. This saves time in entering formulas.

• Choose Edit | Fill Down or to apply a given formula to the selected cells below it.
• Select a column (click in a cell of the desired column), then choose Edit | Column Formula. In a separate dialogue box, type the desired formula, then click OK (e.g., "=A1+2"). The entered formula will then be applied to the entire column that was selected.

Insert Function

Choose Insert | Function for a listing of common functions. Click on a function name below for a full description of its use:

 Basic Statistics Trigonometric Combinatorics Other Functions

If inserted into an empty cell, simply type between the parentheses (i.e., "=functionname()"). If the selected cell is not empty, the function name will be inserted at the end of those contents. To be executed as a formula, you must use the equals sign "=" immediately preceding the function name.

Examples

Shown below are two examples of using the Fill Down option with formulas. Example 2 shows the use of fixed cell references.

Example 1. Using Fill Down with variable cell references.
 A 1 5 2 3 4
 A 1 5 2 =A1+5 3 4
 A 1 5 2 =A1+5 3 =A2+5 4 =A3+5
 A 1 5 2 10 3 15 4 20
Suppose that you have entered a value into a spreadsheet, as shown in cell A1 at right. Next, a formula will be used to relate each cell in column A to the cell above it. Type a formula into cell A2 so that its value is 5 more than the value in A1 ( =A1+5). Then select cells A2 through A4. Choose Edit | Fill Down . The cells to the right display the formulas contained in each cell. NOTE: If a cell contains a formula, the numerical value of the formula is shown unless the individual cell is selected. The numerical values corresponding to each of the formulas described above are shown here.

Example 2. Using Fill Down with fixed and variable cell references.
 A B 1 5 2 10 3 15 4 20
 A B 1 5 =A\$1^2+A1 2 10 3 15 4 20
 A B 1 5 =A\$1^2+A1 2 10 =A\$1^2+A2 3 15 =A\$1^2+A3 4 20 =A\$1^2+A4
 A B 1 5 30.0 2 10 35.0 3 15 40.0 4 20 45.0
Suppose you have data entered in column A. Next, a formula will be used to relate each cell in column B to a fixed cell and a variable cell in column A. Enter a formula into cell B1 so that its value is the square of cell A1 plus the corresponding column A cell value. Then select cells B1 through B4. Choose Edit | Fill Down to obtain the desired formulas in cells B2 through B4, as shown to the right. Notice the fixed and variable references used. The numerical values corresponding to each of the formulas as described above are shown here.

Edit and Insert Options

The Edit and Insert menus offer functionality to move and shift cells, delete and insert rows and columns, and to modify settings and styles of spreadsheet cells.

Move and Shift Cells

Use the Edit menu, toolbar icons, or keyboard shortcuts for quick cut, copy, and paste options for selected regions of cells. Also shift cells or regions of cells at a time.

See also Delete Column(s)/Row(s) and Insert Column(s)/Row(s).
• From the Edit menu, choose to Cut or Copy (control+C) selected cells and temporarily store them on the clipboard.
Note: The temporary storage will be replaced when anything else is cut or copied.
• Choose Edit | Paste (control+V) to view the cut or copied cells on the clipboard. Be sure to click on the the top-left cell of the rectangular region to be pasted into before pasting.
• Choose Insert | Shift Cells Right/Down to move the cell or selected region of cells accordingly.
Note: Any cells that were to the right of/below the selected cells also get shifted to the right/down to make room for the selected cells. Cell formulas are updated where necessary as cells move.

Delete and Insert Row(s)/Column(s)

Use these options to remove and shift entire row(s) and column(s) of a spreadsheet at a time:

• Choose Edit | Delete Row(s)/Column(s) to remove the selected row(s)/columns(s) from your spreadsheet. Any contents that were below/to the right of the deleted selection remain.
Note: This action cannot be reversed.

• Choose Insert | Row(s) to add a row above the selected cell. Choose Insert | Column(s) to add a column to the left of the selected cell.
See also Shift Cells right or down.

Settings and Styles

Customize the display of cells including the vertical alignment, the number of digits displayed (including increase and decrease by one), and the mode. Also choose to edit the column name from the default alphabetical name.

• Choose Edit | Align to specify the horizontal position of text within a selected column. The alignment options are Left ( ), Center ( ), and Right ( ).
• Choose Edit | Set # of digits to determine the number of decimals that are displayed for any data entry that is in formula format (e.g., =0.14) in the selected column(s). You may choose to show 0, 1, 2, 3, 4, 5, 6, or All decimal values (the default setting is 6). Alternatively, select the desired column(s) then choose (or ) to decrease (or increase) the number of digits (decimal places) by by one.
Note: When you manually type a number in a cell that is not a formula it is assumed that the desired # of digits (decimal places) is already set. The Set Digits options only apply to formulas.
• Choose Edit | Column Name then type the desired name into the "Edit Column Name" box. Click OK to change the name from the default naming.
Note: If a column is renamed, you may use the new column name within a formula (as long as the first characters in the name are not numerical) by typing the first characters of the name until the first space. For example, suppose column A is renamed to "Test." In column B type =A1*3 OR =Test1*3.
• Choose Edit | Degrees or Edit | Radians to set the mode of displayed computations in degrees or radians, respectively. The default setting is Degrees.

Tools

Most tools available in spreadsheet are also available within the Statistics | Data Analysis tool. Options to Sort, Graph, or view Summary Statistics, activate a Solver, or a Chi-Square Test are explained below.

Sort

The two Tool menu options for sorting data are Sort All Rows by and Sort All Columns by. Two different methods for using these features are suggested below:

1. Sort groups of rows/columns at a time.
• Without cells selected, choose a sorting option ( , ).
• "In": Control+Click the desired rows/columns to group together and include in the sorting.
• "By": Control+Click the desired rows/columns to determine the sorting (the selected row/column listed first will determine the primary sorting). Click OK.
Note: This method is desirable if the spreadsheet cells are related to one other (e.g., bivariate data with pairings by rows).
2. Sort select rows/columns individually.
• Select the desired rows/columns to sort. Control+Click to select non-adjacent cells
• Choose the desired sorting option ( , ). Only the selected cells will be sorted, all other cells will remain unsorted.
Note: This method is not desirable if the spreadsheet cells are related to one other (e.g., bivariate data that include specific pairings).

Graph

Spreadsheet graph options are organized by type below. The links on each graph type redirect to additional help topics within Data Analysis (or Advanced Apps for Contour plots):
 Univariate Bivariate Statistical Multivariate Use a single column, or Control+Click to select more than one column of data for a "stacked" graph. Two (or more) columns must be selected. Scatterplots require one independent and one dependent column. May involve more than one column of data (multi-column graphs will be color-coded). Uses the number of rows and columns to determine the size of the grid and the entries of these cells as the "height" of the contour plot.

Summary Statistics

Choose Tools | Summary Statistics to compute Descriptive statistics (n, mean, minimum, q1, median, q3, maximum, sample standard deviation, and sample variance) and analyze Regression based on a selected model (linear, quadratic, cubic, quartic, power, exponential, logarithmic, polynomial, sinusoidal).

Note: For Regression analysis first choose the independent and dependent variables, then click OK. A separate Regression Analysis Frame will appear with Results, Graph, and Residuals in tabs across the top.

Solver

To use the Solver tool, first enter the following information into a blank Data Sheet:

1. Enter the letters associated with your variables in a single row. For example, type "x" in cell B2 and "y" in cell B3. (See the completed Data Sheet below.)
2. Enter temporary values for these variables in the row that follows. For example, type "0" in cell C2 and "0" in cell C3. These values will change to the solution values once the system of equations has been solved, if a solution exists.
3. Enter the coefficients for your equations below the corresponding variables, the operation, and the constant term (one equation per row). For example, type "0.2" in cell B4, "0.4" in cell C4, "=" in cell D4, and "160" in cell E4 to represent the equation 0.2x + 0.4y = 160; enter the equation x + y = 500 in a similar manner in row 5.
4. Choose Tools | Solver to open a Solver window, then select "Solve a System" under Problem Type.
5. Enter the correct range for your variable values, then hit Return or Tab. For this example, use B2:C2. If done correctly, you will notice the values you entered display in a comma-delimited list to the right of your entered range.
6. Enter the correct range for your system of equations, then hit Return or Tab. For this example, use B4:E5. Again, you will notice your data sheet values display in a collection of comma-delimited lists.
7. Click Solve to determine the solution, if it exists. For this example, the solution is (200, 300).
 A B C D E 1 Variables x y 2 Values 0 0 3 4 Equations 0.2 0.4 = 160 5 1 1 = 500

Chi-Square Test

To use the Chi-Square Test:

1. First open or create your own bivariate categorical data set (e.g., Crying).
2. Choose Tools | Chi-Square Test to open a Chi-Square Analysis window to obtain the chi-squared statistic. You will need to define the cell range for the frequency data; the default range is B2:C3 which encompasses cells B2, B3, C2, and C3.
• Use the Tests menu and choose to Test for Independence, Test for Homogeneity, or determine a Goodness of Fit.
• Use the Options menu to Show Expected Counts, Show Expected Percents, or Show Critical Values.

Data

Choose a data set from the Data menu for quick access to pre-loaded data examples.