Data → Table
If you find that you need to try different values within a formula to achieve the desired results, you should consider using Data → Table. The Data → Table option allows you to use what-if comparisons to determine the values of formulas if cells contain different values. For example, if you are trying to determine what your payment will be on a loan at various interest rates, you could either manually modify the function, or you could use the Data → Table option.
When you use the Data → Table option, you create a table that
contains the values you want to compare and the results of the
comparisons, as shown in Figure 9-14. In the figure,
column B contains a list of the possible interest rates for the loan.
When you select Data → Table, Excel will use the formula in cell
C3 to calculate the payment for the loan based upon the various
interest rates and place the payment amount in the cell next to the
interest rate. The cells in column F are used to calculate the
formula in C3,
Before selecting the Data → Table option, you need to select the data table, as shown in Figure 9-14. The data table is essentially the column that contains the values that will be substituted in the formula and the column where the results will be placed. Keep in mind you can also use rows for this.
Figure 9-14. Create a data input table for your formula