Using Excel’s What-If Analysis with Data Table for data analysis

Q. You explained Excel’s Scenario Manager in your November 2024 Tech Q&A article and Goal Seek in your December 2024 Tech Q&A article. Can you please explain the final What-If Analysis tool: Data Table?

A. Microsoft Excel’s What-If Analysis is a powerful feature that allows you to explore various scenarios and outcomes by modifying input values within your spreadsheets. This tool is essential for decision-making processes, enabling users to anticipate potential results and plan accordingly. Using What-If Analysis, you can visualize the effects of different variables, making it easier to assess risks and opportunities. In Excel, three primary tools are available for performing What-If Analysis: Scenarios, Goal Seek, and Data Table.

To recap the last couple of months, here are brief definitions of Scenarios and Goal Seek.

■ Scenarios: This tool allows you to save and substitute different sets of values automatically in cells. You can create multiple scenarios on a single worksheet, switch between them effortlessly, and generate a Scenario Summary Report for comparison.

■ Goal Seek: Goal Seek helps you determine the necessary input value to achieve a specific result in a formula. It adjusts a single variable to reach the desired outcome, which is particularly useful for financial projections or target calculations.

Data Table can be used to test different scenarios by systematically changing one or two variables to see their impact on the final outcome. This feature supports side-by-side comparisons of how changes in input affect results.

Let’s look at an example to demonstrate how this works. To follow along, download this Excel workbook. A video demonstration is available at the bottom of this article on the JofA website. Note that the following content is based on Microsoft Excel 365 for PCs. Other versions of Excel may work differently.

For this example, we want to determine the monthly payment on a loan, but we are unsure about the interest rate. We want to see how the payment changes as the interest rate varies between 4% and 10%. We will use Data Table to analyze the impact of different interest rates on the payment. In the attached Excel document on the first sheet, I defined the Loan Amount in cell B1 as 50,000, the Number of Years in cell B2 as 5, the Interest Rate in cell B3 as 6% (this rate will vary in our Data Table), and the Monthly Payment in cell B4 as =PMT(B3/12, B2*12, -B1). Input 4% in cell A10, indicating a 4% interest rate. Put 4.5% in cell A11, and continue with increments of 0.5% all the way down in column A until you get to 10% in cell A22. Put a cell reference to B4, the monthly payment formula, in cell B9. See these inputs in the screenshot below. Note that I have the formula displayed in cells B4 and B9 as a formula in the screenshot instead of the result.

Select cells A9:B22. Go to Data, click the dropdown arrow next to What-if Analysis within the Forecast group, and choose Data Table. The Data Table window will open, asking for inputs into Row input cell: and Column input cell:. In this scenario, we will enter values only into the Column input cell: because our variables are in a column and we are only changing that one variable. The variable is the interest rate, so reference cell B3 next to Column input cell:. Click OK. The monthly payments based on interest rates between 4% and 10% will automatically populate in cells B10 through B22. See the screenshot below of the Excel spreadsheet after using Data Table for this example.

I do not like one thing about this display — the reference in B9. I feel like it is confusing to a user without any context; however, it is necessary to make Data Table work. So, you can do some things with cell B9. You can obviously leave it like it is, but I think it would be confusing to anyone using the table. You could “hide” the formula by changing the cell’s font to white. This is what I usually do. You could also use the custom format option to create a text title or to present an icon.

This example involved changing one variable and seeing the impact on the final result. Let’s use the same example data, but now let’s change two variables: Interest Rate and Number of Years. We still want to determine the monthly payment, but we would like to vary the interest rate and number of years for the loan. We will vary the interest rate between 4% and 10% and the number of years between four years and 10 years. In the attached Excel document on the first sheet, I have defined the loan information in cells B1 through B4 as described above. Now, I have included the interest rates in cells E4:E16 (4%–10%) and the number of years in cells F3:L3 (4–10). Put a cell reference to B4, the monthly payment formula, in cell E3. See these inputs in the screenshot below.

Select cells E3:L16. Go to Data, click the dropdown arrow next to What-if Analysis within the Forecast group, and choose Data Table. The Data Table window will open, asking for inputs into Row input cell: and Column input cell:. In this scenario, we are changing two variables, so we will have input for both the rows and columns. The number of years are listed across row four, so we will reference B2 (the Number of Years) next to Row input cell:. The varying interest rates are listed in column E, so we will reference B3 (the Interest Rate) next to Column input cell:. Click OK. The monthly payments based on interest rates between 4% and 10% and years four through 10 will automatically populate in cells F4 through L16. See the screenshot below of the Excel spreadsheet after using Data Table for this example.

You can leave the number in cell E3 as-is, or you can hide or reformat it as described above, but either way, the cell reference must be there. Again, I like to make the font white so that it is not visible.

By setting up either a one-variable or two-variable data table, you can analyze the effects of varying inputs without altering your existing spreadsheet structure. This makes it an essential tool for decision-makers looking to assess multiple scenarios quickly and effectively.

About the author

Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.

Submit a question 

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org.