How To Create Excel Data Table?
A Data Table is used to generate different scenarios for your formula. Follow these steps to make a Data Table.
- Create a Table that has at least 2 columns. First column filled with test values and second column header with calculation to be tested.
- Select this table range, then click menu -> Data -> What-If Analysis. It will have options for Scenario Manager, Goal Seek & Data Table. Now, a Pop will ask for Row Input Cell and Column Input Cell.
- Choose appropriate cells for Row or Column Input. Then click ok.
- Excel will automatically apply the formula and generate results for other values in Table.
For users who are new to What-If Analysis, the above explanation is not good enough. Lets see with an Example Data Table Creation scenario.
Also Read: How to Convert Word to PDF?
How to Use Excel Data Table
Assume that you want to calculate percentage for marks scored by students. Below Table has the Marks scored out of 200 Marks, by students in a subject.
A B C D Total Marks 200 100% <- ‘B1/200 Jobs 100 Gates 150 Sachin 180
If a student gets 200 out of 200 then he scored 100% marks. Similarly to easily check the percentage of marks scores by other students, we are going to create a Data table.
- Select the table range B1:C4.
- Select Menu -> Data -> What-if Analysis -> Data Table.
- In Column Input Row choose cell B1.
- Click Enter.
Now, Excel will substitute the values remaining and generate the results for other students automatically. The table will look like the one below.
A B C D Total Marks 200 100% =(B1/200)*100 Jobs 100 50% =(100/200)*100 Gates 150 75% =(150/200)*100 Sachin 180 90% =(180/200)*100
Make a Data Table
What we saw in above examples is just with single Column Input. Data Tables can also be extended to generate scenarios for values in multiple columns also. We can make use of the Row Input cell also here.
With Data Tables, Excel can generate multiple scenarios very quickly and saves time in entering formula in each column and row of a Table.
How to Create Data Table Excel?