#Excel for mac what if analysis how to#
#Excel for mac what if analysis update#
#Excel for mac what if analysis series#

If you want to examine the impact of the variable values on other formulas that refer to the same input cell, enter the additional formula(s) to the right of the first formula, as shown in this example. We choose the latter option, and enter this simple formula in E2: =B8

#Excel for mac what if analysis update#

Or, link this cell to the formula in your original dataset (if you decide to change the formula in the future, you would need to update only one cell). Type your formula in the cell one row above and one cell to the right of the variable values (E2 in our case).In this example, we are going to create a column-oriented data table, so we type our variable values in a column (D3:D8) and leave at least one blank column to the right for the outcomes. Enter the variable values either in one column or across one row.Here are the steps to make a one-variable data table:

B2 is the variable you want to test (initial investment).Īnd now, let's do a simple What-If analysis to see what your savings will be in 5 years depending on the amount of your initial investment, ranging from $1,000 to $6,000.

B8 contains the FV formula that calculates the closing balance.

To check different options, you've built the following compound interest calculator where: Suppose you are considering depositing your savings in a bank, which pays 5% interest that compounds monthly. To help you better understand this feature, we are going to follow a specific example rather than describing generic steps.

#Excel for mac what if analysis series#

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.

#Excel for mac what if analysis how to#

How to create a one variable data table in Excel If you are looking to learn about many possible ways to create, clear and format a regular Excel table, not data table, please check out this tutorial: How to make and use a table in Excel. A data table isn't the same thing as an Excel table, which is purposed for managing a group of related data. Although limited to a maximum of two different input cells, a data table enables you to test as many variable values as you want. 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.ĭata 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.Ĭurrently, there exist one variable data table and two variable data table.

Excel data tables - 3 things you should know.

Data table to evaluate multiple formulas.

Instead of testing each variable individually, make a What-if analysis data table and observe all possible outcomes with a quick glance! You have built a complex formula dependent on multiple variables and want to know how changing those inputs changes the results. Learn how to create a one-variable and two-variable table to see the effects of one or two input values on your formula, and how to set up a data table to evaluate multiple formulas at once. The tutorial shows how to use data tables for What-If analysis in Excel.