When you create a formula, you see a single result based on the operators, functions, and data that you used. This situation makes sense when you're nailing down last year's profit-loss report, but it's not always as handy when you're making projections for the future. In these cases, it's often helpful to compare several possibilities. One tool you can use is a variable data table .
A variable data table is simply a table that shows multiple results, based on different source data. For example, you might use a variable data table to see how the return of an investment varies based on different interest rates. Because all the results are shown side-by-side, you can quickly compare them.
You could create your own comparison table by hand without too much trouble. In fact, using the power of relative references, you could create one formula and copy it into several cells to quickly create a table without needing any formula tweaking. Some Excel fans prefer this approach. However, Excel also gives you the ability to create comparison tables through a dedicated feature that can save a few keystrokes.
A one-variable data table provides a single column of results. It's called one-variable because there's only one input value that changes. For example, if you want to compare how an investment payoff changes based on interest rates, you can create a one-variable data table that uses the interest rate as the changing variable.
Here's how ...