Create multi variable data tables in excel

3-way-data-table

Create multi variable data tables in excel

Data tables are an exceptionally useful function in Excel which allows you to (quickly) run sensitivities on you financial models. The limitation however is that at best you can only run a 2 variable data table. This can be limiting as often a financial model has 3 or 4 key inputs which you want to test. Below is a method to create multi variable data tables in excel.


NOTE: this lesson shows you how to do it yourself. If you just want a template to incorporate into your financial models watch the video clip at the bottom for using teh template and go to the next lesson to download them.


Watch the video clip below to understand how to build a multi variable data table. The video tutorial uses a 3 by 3 example i.e. using 3 variables that have 3 options for each variable (high, low and realistic in this case).

To follow along you can download this spreadsheet

Three-way-data-table-excel-spreadsheet

This same logic can be used for more than 3 variables. The templates for these are available in the next lesson.

In the next lesson you are provided with the templates that will allow you to easily incorporate multi variable data tables in your own spreadsheets.

The options available at the moment are

  • 3 by 3
  • 3 by 4
  • 3 by 5
  • 4 by 3
  • 4 by 4
  • 4 by 5
  • 5 by 3
  • 5 by 4
  • 5 by 5

To see how they work watch the video clip below

Back to: Multi variable excel data table- Excel sensitivities on steroids > Multi Variable Data Table Instructions and Downloads