Excel Video 341 introduces Data Tables, another What-If tool that can be very helpful in Excel. The idea is to create a formula using some variables. A Data Table will take a group of rows and/or columns and insert each value in the group into the formula. The result is displayed in a Data Table that makes it easy to look up what happens as the variables change.
Our example today is monthly payments on an equipment loan. If you need a quick refresher on the PMT formula we’re using, watch Excel Video 143. As we provide different interest rates to the formula in cell B8, the Data Table shows the monthly payment at each of those rates.
Note the location of the formula in cell B8. In a one-variable Data Table, the formula goes one row below and one column to the left of our row of interest rates. If my interest rates were in the same column instead of the same row, I’d enter the new formula one column to the right and one row above my column of data. The formula location changes in a two-variable Data Table. Stay tuned. We’ll talk about two-variable Data Tables next time.
