Using Excel What If Add In Data Table Function

As a Microsoft Office Specialist (MOS), and an excel expert, also experienced  in designing thousands of cost model, I have been called in many of times to evaluate other spreadsheets to determine if any improvements or efficiencies be made for improvements.

One area that I have crossed many of times is the use of creating a table with either a one or two variables to calculate. I have seen cases where the user has created hided fields to bounce the calculation. Also in some cases, I have seen that the user has manually calculated the amount and has entered the values in. (NOT RECOMMENDED)the excel user who is familiar with creating complex formulas populate the table out.

The below attachment is an example of creating this table using complex formulas. It took me an excel expert over 5 minutes to complete. I had to make sure that I used the absolute cell reference to get the correct results. Can you image how long it would take an average user to complete. Also just think if your your entire workbook was riddled with these formulas. Formulas actually take up more space and slow down the processing speed.

Now I am going to introduce you to a much better way to do this. First you must install the "What If" add-in. Most users nearly 95% are not aware of this feature. Only will learn it in an advanced excel class or book.

I will give you a brief 101 class about this topic.

-A data table organizes the results of several what-if analyses within a single table.

-A two-variable data table uses two input cells, but unlike a one variable data table, only the value of single result cell can be displayed .

Without explaining this in detail I am attaching a slide below. 

Again not getting into too much technical detail but the above is a finished version of two piece variable table. Each cell is populated when you use the What-If data table function.

The topic is really advanced and the user needs to rearch the topic before attempting to attempt this. A good resource is Wayne Winston books about using Excel to solve business problems.

If anyone wants to learn more about this topic I would be happy to share, Please reply in comment section.