MVP #65: The Amazing Tool Called Data Table In Excel

In Excel, there is an amazing tool called Data Table. It lets you sort of simulate a lot of predefined conditions.

For illustration, I'll create an example we can all relate with. It's the revenue projections of a small vibrant company.

There is a formula in the Revenue field that depends on the Product Price and Marketing Cost.

The next thing, we will do is help the management simulate what the Revenue will be for different combinations of Product Price and Marketing Cost.

This is the sort of task Data Table is great at.

All we have to do is link the cell at the junction of the simulated Product Price and simulated Marketing Cost to the calculated Revenue cell. See the screenshot below for what I mean.

Now we are ready to use Data Table.

Select the entire simulation table.

Then go to Data menu, What-If Analysis and Data Table.

In the small dialog box that comes up, set the Row Input Cell to the Marketing Cost value in the analysis table. And set the Column Input Cell to the Product Price in the analysis table.

And you get what the Revenue will be for the different combinations of Product Price and Marketing Cost.

It's now left for the management of Vibrant Nigeria Limited to choose which combination of Product Price and Marketing Cost will get them the best Profit after factoring the Cost of Sales.

You can register for our upcoming intensive Business Data Analysis and Microsoft Excel training here.