Excel Data Tables in any sheet

Excel data tables are amazingly powerful tools in scenario and sensitivity analysis. In this tutorial, we would like to demonstrate how to create an offsheet data table in any location in your workbook.

Input cell reference is not valid
If you have ever seen this error message “Input cell reference is not valid” when working with Excel Data Tables then you will enjoy this tutorial.

Screenshot 1: Error message "Input cell reference is not valid" from offsheet data table reference

Benefits of Excel data tables

Data Tables can store the results of many different scenarios in one table so that you can compare the results of a number of permutations to the original inputs together in one table. Data tables are created by activating the table dialogue box in Excel menu. This is done by selecting Data „³ Table, and enter the driver for the table in Row input or Column input cell.

Screenshot 2: Input for Data Table (row and column)

Usually the input cell that drives the table is located in the same worksheet with the data table. However, there might be some circumstances whereby you would like to create the data table in another worksheet in your workbook which is different from such input cells. This is what we call an offsheet data table. The accompanied workbook was created to illustrate how to create the Offsheet data table for scenarios.

Data table for scenario analysis

Let’s recap how a data table enables us to compare the results of scenarios together in one data table. Screenshot 3 shows a typical scenario manager for project finance models that allows us to switch between each of the identified case by clicking the switch in cell “E6”. The differences to the Base Case flow through the entire model by linking “Col E” in the table below into various sections of the model.

Screenshot 3: Typical scenario manager for analysis

For example, the price path (cell “E14”) and price flex (cell “E15”) from the scenario manager are linked to the inputs section. The screenshot below illustrates how the variables flow through if Case #2 is selected.

Screenshot 4: Linking in flex cells for sensitivity and scenario analysis

The last thing is to create a data table that allows us to compare the scenarios all in one table. The way the data table works is to sequentially insert each “input value” into the “input cell” that drives the calculations throughout the model. In this example, the driver is the switch in cell “E6” of the scenario manager and a data table as shown below can be created by entering the driver cell “E6” into the dialogue box in Screenshot 1.

Screenshot 5: Scenario data table

Create an offsheet data table

So far the table driver (the switch in cell “E6” in Screenshot 3) is located in the same worksheet with the created data table (Screenshot 5). How to create a data table in a different location, for example in another worksheet called “Data Table”?

Step 1: Create a driver cell in another worksheet

For example, let’s create an offsheet data table in “Data table” tab. A new cell “D6” will drive the table. This cell needs to be empty or otherwise will overwrite the switch cell “E6” in Screenshot 3.

Screenshot 6: Create a driver cell

Step 2: Insert new cells in scenario manager

Refer to Screenshot 7:

• Go back to “Scenario” tab and insert cell “E7” 
• Cell “E7” is custom formatted such that if no value is entered in cell “D6” of the “Data table” tab, it will be appeared as “na”. 
• Insert cell “E8” with formula as shown below. 
• Download the workbook and learn how the overwriting mechanics work if a value is entered in cell “D6” of the “Data table” tab. 
• Remember to adjust formula in “Col E” of the scenario manager to refer to the new cell “E8”. 

Screenshot 7: New cells in scenario manager

Step 3: Create offsheet data table

Now we are ready to create an offsheet data table. Select the entire table area and impute cell “D6” created in Step 1 in the row input cell in the dialogue box. Remember to press F9 to calculate table.

Screenshot 8: Complete data table with offsheet references