How to create and use one-input data tables in Microsoft Excel

Article translations Article translations
Article ID: 282855 - View products that this article applies to.
This article was previously published under Q282855
Expand all | Collapse all

On This Page

SUMMARY

This article describes how to create and use one-input tables in Microsoft Excel, which allow you to test how changes in one variable affect a formula.

MORE INFORMATION

You can organize one-input tables in two ways: column input or row input.

How to organize the input values in a column

Column-input tables are organized with the values listed in a vertical array and the formulas listed horizontally. To create a simple column input table, follow these steps:
  1. Create a new workbook.
  2. In cells B3:B6, type the following data:
         Cell       Value
         ----------------
         B3           10
         B4           13
         B5           14
         B6           19
    						
    These values are the variables that Excel will substitute into the formulas.
  3. In cell C2, type the following formula:
    =B1+2
    NOTE: In this formula, B1 is the column-input cell. This column-input cell represents the variable value in the formula. This cell must be located outside the table (it may or may not contain data). Because this table is set up in cells B2:E6, and B1 is outside the table, B1 is a valid column-input cell.
  4. In cell D2, type the following formula:
    =B1*2
  5. In cell E2, type the following formula:
    =INT(B1/2)
  6. Select cells B2:E6.
  7. On the Data menu, click Table.
  8. In the Column Input Cell box, type B1. Because this is a one input table, leave the Row Input Cell box blank.
One-Input table with input values in a column (with formulas displayed)
=======================================================================

      | A |  B  |    C         |     D        |      E
   ---|---|-----|--------------|--------------|-----------
    1 |   |     |              |              |
    2 |   |     |=B1+2         |=B1*2         |=INT(B1/2)
    3 |   |   10|{=TABLE(,B1)} |{=TABLE(,B1)} |{=TABLE(,B1)}
    4 |   |   13|{=TABLE(,B1)} |{=TABLE(,B1)} |{=TABLE(,B1)}
    5 |   |   14|{=TABLE(,B1)} |{=TABLE(,B1)} |{=TABLE(,B1)}
    6 |   |   19|{=TABLE(,B1)} |{=TABLE(,B1)} |{=TABLE(,B1)}


One-Input table with input values in a column (with values displayed)
=====================================================================

      | A | B |  C  |  D |   E
   ---|---|---|-----|----|--------
    1 |   |   |     |    |
    2 |   |   |#+2  |#*2 |INT(#/2)
    3 |   | 10|   12|20  |       5
    4 |   | 13|   15|26  |       6
    5 |   | 14|   16|28  |       7
    6 |   | 19|   21|38  |       9
				
The values displayed in cells C2, D2, and E2 are number formats. To duplicate these values, follow these steps:
  1. Select cell C2.
  2. On the Format menu, click Cells.
  3. Click the Number tab.
  4. In the Category list, click Custom.
  5. In the Type box , type "#+2" (with the quotation marks).
  6. Click OK.
  7. Repeat steps 1-6, but select cells D2 and E2 in step 1, and enter the formats as "#*2" and "INT(#/2)" respectively (including the quotation marks).

How to organize the input values in a row

Row-input tables are organized with the variable values listed in a horizontal array and the formulas listed vertically. To create a simple row-input table, follow these steps:
  1. In cells C9:F9, type the following data:
         Cell       Value
         ----------------
         C9         19.95
         D9         20.98
         E9         13.50
         F9         10
    						
    These values are the variables that Excel will substitute into the formulas.
  2. In cell B10, type the following formula:
    =A10*7.8%
    NOTE: In this formula, A10 is the row-input cell. The row-input cell represents the variable value in the formula and must be located in a cell outside the table. This cell may or may not contain data. Because this table is set up in cells B9:F11, and A10 is outside the table, A10 is a valid row-input cell.
  3. In cell B11, type the following formula:
    =A10+A10*7.8%
  4. Select cells B9:F11.
  5. On the Data menu, click Table.
  6. In the Row Input Cell box, type A10. Because this is a one-input table, leave the Column Input Cell box blank.
One-Input table with row input cell (with formulas displayed):
==============================================================

   | A |     B       |      C        |     D        |     E        |    F
---|---|-------------|---------------|--------------|--------------|-----------
   |   |             |               |              |              |
 9 |   |             |        19.95  |       20.98  |        13.5  |         10
 10|   |=A10*7.8%    |{=TABLE(A10,)} |{=TABLE(A10,)}|{=TABLE(A10,)}|{=TABLE(A10,)}
 11|   |=A10+A10*7.8% |{=TABLE(A10,)} |{=TABLE(A10,)}|{=TABLE(A10,)}|{=TABLE(A10,)}


One-Input table with row input cell (with values displayed):
============================================================

      | A |   B  |   C  |   D  |   E  |   F
   ---|---|------|------|------|------|-----
      |   |      |      |      |      |
    9 |   |      |19.95 |20.98 |13.50 |10.00
    10|   |Tax   | 1.56 | 1.64 | 1.05 | 0.78
    11|   |Total |21.51 |22.62 |14.55 |10.78
				
The values displayed in cells B10 and B11 are number formats. To duplicate these values, follow these steps:
  1. Select cell B10.
  2. On the Format menu, click Cells.
  3. Click the Number tab.
  4. In the Category list, click Custom.
  5. In the Type list, type "Tax" (with the quotation marks).
  6. Click OK.
  7. Repeat steps 1-6, but select cell B11 in step 1, and type the format as "Total" (with the quotation marks).

REFERENCES

For more information about how to use data tables, click the following article numbers to view the articles in the Microsoft Knowledge Base:
282851 How to use Microsoft Excel data tables to analyze information in a database
282856 How to create and use two-input data tables in Microsoft Excel
282852 An overview of data tables in Microsoft Excel

Properties

Article ID: 282855 - Last Review: January 29, 2007 - Revision: 5.2
APPLIES TO
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 98 for Macintosh
  • Microsoft Excel 97 Standard Edition
Keywords: 
kbhowto KB282855

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com