Excel: Data Table

Sometimes one answer isn't enough. Occasionally, you need to see multiple possibilities. When this happens, consider adding a data table to your sheet. A data table is a range that evaluates changing variables in a single formula. In other words, it's a simple what-if analysis: How does changing an input value change the results? Instead of viewing multiple sheets, you can examine the possibilities with a quick glance at one data table.

In Microsoft Excel, a data table is one of the What-If Analysis tools that allows you to try out different input values for formulas and see how changes in those values affect the formulas output. Data Tables are, especially, useful when a formula depends on several values, and you'd like to experiment with different combinations of inputs and compare the results. Currently, there exist one variable data table and two variable data table. Although limited to a maximum of two different input cells, a data table enables you to test as many variable values as you want. One variable data table in Excel allows testing a series of values for a single input cell and shows how those values influence the result of a related formula. To help you better understand this feature, we are going to follow a specific example rather than describing generic steps.