Engineers-Excel.com

Data Tables



A powerful, but under utilized feature of Excel is Data Tables. This feature is used in many of the Tools on this website to evaluate functions at multiple points.

Lets take an example: we have to evaluate a function y = x - 2 from x = 1 to x = 10, at intervals of 1. Quite straightforward: key in 1,2,3…10 into 10 rows, the function into the second row and first column and fill the series over the remaining rows.

y=x-2

Now, we change our function to y=x + 2. How do we update the cells ?

Usual way:

Change the function in the first row and copy it into the cells below. We repeat this process every time we change the function.

y=x+2

Using Data Tables:


Select the table, and on the menu bar, go to Data, then Tables and choose the first cell in the x column (A2, in our example) as the column input cell. Click OK, and we are done. Now, the cells will be re-evaluated whenever the function is changed. Try changing the function in cells B2, all the cells in the table will be evaluated to the same function.

Data Table1

DataTable2

In this example, we have used a 1-dimensional table, it is also possible to create a 2-dimensional table, where the function can have 2 variables. The variables have to be arranged in rows and columns.