EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Tools Data Table in Excel
 

Data Table in Excel

Madhuri Thakur
Article byMadhuri Thakur

Updated May 9, 2023

Data Table in Excel

 

 

Data Table in Excel

The Data Tables in Excel are used to analyze the changes in your final result when certain variables are changed from your function or formula. Data tables are one of the existing parts of What-If analysis tools, which allow you to observe your result by experimenting with different variables values and comparing the outcomes stored by the data table.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

How to Create Data Table in Excel? There are two types of a data table, which are as follows:

  1. One-Variable Data Table.
  2. Two-Variable Data Table.

Data Table in Excel is very simple and easy to create. Let’s understand the working of the Data Table in Excel with Some Examples.

You can download this Data Table Excel Template here – Data Table Excel Template

Examples of Data Table in Excel

Example #1 – One-Variable Data Table

One-variable data tables are efficient in the case of analyzing the changes in the result of your formula when you change the values for a single input variable.

Use case of One-Variable Data Table in Excel:

The one-variable data table proves useful in scenarios where a person observes how different interest rates affect their mortgage amount. Consider the below figure, which shows the mortgage amount calculated based on the interest rate using the PMT function.

Data Table Example 1

The table above shows the data where the mortgage amount is calculated based on the interest rate, mortgage period, and loan amount. It uses the PMT formula to calculate the monthly mortgage amount, which can be written as =PMT (C4/12, C5,-C3).

In the case of observing the monthly mortgage amount for different interest rates, the interest rate is considered a variable. To do this, there is a need to create a one-variable data table. The steps to create the one-variable data table are as follows:

Step 1: Prepare a column comprising different values for the interest rates. We have entered different values for interest rates in the column, which is highlighted in the figure.

Data Table Example 1-1

Step 2: In the cell (F2), which is one row above and diagonal to the column you prepared in the previous step, type this = C6.

Data Table Example 1-2

Step 3: Select the entire prepared column by values of different interest rates along with the cell where you had inserted the value, i.e., F2 cell.

Data Table Example 1-3

Step 4: Click on the ‘Data’ tab and select ‘What-If Analysis’; select’ Data Table’ from the options popped down.

Data Table Example 1-4

Step 5: The data table dialog box will appear.

Data Table Example 1-5

Step 6: In the Column input cell, refer to cell C4 and click OK.

Data Table Example 1-6

In the dialog box, we refer to cell C4 in the Column input cell and keep the row input cell empty as we prepare a data table with one variable.

Step 7: After following all the steps, we get all the different mortgage amounts for all entered interest rates in column E (unmarked), and the different mortgage amounts are observed in column F (marked).

Data Table Example 1-7

 Example #2 – Two-Variable Data Table

Two-variable data tables are useful in scenarios where a user needs to observe the changes in the formula’s result when they simultaneously change two input variables.

Use-case of Two-Variable Data Table in Excel:

You can use the two-variable data table to observe how different interest rates and loan amounts change the amount of your mortgage payment. Instead of calculating individual values separately, we can observe them with instantaneous results. Consider the below figure, which shows the mortgage amount calculated based on the interest rate using the PMT function.

Data Table Example 2

The above example is similar to our example shown in the previous case for a one-variable data table. It uses the PMT formula to calculate the monthly mortgage amount, which can be written as =PMT (C4/12, C5,-C3). The calculation of the mortgage amount in cell C6 is based on the interest rate, mortgage period, and loan amount.

To explain the two-variable data table concerning the above example, we will show the different mortgage amounts and choose the best which suits you by observing the different values of interest rates and loan amounts. To do this, there is a need to create a two-variable data table. The steps to create the one-variable data table are as follows:

Step 1: Prepare a column comprising different interest rates and loan amount values.

 Example 2-1

We have prepared a column consisting of the different interest rates, and in the cell diagonal to starting cell of the column, we have entered the different values of the loan amount.

Step 2: In cell (E2), one row above the column you prepared in the previous step, type this = C6.

 Example 2-2

Step 3: Select the entire prepared column by values of different interest rates along with the cell where you had inserted the value, i.e., E2 cell.

 Example 2-3

Step 4: Click on the ‘Data’ tab and select ‘What-If Analysis’; select’ Data Table’ from the options popped down.

 Example 2-4

Step 5: A Data table dialog box will appear. Click OK after selecting both variables, the ‘Column input cell’ (C4) and the ‘Row input cell’ (C3), as we change them both.

 Example 2-5

Step 6: After all the steps, we get different mortgage amounts values for interest rates and loan amount values.

 Example 2-6

Things to Remember 

  1. You leave the ‘Row input cell’ blank in a one-variable data table. In a two-variable data table, you fill the ‘Row input cell’ and ‘Column input cell’.
  2. Once you perform the What-If analysis and calculate the values, you cannot change or modify any cell within the set of values.

Recommended Articles

This has been a guide to a Data Table in Excel. Here we discuss its types and how to create data table examples and downloadable Excel templates. You may also look at these useful functions in Excel –

  1. Two-Variable Data Table in Excel
  2. One Variable Data Table in Excel
  3. Excel Data Visualization
  4. Database Function in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Data Table Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Data Table Excel Template

EDUCBA

डाउनलोड Data Table Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW