Data Tables

How to create Excel Data Tables

Overview

Unlike Models, Excel will only calculate one Case of your Model at a time, and you will need to use the Scenario and Sensitivity controls in your spreadsheet to change Case.

However, Excel Data Tables allow you to calculate results from different sets of inputs simultaneously, and display all results in one place. While this will not show you all outputs, it can be useful for comparing specific metrics over several Scenarios and Sensitivities, as shown in the example below.

A Data Table in Excel, generated by Models

Due to the limitations of Excel Data Tables, which can only respond to a single input cell, when you add a Data Table to your Layout, Scenarios and Sensitivities are combined into a list of Cases that contains all combinations.

We also add a Cases sheet to the Layout, which contains the list of Cases. This sheet can be moved and hidden as desired.

Creating a Data Table

To create a Data Table, simply go to the Tables tab of the Model Editor and add a new item. You will need to enter a name and choose which combinations of Cases to show.

If you add new Scenarios and Sensitivities to the Model, these will not automatically appear in the Data Table. You will need to edit the selected Cases if you want them to be shown.

As shown in the screenshot below, once you have created the Data Table, you can edit it in the same way you edit a Sheet in the sheet preview. In a Data Table, all sheet items are Metrics.

Use line formatting and group spacing to change the appearance of your Table.

A Data Table in Models

Adding a Data Table to the Layout

To add a Data Table to the Layout, you must perform the following steps:

1. Add the Data Table to a Dashboard. You must place the Data Table in the main Panel of the Dashboard, and the Data Table should be the only item in the Dashboard.
2. Add a Dashboard Sheet containing the Dashboard to the Layout