Quick Links:
Help Topics Commands Index Custom Apps Save & Print

Spreadsheet

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.

See also Insert Function, Examples.

Getting Started

Some general tips on using formulas are listed below:

See also Examples.

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.

See also Getting Started.

Insert Function

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

See also Algebra & Functions Commands.
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).

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:

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.

See also Sort data.

 

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.
    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.
  3. 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.

See also Statistics & Probability for additional help topics.

 

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.

Data

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

See also Data Sets for more information.