How to do a data table in excel. Together with the formula in cell d9 refers to cell b6 excel knows that it should replace cell b4 with 25. In the cell f2 which is one row above and diagonal to the column which was prepared by you in the previous. On the data tab in the forecast.
Now the sensitivity analysis table is created as below screenshot shown. Choose data table from the what if analysis option in the data tools group figure e. We are going to calculate the total profit if you sell 60 for the highest price 70 for.
Heres what we do. Add one more column to the data table range column f and link f2 to b10 interest formula. Variable values in d3d8 and e2 linked to b8 balance.
In the popping out data table dialog box please 1 in the row input cell box specify the cell with chairs sales volume b3 in my case 2 in the column input cell box specify the cell with chair price b4 in my case and then 3 click the ok button. Select the data table range. Select the range a12b17.
In cell b10 compute the interest with this formula. Now youre ready to generate the data table as follows. Select the range d9j18.
Click on data tab what if analysis data table step 5. Data table dialogue box will come upin the column input cell select the cell b3 which contains the minimum growth rate percentage and in the row input cell select the cell b4. Prepare a column which consists of different values for the interest rateswe have entered different values.
In excel in the chart tools group there is a function to add the data table to the chart. Select the cell range b7c17. In this case thats a11d29 dont include the labels in row 10.
Make sure youre working on the home tab on excels ribbon and click on format as table and choose a style theme to convert your data to a table. Copy the original formula entered in cell b5 into cell c7 by typing equal to and then clicking cell b5. Heres how to quickly create a table in excel.
Click the data tab. One variable data table 1. Data table in excel example 1 one variable data table step 1.
The range of the data table. Select cell b12 and type d10 refer to the total profit cell. Use the keyboard shortcut ctrl t to convert your data to a table.