###### The Double Negative

October 21, 2021###### Working with Formulas in Excel

October 28, 2021
**Understanding 2 variable Data Tables**

This tool helps you present different results in a table by using different combinations of inputs that have been used in a formula. As for now, Microsoft Excel allows data tables by changing either one or two variables.
Here we will be explaining, how to create data tables by changing two variables in a formula.

In the above video, PMT is calculated using three variables interest, years (no. of periods) and present value (PV). We will be creating a data table by using different combinations of interest rates and years.

**Step 1:** After making a table with the combinations you would like to analyze, link the cell with a formula to the top left corner of the table created as shown below.

**Step 2:** Select the table and go to the Data Table option.

**Data Tab > Forecast Section > What If Analysis > Data Table**

**Step 3:** The dialog box that appears has two parts Row Input cell & Column Input cell.

**Row Input cell:** Select the cell that is used as an input in the formula and is populated in the first row of the table. Highlighted in ** blue.**

**Column Input cell:** Select the cell input that is used in the formula and is populated in the first column of the table. Highlighted in **green.**

**Row Input cell:**First row of the table shows no. of years and the cell used to represent the same in the formula is C7, shown in ** blue.**

**Column Input cell:**First column of the table shows interest and the cell used to represent the same in the formula is C6, shown in **green.**

The data table created above helps in knowing things like,* ‘What if the interest increases to 11% and I take a loan for 4 years instead, how much EMI will I have to pay then?’*

You need to pay $5169.1.

With data tables by your side, you can answer more such “what if” questions in lesser time.

**Contributor: **Sonia Keswani

I am a B.Com(Hons.) graduate from South City College (Morning) and have been working at Leveraged Growth as a Finance Intern for the past 1 month. I am pursuing CFA and aspire to be a Financial Analyst in the future.