How to make Data Table in Excel: One-variable data table and Two-variable data table

Before start learning how to make a Data Table, it is essential to learn the basic about Data Table and when we can use Data Table.

What if Analysis using Data Table in Excel

What is a Data Table?

A range of cells in where values can be changed in some of the cells and use of the formula to get different answers to a problem. Data tables are part of a suite of commands that are called what-if analysis tools. When you use data tables, you are doing what-if analysis. What-if analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet.

Types of Data Table:

There are two types of Data Table based on the number of variables and formulas that you want to test.

  • One-variable data table
  • Two-variable data table

When and Why?

Instead of creating a different scenario, you can create a data table to quickly try out different values for formulas. You can create a one-variable data table or a two-variable data table.

Think: you can use a data table to vary the interest rate and term length that are used in a loan to determine possible monthly payment amounts.


Case Study: Assume you own a book store and have 100 books in storage. You sell a certain % for the highest price of $50 and a certain % for the lower price of $20. If you sell 60% for the highest price, cell D10 below calculates a total profit of 60 * $50 + 40 * $20 = $3800.

Let’s create both One-variable data table and Two-Variable data table for this case study.

Read More: Learn Excel Tutorial Online – Beginner to Advanced Level Users

One-Variable Data Table in Excel

To create a One-variable data table, let’s execute the following steps:

One-Variable Data Table

Step 1: Create a table of Book sale same as like above image. Value of the cell D10 is showing the total profit. In column A from cell A13 to A17 are showing different percentages. Range B6:D8 is the table of Number of Book with Unit profit.

One Variable Data Table

Step 2: Select cell B12 and type =D10 (Refer to the total profit cell). Press Enter. Now Cell B12 is showing the value of profit cell $3800.

Select the range

Step 3: Select the range A12:B17.

Click on Data Tab

Step 4: Click on Data Tab.

Click on What-If Analysis from Forecast Group

Step 5: Click on What-If Analysis from Forecast Group.

Select Data Table from the list of What-If Analysis

Step 6: Select Data Table from the list of What-If Analysis.

Click in the Column input cell

Step 7: Then Data Table Dialog Box will be appeared.  Click in the Column input cell box (the percentages are in a column) and select cell C4. This is a one variable data table so we leave the Row input cell blank.

We have selected cell C4 because the percentages refer to cell C4 (% sold for the highest price). Together with the formula in cell B12, Excel now knows that it should replace cell C4 with 60% to calculate the total profit, replace cell C4 with 70% to calculate the total profit, etc.

Step 8: Click OK.

showing some new values

Now the range A12:B17 is showing some new values from B13 to B17.  if you sell 60% for the highest price, you obtain a total profit of $3800, if you sell 70% for the highest price, you obtain a total profit of $4100, etc.

This is how One Variable Data table works, Now lets learn how to use Two-Variable data table

Read More: Know Everything About Excel VBA


Two-Variable Data Table

To create a Two-variable data table, let’s go through the following steps:

How to calculate profit in Excel

Step 1: Delete values from B12 to B17. Select cell A12 and type =D10 (refer to the total profit cell).

Type the different unit profits

Step 2: Type the different unit profits (highest price) in row 12 from B12 to D12.

Select the range

Step 3: Select the range A12:D17.

click What-If Analysis and select Data Table from the list

Step 4: On the Data tab, click What-If Analysis and select Data Table from the list.

Click inside the Row input cell box

Step 5: Click inside the Row input cell box (the unit profits are in a row) and select cell D7. We have selected cell D7 because the unit profits refer to cell D7.

Click inside the Column input cell box

Step 6: Click inside the Column input cell box (the percentages are in a column) and select cell C4. We have selected cell C4 because the percentages refer to cell C4.

Together with the formula in cell A12, Excel now knows that it should replace cell D7 with $50 and cell C4 with 60% to calculate the total profit, replace cell D7 with $50 and cell C4 with 70% to calculate the total profit, etc.

Step 7: Click OK.

The formula bar indicates that the cells contain an array formula

If you sell 60% for the highest price, at a unit profit of $50, you obtain a total profit of $3800, if you sell 80% for the highest price, at a unit profit of $60, you obtain a total profit of $5200, etc. The formula bar indicates that the cells contain an array formula. Therefore, you cannot delete a single result. To delete the results, select the range B13:D17 and press Delete.

Now You have learned How to create Data Table Successfully..!!

Related Tutorials

Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Split Cells in Excel using Text to Column
June 7, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018
5 Powerful Tricks to Format cells in Excel
May 31, 2018
Insert a Picture into a Cell in Excel
May 25, 2018
What is ISFORMULA Function and FORMULATEXT Function
May 21, 2018
How to Use SUBSTITUTE Function
May 21, 2018
Excel Quartile Function in Excel
May 8, 2018
How to use the Excel PERCENTILE function
May 7, 2018
Insert or Type degree symbol in Excel with Autocorrect Feature
May 7, 2018
25% Discount
No prize
All Courses at $200
Almost!
10% Discount
Free Ebook
No Prize
No luck today
Almost!
50% Discount
No prize
80% Discount
Get a chance to boost your knowledge!
Use this coupon code for any course that you wish for. 
Our in-house rules:
  • You can choose any course & redeem coupon
  • If you find any difficulty, mail us on [email protected]
  • Wheel Spin will end soon
  • Coupon code can be applied within 2 days.