How To Make A Data Table Excel

Ane Variable Data Table | Two Variable Data Tabular array

Instead of creating different scenarios, you can create a data table to rapidly try out different values for formulas. You lot can create a i variable data table or a two variable data table.

Assume you own a book store and take 100 books in storage. Y'all sell a sure % for the highest price of $50 and a certain % for the lower price of $20. If you sell 60% for the highest price, cell D10 below calculates a total profit of 60 * $fifty + forty * $20 = $3800.

One Variable Data Tabular array

To create a i variable data table, execute the following steps.

1. Select cell B12 and type =D10 (refer to the total profit cell).

2. Type the different percentages in column A.

3. Select the range A12:B17.

We are going to calculate the total turn a profit if yous sell sixty% for the highest price, seventy% for the highest toll, etc.

One Variable Data Table in Excel

4. On the Data tab, in the Forecast group, click What-If Assay.

Click What-If Analysis

five. Click Data Table.

Click Data Table

6. Click in the 'Column input prison cell' box (the percentages are in a column) and select cell C4.

Nosotros select cell C4 considering the percentages refer to cell C4 (% sold for the highest toll). Together with the formula in cell B12, Excel now knows that it should replace jail cell C4 with lx% to calculate the total profit, supplant prison cell C4 with 70% to calculate the full profit, etc.

Column Input Cell

Annotation: this is a 1 variable data table so we exit the Row input cell blank.

vii. Click OK.

Effect.

One Variable Data Table Result

Determination: if yous sell 60% for the highest toll, you obtain a total profit of $3800, if you sell lxx% for the highest price, you obtain a total profit of $4100, etc.

Note: the formula bar indicates that the cells contain an array formula. Therefore, you cannot delete a single result. To delete the results, select the range B13:B17 and printing Delete.

Two Variable Data Tabular array

To create a two variable information tabular array, execute the following steps.

1. Select cell A12 and blazon =D10 (refer to the total turn a profit cell).

2. Type the different unit profits (highest price) in row 12.

3. Type the different percentages in column A.

four. Select the range A12:D17.

We are going to calculate the total profit for the different combinations of 'unit of measurement profit (highest price)' and '% sold for the highest cost'.

Two Variable Data Table in Excel

5. On the Data tab, in the Forecast grouping, click What-If Analysis.

Click What-If Analysis

6. Click Data Table.

Click Data Table

7. Click in the 'Row input cell' box (the unit profits are in a row) and select cell D7.

8. Click in the 'Column input cell' box (the percentages are in a column) and select cell C4.

We select jail cell D7 because the unit profits refer to cell D7. We select prison cell C4 considering the percentages refer to cell C4. Together with the formula in cell A12, Excel now knows that it should supplant cell D7 with $50 and cell C4 with lx% to summate the total profit, supercede cell D7 with $50 and cell C4 with 70% to calculate the total profit, etc.

Row and Column Input Cell

9. Click OK.

Result.

Two Variable Data Table Result

Determination: if you lot sell threescore% for the highest cost, at a unit profit of $50, yous obtain a full profit of $3800, if you sell 80% for the highest cost, at a unit of measurement profit of $60, y'all obtain a total profit of $5200, etc.

Annotation: the formula bar indicates that the cells incorporate an array formula. Therefore, you cannot delete a single outcome. To delete the results, select the range B13:D17 and printing Delete.