WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Data Table in Excel

Data Table in Excel

What is a Data Table in Excel?

Data tables in excel are used to compare variables and their impacts on the result and overall data, data table is a type of what-if analysis tool in excel and is present in the data tab in the what-if analysis, this tool asks for a row input and a column input table to make a data table and the impact is calculated by one variable or two-variable data table.

Types of Data Table in Excel

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

1) One-Variable Data Table in Excel

The basic requirement for the data table is to create a base or testing model. You need to instruct your data table which formulas from your data model you want to test. The one-variable data table is best suited when you want to see how the eventual result changes when you change the input variables.

Example

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

Mr. Murali running a factory named ABC Pvt Ltd. In addition, he is estimating the revenue for the year 2019. The below table shows the revenue for the year 2018 and estimated revenue at different increment levels.

One-Variable Data Table - Example
The above table shows revenue for the year 2018 is 15 lakh USD, and expecting a minimum growth of 12% for the next year. Now, Murali wants a data table, which shows the revenue growth table at different increment rates. He wants the scenario table in the below format.

Apply the Data Table technique to get the desired results.

One-Variable Data Table - Example 1

Step 1: Create this data table format in an excel file. The estimated revenue for 2019 is shown in the cell B5.

One-Variable Data Table - Example 1-1

Step 2: Select the cell D8 and give a link to the cell B5 (estimated revenue cell). Now the cell D8 showing the estimated revenue for 2019.

One-Variable Data Table - Example 1-2

Step 3: Select the range from C8 to D19.

One-Variable Data Table - Example 1-3

Step 4: Click on Data Tab > What-if-Analysis > Data Table

One-Variable Data Table - Example 1-4

Step 5: Data Table dialogue box will come up. In the Column Input Cell, select the cell B3 (which contains the minimum growth rate percentage).

Since it is a one-variable data table, leave out Row Input Cell. The reason why we have selected the cell B3 because, based on the different growth rates, we are going to create the scenarios. Now, the data table understands at 12% the estimated revenue is 15 lakh USD. Similarly, it will create scenarios for 12.5%, 13.5%, 14.5% and so on.

One-Variable Data Table - Example 1-5

Step 6: Click on ok to create different scenarios.

One-Variable Data Table - Example 1-6

Now the range D9:D19 showing some new values. From the table, it is very clear that @12.5% growth rate estimated revenue will be 16.875 lakh USD, and @14.5% estimated revenue would be 17.175 lakh USD and so on.

This is how one variable data table example works. You can show this in a chart as well.

Data Table - Chart

2) Two-Variable Data Table in Excel

We can use a two-variable data table to analyze scenarios if two variables change at a time. For this, we need two ranges of possible input values for the same formula. That means here it will affect both Row & Column.

Example

Now go ahead and examine this two-variable data table example.

Mr. Murali was thinking of revenue growth at different rates. Similarly, he is planning to give discounts to his customers to increase sales opportunities.

The below table shows Murlali’s plan to increase the growth in the next year. He wants to estimate the revenue at different growth levels with different discount rates.

Two-variable data table 1

Two-variable data table 1-1

Step 1: Create an above Data table in excel.

Two-variable data table example 1

Step 2: In the cell, B6 put the below formula to calculate eventual revenue after the discount.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

=B2 + (B2*B3)-(B2*B4)

Two-variable data table example 1-1

First, it will add the growth rate from the previous year and deduct the discount rate.

Cell D9 contains the reference to the cell B6.

example 1-2

Now, the above table shows the possible growth rate from D10 to D18 (Column Value) and a possible discount rate from E9 to J9 (Row Value).

Step 3: Select the range D9:J18.

example 1-3

Step 4: Click on Data Tab > What-if-Analysis > Data Table

example 1-4

Step 5: Data Table dialogue box will come up. In the Column Input Cell, select the cell B3 (which contains the minimum growth rate percentage), and in the Row Input Cell, select the cell B4.

Together with the formula in cell D9 (refers to cell B6), Excel knows that it should replace cell B4 with 2.5% (cell E9), and cell B3 with 12.5% (cell D10), and so on for others as well.

example 1-5

Step 6: Click on the ok option.

Example 1-5

Now, if Murali is aiming at a 13.5% growth with a 2.5% discount rate, then the revenue will be 16.65 lakh USD. If Murali is aiming at achieving 17 lakh USD, next year’s maximum discount he can give is 3%, and it can give him a revenue of 17.025 lakh USD.

This is how helpful is the Data Table to create different scenario models to assist the decision-making process.

Important Notes:

  • A Data Table will show you how by changing the certain values in your function, you can change the result of the formula.
  • It stores the results of many variable scenarios in one table so that you can finalize the best scenario for your business or project. The results are written in the table format.
  • It is an array formula, which allows multiple calculations in a single location.
  • Once you have calculated the values through the data table, you cannot undo the action (Ctrl + Z). However, you can manually delete all the values from the table.
  • You are not allowed to modify a single cell in the data model. Because it is an array, you have to delete everything.
  • It would be so confusing to select the Row input cell and the column input cell. You need to select the cells properly to get accurate results.
  • The data table in excel does not need to be refreshed all the time, unlike the pivot table.

Recommended Articles

This has been a guide to Data Table in Excel. Here we discuss how to create Data Table (one variable and two-variable data tables along with practical examples. You may learn more about excel from the following articles –

  • Two-Variable Data Table in Excel
  • VBA Refresh Pivot Table
  • Merge Tables Excel
  • Data Validation in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Login

Forgot Password?

WallStreetMojo

Download DATA Table Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More