O'Reilly logo

Excel 2010: The Missing Manual by Matthew MacDonald

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Variable Data Tables

When you create a formula, you generate 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. You could use a variable data table to see how the return of an investment varies based on different interest rates. Because Excel shows all the results 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, when you use variable data tables you save more than a few keystrokes.

Note

Variable data tables have nothing in common with the similarly named table feature, which you'll learn about in Chapter 14.

Creating a One-Variable Data Table

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required