About Data Tables Data tables are part of a suite of commands sometimes called what-if analysis1tools. A data table is a range of cells that shows how changing certain values in your formulas2affects the results of the formulas. Data tables provide a shortcut for calculating multiple versions in one operation and a way to view and compare the results of all of the different variations together on your worksheet. One-variable data tables For example, use a one-variable data table if you want to see how different interest rates affect a monthly mortgage payment. In the following example, cell D2 contains the payment formula, =PMT(B3/12,B4,-B5), which refers to the input cell B3. Two-variable data tables A two-variable data table can show how different interest rates and loan terms will affect the mortgage payment. In the following example, cell C2 contains the payment formula, =PMT(B3/12,B4,-B5), which uses two input cells, B3 and B4. 1. what-if analysis: A process of changing the values in cells to see how those changes affect the outcome of formulas on the worksheet. For example, varying the interest rate that is used in an amortization table to determine the amount of the payments. 2. formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=). Data table calculations Data tables recalculate whenever a worksheet is recalculated, even if they have not changed. To speed up calculation of a worksheet that contains a data table, you can change the Calculation options to automatically recalculate the worksheet but not data tables. List of values that Excel subsitutesin the input cell, B3.Input cellColumn Input cellRow Input cellList of values that Excel subsitutesList of values that Excel subsitutesin the column input cell, B3.in the row input cell, B4.
Create a one-variable data table You must design one-variable data tables3 so that input values are listed either down a column (column-oriented) or across a row (row-oriented). Formulas2 used in a one-variable data table must refer to an input cell41.Type the list of values you want to substitute in the input cell either down one column or across one row. 2.Do one of the following: •If the data table is column-oriented, type the formula in the row above the first value and one cell to the right of the column of values. Type any additional formulas to the right of the first formula. •If the data table is row-oriented, type the formula in the column to the left of the first value and one cell below the row of values. Type any additional formulas below the first formula. 3.Select the range of cells that contains the formulas and values you want to substitute. 4.On the Data menu, click Table. 5.Do one of the following: •If the data table is column-oriented, type the cell reference5 for the input cell in the Column input cell box. •If the data table is row-oriented, type the cell reference for the input cell in the Row input cell box. 2. formula: A sequence ofvalues, cell references, names, functions, or operators in a cell that togetherproduce a newvalue. A formula always begins with an equal sign (=). 3.data table: A range of cells that shows the results ofsubstituting different values in one ormore formulas. There are two types ofdata tables:one-input tables and two-input tables. 4. input cell: The cell in which each input value from a data table is substituted. Any cell on a worksheetcanbe the input cell. Although the inputcell does not needto be partof the data table, the formulas indata tables must refer to the input cell. 5. cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the referenceof the cell that appears at the intersection of column B and row 3 is B3.