Tech Tips with Toby
PHPP is the software Passive House designers use to model the performance of buildings—before they are built. It’s a front-end on an Excel spreadsheet. Now, most people use Excel, at least a little bit. But Excel is a powerful beast of a software programme and there are tonnes of shortcuts and tools that make it faster and easier to get your work.
Here are my favourite tricks for using PHPP (version 9.6a on a PC). Scribble a couple of tips on a post-it note and stick it to your computer for a few days, until you get in the habit of using them and can remember the keystrokes.
- Goal Seek: Data menu > WhatifAnalysis > Goal Seek
Quickly calculates the input needed to produce a specified result. This is useful in multiple scenarios. As an example, if I required a floor U-Value of 0.5, I can use the Goal Seek function to enter that U-Value. I discover I would require 73mm of foam insulation to achieve that result.
- Watch window: Formulas menu > Watch Window
Adding specified cells to the watch window allows you to quickly see how changing inputs impact the building. For example, how different window glass impacts heating and/or overheating. I normally have all of the main items on the verification sheet in my watch window.
- Conditional Formatting: Home toolbar > Conditional Formatting > Colour Scales
Very useful on the shading sheet when you’re faced with a wall of numbers. Set the colour to change from white to red as the percentage value changes. Then you have an easy visual cue to see which windows have lots of shading.
- Protect data: Tools menu > Protection
Make sure sheets are protected. If you ever need to unprotect a sheet, re-protect it immediately after. This is a useful check against accidentally messing with the formulas.
- Hide worksheets
If you’re not using renewable energy, for example, hide the PV and SolarDHW sheets by right-clicking on the sheet’s tab and selecting Hide from the pop-up menu.The following shortcuts seem almost too trivial to mention, but once they become second nature you’ll be moving around the sheets faster and more easily.
Allows you to edit the active cell; shows the formula if one exists. (Bonus for PC users: it also lets you rename a file in File Explorer.)
- Paste Special – Control-Alt-V
Brings up this menu where you can choose what exactly you want to paste. Each underlined letter is the shortcut for that option. For example, typing V Enter will paste only the value of what is on your clipboard.
Get into a drop-down menu without using your mouse.
- Control-Shift-Right Arrow
Select an entire row of a table. This is perfect for selecting and copying a component to user-entered data. (Bonus: hold down the Control key and use the arrows to easily and quickly move around a sheet.)
- Control-PageUp and Control-PageDown
Moves between tabs: find your way around sheets without using the mouse.
— Toby Brooke is a Building Science Analyst at Sustainable Engineering Ltd