## Basic of Two way Data Table

This is an extension of the post What-If analysis in #Excel.

If you do not know about Goal Seek or One-Way Data Table, you may read Start the new decade with Goal Seek in #Excel and What-If analysis in #Excel before moving on.

We talked about a situation that we’d to have a list of outcomes with different initiate savings (Variable), by using One way Data Table.  Since we have two variables (the other one is the Weekly increment%), we may actually see the outcomes of different scenarios in a cross table, like this:

With Two way Data Table under What-If analysis, this can be achieved easily… as long as you know the setup required. 😉

### The Setup

This initiate setup is discussed here.  What we needed is a formula (resides in B4) resulted from at least two variables (reside in B2 and B5).

Then we put the two variables of interest in a cross table (at the range H4:M14) like this:

Tip: We may have a shorter or longer lists of variables.  We can also change the hard-coded values later.

The most tricky part is the simple formula (=B4, i.e. the output) we put in upper left corner (H4) of the range.  That is the key!

Once we have this setup properly, the rest is quite straight-forward:

Select the range (H4:M14), then

1. Go to Data tab
2. What-If Analysis
3. Data Table

Input the Row and Column input cell accordingly, as follow:

Honestly, when I first encountered this (long time ago), I found it quite confusing… which one is Row? which one is Column?  I did that even by trial and error (or wild guessing… 😁)

Nevertheless when we look wider, it’s quite intuitive indeed.

• Row input cell is the cell reference where we put the variables on row;
• Column input cell is the cell reference where we put the variables on column.

Isn’t it clear?

After clicking OK…

As simple as this!

With this two-way Data Table, we can answer the question like:

“What if I want my Total Saving to be just over \$2000… What is the initiate saving and weekly increment % I need to achieve that?”

Data Table is another hidden gem waiting for you to discover in Excel.  It is powerful yet easy to set up!  I hope you will treasure it. 😉