Example of Excel Data Table (6 Criteria)

In Excel, we apply basic to complex formulas. Depending on the scenario the use of the formula’s changes. In case you have any formula dependent on multiple variables, and you want to see how the changes occur with the change of the inputs. You may change all variables individually which is time consuming so you can use the Data Table feature from the Excel ribbon. You can use the What-If_Analysis Data Table tool to observe all values outcomes at a glance. In this article, I’m going to show you an example(s) of Excel data table.

To make the explanation of the examples clearer, I’m going to use the information of any company, Capital, Growth Per Year, Total Revenue, Years, and  Revenue in Years.

Sample dataset of Excel Data Table Example


Download to Practice


Information of Excel Data Table

Before diving into the examples, I want to give some basic ideas of the data table. There are 2 types of data tables.

One-Variable Data Table

One variable data table allows testing a series of values for a single input cell; it can be either Row input cell or Column input cell and shows how those values change the result of a related formula.

It is best suited when you want to see how the eventual result changes when you change the input variables.

Two-Variable Data Table

It allows testing a series of values for a double input cell; You can use both  Row input cell and Column input cell and shows how changing two input values of the same formula changes the output

It is best suited when you want to see how the eventual result changes when you change two input variables.


Examples of Excel Data Table 

1. One Variable Data Table Example – Generating Total Revenue

I’m going to show you an example of Excel data table by using one variable data of a company. I want to observe the total revenue changes if I use different growth percentages.

As I’ve information of Capital and Growth Per Year of the company. Now, I want to know how the Total Revenue will change for the given percentages.

One Variable Data Table Example - Generating Total Revenue

First, by using the value of Capital and Growth Per Year I’ll determine the Total Revenue.

⏩ In cell C5, type the following formula.

=C3+C3*C4

Here, I multiplied the Capital with the Growth Per Year and then added the result with the Capital to get the Total Revenue.

Press ENTER, and you will get the Total Revenue in the year with 13% growth.

One Variable Data Table Example - Generating Total Revenue

Now, I want to perform a What-If-Analysis to see how the Total Revenue will change if I use the Growth Per Year ranging from 13% to 17% depending on the Capital amount of the company.

To apply one variable Data Table, place the formula of Total Revenue in the F4 cell.

➤ Select the cell range to apply Data Table 

I selected the cell range E4:F10

➤ Open Data tab >> from Forecast >> go to What-If-Analysis >> select Data Table

One Variable Data Table Example - Generating Total Revenue

➤ A dialog box will pop up.

From there select any input cell. As I want to see the changes in the column depending on growth per year.

➤ I selected C4 in Column input cell

Finally, click OK.

Result

Hence, you will get the Total Revenue for all the selected percentages at a glance.

One Variable Data Table Example - Generating Total Revenue

Read more: How to create a one variable data table in Excel


2. One Variable Data Table Example – Observing Revenue Change

In the above example, I have shown you how the Total Revenue changes depending on different growth percentages.

Now, I will show you how the Total Revenue will change If I use Capital ranging from 50,000 to 100,000 while keeping the Growth Per Year 13%.

To apply one variable Data Table, place the formula of Total Revenue in the F4 cell.

One Variable Data Table Example - Observing Revenue Change

Here, placed the formula in the F4 cell.

➤ Select the cell range to apply Data Table 

I selected the cell range E4:F10

➤ Open Data tab >> from Forecast >> go to What-If-Analysis >> select Data Table

One Variable Data Table Example - Observing Revenue Change

➤ A dialog box will pop up.

From there select any input cell. As I want to see the changes in the column depending on capital.

➤ I selected C3 in Column input cell

Finally, click OK.

Result

Therefore, you will get the Total Revenue for all the selected capitals at a glance.

One Variable Data Table Example - Observing Revenue Change


3. Example of Row Oriented Data Table

If you want to use one variable data table horizontally then you also can do it.

First, place the formula in the E5 cell.

Then, type the values in a row while keeping one empty row below.

Example of Row Oriented Data Table

➤ Select the cell range to apply Data Table 

I selected the cell range E4:I5

➤ Open Data tab >> from What-If-Analysis >> select Data Table

➤ A dialog box will pop up.

From there select any input cell. As I want to see the changes in a row depending on percentages of growth per year

➤ I selected C4 in Row input cell

Finally, click OK.

Example of Row Oriented Data Table

Result

Now, you will get the Total Revenue for all the selected percentages


Similar Readings:


4. Two-Variable Data Table Example

The using steps of two variable data tables are the same as one variable data table except that we enter two ranges of input values.

Here, I’ve modified the dataset a bit, given below.

Two-Variable Data Table Example

To calculate Revenue in 3 years,

⏩ In cell C5, type the following formula.

=(C3+C3*C4)*C5

Here, I multiplied the Capital with the Growth Per Year and added the result with the Capital then multiplied it by Years to get the Revenue in 3 years.

