Show All

Hide All

Data tables are part of a suite of commands sometimes called what-if analysis (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.) tools. A data table is a range of cells that shows how changing certain values in your formulas (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 (=).) affects 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.

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.