EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
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.

Start Your Free Excel Course

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

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
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests
 137+ Hours of HD Videos
36 Courses
13 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.9
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

Download Data Table Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

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

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Data Table Excel Template

EDUCBA

डाउनलोड Data Table Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more