Press ENTER, and you will get the Revenue in 3 years with 13% growth.

Two-Variable Data Table Example

Now, I want to perform a What-If-Analysis to see how the Revenue will change in different Years with Growth Per Year ranging from 13% to 17% depending on the Capital amount of the company.

To apply a two-variable Data Table, place the formula of Total Revenue in the E4 cell.

➤ Select the cell range to apply Data Table 

I selected the cell range E4:I11

➤ Open Data tab >> from What-If-Analysis >> select Data Table

Two-Variable Data Table Example

A dialog box will pop up.
From there select two input cells.

➤ I selected C5 in Row input cell
Because, I kept the Years in a row F4:H4

➤ I selected C4 in Column input cell
As I kept Growth Rate in a column E5:E11

Finally, click OK.

Result

Now, you will get the Revenue for all the selected percentages and years.

Two-Variable Data Table Example

Read more: How to Create a Two Variable Data Table in Excel


5. Compare Multiple Results Using Data Table

If you want you can compare multiple results using Data Table.

Let me show you a comparison between Revenue and Interest using Data Table. I’m going to use the information given in the dataset below.

Example of Comparing Multiple Results Using Data Table

First, To calculate the Total Revenue.

⏩ In cell C5, type the following formula.

=C3+C3*C4

Here, I multiplied the Capital with the Growth Per Year and then added the result with the Capital to get the Total Revenue.

Press ENTER, and you will get the Total Revenue in the year with 13% growth.

Example of Comparing Multiple Results Using Data Table

Now, to calculate the Interest,

⏩ In cell C5, type the following formula.

=C5-C3

Here, I subtracted the Capital from the Total Revenue to get the Interest.

Press ENTER you will get the Interest.

Example of Comparing Multiple Results Using Data Table

Now, I will compare the Total Revenue and Interest using the Data Table while changing the Growth Per Year ranging from 13% to 17% while the Capital amount is $55,00.

Example of Comparing Multiple Results Using Data Table

To apply one variable Data Table, place the formula of Total Revenue in the F4 cell.

Again, place the formula of Interest in the G4 cell.

Example of Comparing Multiple Results Using Data Table

➤ Select the cell range to apply Data Table 

I selected the cell range E4:G10

➤ Open Data tab >> from What-If-Analysis >> select Data Table

➤ A dialog box will pop up.

From there select any input cell. As I want to see the changes in the column depending on growth per year.

➤ I selected C4 in Column input cell

Finally, click OK.

Result

Hence, you will get the Total Revenue and Interest for all the selected percentages.

Example of Comparing Multiple Results Using Data Table


6. Example of Data Table Modification 

You can modify a data table depending on your needs. In this section, I’m going to describe the table modification with examples.

6.1. Edit Data Table

If you want, you can edit the Data Table.

Here, I’ve taken a dataset where the Data Table is already applied to show you an example of editing an Excel data table.

Example of Editing Data Table

➤ First, select the data table range from where you want to replace or edit data.

I selected the range F4:F10

Now, remove the data table formula from any cell.

Example of Editing Data Table

Insert the value of your choice and press CTRL + ENTER.

Result

Now, the inserted same value will be in all the selected cells.

Example of Editing Data Table

As the Data Table formula is gone, you can edit any cell individually.


6.2. Delete Data Table

Naturally, you can’t Delete any cell from the Data Table.

Let me show you how you an example of deleting an Excel Data Table. To perform the task, I’m going to use the dataset given below.

Delete Data Table

If you try to delete any cell from the data table then it will show you a warning message which is Can’t change part of a data table.

➤ To delete the data table, select the entire range of the data table.

I selected the cell range E3:G10

Now, press DELETE from the keyboard.

Delete Data Table

Here, the entire data is deleted.

You also can use the menu context to delete the data table.

➤ Select the entire range of the data table.

I selected the cell range E3:G10

Delete Data Table

Now, right click on the mouse.

➤ From the context menu select Delete

➤ Now, a dialog box will appear then select any Delete option of your choice and click OK.

Here the Data Table is deleted.

Data Table is deleted


Things To Remember

In case in your data table, you have multiple variable values and formulas which may slow down your Excel, then you can disable automatic recalculations in that and all other data tables and will speed up recalculations of the entire workbook.

Open Formulas tab >> from Calculation Options >> select Automatic Except Data Tables

🔺 If Data Table is applied then you can’t undo the action.

🔺 Once What-If-Analysis is performed, and the values are calculated then it is impossible to change or modify any cell from the set of values.


Practice Section

I’ve provided practice sheets in the workbook to practice these explained examples.

Practice Examples of data Table


Conclusion

In this article, I have shown 6 examples of the Excel data table example. Then, I also tried to mention things you need to remember while using the data table. Lastly, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.


Further Readings

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo