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**.

**Table of Contents**hide

**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.

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.

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**

➤ 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.

**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.

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**

➤ 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.

**Read More:**** How to Create a Data Table in Excel (7 Ways)**

**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.

➤ 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**.

__Result__

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

**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.

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.

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**

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.

**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.

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.

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**.

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**.

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.

➤ 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.

**Read More:** **How to Add Data Table in an Excel Chart (4 Quick Methods)**

**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.

➤ 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.

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

__Result__

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

*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.

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**.**

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**

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.

**Read More:** **Data Table Not Working in Excel (7 Issues & Solutions)**

**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.

**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.