How to Use Data Table in Excel

To easily test out various formula values, you can make a data table rather than distinct scenarios. A one variable data table or a two variable data table can be made. Let's say you run a bookshop and have 100 books stored away. You sell a specific percentage for the greatest price, which is $50, and a specific percentage for the lowest price, which is $20. Cell D10 below estimates a total profit of 60 * $50 + 40 * $20 = $3800 if you sell 60% for the highest price.

How to create an Excel data table with one variable?

Follow these instructions to construct a data table with only one variable.

Choose cell B12, then enter =D10 (refer to the total profit cell).Fill out column A with the different percentages.Choose the A12–B17 range.We'll figure out the overall profit if you sell 60% at the highest price, 70% at the highest price, etc.

Click What-If Analysis under the Forecast category on the Data tab.

Click on data table.

The percentages are in a column; click the Column input cell box and choose cell C4.Because the percentages are based on cell C4, we choose cell C4 ( percent sold for the highest price). With the help of the formula in cell B12, Excel now understands that it must substitute 60 percent for cell C4 to compute the whole profit, 70 percent for cell C4 to calculate the total profit, etc.

Because there is only one variable in this data table, the Row input cell is left empty.Click on ok.Result:

Conclusion: 

If you sell 60% of your inventory at the highest price, you will make a total profit of $3800. If you sell 70% of your inventory at the highest price, you will make a total profit of $4100.It should be noted that the formula bar shows that the cells have an array formula. As a result, you are unable to delete a single result. Select the range B13:B17 and press Delete to remove the results.

How to create an Excel data table with two variables?

Follow these instructions to construct a data table with two variables.Choose cell A12, then enter =D10 (refer to the total profit cell).In row 12, enter the various unit profits (highest price).Fill out column A with the different percentages.Choose the A12–D17 range.For each combination of unit profit (highest price) and percent sold for the highest price, we will compute the overall profit.

Click What-If Analysis under the Forecast category on the Data tab.

Click on data table.

The unit profits are in a row; click in the Row input cell box and choose cell D7.The percentages are in a column; click the Column input cell box and choose cell C4.The unit earnings refer to cell D7, thus we choose cell D7. Since the percentages are based on cell C4, we choose cell C4. With the help of the formula in cell A12, Excel now understands that it must substitute $50 for cell D7 and 60% for cell C4 in order to calculate the entire profit, $50 for cell D7 and 70% for the whole profit, etc.

Click on ok.Result:

Note: This above written article is an attempt to show you how to use data table in excel online, 2016 and 2019, in both windows and mac.You just need to have a little understanding of how and which way things work and you are good to go with your work. With having this basic knowledge or information of how to use it, you can also access and use different other options on excel or spreadsheet. Also, it is very similar to Word or Document. So, in a way, if you learn one thing, like Excel, you can automatically learn how to use Word as well because both of them are very similar in so many ways. If you want to know more about WPS Office, you can download WPS Office to access, Word, Excel, PowerPoint for free.  

3 Key points about using data tables in Excel

1. For a data table to be created successfully, the input cell must be on the same sheet as the data table. Double-check that the cell references in the formula are accurate and aligned with the input values.

2. The resulting cells cannot be edited individually because the data table results are calculated with an array formula. You can only edit or delete the entire array of cells, as explained below.

3. It need not be refreshed, unlike a pivot table. A change in the source dataset's values or formula causes the Excel data table to update automatically.

FAQs about Excel data table

1. Why your Excel Data Table Does Not Work and How You Can Fix?

The Excel data table may not work due to incorrect references, formulas, or input values. To fix them, you should review and verify references and formulas and ensure accurate input values are entered.

2. How to Link the Excel data table to input cells on multiple or different worksheets?

To link the Excel table to input cells on multiple worksheets, select the desired input cells on multiple or different worksheets and reference them in the data table using the worksheet name followed by '!' and the cell reference.

Summary

Excel data table is a powerful tool that helps you explore different scenarios by displaying the results of a formula or function for various input values. Depending on your requirements, you can create a data table using a single, two, and three variables. It can be used for many purposes, including financial modelling, scenario planning, sensitivity analysis, and data optimisation. If you are unfamiliar with MS Excel, you can also create it using WPS Office.

WPS Office is a free alternative to MS Office offering similar functionality to Excel and allows you to perform data analysis, including creating data tables. Download WPS Office on your PC and enjoy seamless computations of this lightweight software.