How to analyze Scenario Modeling Results with Pivot Tables

Pivot tables allow you to group results to show the average impact of a particular scenario and are very useful in reviewing changes across energy models or scenarios. 

Follow along with this tutorial or watch the video below to learn how to use Pivot Tables.

1. Download your Scenario Modeling spreadsheet results and open them in your spreadsheet tool - such as Microsoft Excel. It should look like this:

It will have the Project Name, Project Id, and Scenario Id (or name) for every scenario and energy model combination. It also includes some descriptive information about each energy model. To the right of the project info, it includes the evaluation results for this Scenario Modeling project. Those results will fall into these categories - you can learn more about these here.

• Index or Score
• Buffer
• Compliance

2. To build a Pivot Table select all of the data. I do this by using a shortcut key. 

• Click cell A1
• While holding shift and control press the down arrow key twice and the right arrow key twice. Now all of the data is selected.

3. Now press the insert button and select PivotTable. 

• Insert Tabe
• Press PivotTable button on the far right

4. Press okay on the modal that pops up to add the Pivot Table to a new sheet.

5. Now you have the PivotTable editor visible.

• I find it most useful to group energy models by Scenario ID to see each Scenario’s impact on the evaluation metrics I analyzed. 
• I’ll show how to do that first

6. Go to the PivotTable Fields view and click Scenario Id. It will automatically add that to the rows column. Good. 

• Then select HERS Score: Score. It will automatically add that to the Values section. Good. 
• However, it sums the HERS score and that doesn’t make sense. We want an average HERS Score. 

7. Click on the arrow next to the “Sum of HERS Score” and then press Value Field Settings. You’ll see this screen. Select Average instead of Sum.

Now you’ll see the average HERS Score of all Scenarios. Repeat this for the HERS Score Change data field to see the average change in HERS score for each scenario. 

8. Your pivot table will look like this now: 

Now you should be able to explore other ways to group and summarize the data that best meets your needs.