logo-wlogologo-wlogo-w
  • Home
  • Classes
  • Training
  • Consulting
  • Blog
  • Contact
✕
The Double Negative
October 21, 2021
Working with Formulas in Excel
October 28, 2021

Working with Data Tables

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.

01 PMT

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.

02 Linking

Step 2: Select the table and go to the Data Table option.
Data Tab > Forecast Section > What If Analysis > Data Table

1

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

2

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.

3

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.

4
03 Data Table
5

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.
IMG-20191023-WA0003_2
Share
2

Related posts

December 16, 2021

Stacking in EXCEL


Read more
December 9, 2021

SIMPLE EXCEL FUNCTIONS YOU SHOULD KNOW


Read more
November 25, 2021

Circular Referencing Solution


Read more

Comments are closed.

✕

Categories

  • Google
  • MS Excel
  • MS Word
  • Power BI
  • Uncategorized
  • Stacking in EXCEL
    December 16, 2021
  • SIMPLE EXCEL FUNCTIONS YOU SHOULD KNOW
    December 9, 2021
  • Tips and Tricks of MS PowerPoint
    December 3, 2021
  • Circular Referencing Solution
    November 25, 2021
  • Printing tricks
    November 18, 2021
    Disclaimer Policy | Terms of Service
    Powered by Aswini Aswini Bajaj
    Website Designed & Developed by 'A PIXEL HOUSE'