✨ OfficeTricks

How to Create Data Table Excel?

📅 Published: 2015-12-07 📁 Category: Excel Formula Tricks, Excel VBA Macro

How To Create Excel Data Table?

A Data Table generates different scenarios for your formula. You can use it to do What-if analysis. Lets first create a Excel Table that has at least 2 columns. This will help in understanding how Data tables work.
  1. First column filled with test values
  2. Second column header with calculation to be tested.
Or create a table as in Example section. Now We a table. Lets create a Data Table with this.
  • Select Table range.
  • Click Menu -> Data -> What-If Analysis.
  • Select 'Data Tables'.
  • Choose appropriate cells for Row or Column Input.
  • 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 Use Excel Data Table? - Example

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.

What-if Analysis & Data Tables

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 in Excel

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. You can use Row Input cell also. Read Also: How To Make Excel Sort By Click Column Header? With Data Tables, Excel can generate multiple scenarios very quickly and saves time in entering formula in each column and row of a Table.