Excel Data Tables: See Many What-If Answers at Once

By Ravindra Bagale, Excel & Data Analytics trainer LinkedIn Published: 6 October 2026 Last updated: 6 October 2026

Rani is planning a ₹5,00,000 loan. The bank says the rate could be anywhere from 8% to 11%, and she can choose 3, 5, 7 or 10 years. Instead of changing the rate cell 28 times and noting each EMI, she builds a Data Table that shows every answer at once.

A Data Table (Data > What-If Analysis > Data Table) runs one formula many times with different input values and lists the results. A one-variable table changes one input (rate). A two-variable table changes two inputs (rate and years) and fills a grid.

Download the practice file (data-tables-what-if.xlsx)

Excel file, 7 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.

Quick answer

Build the model: loan in B2, rate in B3, years in B4, EMI in B5 =PMT(B3/12,B4*12,-B2).
Type the rates down a column, for example D3:D9, and put =B5 in E2 (one row above, one column right).
Select D2:E9.
Click Data > What-If Analysis > Data Table. Leave Row input empty; Column input cell = B3. Click OK.
E3:E9 now show the EMI for each rate.

=PMT(B3/12,B4*12,-B2)

One-variable table: EMI by rate

Rate	EMI for ₹5,00,000 over 5 years
8%	₹10,138
8.5%	₹10,258
9%	₹10,379
9.5%	₹10,501
10%	₹10,624
10.5%	₹10,747
11%	₹10,871

Excel fills the cells with {=TABLE(,B3)}. You cannot edit one cell of the result; select the whole result range to delete it.

Two-variable table: rate × years

Type the rates down G3:G9 and the years across H2:K2.
In the corner cell G2 type =B5 (the EMI formula).
Select G2:K9.
Data > What-If Analysis > Data Table: Row input cell = B4 (years, because they are in a row), Column input cell = B3 (rates, in a column).
Click OK. Each cell shows the EMI for that rate and number of years.

Rate \ Years	3	5	7	10
8%	₹15,668	₹10,138	₹7,793	₹6,066
9%	₹15,900	₹10,379	₹8,045	₹6,334
10%	₹16,134	₹10,624	₹8,301	₹6,608
11%	₹16,369	₹10,871	₹8,561	₹6,888

The practice file has a Check grid sheet with the same EMIs worked out with normal PMT formulas, so you can compare your data table with it.

Traps

Input cells must be on the same sheet as the data table.
Mixing up Row and Column input gives wrong numbers with no error. Rule: values in a row → Row input cell.
Big data tables slow a workbook. Use Formulas > Calculation Options > Automatic Except for Data Tables and press F9 when you need fresh numbers.
The formula cell (E2 or G2) can be hidden with a custom number format ;;; so the table looks clean.

Ravindra Bagale's Tip

Highlight the row of the rate the bank is actually offering with conditional formatting. A table of 28 EMIs is useful; a table where the realistic one stands out is persuasive.

Practice task

Open the Model sheet. Check B5 shows the EMI for 9% and 5 years.
Select D2:E9 and create a one-variable data table with Column input B3.
Select G2:K9 and create a two-variable data table with Row input B4 and Column input B3.
Compare a few results with the Check grid sheet.
Change the loan in B2 to 800000 and watch both tables update.

Learn it properly

Course lessons:

Frequently asked questions

Why does my data table show the same value in every row?

The input cell is wrong or the formula does not use it. Check that the EMI formula refers to B3 and that B3 was the input cell.

Can I delete one cell from a data table?

No. Select the whole result range and press Delete.

Do data tables work in Excel for the web?

You can view and recalculate them, but creating one needs desktop Excel.

How is this different from Goal Seek?

Goal Seek finds one input for one target. A data table shows the result for many inputs at once.