Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

How to Create Data Tables in Excel

We can create one variable data table or two variable data table with the What-If Analysis feature. This tutorial will walk all levels of excel users on how to create a one variable data table and a two variable data table.

Figure 1 – How to make data table in Excel 

What is a One Variable and Two Variable Data Table? 

A one variable data table is a data table that has only one column or row of input values and multiple results while a two variable data table has two input values with only a single result. 

How to Create a One-Variable Data Table

We will use the table in figure 2 to create a one-variable data table. We want to see how the cost per unit of item we produce will change. When the cost per unit of item increases, we might not be able to favorably compete with our competitors and when it reduces, we can compete. The table helps us to make business decisions.

Figure 2 – How to Create a One-Variable Data Table

  • The formula in Cell B11 is =B8/B10
  • We will input the name of the cell containing the formula in Cell E5 and press enter

Figure 3 – One variable data table in Excel

  • We will select Cell D5 to Cell E10

Figure 4 – How to create a one variable data table

 

  • We will click on the Data tab and click on the What-If Analysis in the Forecast group

 

Figure 5 – Click What-If Analysis

  • We will select Data Table from the drop-down

Figure 6 – Data table Excel dialog box

  • We have only one variable that will change and that is the Regular Production Cost
  • We will insert the Cell reference (B10) for the Column input Cell

Figure 7 – Column Input Cell

 

  • We will press OK

 

Figure 8 – One variable data table

The values show how the cost of producing a unit of metal rod will change when will produce only 800, 900, 950, 700, and 750 units respectively.

How to Create a Two-Variable Data Table

We will use the table in figure 9 to create a two-variable data table. We want to see how the cost per unit of item we produce will change taking into consideration the Regular Production cost and the markup. The markup tells us how much we can add to the production cost so we can set a favorable selling price.

  • The formula in Cell B11 is =B8/B10
  • The formula in Cell B13 is =B11+(B11*B12)
  • We will input the name of the cell containing the formula in Cell E5 and press enter

Figure 9 – How to Create a two-variable data

  • We will select Cell D5 to Cell F10

Figure 10 – How to create a two variable data table

 

  • We will click on the Data tab and click on the What-If Analysis in the Forecast group

 

Figure 11 – Click What-If Analysis

  • We will select Data Table from the drop-down

Figure 12 – Data table Excel dialog box

  • We have only two variables that will change and that is the Regular Production Cost and the markup
  • We will insert the Cell reference (B10) for the Column input Cell and Cell reference (B12) for the Row input cell

Figure 13 – Data table Excel dialog box 

 

  • We will press OK

 

Figure 14 – Two variable data table

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc