Data Table in Excel (Create, Edit, and Delete)

In this article, we’ll learn how to create, edit, and delete a data table in Excel.

Creating a data table in Excel is an important feature of Excel because it will make any type of calculation easier and faster. However, working with data tables in Excel may present some challenges and issues. Common issues in Excel data tables are large datasets, formula errors, data consistency, and performance optimization.

Furthermore, you can change any type of calculation easily by changing input data.

Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

What Is a Data Table in Excel?

A Data Table in Excel is a powerful feature that allows you to perform what-if analysis by testing different combinations of input values and observing their impact on the output of a formula. Data tables are particularly useful when evaluating different input variables and their impact on the results of a calculation that relies on outcomes.

How to Create a Data Table in Excel

Here, we will learn how to create One-Variable and Two-Variables Data tables in Excel.

1. Make One Variable Data Table

A one-variable data table in Excel demonstrates the impact of multiple values on related formulas. Here, you can make a data table with one variable using the Data Table feature.

Moreover, you can create a column or row-oriented both types of data tables.

One Variable Column-Oriented Data Table

We’ll apply the following formula in cell C8 to calculate the total balance.

Here C4 is the Investment, C5 is the number of years, and C6 is the Rate of Interest.

• Hence, link up the first row of Total Balance with the result in cell C8.
• Select the data range E5:F10 >> Go to the Data tab >> Choose Forecast group >> Select the Data Table feature from the What-If Analysis drop-down list.

• After that, a Data Table dialog box will appear >> Enter the input C4 with absolute reference in the Column input cell as it is a column-oriented data table >> Press OK.

• Finally, you will get all the Total Balances for different Investments.

One Variable Row-Oriented Data Table

Similarly, you can make one variable row-oriented Data Table.

• You will have to link up the first column of Total Balance with the result in cell C8.
• Select the data range C10:H11 and go to the Data tab >> Forecast group >> What-If Analysis >> Data Table.
• In the Data Table dialog box, type C4 with absolute reference in the Row input cell as it is a row-oriented data table >> Hit OK.

Finally, you will be able to get the various results in the Total Balance row.

2. Create Two Variable Data Table

Now, we will create a data table for two variables like Investment and Rate of Interest (we have changed the values in the Rate of Interest column for this method), and for the changes of these two variables, we will see the changes of the Total value.

Linking Total Balance with cell C11.

• From the What-If Analysis feature, pops up a dialog box named Data Table. After that, do like the below screenshot.

• Here is the final output of the two variables data table in Excel.

How to Edit a Data Table Results in Excel

You can change or edit calculated values in the Data Table. But you can replace the value of the calculation cells in the Data Table. Let’s follow the instructions below to learn!

• Select data table cell ranges >> go to Formula Bar >> Type your desired result in the Formula Bar >> Press Ctrl + Enter.

• Hence, you can insert your desired value into all of the selected cells.

How to Delete a Data Table in Excel

You will not be able to delete any specific values from the cells that contain the result of the Data Table. But you can delete all the entities of the Data Table.

While deleting any specific value from the Data Table, a warning message pops up showing “Can’t change part of a data table”

• To delete the data table, select the entire result of the data table and press the Delete key.

Things to Remember

• Any single cell of the array can not be deleted or edited as the table is created with an array formula.
• Be aware of referencing the Row input cell or Column input cell according to the pattern of your data table.
• The input cells have to be on the same sheet of the data table.
• 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.

Conclusion

A data table in Excel provides a versatile platform for creating, editing, and analyzing data. With its range of functionalities and flexibility, data tables empower users to handle diverse datasets and derive valuable insights from their information.

Frequently Asked Questions

1. Can I have multiple input cells in a Data Table?

Answer: Yes, you can create Data Tables with multiple input cells. This is known as a two-variable Data Table, where you can explore how two different input variables simultaneously affect the formula’s output.

2. Can I modify the input values in a Data Table?

Answer: Yes, you can easily modify the input values in a Data Table. Simply change the values in the input cells used for the Data Table, and Excel will automatically recalculate and update the corresponding results.

3. Is it possible to create a Data Table for non-numeric values?

Answer: Yes, Data Tables can be used with non-numeric values as well. Whether you are analyzing text, dates, or other non-numeric data, Excel can handle it. Just ensure that your formula is compatible with the data type you are working with.

4. Can I save a Data Table as a separate entity within Excel?

Answer: No, Data Tables are not saved as separate entities in Excel. They are dynamic calculations based on the underlying data and formulas. However, you can save the entire workbook that contains the Data Table for future use, including the associated table and its settings.

Data Table in Excel: Knowledge Hub

<< Go Back to What-If Analysis in Excel | Learn Excel